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 d WITH (NOLOCK)
LEFT OUTER JOIN --- LinkedServer SQL Server 2008 R2
[SQL2008R2].[master].sys.system_objects AS r 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 NAME	DMV TYPE	DMV TYPE DESC
sys.dm_db_database_page_allocations	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_db_fts_index_physical_stats	V	VIEW
sys.dm_db_log_space_usage	V	VIEW
sys.dm_db_objects_disabled_on_compatibility_level_change	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_db_uncontained_entities	V	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	V	VIEW
sys.dm_fts_index_keywords_by_property	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_fts_semantic_similarity_population	V	VIEW
sys.dm_hadr_auto_page_repair	V	VIEW
sys.dm_hadr_availability_group_states	V	VIEW
sys.dm_hadr_availability_replica_cluster_nodes	V	VIEW
sys.dm_hadr_availability_replica_cluster_states	V	VIEW
sys.dm_hadr_availability_replica_states	V	VIEW
sys.dm_hadr_cluster	V	VIEW
sys.dm_hadr_cluster_members	V	VIEW
sys.dm_hadr_cluster_networks	V	VIEW
sys.dm_hadr_database_replica_cluster_states	V	VIEW
sys.dm_hadr_database_replica_states	V	VIEW
sys.dm_hadr_instance_node_map	V	VIEW
sys.dm_hadr_name_id_map	V	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	V	VIEW
sys.dm_logpool_sharedcachebuffers	IF	SQL_INLINE_TABLE_VALUED_FUNCTION
sys.dm_logpool_stats	V	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	V	VIEW
sys.dm_os_memory_broker_clerks	V	VIEW
sys.dm_os_server_diagnostics_log_configurations	V	VIEW
sys.dm_resource_governor_resource_pool_affinity	V	VIEW
sys.dm_tcp_listener_states	V	VIEW