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;