To go with them are the matching blank results spreadsheets for SQL Server 2005 and SQL Server 2008. The spreadsheets have their tabs labeled in order to match the queries. That way, it is easy to run each query, click the top left box in the results grid to select all columns and rows, right-click and select “Copy with Headers” to copy all of the results to the Windows clipboard. Then you can paste the results to the appropriate tab in the spreadsheet.

If you are still using the SQL Server 2005 version of SQL Server Management Studio (SSMS), you will need to make sure you change a setting under “Tools”, “Options”, “Query Results”, “SQL Server”, “Results to Grid”, to enable “Include column headers when copying or saving the results”, like you see below, before you run the queries.

Otherwise, you will lose the column headers, which makes the results much harder to interpret.

As always, you will need to have VIEW SERVER STATE permission to run most of the DMV queries.

I advise you to run each query one at a time, after reading the comments. The last query in the script (to check index fragmentation) could take quite a while to run, depending on your database size and your hardware infrastructure.

— this code returns the correct statistics (without the join to sys.indexes)
—————————————————————————–
select
OBJECT_SCHEMA_NAME(o.object_id) as SchemaOwner,
o.name as TableOwner,
s.name as StatisticsName
from sys.objects o with (nolock)
inner join sys.stats s with (nolock)
on o.object_id = s.object_id
where 1 = 1
and o.is_ms_shipped = 0
and OBJECT_SCHEMA_NAME(s.object_id) not in (‘sys’)
and OBJECT_NAME(s.object_id) not in (‘sysdiagrams’)
and o.type = ‘U’
and o.name = ‘ErrorLog’

Hi Glenn,
I’ve just discovered this wonderful post. I’ve not tried them, yet, but I wanted to say thank you for taking the time to share such a great collection of nicely documented and highly useful code and for maintaining it. You’re a truly generous and thoughtful person. Thanks, Glenn!