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
-- http://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.

About these ads
This entry was posted in SQL Server 2008 R2 and tagged , . Bookmark the permalink.

5 Responses to New and Improved DMVs in SQL Server 2008 R2 SP1

  1. Pingback: Mudan̤as em DMVs no SQL Server 2008 R2 SP1 | Vladimir M. B. Magalḥes РSQL Server DBA

  2. Thanks Glenn, These are very useful, checking the location of errorlog files,sqlservr.exe files etc has got lot of easier with these DMV’s.

  3. Pingback: SQL Server 2008 R2 SP1 CU3 | Glenn Berry's SQL Server Performance

  4. Pingback: SQL Server 2008 R2 RTM CU10 | Glenn Berry's SQL Server Performance

  5. Pingback: October 2011 SQL Server 2012 Diagnostic Information Queries | Glenn Berry's SQL Server Performance

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