A SQL Server Hardware Nugget A Day – Day 5

For Day 5, I will be covering a few tools that can be used for hardware identification. Since quite a few database professionals do not have direct access to their database servers (i.e. they cannot login to their database server via RDP), I will talk about what you can learn about your hardware from T-SQL.

You can query sys.dm_os_sys_info and find out your physical CPU Socket count, your hyperthread ratio, your logical CPU count, and the amount of physical memory in the machine.

One frustrating fact is that you cannot tell the difference between hyper-threaded cores and physical cores when you see the hyperthread_ratio result. For example, if you had a dual core processor with hyper-threading enabled, the hyperthread_ratio would be 4 (2×2), while a true quad-core core processor (with no hyper-threading) would also have a  hyperthread_ratio of 4 (4×1).

Each new major version of SQL Server has added some additional columns to sys.dm_os_sys_info, which makes this query a little more useful. That is why I have three different versions of the query shown in Listing 1.

-- Hardware Information for SQL Server 2005
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 OPTION (RECOMPILE);


-- Hardware information from SQL Server 2008 and 2008 R2
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 OPTION (RECOMPILE);


-- Hardware information from SQL Server Denali
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio 
AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count], 
physical_memory_kb/1024 AS [Physical Memory (MB)], 
affinity_type_desc, virtual_machine_type_desc, 
sqlserver_start_time
FROM sys.dm_os_sys_info OPTION (RECOMPILE);

Listing 1: Getting hardware information from SQL Server with T-SQL

I am lobbying Microsoft pretty hard to add at least one new column to the sys.dm_os_sys_info DMV in SQL Server Denali.  I feel strongly enough about it that I just opened a Microsoft Connect Item about it. Please take a minute and vote for that Connect Item!

If you do have access to your SQL Server machine, there are several great, free tools that you can use to determine a wealth of useful information about your hardware, which I will talk about tomorrow.

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

4 Responses to A SQL Server Hardware Nugget A Day – Day 5

  1. Pingback: A SQL Server Hardware Nugget A Day – Day 5 | Glenn Berry's SQL …

  2. Pingback: A SQL Server Hardware Nugget A Day – Series Recap | Glenn Berry's SQL Server Performance

  3. Pingback: SQL Server Physical Hardware Information - Virtualization - sql - Techish.net

  4. Pingback: Logical CPU, Physical CPU and hyper threading - LivingForSqlServer - SQLServerCentral.com

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