As I recently discussed, I often hear from database professionals who are not allowed to access their database servers directly. Instead, they can only use SQL Server Management Studio (SSMS) to remotely manage the database servers that they are responsible for.

Unfortunately, this makes it much more difficult for the DBA to find out some very basic hardware information about all of the database servers that they have to manage. In SQL Server 2008 R2, you can use a T-SQL DMV query against sys.dm_os_sys_info (as shown in Listing 1)

-- Hardware information from SQL Server 2008 and 2008 R2
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio
AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)],
affinity_type_desc, sqlserver_start_time
FROM sys.dm_os_sys_info OPTION (RECOMPILE);

Listing 1: Getting Hardware Information from SQL Server 2008 and 2008 R2

This gives us the logical CPU count, the hyper-thread ratio, the physical CPU count, the Physical Memory, the CPU affinity type, and the SQL Server start time. Unfortunately, we have no idea what type of processor we are dealing with. Is it a top of the line, fire-breathing, 3.46GHz Intel Xeon X5690, or is it a much more humble, older processor?

This is very important information for the DBA to be aware of. For example, you might be trying to decide whether you should implement Data Compression on a few more indexes in your database. Knowing what type of processor(s) you have in your server is one data point that should go into your decision making process. For Data Compression, you would also want to consider how volatile the index is, what your estimated compression ratio is, and what your average CPU utilization rate is (all of which you can determine from my DMV diagnostic queries), but you would still be in the dark about the CPU type.

The CTP1 Build of SQL Server Denali has added at least one very useful column to sys.dm_os_sys_info, which is the virtual_machine_type_desc column, (which does not show up in the BOL entry yet). This will give you some idea about your hardware virtualization environment. The SQL Server Denali CTP1 version of this query is shown in Listing 2.

-- Hardware information from SQL Server Denali
SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio
AS [Hyperthread Ratio],
cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_kb/1024 AS [Physical Memory (MB)],
affinity_type_desc, virtual_machine_type_desc,
sqlserver_start_time
FROM sys.dm_os_sys_info OPTION (RECOMPILE);

Listing 2: Getting Hardware Information from SQL Server Denali

Another scenario where having a Processor Description column exposed in sys.dm_os_sys_info would be useful is if you are managing a large number of servers, perhaps by using a Central Management Server (CMS) in SQL Server Management Studio. Getting the processor description back from each server would be very helpful as you try to decide how to allocate and balance your workload across your available servers.

I also think it is very important to be aware of the age and relative performance of the processors in your servers. The latest processors are so much better than processors from even two to three years ago, that you have the opportunity to to upgrade or consolidate your hardware according to your priorities, in order to reduce your management overhead or increase your performance and scalability.

If you are unable to run a tool like msinfo32 or CPU-Z (since you are not allowed to access your database servers), how are you supposed to discover that you have a bunch of four to five year old processors in your servers, that have less CPU performance than my laptop?

If you agree with me about this issue, you can help convince Microsoft to take care of it in the SQL Server Denali release cycle, by going to Microsoft Connect and voting up this item. If you have a moment to leave a comment in the Connect Item, that would also be very helpful.