Scenario
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 = '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
Thanks for reading this article,
Next steps :
- Add this script to your database toolkit
- Share this with your colleagues because Sharing is Learning
- Comment below if you need any assistance
Powered by CodeReview – Let’s make it Better!