New and Improved DMVs in SQL Server 2008 R2 SP1

With relatively little fanfare, Microsoft added a few new DMVs into the SQL Server 2008 R2 SP1 release, which came out a few months ago. They also added some new columns to at least one existing DMV (sys.dm_os_sys_info).

Some of these could be pretty useful for basic diagnostic purposes. Just keep in mind that you must have SQL Server 2008 R2 SP1 (or later) installed for these to work.

I have some examples of these in the code block below:

-- New DMVs SQL Server 2008 R2 Service Pack 1
-- Glenn Berry 
-- October 2011
-- https://sqlserverperformance.wordpress.com/
-- Twitter: GlennAlanBerry

-- Windows information from SQL Server 2008 R2 SP1
SELECT windows_release, windows_service_pack_level, 
       windows_sku, os_language_version
FROM sys.dm_os_windows_info;

-- SQL Server Services information from SQL Server 2008 R2 SP1
SELECT servicename, startup_type_desc, status_desc, 
last_startup_time, service_account, is_clustered, cluster_nodename
FROM sys.dm_server_services;


-- Volume info for all databases on the current instance
SELECT DB_NAME(f.database_id) AS [DatabaseName], f.file_id, 
vs.volume_mount_point, vs.total_bytes, vs.available_bytes
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
ORDER BY f.database_id;


-- Volume info for the current database
SELECT database_id, f.file_id, volume_mount_point, total_bytes, available_bytes
FROM sys.database_files AS f
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), f.file_id);


-- SQL Server Registry information from SQL Server 2008 R2 SP1
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry;


-- Hardware information from SQL Server 2008 R2 SP1
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)], 
sqlserver_start_time , affinity_type_desc, virtual_machine_type_desc 
FROM sys.dm_os_sys_info;

You should give these a try.