New SQL Server 2014 Dynamic Management Views

By: Daniel Farina | Updated: 2013-12-20 | Comments | Related: > Dynamic Management Views and Functions

Problem

SQL Server 2014 offers many new features and along with that comes many new tools to help manage these new features. In this tip I will introduce the new Dynamic Management Views in SQL Server 2014.

Solution

Every SQL Server release comes with new and improved features and because of this, new Dynamic Management Views are included allowing us to collect metrics about these new features.

This version of SQL Server includes amongst its features:

The Hekaton Engine that brings us an optimistic latch free and lock free environment for In-Memory OLTP workloads.
Buffer Pool Extension that allows us to integrate a fast nonvolatile storage unit as an SSD disk to extend the SQL Server instance Buffer Pool and therefore reduce IO latency and increase transaction throughput.

Obviously these new features introduced a new set of DMVs which I will outline in this tip.

SQL Server Dynamic Management Views for Memory-Optimized Tables

The introduction of In Memory OLTP in SQL Server 2014 brings us a new category of DMVs that gives us the possibility to capture real time metrics of the Hekaton engine. All Dynamic Management Views and functions that contain "xtp" (an acronym of eXtreme Transaction Processing) in its name refer to the Hekaton In-Memory OLTP Engine.

There are two types of In-Memory OLTP Dynamic Management Views:

Database specific: These DMV's give information and statistics of the current database. These start with "sys.dm_db_xtp_"
Instance specific: Returns information and statistics concerning the entire In-Memory OLTP Engine of the instance. These start with "sys.dm_xtp_"

Here is the list of DMVs:

sys.dm_db_xtp_checkpoint_stats: Shows Checkpoint statistics of the current database.
sys.dm_db_xtp_checkpoint_files: This DMV shows information about checkpoint files, like the type of file (DATA or DELTA files) its size and relative path.
sys.dm_db_xtp_gc_cycle_stats: Shows garbage collection cycles for the current database.
sys.dm_db_xtp_hash_index_stats: This DMV is very useful to deal with Hash indexes. You can determine if a Hash index is low on buckets or has many duplicate keys. This was introduced in my previous tip "Considerations on BUCKET_COUNT on hash indexes for Memory-Optimized tables".
sys.dm_db_xtp_index_stats : This DMV contains statistics about Hash and Range indexes collected since the last database restart. It is the Memory-Optimized tables equivalent to sys.dm_db_index_usage_stats.
sys.dm_db_xtp_memory_consumers: This DMV reports stats for memory consumers of the current database. The view returns a row for each memory consumer that the database engine uses.
sys.dm_db_xtp_merge_requests: Use this DMV to view status of data and delta files merge operations both SQL Server and user generated.
sys.dm_db_xtp_table_memory_stats: Shows allocated and used memory of user and system tables.
sys.dm_db_xtp_transactions: Displays information about current transactions on the In-Memory OLTP database engine.
sys.dm_xtp_gc_stats: Gives information about the garbage-collection (GC) process on Memory-Optimized Tables.
sys.dm_xtp_gc_queue_stats: Outputs information about each GC worker queue on the server, and various statistics about each. There is one queue per core.
sys.dm_xtp_system_memory_consumers: Reports system level memory consumers for In-Memory OLTP.
sys.dm_xtp_transaction_stats: Reports statistics about transactions that have run since the server started.

Undocumented SQL Server Dynamic Management Views and Functions

sys.dm_db_xtp_nonclustered_index_stats: Displays statistics of Range Indexes in Memory-Optimized Tables.
sys.dm_db_xtp_object_stats: Reports row insert, update and delete attempts in Memory-Optimized tables
sys.dm_xtp_threads: Shows information about Hekaton threads like Base Address and thread type.
sys.dm_xtp_transaction_recent_rows: As its name says, returns information of recent rows within transactions.
sys.fn_dblog_xtp: Like sys.fn_dblog, displays transaction log information, but adds the following Hekaton specific columns:

