Post
Ask Question

New DMVs in SQL Server 2014

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.

  • sp_cci_tuple_mover
  • sp_db_ebcdic277_2
  • sp_db_enable_clustered_columnstores
  • sp_db_selective_xml_index
  • sp_MSgetgenstatus4rows
  • sp_xtp_merge_checkpoint_files
  • sp_set_cardinality_estimation_model_110

NEW FIELDS IN EXISTING VIEWS

There are the following new fields in existing views in SQL Server 2014.

  • all_parameters -- new field: is_nullable
  • all_sql_modules -- new field: uses_native_compilation
  • availability_databases_cluster -- new field: truncation_lsn
  • databases -- new field: containment_desc
  • databases -- new field: target_recovery_time_in_seconds
  • dm_exec_query_stats -- new field: statement_sql_handle
  • dm_exec_query_stats -- new field: statement_context_id
  • dm_exec_requests -- new field: statement_sql_handle
  • dm_exec_requests -- new field: statement_context_id
  • dm_hadr_database_replica_states -- new field: low_water_mark_for_ghosts
  • dm_io_pending_io_requests -- new field: io_handle_path
  • dm_logpool_stats -- new field: total_pages
  • dm_logpool_stats -- new field: private_pages
  • dm_os_buffer_descriptors -- new field: is_in_bpool_extension
  • dm_os_memory_cache_entries -- new field: time_to_generate
  • dm_os_memory_cache_entries -- new field: use_count
  • dm_os_memory_cache_entries -- new field: average_time_between_uses
  • dm_os_memory_cache_entries -- new field: time_since_last_use
  • dm_os_memory_cache_entries -- new field: probability_of_reuse
  • dm_os_memory_cache_entries -- new field: value
  • dm_os_worker_local_storage -- new field: filestream_address
  • dm_os_worker_local_storage -- new field: qe_cc_address
  • dm_os_worker_local_storage -- new field: xtp_address
  • dm_resource_governor_configuration -- new field: max_outstanding_io_per_volume
  • dm_resource_governor_resource_pools -- new field: min_iops_per_volume
  • dm_resource_governor_resource_pools -- new field: max_iops_per_volume
  • dm_resource_governor_resource_pools -- new field: read_io_queued_total
  • dm_resource_governor_resource_pools -- new field: read_io_issued_total
  • dm_resource_governor_resource_pools -- new field: read_io_completed_total
  • dm_resource_governor_resource_pools -- new field: read_io_throttled_total
  • dm_resource_governor_resource_pools -- new field: read_bytes_total
  • dm_resource_governor_resource_pools -- new field: read_io_stall_total_ms
  • dm_resource_governor_resource_pools -- new field: read_io_stall_queued_ms
  • dm_resource_governor_resource_pools -- new field: write_io_queued_total
  • dm_resource_governor_resource_pools -- new field: write_io_issued_total
  • dm_resource_governor_resource_pools -- new field: write_io_completed_total
  • dm_resource_governor_resource_pools -- new field: write_io_throttled_total
  • dm_resource_governor_resource_pools -- new field: write_bytes_total
  • dm_resource_governor_resource_pools -- new field: write_io_stall_total_ms
  • dm_resource_governor_resource_pools -- new field: write_io_stall_queued_ms
  • dm_resource_governor_resource_pools -- new field: io_issue_violations_total
  • dm_resource_governor_resource_pools -- new field: io_issue_delay_total_ms
  • dm_xe_sessions -- new field: session_source
  • master_files -- new field: credential_id
  • parameters -- new field: is_nullable
  • resource_governor_resource_pools -- new field: min_iops_per_volume
  • resource_governor_resource_pools -- new field: max_iops_per_volume
  • sql_modules -- new field: uses_native_compilation
  • system_parameters -- new field: is_nullable
  • system_sql_modules -- new field: uses_native_compilation
  • table_types -- new field: is_memory_optimized
  • tables -- new field: is_memory_optimizedtables -- new field: durability
  • tables -- new field: durability_desc
  • xml_indexes -- new field: xml_index_type
  • xml_indexes -- new field: xml_index_type_description
  • xml_indexes -- new field: path_id

NEW FIELDS IN EXISTING FUNCTIONS

There are the following new fields in existing functions in SQL Server 2014.

  • dm_exec_cursors -- new field: statement_sql_handle
  • dm_exec_cursors -- new field: statement_context_id
  • dm_logpool_consumers -- new field: log_consumer_ref_counter
  • fn_dblog -- new field: Log Record
  • fn_dump_dblog -- new field: Log Record
  • fn_get_audit_file -- new field: audit_schema_version
  • fn_get_audit_file -- new field: sequence_group_id

Refer to: http://www.brentozar.com/


Similar Articles