2012 Dynamic Management Views and Functions

2012 Dynamic Management Views and Functions

This evening I decided to spend some time trying out the new dynamic management views (DMV) and functions (DMF) in SQL 2012. First a little history, DMVs and DMFs were introduced with SQL 2005 and grouped based on their uses. As new versions of SQL Server were released existing DM groups were modified to include new DMVs / DMFs. With SQL 2005 there were 12 groups, 18 with 2008, 19 with 2008 R2. As Microsoft continues to add and refine features we end up with 20 groups with SQL2012, so far. In SQL 2012 we see one new group and a few slightly modified groups. With the new DR/HA feature, AlwaysOn, a new group of DMVs / DMFs is added the AlwaysOn Availability Groups Dynamic Management Views and Functions group. I will not go into the every new DMV/DMF in this group, but here are few just for an overview.

sys.dm_hadr_availability_group_states –- Returns the availability group state along with synchronization health.

dm_hadr_availability_replica_cluster_nodes — Returns replica state of availability group.

dm_hadr_cluster_members — If the availability groups have WSFC quorum, this query will return the members and their state. (On or Offline)

sys.dm_hadr_cluster_networks –- Returns information about availability groups subnet.

The Filestream-Related Dynamic Management Views group also receives a makeover and is now Filestream and FileTable Dynamic Management Views. The Filestream DMVs remain completely unchanged; however the group does receive one new member from the FileTable family, the dm_filestream_non_transacted_handles DMV. This view returns the open non-transactional file handles associated with FileTable data.

The Full-Text Search Related Dynamic Management View group is also slightly modified and now referred to as the Full-Text Search and Semantic Search Related Dynamic Management Views group. The Full-Text search family received one new member to better display property information for a full-text index, the dm_fts_index_keywords_by_property function. On the Semantics side of the family tree is dm_fts_semantic_similarity_population DMV, which returns information about the population of the document similarity index.

SQL 2012 is not due out for a few more months and I do suspect that new DMV/DMFs may be added and others modified before launch.

AlwaysOn DMVs and DMFs

Dynamic Management Views and Functions