While browsing Books Online I found sys.dm_exec_query_optimizer_info, which returns statistics about the query optimizer to help you tune a workload.
SQL SERVER – 2005 – Find Index Fragmentation Details – Slow Index Performance
A slow index turned out to be heavily fragmented. My quick script uses sys.dm_db_index_physical_stats to show index fragmentation details for every index.
SQL SERVER – 2005 – Find Highest / Most Used Stored Procedure
Which stored procedure runs most often? My small SQL Server 2005 script finds the most used stored procedure, with execution count, worker time and calls per second.
SQL SERVER – 2005 – Retrieve Any User Defined Object Details Using sys objects Database
The sys.objects catalog view has a row for every schema-scoped object, so you can retrieve any user defined object details, like foreign keys, by object type.
SQL SERVER – 2005 – Retrieve Processes Using Specified Database
Jim Sz shared a quick script to retrieve processes that use a specific database from sys.sysprocesses, with spid, status, host name and login.
SQL SERVER – Get a Row Per File of a Database as Stored in the Master Database
Each database has at least two files. This quick script on sys.master_files returns a row per file of a database, just as it is stored in master.
SQL SERVER – Reclaim Space After Dropping Variable – Length Columns Using DBCC CLEANTABLE
Dropping a variable length column does not shrink the table. DBCC CLEANTABLE reclaims that space without an index rebuild. Example on AdventureWorks.