• Categories

  • Recent Posts

  • Archives

  • Copyright Notice

    Copyright © Nancy Hidy Wilson, 2010-2013. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Nancy Hidy Wilson and nancyhidywilson.wordpress.com with appropriate and specific direction to the original content.

My Denali First Glance – New DMVs

All it takes to know that Denali (SQL Server Version 11) will be a major upgrade from SQL Server 2008 R2 (SQL Server Version 10.5) is to compare the number of Dynamic Management Views and Functions (DMVs) available. 

select count(*) from master.sys.sysobjects where name like 'dm%'

SQL Server 2008 R2 SP1 reports 141 DMVs while Denali CTP3 counts 174! Let’s see….174-141=33! Here’s what’s new – and just for good measure to ensure I didn’t leave any out, I’ll number them:

1.            dm_db_database_page_allocations

2.            dm_db_fts_index_physical_stats

3.            dm_db_log_space_usage

4.            dm_db_objects_disabled_on_compatibility_level_change

5.            dm_db_uncontained_entities

6.            dm_exec_describe_first_result_set

7.            dm_exec_describe_first_result_set_for_object

8.            dm_filestream_non_transacted_handles

9.            dm_fts_index_keywords_by_property

10.         dm_fts_semantic_similarity_population

11.         dm_hadr_auto_page_repair

12.         dm_hadr_availability_group_states

13.         dm_hadr_availability_replica_states

14.         dm_hadr_cluster

15.         dm_hadr_cluster_members

16.         dm_hadr_cluster_networks

17.         dm_hadr_database_replica_cluster_states

18.         dm_hadr_database_replica_states

19.         dm_hadr_instance_node_map

20.         dm_hadr_name_id_map

21.         dm_logconsumer_cachebufferrefs

22.         dm_logconsumer_privatecachebuffers

23.         dm_logpool_consumers

24.         dm_logpool_hashentries

25.         dm_logpool_sharedcachebuffers

26.         dm_logpool_stats

27.         dm_logpoolmgr_freepools

28.         dm_logpoolmgr_respoolsize

29.         dm_logpoolmgr_stats

30.         dm_os_cluster_properties

31.         dm_os_memory_broker_clerks

32.         dm_os_server_diagnostics_log_configurations

33.         dm_tcp_listener_states


Almost everywhere you look in the Denali Database Engine much of what is “new” revolves around Availability Groups and HADR (High Availability / Disaster Recovery) options. Thus, many of the new DMVs (10) provide insight into these features. These DMVs have the naming convention – dm_hadr_%.  I’ll investigate these further when I get around to testing out AlwaysOn. But, in the meantime, you’ll find most but (as of this writing) not all of these documented in the Denali BOL DMV section (http://msdn.microsoft.com/en-us/library/ff877943(v=SQL.110).aspx) under the “AlwaysOn Availability Groups Dynamic Management Views and Functions”. You’ll also find that the new DMV, dm_tcp_listener_states, is also listed in this group in BOL, so I guess that makes 11 new DMVs in this category.

Another 10 new DMVs appear to deal with log information, but I can’t find any documentation on the ones using the dm_log% naming at present and most appear to be functions. Nor is information on dm_db_log_space_usage readily available. I suspect this deficiency will be remedied by RTM.  However, by running a SELECT query on sys.dm_db_log_space_usage, you’ll find that it returns a single row for the current or specified database with the following columns – database_id, total_log_size_in_bytes, used_log_space_in_bytes, used_log_space_in_percent.

I like this one tremendously – it provides exactly what I expected based on the name. I’ve been working with SQL Server since version 4.21 and now – finally – I no longer have to parse DBCC results or other DMVs and do these log space calculations for myself! Yippee!

On the other hand, dm_db_objects_disabled_on_compatibility_level_change isn’t exactly what I expected based on the name. I finally found it documented under the new section of DMVs in BOL called “Spatial Related Dynamic Management Views and Functions”. Huh? I’ll save you the trouble of clicking the link…BOL states that this DMV: Lists the persisted computed columns and spatial indexes that will be disabled as a result of changing compatibility level.Ok – so I’m guessing by the name that perhaps this will be expanded on in the future, but right now it is a fairly limited set of objects to which it applies.

If that list of 33 isn’t enough for you, there are the following DMVs which could also be classified as new to Denali, but were also “retroactively” released with SP1 for SQL Server 2008 R2. So if you’ll be skipping SQL Server 2008 R2 SP1, then these will definitely be new, too, when you move to Denali.  

  • dm_os_volume_stats
  • dm_os_windows_info
  • dm_server_memory_dumps
  • dm_server_services
  • dm_server_registry

My favorites from this list are dm_os_windows_info and dm_server_services. These two DMVs allow me to determine the Windows version my SQL Server instance is running on as well as information about the instance’s SQL Server services, such as start mode and service account name, without resorting to WMI or SQL-SMO queries. I can now get this information directly via a T-SQL query. The DMV dm_os_volume_stats is actually a function to which you’ll need to provide a database id and file id and it will return the size of the volume and remaining space on the volume containing that file, plus other details. I have to confess I was a little disappointed that this didn’t return a row for every volume containing any database file, so that I could quickly see if any of the SQL Server data volumes have space issues. But, I guess something has to remain for Microsoft to add in future releases!