Since it is almost January 2011, I thought I would post the January 2011 versions of the SQL Server 2005 Diagnostic Information Queries and the SQL Server 2008 Diagnostic Information Queries.
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.
Glenn Berry's SQL Server Performance 
Pingback: The Rambling DBA: Jonathan Kehayias : An XEvent a Day (30 of 31) – Tracking Session and Statement Level Waits
Hi, Glenn
I noticed an issue in your coding of your recent ‘Updated SQL 2005 and 2008 Diagnostic Queries’ regarding the statistics.
— drop and create statistics (as test)
—————————————
use AdventureWorks
if exists (select * from sys.stats where name = N’MYSTATS’ and object_id = object_id(N'[dbo].[ErrorLog]’))
drop statistics [dbo].[ErrorLog].[MYSTATS]
create STATISTICS [MYSTATS] on [dbo].[ErrorLog]([UserName], [ErrorNumber], [ErrorState])
— this is your code
——————–
— When were Statistics last updated on all indexes?
SELECT
OBJECT_SCHEMA_NAME(o.object_id) as SchemaOwner,
o.name, i.name AS [Index Name],
STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
s.auto_created, s.no_recompute, s.user_created, st.row_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = ‘U’
and o.name = ‘ErrorLog’
–ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);
— 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’
Kind regards.
Ron
Hi Glenn
This script is gives you more possibilities:
use AdventureWorks
select @@SERVERNAME as ServerName,
DB_NAME(DB_ID()) as DatabaseName,
OBJECT_SCHEMA_NAME(t.OBJECT_ID) as SchemaOwner,
t.name as TableName,
i.name as IndexName,
st.name as StatisticsName,
STATS_DATE(i.object_id, i.index_id) AS LastUpdated,
ps.row_count as RecordCount,
st.auto_created as IsAutoCreated,
st.user_created as IsUserCreated,
st.no_recompute as IsNoRecompute
from sys.tables t with (nolock)
inner join sys.stats st with (nolock)
on st.object_id = t.object_id
inner join sys.indexes i with (nolock)
on t.object_id = i.object_id
inner join sys.dm_db_partition_stats as ps with (nolock)
on t.object_id = ps.object_id
and i.index_id = ps.index_id
where 1 = 1
and t.name ‘sysdiagrams’
Kind regards
Ron
Running the script on mirror fails with
Msg 927, Level 14, State 2, Line 237
Database ‘AdventureworksDR’ cannot be opened. It is in the middle of a restore.
ob,
You can’t run database specific queries against a mirrored database. You can run instance level queries.
Pingback: SQL Server Diagnostic Queries « SqlSchool.gr
Pingback: SQL SERVER – Guest Post – Jonathan Kehayias – Wait Type – Day 16 of 28 « Journey to SQL Authority with Pinal Dave
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!
Pingback: An XEvent a Day (30 of 31) – Tracking Session and Statement Level Waits | Jonathan Kehayias