{"id":720,"date":"2013-04-15T15:08:09","date_gmt":"2013-04-15T22:08:09","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/glenn\/?p=720"},"modified":"2018-11-13T10:52:08","modified_gmt":"2018-11-13T18:52:08","slug":"a-sql-server-hardware-tidbit-a-day-day-15","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/glenn\/a-sql-server-hardware-tidbit-a-day-day-15\/","title":{"rendered":"A SQL Server Hardware Tidbit a Day – Day 15"},"content":{"rendered":"

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.<\/p>\n

If you have VIEW SERVER STATE permission<\/a> on your instance, you can query sys.dm_os_sys_info<\/a> 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.<\/p>\n

One frustrating fact is that you cannot tell the difference<\/a> 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\u00d72), while a quad-core core processor (with no hyper-threading) would have a hyperthread_ratio of 4 (4\u00d71).<\/p>\n

-- Hardware Information for SQL Server 2005<\/span>\r\nSELECT<\/span> cpu_count AS<\/span> [Logical CPU Count<\/span>], hyperthread_ratio AS<\/span> [Hyperthread Ratio],\r\ncpu_count\/hyperthread_ratio AS<\/span> [Physical CPU Count<\/span>], \r\nphysical_memory_in_bytes\/1048576 AS<\/span> [Physical Memory (MB)]\r\nFROM<\/span> sys.dm_os_sys_info WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);\r\n\r\n-- Hardware information from SQL Server 2008<\/span>\r\nSELECT<\/span> cpu_count AS<\/span> [Logical CPU Count<\/span>], hyperthread_ratio AS<\/span> [Hyperthread Ratio],\r\ncpu_count\/hyperthread_ratio AS<\/span> [Physical CPU Count<\/span>], \r\nphysical_memory_in_bytes\/1048576 AS<\/span> [Physical Memory (MB)], sqlserver_start_time\r\nFROM<\/span> sys.dm_os_sys_info WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);\r\n\r\n-- Hardware information from SQL Server 2008 R2  <\/span>\r\nSELECT<\/span> cpu_count AS<\/span> [Logical CPU Count<\/span>], hyperthread_ratio AS<\/span> [Hyperthread Ratio],\r\ncpu_count\/hyperthread_ratio AS<\/span> [Physical CPU Count<\/span>], \r\nphysical_memory_in_bytes\/1048576 AS<\/span> [Physical Memory (MB)], sqlserver_start_time, affinity_type_desc \r\nFROM<\/span> sys.dm_os_sys_info WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);\r\n\r\n-- Hardware information from SQL Server 2012<\/span>\r\nSELECT<\/span> cpu_count AS<\/span> [Logical CPU Count<\/span>], hyperthread_ratio AS<\/span> [Hyperthread Ratio],\r\ncpu_count\/hyperthread_ratio AS<\/span> [Physical CPU Count<\/span>], \r\nphysical_memory_kb\/1024 AS<\/span> [Physical Memory (MB)], affinity_type_desc, \r\nvirtual_machine_type_desc, sqlserver_start_time\r\nFROM<\/span> sys.dm_os_sys_info WITH<\/span> (NOLOCK) OPTION<\/span> (RECOMPILE);<\/pre>\n