SQL Server 2012 Diagnostic Information Queries (April 2012)

It is hard to believe, but SQL Server 2012 has been GA for nearly a month now. Because of this, I thought it was a good time to release an updated version of my SQL Server 2012 Diagnostic Information Queries. These queries are meant to be used with SQL Server 2012, but many of them will also work with SQL Server 2008 R2 SP1 or later. There are a few breaking changes in some of the DMVs between SQL Server 2008/2008 R2 and SQL Server 2012. Some of the queries in this set will also work with older versions of SQL Server.

There are a number of new and revised queries in this version. There also are some additional comments on how to interpret the results of the queries, plus some changes in the order of the queries. I also did some minor cleanup of several of the queries. As always, you will need VIEW SERVER STATE permission to run most of these queries. I recommend that you run them one at a time, after reading the directions and interpretation comments.

Finally, in case you were not aware, Microsoft has already released SQL Server 2012 RTM CU1, which I strongly recommend you get applied for any new SQL Server 2012 deployment that you may be working on.

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

One Response to SQL Server 2012 Diagnostic Information Queries (April 2012)

  1. Alexander Suprun says:

    Hi Glenn,

    Thanks for the updated scripts!

    In script for 2012 I found 2 exactly the same queries:

    – SQL Server Services information (SQL Server 2008 R2 SP1 or greater)
    SELECT servicename, startup_type_desc, status_desc,
    last_startup_time, service_account, is_clustered, cluster_nodename
    FROM sys.dm_server_services OPTION (RECOMPILE);

    – SQL Server Services information from SQL Server 2012
    SELECT servicename, startup_type_desc, status_desc,
    last_startup_time, service_account, is_clustered, cluster_nodename
    FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

    I think you can safely remove one of them.

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