Its been a couple of months since I have released an updated version of my SQL Server 2008 Diagnostic Information Queries, so I thought it was about time for a new version.
The first half of this set of queries is focused on the hardware and SQL Server Instance, so it does not matter which database you are connected to when you run them. The second half of the set is database specific, so you want to be connected to the database you are interested in, rather than the Master database. Many of these queries are DMV queries, which require VIEW SERVER STATE permission.
I recommend that you run each query individually, after reading the instructions and interpretation comments for that query. Personally, I like to paste the results for each query into a separate tab in a spreadsheet so I can keep them for baseline purposes.
I also strongly recommend that you look at all of the results, and consider everything you see before you make any rash decisions. For example if you see indexes show up in the “missing indexes” query, don’t go wild and add every index that shows up. A big part of being a wise DBA is to look at the entire picture, and use your judgment, experience and common sense as you interpret the results of these queries, and decide what (if anything) to do about them.
Glenn Berry's SQL Server Performance 
Hi Glen
Thanks for the latest version of your diagnostic queries – I’ve been following their development for some time now and they have proven to be extremely useful.
During a run of troubleshooting that I performed earlier this week I have noticed that one of the queries deviates slightly from the way in which BOL suggests that a join condition should be written:
http://msdn.microsoft.com/en-us/library/ms189792.aspx
…the deviation can be seen on lines 601-602 of the September 2011 version of your script:
INNER JOIN sys.partitions AS p WITH (NOLOCK)
ON a.container_id = p.hobt_id
BOL indicates that the column on which a.container_id should join is dependent on the value in the sys.partitions.type column:
Quote:
“ID of the storage container associated with the allocation unit.
If type = 1 or 3, container_id = sys.partitions.hobt_id.
If type is 2, then container_id = sys.partitions.partition_id.”
I just thought I’d raise this in case you wanted to modify the query for a future release of the scripts.
Thanks
Chris
Pingback: September 2011 SQL Server 2008 Instance Level Diagnostic Queries | Glenn Berry's SQL Server Performance
Pingback: SQL Server 2008 Diagnostic Queries « WebJagger’s Blog
Hi Glenn
just a quick heads up on the database file locations. If using Windows mount points then it’s perfectly valid for all database files (inc tempdb) to be using the same drive letter
Regards
Perry Whittle
Even so, I think it is better to use separate drive letters so that you have better visibility as to what is going on using standard diagnostic tools.
Pingback: Performance Tunning Part 2 | Vinay Thakur – Sql Server DBA