A DMV a Day – Day 3

The DMV for Day 3 is sys.dm_os_sys_info, which is described by BOL as:

Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server.

That is not the best description that I ever read… This query tells you how many physical and logical CPUs you have on your SQL Server instance. It also gives you the hyperthread_ratio and the amount of physical RAM, along with the last SQL Server Start time. This query works with SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Hardware information from SQL Server 2008 
-- (Cannot distinguish between HT and multi-core)
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)], sqlserver_start_time
FROM sys.dm_os_sys_info;

Here is the version for SQL Server 2005:

-- Hardware information from SQL Server 2005 
-- (Cannot distinguish between HT and multi-core)
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)]
FROM sys.dm_os_sys_info;

This query is very useful for finding out some basic hardware information about your database server, especially if you work in an organization that does not give DBAs any direct access to their database servers. As I indicate in the comment, it cannot tell the difference between actual physical processor cores and hyperthreading. The hyperthread_ratio treats both multi-core and hyperthreading the same (which they are as far as the logical processor count goes), so you cannot tell the difference between a quad-core processor and a dual-core processor with hyper-threading enabled. Both of these examples would have a hyperthread_ratio of 4.

This entry was posted in SQL Server 2008. Bookmark the permalink.

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