Looking for Good DMV/Database Admin Queries!

I like to collect useful database administration queries that leverage the SQL Server 2005 and 2008 DMVs.  Heck, I'm still interested in SQL Server 2000 queries too.  I thought I'd make my search public so that a) you can share your favorite queries here or great reference queries written by others and publicly posted on the Internet, and b) everyone can benefit from this collaborative approach to DMV queries.  If you're aware of collections of scripts, for example like those available from the SQLCAT team, please post the location of the collections or libraries.

The intent is to provide ourselves with a set of scripts they can use to perform tasks that would otherwise require them to hit BOL heavily to research what DMVs or system catalog views they need to access to get what they want. These types of activities are not easily performed from within the query tool user interface.

I’m requesting everyone to post or reference your favorite queries in any of the following categories below. The queries could be in your notes, from web sites like MSDN or TechNet or SQLServerPedia.com, from our great SQL Server bloggers and MVPs.  (Be sure to give credit to the originator when you post it here.)  Speaking of favorite scripts, you might want to check out SQLServerPedia.com, if you haven't already done so.  The wiki is getting quite large and there's lots of new information popping up daily.  Looking for more good query samples?  If you didn't already know it, be sure to check the Samples folder in your SQL Server installation.  Microsoft has a lot of examples in their SQL Server 2005 Script Library.

When posting, please:

·         Describe briefly what the snippet does

·         Describe if this is a 2005/2008 query or just 2000

·         Provide the SQL / Script and indicate if there are any parameters or if the SQL can be run without modification

Here are some categories I'm looking for, but if you have something not addressed here, please post it:

·         Object Sizes – a list of objects in a database with their sizes

·         Missing Indexes

·         Index Utilization – all indexes

·         Index Utilization - on a specific table

·         Index Fragmentation – all indexes

·         Index Fragmentation – on a specific table

·         Index Defrag options – various with defrag, rebuild, online, offline, heap, etc.

·         SQL Performance – leveraging the 2005+ DMVs for worst performers, active statements

·         CPU and Optimization

·         Buffer Cache

·         Wait Stats

·         Deadlocks

·         Plan Guide Queries

·         SQL Trace

·         Backup History – or other backup related queries

Thanks in advance!

-Kevin