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.