SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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,
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],
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],
FROM sys.dm_os_sys_info;


Posted by Dukagjin Maloku on 12 May 2010

Thanks for sharing!

Posted by Jason Brimhall on 12 May 2010


Posted by Anonymous on 13 May 2010

Pingback from  asp.net sql web server help!? | BingSite

Posted by CarlaAbanes on 5 August 2012

thanks for sharing! i just upgraded one of our legacy systems to sql 2008 r2 from sql 2000 and this has been helpful :)

Leave a Comment

Please register or log in to leave a comment.