New DMVs in SQL Server 2014

Sharad Gupta
Jul 11, 2013
8.7k
0
0
Article

Introduction

SQL Server 2014 has many new internal tables, views, Stored Procedures and functions or you can say SQL Server 2014 has many new Dynamic Management Views and Functions (DMVs), here's a few of the new instrumentation tables and views.

NEW INTERNAL TABLES

There are the following internal tables in the SQL Server 2014. You can see those tables by executing the query given below.

select * from sys.internal_tables

plan_persist_context_settings
plan_persist_plan
plan_persist_query
plan_persist_query_text
plan_persist_runtime_stats
plan_persist_runtime_stats_interval

NEW VIEWS

There are the following views in SQL Server 2014. You can see the views by executing the query given below.

select * from sys.system_views

column_store_row_groups
dm_db_merge_requests
dm_db_xtp_checkpoint
dm_db_xtp_checkpoint_files
dm_db_xtp_gc_cycle_stats
dm_db_xtp_hash_index_stats
dm_db_xtp_index_stats
dm_db_xtp_memory_consumers
dm_db_xtp_object_stats
dm_db_xtp_table_memory_stats
dm_db_xtp_transactions
dm_io_cluster_shared_volumes
dm_os_buffer_pool_extension_configuration
dm_resource_governor_resource_pool_volumes
dm_xe_database_session_event_actions
dm_xe_database_session_events
dm_xe_database_session_object_columns
dm_xe_database_session_targets
dm_xe_database_sessions
dm_xtp_consumer_memory_usage
dm_xtp_gc_queue_stats
dm_xtp_gc_stats
dm_xtp_memory_stats
dm_xtp_system_memory_consumers
dm_xtp_threads
dm_xtp_transaction_recent_rows
dm_xtp_transaction_stats
event_session_actions
event_session_events
event_session_fields
event_session_targets
event_sessions
hash_indexes
selective_xml_index_namespaces
selective_xml_index_paths

NEW FUNCTIONS

There are the following new functions in SQL Server 2014 and you can see those functions by executing the query given below.

SELECT * FROM sys.all_objects where type in ('FN','AF','FS','FT','IF','TF') order by name

fn_dblog_xtp
fn_dump_dblog_xtp
fn_hadr_is_primary_replica

Here are the types:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL Stored Procedure
PC = Assembly (CLR) Stored Procedure
FN = SQL scalar-function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication filter procedure
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) trigger
TR = SQL trigger
IF = SQL inlined table-valued function
TF = SQL table-valued function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended Stored Procedure
IT = Internal table

NEW FUNCTIONS IN USER DATABASES/MSDB

There are the following new functions in user databases in SQL Server 2014:

fn_sysdac_get_currentusername
fn_sysdac_get_username
fn_sysdac_is_currentuser_sa
fn_sysdac_is_login_creator

NEW STORED PROCEDURES IN MASTER

There are the following new Stored Procedure in the master database. You get the list of Stored Procedure by executing "sp_help" in it.