Some New DMV Queries for SQL Server 2008 R2

I was spelunking around in SQL Server 2008 R2 today, after looking at a PowerPoint presentation by Madhan Arumugam about some of the low-level engine improvements in SQL Server 2008 R2 (when it is running on Windows Server 2008 R2), I discovered some new information that can be uncovered in DMV queries in SQL Server 2008 R2.

The first query is adapted from one of the samples in Madhan’s presentation. The second two show some new columns in sys.dm_os_sys_info. One mysterious thing I noticed is that the SQL Server 2008 R2 BOL talks about two more new columns (virtual_machine_type and virtual_machine_type_desc) that don’t show up when I query that DMV.

-- Get processor affinity, NUMA node and processor group information
-- SQL Server 2008 R2 Only
SELECT mn.memory_node_id,
CAST(osn.cpu_affinity_mask AS BINARY(8)) AS [CPUMask],
CAST(osn.online_scheduler_mask AS BINARY(8)) AS [OnlineSchedulerMask], 
osn.online_scheduler_count, osn.active_worker_count, osn.processor_group
FROM sys.dm_os_memory_nodes AS mn
INNER JOIN sys.dm_os_nodes AS osn
ON mn.memory_node_id = osn.memory_node_id
WHERE osn.node_state_desc NOT LIKE '%DAC%'
ORDER BY osn.processor_group, osn.cpu_affinity_mask;


-- Look at new columns in sys.dm.os_sys_info in SQL Server 2008 R2
SELECT affinity_type, affinity_type_desc,  
time_source, time_source_desc,
process_kernel_time_ms, process_user_time_ms 
FROM sys.dm_os_sys_info;


-- Hardware information from SQL Server 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, time_source_desc,
process_user_time_ms, 
CAST (CAST(process_user_time_ms AS FLOAT) /
(CAST(process_kernel_time_ms AS FLOAT) + CAST (process_user_time_ms AS FLOAT)) 
* 100 AS DECIMAL(9,2)) AS [% SQL User Time],
process_kernel_time_ms,
CAST (CAST(process_kernel_time_ms AS FLOAT) /
(CAST(process_kernel_time_ms AS FLOAT) + CAST (process_user_time_ms AS FLOAT)) 
* 100 AS DECIMAL(9,2)) AS [% SQL Kernel Time],
sqlserver_start_time
FROM sys.dm_os_sys_info;

This entry was posted in SQL Server 2008 R2. Bookmark the permalink.

2 Responses to Some New DMV Queries for SQL Server 2008 R2

  1. Michael says:

    Do you have a link to the information about the R2 low-level engine improvements? That sounds enteresting.

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