sqlskills-logo-2015-white.png

A SQL Server Hardware Tidbit a Day – Day 15

For Day 15 of this series, 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.

If you have VIEW SERVER STATE permission on your instance, 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. Depending on what version of SQL Server you are using, you can also get a few more items of information. 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.

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 quad-core processor with hyper-threading enabled, the hyperthread_ratio would be 8 (4×2), while a quad-core core processor (with no hyper-threading) would have a hyperthread_ratio of 4 (4×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 WITH (NOLOCK) OPTION (RECOMPILE);

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

-- Hardware information from SQL Server 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, affinity_type_desc 
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

-- Hardware information from SQL Server 2012
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 WITH (NOLOCK) OPTION (RECOMPILE);

Listing 1: Hardware Information From Different SQL Server Versions

If you do have access to logon directly 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.

3 thoughts on “A SQL Server Hardware Tidbit a Day – Day 15

  1. I ran this above query and this is the result I get. I need to know if these settings are normal.

    Logical CPU count 2
    Hyperthread Ratio 1
    Physical CPU count 2
    Physical memory 8191

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.