Column Name	Type
operation_desc	nvarchar(30)
tx_end_timestamp	bigint
total_size	int
table_id	bigint
newrow_identity	bigint
newrow_data	varbinary(8000)
newrow_datasize	int
oldrow_begin_timestamp	bigint
oldrow_identity	bigint
oldrow_key_data	varbinary(8000)
oldrow_key_datasize	int
xtp_description	nvarchar(1024)

sys.fn_dump_dblog_xtp: Same as above, but also works with backup devices, just like sys.fn_dump_dblog.

Operating System related SQL Server Dynamic Management Views

sys.dm_os_buffer_pool_extension_configuration: Returns configuration information about the buffer pool extension, a new feature of SQL Server 2014 that allows us to extend the buffer pool cache with nonvolatile storage like a SSD disk.

Execution Related SQL Server Dynamic Management Views

sys.dm_exec_query_profiles: The purpose of this DMV is to monitor in real time query profiles. In layman terms, when you execute a query with any profiling option you can watch its progress using this DMV.

I/O Related SQL Server Dynamic Management Views and Functions

sys.dm_io_cluster_valid_path_names: This is what this DMV returns.

Column Name	Type
path_name	nvarchar(256)
cluster_owner_node	nvarchar(60)
is_cluster_shared_volume	bit

SQL Server Resource Governor Dynamic Management Views

sys.dm_resource_governor_resource_pool_volumes: This DMV shows information about the current resource pool IO statistics for each disk volume. It is useful to troubleshoot IO latency.

SQL Server AlwaysOn Availability Groups Dynamic Management Views and Functions

sys.fn_hadr_is_primary_replica: This function return 1 if the database on the current instance is the primary replica.
sys.dm_hadr_cluster: Returns information about the quorum of a Windows Server Failover Cluster on an AlwaysOn Availability Group or an AlwaysOn Failover Cluster Instance.
sys.dm_hadr_cluster_members: Shows information about Cluster Members.
sys.dm_hadr_cluster_networks: Returns network information about Windows Server Failover Cluster members participating in AlwaysOn Failover Cluster Instances or AlwaysOn Availability Groups.

Not a SQL Server DMV

sys.column_store_row_groups: Shows information about clustered columnstore indexes like row group state, total physical stored rows, including those marked as deleted, and deleted ones in other column. This DMV is useful to determine which row groups have a high percentage of deleted rows and should be rebuilt.

How to list all new SQL Server objects between versions

If you have installed both SQL Server 2012 and SQL Server 2014 then you can follow the next steps to list all new objects.

1 - Create a Linked server to the SQL Server 2014 instance.

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server       = N'SQL2014',
                                   @srvproduct   = N'SQL Server'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname    = N'SQL2014',
                                     @useself       = N'False',
                                     @locallogin    = NULL,
                                     @rmtuser       = N'sa',
                                     @rmtpassword   = '########'

2 - Execute the script below

USE master 
GO
SELECT  A.name ,
        A.schema_id ,
        A.type ,
        A.type_desc ,
        C.name AS 'COLUMN NAME' ,
        C.column_id ,
        ct.name ,
        C.max_length ,
        C.precision ,
        C.scale ,
        C.is_nullable 
FROM    SQL2014CTP1.master.sys.all_objects  A
        LEFT JOIN master.sys.all_objects B 
    ON A.name = B.name
        LEFT JOIN SQL2014CTP1.master.sys.all_columns C 
    ON A.object_id = C.object_id
         LEFT JOIN SQL2014CTP1.master.sys.types CT 
    ON C.system_type_id = CT.system_type_id
                    AND C.user_type_id = CT.user_type_id
    WHERE B.object_id IS null
    ORDER BY A.name

Next Steps

If you still don't have a version of SQL Server 2014, download a trial version here.
Review Understanding Catalog Views in SQL Server 2005 and 2008.
In this tip you will learn about SQL Server System Objects in User Defined Databases.
Review SQL Server Dynamic Management Views/Functions tips category.

About the author

Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2013-12-20