SQL Server 2012 - New and Modified Dynamic Management Views and Functions - DMVs

 

That's just about the limit. In Books Online for SQL Server 2012 (BOL) by reading What's New (Database Engine), I came around the topic Manageability Enhancements (Database Engine) and its paragraph New and Modified Dynamic Management Views and Functions, and I only see six added or modified system views. And I think by myself, these are the same six DMVs I had read in CTP Denali BOL - no update?

But I knew that there are more new DMVs. I do not mean the AlwaysOn Availability Groups Dynamic Management Views and Functions of the AlwaysOn feature etc. ... - So, I think by myself - figure out!

OK, I have a SQL Server 2008 R2 instance and a SQL Server 2012 instance (both are only for testing, no production environment!). But I don't want to query each instance and copy the result for further investigations in excel - like this way:

USE [master];
GO
SELECT name, type, type_desc
FROM sys.system_objects  WITH (NOLOCK)
WHERE name LIKE 'dm_%'
ORDER BY name OPTION (RECOMPILE);
GO

 

New Dynamic Management Views and Functions in SQL Server 2012

I create a valid linked server point form SQL Server 2012 to SQL Server 2008 R2 (@server = N'SQL2008R2'), then I run below query from SQL Server 2012 - which will find DMVs that did not exists in 2008 R2 ('SQL2008R2') -  comparing the datasets in sys.system_objects.

 

USE [master];  --- execute on SQL Server 2012
GO
SELECT
       N'sys.' d.[name] AS [DMV NAME],
       d.[type] AS [DMV TYPE],
       d.type_desc AS [DMV TYPE DESC]
FROM
      [master].sys.system_objects AS WITH (NOLOCK)
LEFT OUTER JOIN --- LinkedServer SQL Server 2008 R2
      [SQL2008R2].[master].sys.system_objects AS WITH (NOLOCK)
ON
      d.[name] r.[name] ---* collate Latin1_General_CI_AS
      AND d.[schema_id] = r.[schema_id]
WHERE
      r.[name] IS NULL
      AND d.[schema_id] = 4
      AND d.[is_ms_shipped] = 1
      AND d.[name] like 'dm_%'
ORDER BY
      d.[name] OPTION (RECOMPILE);
GO

Linked Query Result

I get this resultset, and YES SQL Server 2012 has really 36 new Dynamic Management Views and Functions.

 
DMV NAMEDMV TYPEDMV TYPE DESC
sys.dm_db_database_page_allocations IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_db_fts_index_physical_stats VIEW
sys.dm_db_log_space_usage VIEW
sys.dm_db_objects_disabled_on_compatibility_level_change IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_db_uncontained_entities VIEW
sys.dm_exec_describe_first_result_set IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_exec_describe_first_result_set_for_object IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_filestream_non_transacted_handles VIEW
sys.dm_fts_index_keywords_by_property IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_fts_semantic_similarity_population VIEW
sys.dm_hadr_auto_page_repair VIEW
sys.dm_hadr_availability_group_states VIEW
sys.dm_hadr_availability_replica_cluster_nodes VIEW
sys.dm_hadr_availability_replica_cluster_states VIEW
sys.dm_hadr_availability_replica_states VIEW
sys.dm_hadr_cluster VIEW
sys.dm_hadr_cluster_members VIEW
sys.dm_hadr_cluster_networks VIEW
sys.dm_hadr_database_replica_cluster_states VIEW
sys.dm_hadr_database_replica_states VIEW
sys.dm_hadr_instance_node_map VIEW
sys.dm_hadr_name_id_map VIEW
sys.dm_logconsumer_cachebufferrefs IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logconsumer_privatecachebuffers IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_consumers IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_hashentries VIEW
sys.dm_logpool_sharedcachebuffers IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_stats VIEW
sys.dm_logpoolmgr_freepools IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_respoolsize IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpoolmgr_stats IF SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_os_cluster_properties VIEW
sys.dm_os_memory_broker_clerks VIEW
sys.dm_os_server_diagnostics_log_configurations VIEW
sys.dm_resource_governor_resource_pool_affinity VIEW
sys.dm_tcp_listener_states VIEW

... Now you can query Books Online for SQL Server 2012 ....

To struggle through these new Dynamic Management Views and Functions in SQL Server 2012, will be an ongoing and hard work :-)

I wish you a nice  day,
tosc

Torsten Schuessler