January 2012 SQL Server 2008/2008 R2 Diagnostic Information Queries

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.

About these ads
This entry was posted in SQL Server 2008, SQL Server 2008 R2 and tagged , . Bookmark the permalink.

4 Responses to January 2012 SQL Server 2008/2008 R2 Diagnostic Information Queries

  1. Pontus says:

    Cool, I love these!!, is March 2011 the latest for sql 2005?

  2. Pingback: Don’t Plan to Fail « Mike Donnelly, SQLMD

  3. @Kos1mo says:

    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);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s