Getting to Know Your Hardware

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:

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

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

Advertisements
This entry was posted in DBA, PowerShell, TSQL and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s