Since Microsoft recently released a couple of new Cumulative Updates for SQL Server 2008, I thought it was finally time to release a new version of my SQL Server 2008/2008 R2 Diagnostic Information Queries for January 2012. You will need VIEW SERVER STATE permission for most of these queries, and some of them will only work with SQL Server 2008 R2 SP1 or greater (as noted in the comments for the individual queries). As always. I strongly recommend that you run each query individually, after reading the comments and instructions. I also recommend that you don’t make any rash decisions based on the results of a single query.
For example, if you see many rows returned from the “missing indexes” query, you should not just start adding indexes in a willy-nilly fashion. Instead, you should consider your complete workload, do some more information gathering and analysis, and finally use your own good judgment as you decide what to do based on these queries.
Glenn Berry's SQL Server Performance 
Cool, I love these!!, is March 2011 the latest for sql 2005?
That’s the latest that I can find. https://sqlserverperformance.wordpress.com/2011/03/07/march-2011-version-of-sql-server-2005-and-2008-diagnostic-queries/
Pingback: Don’t Plan to Fail « Mike Donnelly, SQLMD
Awesome! Not to be missed! Love the way these queries are organized and how they pull relevant info up top.
PLE for those of us with lots of named 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);