sqlskills-logo-2015-white.png

SQL Server Diagnostic Information Queries Detailed, Day 8

For Day 8 of this series, we start out with Query #16, which is Hardware Info.  This query retrieves some hardware-related information from the sys.dm_os_sys_info dynamic management view. Query #16 is shown in Figure 1.

   1: -- Hardware information from SQL Server 2016  (Query 16) (Hardware Info)

   2: SELECT cpu_count AS [Logical CPU Count], scheduler_count, hyperthread_ratio AS [Hyperthread Ratio],

   3: cpu_count/hyperthread_ratio AS [Physical CPU Count], 

   4: physical_memory_kb/1024 AS [Physical Memory (MB)], committed_kb/1024 AS [Committed Memory (MB)],

   5: committed_target_kb/1024 AS [Committed Target Memory (MB)],

   6: max_workers_count AS [Max Workers Count], affinity_type_desc AS [Affinity Type], 

   7: sqlserver_start_time AS [SQL Server Start Time], virtual_machine_type_desc AS [Virtual Machine Type], 

   8: softnuma_configuration_desc AS [Soft NUMA Configuration]

   9: FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

  10:  

  11: -- Gives you some good basic hardware information about your database server

  12: -- Cannot distinguish between HT and multi-core

  13: -- Note: virtual_machine_type_desc of HYPERVISOR does not automatically mean you are running SQL Server inside of a VM

  14: -- It merely indicates that you have a hypervisor running on your host

  15: -- Soft NUMA configuration is a new column for SQL Server 2016

Figure 1: Query #16 Hardware Info

This query tells you some very useful information about your hardware, even though it is frustratingly incomplete. You can determine your physical socket count, and the total number of logical processors, but you cannot tell what type of processor you have or whether Intel Hyper-Threading is enabled or not. You can also determine how much RAM is visible to the machine you are running on (physical or virtual).

You can also tell whether a hypervisor is present on the host you are running on, and whether someone has manually modified CPU process affinity. New in SQL Server 2016, you can see whether soft NUMA has been enabled. Finally, you can see the last time the SQL Server Service was started, which is very important to know when you are interpreting the results of many of the other queries in this set. It would be very nice if the results from the next two queries were also included in this DMV.

 

Query #17 is System Manufacturer.  This query tries to read the SQL Server Error log to determine the manufacturer and model number of the server that you are running on. If you are running inside of a hypervisor virtual machine, you will see that instead of the actual host hardware. Query #17 is shown in Figure 2.

   1: -- Get System Manufacturer and model number from SQL Server Error log (Query 17) (System Manufacturer)

   2: EXEC sys.xp_readerrorlog 0, 1, N'Manufacturer'; 

   3:  

   4: -- This can help you determine the capabilities and capacities of your database server

   5: -- Can also be used to confirm if you are running in a VM

   6: -- This query might take a few seconds if you have not recycled your error log recently

   7: -- This query will return no results if your error log has been recycled since the instance was started

Figure 2: Query #17 System Manufacturer

Knowing the brand and model of the server you are running on helps you figure out other useful information, such as how many PCIe expansion slots it has, how much memory it supports, and what type of processors it supports. It also helps you figure out how old it might be. This query will return no result if the SQL Server Error log has been recycled enough times to rollover since it was started. I really hate having to pull information like this from the SQL Server Error log, it would be much better to get it from a DMV.

 

Query #18 is Processor Description.  This query retrieves the processor description information from the Windows Registry. Query #18 is shown in Figure 3.

   1: -- Get processor description from Windows Registry  (Query 18) (Processor Description)

   2: EXEC sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'HARDWARE\DESCRIPTION\System\CentralProcessor\0', N'ProcessorNameString';

   3:  

   4: -- Gives you the model number and rated clock speed of your processor(s)

   5: -- Your processors may be running at less than the rated clock speed due

   6: -- to the Windows Power Plan or hardware power management

   7:  

   8: -- You can use CPU-Z to get your actual CPU core speed and a lot of other useful information

   9: -- http://www.cpuid.com/softwares/cpu-z.html

  10:  

  11: -- You can learn more about processor selection for SQL Server by following this link

  12: -- https://www.sqlskills.com/blogs/glenn/processor-selection-for-sql-server/

Figure 3: Query #18 Processor Description

I think it is critical to know exactly what processor(s) you are running on your SQL Server instances. This helps you understand the capabilities of the system and how old it might be. It is also very important to know this if you are thinking about doing a server upgrade or consolidation effort. Even though the gory details of processors are probably not that interesting to many database professionals, it is actually pretty important for licensing purposes and for performance and scalability.

One thought on “SQL Server Diagnostic Information Queries Detailed, Day 8

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.