Since it is nearly March, I will go ahead and jump the gun by a few days and post an updated version of my SQL Server 2008/2008 R2 Diagnostic Queries for March 2012.
For this version, I have added information about the latest SQL Server 2008 R2 Cumulative Updates, added some more interpretation information for many of the queries, and added some new columns to some of the existing queries.
This set of queries is only for SQL Server 2008 or SQL Server 2008 R2. Some of the queries (which are marked), will only work with SQL Server 2008 R2 SP1 since they use DMVs or DMFs that were added in SP1.
As always, I recommend that you run the queries one at a time, after reading the comments and instructions for each query. You should not make any rash decisions based on the results of a single query. Instead, you should gather more information and use your good judgment and experience before you do something like dropping an index.
Glenn Berry's SQL Server Performance 
Pingback: Don’t Plan to Fail « Mike Donnelly, SQLMD
Glenn, I love these diagnostic queries. I think you might have the output column name of the Volume info for all databases on the current instance a little mixed up.
The column Space Used % should actually be labelled Space Free % as it’s dividing the Available Space by the Total Space. For example my System DB Drive is showing 21340921856 total_bytes and 20661043200 available_bytes, and Space Used% is showing 100%.
These are awesome Glenn. Thanks for sharing!
Pingback: Something for the Weekend – SQL Server Links 02/03/12
Glenn, thanks for these scripts. I have used different versions since 2009 and they are always very helpful.
I noticed the same problem as Jason, [Space Used %] should be [Space Available %].
Hello Glen!
First I would like to say thank YOU for your great work!
I am using your queries on daily basis and I found useful one addition that i made. Maybe it will be a good idea to add it to your next release of Diagnostic queries. Please have a look 😉
Output from this can be used to excel line chart and it show you how much your storage performance is degrading over time based on backup history.
— Storage writes throughput from backup history
WITH Thr (
database_name,
backup_finish_date,
duration_in_min,
server_name,
size_in_MB
–,recovery_model –optional atributes
–,is_readonly –optional atributes
)
AS
(
SELECT database_name,
backup_finish_date,
DATEDIFF(mi,backup_start_date,backup_finish_date) as duration_in_min,
server_name,
backup_size/1048576
–,recovery_model –optional atributes
–,is_readonly –optional atributes
FROM msdb..backupset
)
SELECT t.*
,(size_in_MB/ duration_in_min) as throughput_in_MB_per_min
FROM Thr as t
WHERE duration_in_min > 0
ORDER BY Backup_finish_date
PS: maybe it will not be the best script, but I am trying to return something useful to the community. Hope it helps.
Awesome collection, as always! Thanks so much!
I can’t think of a reason this tweaked PLE section would have any disadvantages? If you can’t either would love to see it in a release! 🙂
— Page Life Expectancy (PLE) value, multiple instances
SELECT cntr_value AS [Page Life Expectancy], [object_name]
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE [object_name] LIKE N’%Buffer Manager%’ — Modified for multiple instances
AND counter_name = N’Page life expectancy’ OPTION (RECOMPILE);