Should a DBA know what kind of hardware they are running on? And better yet, should they care?
My answer to both questions would be a firm … YES!
Here are two reasons why a DBA should know and care about their hardware:
- Investigating performance issues on a virtual server can be a little different than on a physical server. With a virtual server, you have another layer of software (Hyper V or VMWare), which could impact your performance. So, you will need to know how to investigate performance issues at that layer too.
- Since a lot of DBA’s handle SQL Server licensing, knowing CPU information (i.e. sockets, cores) of your hardware is crucial to figuring out SQL Server licensing.
With those reasons in mind, I am going to use this post to show how to obtain some basic CPU information through T-SQL and PowerShell.
Determine server type – virtual or physical
- T-SQL – With SQL Server versions of 2008 R2 and newer, the dynamic management view, dm_os_sys_info, contains columns called virtual_machine_type and virtual_machine_type_desc which will help you determine if the server is virtual. (https://msdn.microsoft.com/en-us/library/ms175048.aspx)
SELECT @@SERVERNAME AS server_name, virtual_machine_type_desc FROM sys.dm_os_sys_info
- PowerShell – By using the Win32_ComputerSystem class, you can determine whether the server is virtual or physical. For virtual servers, the Manufacturer will say “Microsoft Corporation” or “VMWare”, and the Model will say “Virtual Machine” or “VMWare Virtual Platform”. (https://msdn.microsoft.com/en-us/library/aa394102.aspx)
Get-WmiObject Win32_ComputerSystem -ComputerName "ServerName" | Select Name, Manufacturer, Model
Determine CPU information – sockets and cores
- T-SQL – You can get some basic CPU information by looking at the dynamic management view, dm_os_sys_info. The column cpu_count will give the total amount of logical CPU cores, and the column hyperthread_ratio will give the number of logical or physical cores that are exposed by one processor. However, SQL Server can NOT tell the difference between when hyper-threading is enabled and multi-core processors with those columns. (https://msdn.microsoft.com/en-us/library/ms175048.aspx)
SELECT @@SERVERNAME AS server_name, cpu_count AS logical_CPU_cores, cpu_count/hyperthread_ratio AS physical_CPU_sockets FROM sys.dm_os_sys_info
- PowerShell – By using the Win32_Processors class, you can determine how many CPUs (sockets), the number of cores per CPU, and whether hyper-threading is enabled. (https://msdn.microsoft.com/en-us/library/aa394373.aspx)
The output of the following code gives how many CPUs (processors or sockets), the number of cores per processor, and the number of logical processors. If the number of cores and the number of logical processors is not equal, then hyper-threading is enabled.
Get-WmiObject Win32_Processor -ComputerName "ServerName" | Select SocketDesignation, NumberOfCores, NumberOfLogicalProcessors
So, hopefully this post will be helpful for DBA’s to get to know their hardware