Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

New SQL Server 2014 Dynamic Management Views


By:   |   Last Updated: 2013-12-20   |   Comments   |   Related Tips: More > 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:

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

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


Last Updated: 2013-12-20


next webcast button


next tip button



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

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools