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.