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.
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.
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.
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.