April Version of SQL Server 2008 Diagnostic Queries

I have added two new queries to this month’s version, to try to collect some more hardware information about the database server. I specifically wanted to find out more information about the processor type from T-SQL, since many DBAs don’t have direct access to their database server to run a tool like CPU-Z.

Here are the two new queries:

-- Get System Manufacturer and model number from 
-- SQL Server Error log. This query might take a few seconds 
-- if you have not recycled your error log recently
EXEC xp_readerrorlog 0,1,"Manufacturer"; 


-- Get processor description from Windows Registry
-- (Uncomment query to make it work)
--EXEC xp_instance_regread 
--'HKEY_LOCAL_MACHINE',
--'HARDWARE\DESCRIPTION\System\CentralProcessor',
--'ProcessorNameString';

If we had a processor description column in the sys.dm_os_sys_info DMV, it would be a lot easier to collect this type of information. I have filed a Connect item to get this added to SQL Server Denali. If you agree that this is important, please vote it up!

Just click on the link to get the full SQL Server 2008 Diagnostic Information Query (Apr 2011), and the matching version of the results spreadsheet.

As always, you will need VIEW SERVER STATE permission to run many of these queries. You should run each query one at a time (after reading my notes for each one), rather than running the entire batch. That way, you can study the results, and see how long each query takes to complete in your environment.

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

9 Responses to April Version of SQL Server 2008 Diagnostic Queries

  1. Calvin Jones says:

    The 2nd parameter of the call to xp_instance_regread needs a processor/core number after CentralProcessor

    — Get processor description from Windows Registry
    EXEC xp_instance_regread
    ‘HKEY_LOCAL_MACHINE’,’HARDWARE\DESCRIPTION\System\CentralProcessor’,
    ‘ProcessorNameString';

  2. Jules Winnfield says:

    Glenn, thanks a lot for your queries.
    Is there a reason for not including the “Detect blocking” query in the 2008 version?

  3. Michael Rybicki says:

    Hey Glenn thanks for another update. I find your queries extremely helpful – all in one place. Very nice.

    I see BROKER_EVENTHANDLER in a lot of my servers and according to the Microsoft blog below it’s actually supposed to be a high wait time because it’s counting the amount of time it has been waiting to do something. Am I interpreting this one correctly – and if so, shouldn’t it be excluded since a high wait time for that is actually good?

    http://blogs.msdn.com/b/sql_service_broker/archive/2008/12/01/service-broker-wait-types.aspx

    Thanks.

  4. Pingback: DMVs for the SQL Artist | SQL Internals & MCM Training

  5. Glenn Berry says:

    I have actually decided to exclude BROKER_EVENTHANDLER from the cumulative wait stats.

  6. Pingback: What Are You Waiting For? | John Sansom - SQL Server DBA in the UK

  7. Pingback: Standing on the Shoulders of Giants | Art of the DBA

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