Saturday, June 14, 2014

New SQL Server 2014 Dynamic Management Views

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 

No comments:

Popular Posts