Full List of SQL Server 2014 DMVs

I'm not sure how useful this will be to others, but I keep searching for a single, comprehensive list of all the SQL Server DMVs with short descriptions. I am looking to complete a certification and this should prove handy for studying. This is certainly no replacement for the full descriptions and examples on MSDN, but I thought it worth putting together.

A simple query allows you to list all the DMVs on your SQL Server instance (see below - taken from Pinal Dave's blog), but it does not provide any descriptions.

AlwaysOn Availability Group
sys.dm_hadr_auto_page_repair
Returns a row for every automatic page-repair attempt on any availability database on an availability replica that is hosted for any availability group by the server instance.
sys.dm_hadr_availability_group_states
Returns a row for each AlwaysOn availability group that possesses an availability replica on the local instance of SQL Server. Each row displays the states that define the health of a given availability group.
sys.dm_hadr_availability_replica_cluster_nodes
Returns a row for every availability replica (regardless of join state) of the AlwaysOn availability groups in the Windows Server Failover Clustering (WSFC) cluster.
sys.dm_hadr_availability_replica_cluster_states
Returns a row for each AlwaysOn availability replica (regardless of its join state) of all AlwaysOn availability groups (regardless of replica location) in the Windows Server Failover Clustering (WSFC) cluster.
sys.dm_hadr_availability_replica_states
Returns a row for each local availability replica and a row for each remote availability replica in the same AlwaysOn availability group as a local replica. Each row contains information about the state of a given availability replica.
sys.dm_hadr_cluster
If the Windows Server Failover Clustering (WSFC) node that hosts an instance of SQL Server that is enabled for AlwaysOn Availability Groups has WSFC quorum, sys.dm_hadr_cluster returns a row that exposes the cluster name and information about the quorum. If the WSFC node has no quorum, no row is returned.
sys.dm_hadr_cluster_members
If the WSFC node that hosts a local instance of SQL Server that is enabled for AlwaysOn Availability Groups has WSFC quorum, returns a row for each of the members that constitute the quorum and the state of each of them.
sys.dm_hadr_cluster_networks
Returns a row for every WSFC cluster member that is participating in an availability group's subnet configuration. You can use this dynamic management view to validate the network virtual IP that is configured for each availability replica.
sys.dm_hadr_database_replica_cluster_states
Returns a row for each AlwaysOn availability replica (regardless of its join state) of all AlwaysOn availability groups (regardless of replica location) in the Windows Server Failover Clustering (WSFC) cluster.
sys.dm_hadr_database_replica_states
Returns a row for each database that is participating in an AlwaysOn availability group for which the local instance of SQL Server is hosting an availability replica.
sys.dm_hadr_instance_node_map
For every instance of SQL Server that hosts an availability replica that is joined to its AlwaysOn availability group, returns the name of the Windows Server Failover Clustering (WSFC) node that hosts the server instance.
sys.dm_hadr_name_id_map
Shows the mapping of AlwaysOn availability groups that the current instance of SQL Server has joined to three unique IDs: an availability group ID, a WSFC resource ID, and a WSFC Group ID. The purpose of this mapping is to handle the scenario in which the WSFC resource/group is renamed.
sys.dm_tcp_listener_states
Returns a row containing dynamic-state information for each TCP listener.
Change Data Capture
sys.dm_cdc_log_scan_sessions
Returns one row for each log scan session in the current database. The last row returned represents the current session.
sys.dm_repl_traninfo
Returns information on each replicated or change data capture transaction.
sys.dm_cdc_errors
Returns one row for each error encountered during the change data capture log scan session.
Change Tracking
sys.dm_tran_commit_table
Displays one row for each transaction that is committed for a table that is tracked by SQL Server change tracking.
Common Language Runtime
sys.dm_clr_appdomains
Returns a row for each application domain in the server. Application domain (AppDomain) is a construct in the Microsoft .NET Framework common language runtime (CLR) that is the unit of isolation for an application. You can use this view to understand and troubleshoot CLR integration objects that are executing in Microsoft SQL Server.
sys.dm_clr_properties
Returns a row for each property related to SQL Server common language runtime (CLR) integration, including the version and state of the hosted CLR.
sys.dm_clr_loaded_assemblies
Returns a row for each managed user assembly loaded into the server address space. Use this view to understand and troubleshoot CLR integration managed database objects that are executing in Microsoft SQL Server.
sys.dm_clr_tasks
Returns a row for all common language runtime (CLR) tasks that are currently running.
Database Mirroring
sys.dm_db_mirroring_connections
Returns a row for each connection established for database mirroring.
sys.dm_db_mirroring_auto_page_repair
Returns a row for every automatic page-repair attempt on any mirrored database on the server instance. This view contains rows for the latest automatic page-repair attempts on a given mirrored database, with a maximum of 100 rows per database.
Database Related
sys.dm_db_file_space_usage
Returns space usage information for each file in the database.
sys.dm_db_fts_index_physical_stats
Returns a row for each full-text or semantic index in each table that has an associated full-text or semantic index.
sys.dm_db_partition_stats
Returns page and row-count information for every partition in the current database.
sys.dm_db_persisted_sku_features
Use the sys.dm_db_persisted_sku_features dynamic management view to list all edition-specific features that are enabled in the current database.
sys.dm_db_session_space_usage
Returns the number of pages allocated and deallocated by each session for the database.
sys.dm_db_task_space_usage
Returns page allocation and deallocation activity by task for the database.
sys.dm_db_uncontained_entities
Shows any uncontained objects used in the database. Uncontained objects are objects that cross the database boundary in a contained database.
sys.dm_db_wait_stats (Azure SQL Database)
Returns information about all the waits encountered by threads that executed during operation. You can use this aggregated view to diagnose performance issues with Azure SQL Database and also with specific queries and batches.
sys.dm_database_copies (Azure SQL Database)
Returns information about the database copy.
sys.dm_operation_status (Azure SQL Database)
Returns information about operations performed on databases in a Azure SQL Database server.
sys.dm_db_objects_impacted_on_version_change (Azure SQL Database)
This database-scoped system view is designed to provide an early warning system to determine objects that will be impacted by a major release upgrade in Azure SQL Database.
sys.dm_db_resource_stats (Azure SQL Database)
Returns CPU, I/O, and memory consumption for an Azure SQL Database database. One row exists for every 15 seconds, even if there is no activity in the database. Historical data is maintained for one hour.
Execution Related
sys.dm_exec_background_job_queue
Returns a row for each query processor job that is scheduled for asynchronous (background) execution.
sys.dm_exec_background_job_queue_stats
Returns a row that provides aggregate statistics for each query processor job submitted for asynchronous (background) execution.
sys.dm_exec_cached_plans
Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.
sys.dm_exec_cached_plan_dependent_objects
Returns a row for each Transact-SQL execution plan, common language runtime (CLR) execution plan, and cursor associated with a plan.
sys.dm_exec_connections
Returns information about the connections established to this instance of SQL Server and the details of each connection.
sys.dm_exec_cursors
Returns information about the cursors that are open in various databases.
sys.dm_exec_describe_first_result_set
This dynamic management function takes a Transact-SQL statement as a parameter and describes the metadata of the first result set for the statement.
sys.dm_exec_describe_first_result_set_for_object
This dynamic management function takes an @object_id as a parameter and describes the first result metadata for the module with that ID. The @object_id specified can be the ID of a Transact-SQL stored procedure or a Transact-SQL trigger. If it is the ID of any other object (such as a view, table, function, or CLR procedure), an error will be specified in the error columns of the result.
sys.dm_exec_plan_attributes
Returns one row per plan attribute for the plan specified by the plan handle. You can use this table-valued function to get details about a particular plan, such as the cache key values or the number of current simultaneous executions of the plan.
sys.dm_exec_procedure_stats
Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached.
sys.dm_exec_query_memory_grants
Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute. Queries that do not have to wait on a memory grant will not appear in this view.
sys.dm_exec_query_optimizer_info
Returns detailed statistics about the operation of the SQL Server query optimizer. You can use this view when tuning a workload to identify query optimization problems or improvements.
sys.dm_exec_query_plan
Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing.
sys.dm_exec_query_profiles
Monitors real time query progress while the query is in execution.
sys.dm_exec_query_resource_semaphores
Returns the information about the current query-resource semaphore status in SQL Server. sys.dm_exec_query_resource_semaphores provides general query-execution memory status and allows you to determine whether the system can access enough memory.
sys.dm_exec_query_stats
Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
sys.dm_exec_requests
Returns information about each request that is executing within SQL Server.
sys.dm_exec_sessions
Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks.
sys.dm_exec_sql_text
Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.
sys.dm_exec_text_query_plan
Returns the Showplan in text format for a Transact-SQL batch or for a specific statement within the batch. The query plan specified by the plan handle can either be cached or currently executing.
sys.dm_exec_trigger_stats
Returns aggregate performance statistics for cached triggers. The view contains one row per trigger, and the lifetime of the row is as long as the trigger remains cached.
sys.dm_exec_xml_handles
Returns information about active handles that have been opened by sp_xml_preparedocument.
Extended Events
sys.dm_xe_map_values
Returns a mapping of internal numeric keys to human-readable text.
sys.dm_xe_object_columns
Returns the schema information for all the objects.
sys.dm_xe_objects
Returns a row for each object that is exposed by an event package.
sys.dm_xe_packages
Lists all the packages registered with the extended events engine.
sys.dm_xe_session_event_actions
Returns information about event session actions. Actions are executed when events are fired. This management view aggregates statistics about the number of times an action has run, and the total run time of the action.
sys.dm_xe_session_events
Returns information about session events. Events are discrete execution points. Predicates can be applied to events to stop them from firing if the event does not contain the required information.
sys.dm_xe_session_object_columns
Shows the configuration values for objects that are bound to a session.
sys.dm_xe_session_targets
Returns information about session targets.
sys.dm_xe_sessions
Returns information about an active extended events session. This session is a collection of events, actions, and targets.
Filestream and FileTable
sys.dm_filestream_file_io_handles
Displays the currently open transactional file handles.
sys.dm_filestream_file_io_requests
Displays current file input and file output requests.
Full-Text and Semantic Search
sys.dm_fts_active_catalogs
Returns information on the full-text catalogs that have some population activity in progress on the server.
sys.dm_fts_fdhosts
Returns information on the current activity of the filter daemon host or hosts on the server instance.
sys.dm_fts_index_keywords_by_document
Returns information about the document-level content of a full-text index for the specified table. A given keyword can appear in several documents.
sys.dm_fts_index_keywords_by_property
Returns all property-related content in the full-text index of a given table. This includes all data that belongs to any property registered by the search property list associated with that full-text index.
sys.dm_fts_index_keywords
Returns information about the content of a full-text index for the specified table.
sys.dm_fts_index_population
Returns information about the full-text index populations currently in progress.
sys.dm_fts_memory_buffers
Returns information about memory buffers belonging to a specific memory pool that are used as part of a full-text crawl or a full-text crawl range.
sys.dm_fts_memory_pools
Returns information about the shared memory pools available to the Full-Text Gatherer component for a full-text crawl or a full-text crawl range.
sys.dm_fts_outstanding_batches
Returns information about each full-text indexing batch.
sys.dm_fts_parser
Returns the final tokenization result after applying a given word breaker, thesaurus, and stoplist combination to a query string input. The output is equivalent to the output if the specified given query string were issued to the Full-Text Engine.
sys.dm_fts_population_ranges
Returns information about the specific ranges related to a full-text index population currently in progress.
sys.dm_fts_semantic_similarity_population
Returns one row of status information about the population of the document similarity index for each similarity index in each table that has an associated semantic index.
Index Related
sys.dm_db_index_operational_stats
Returns current lowore-level I/O, locking, latching, and access method activity for each partition of a table or index in the database. Memory-optimized indexes do not appear in this DMV.
sys.dm_db_index_physical_stats
Returns size and fragmentation information for the data and indexes of the specified table or view in SQL Server. For an index, one row is returned for each level of the B-tree in each partition. For a heap, one row is returned for the IN_ROW_DATA allocation unit of each partition. For large object (LOB) data, one row is returned for the LOB_DATA allocation unit of each partition. If row-overflow data exists in the table, one row is returned for the ROW_OVERFLOW_DATA allocation unit in each partition. Does not return information about xVelocity memory optimized columnstore indexes.
sys.dm_db_index_usage_stats
Returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.
sys.dm_db_missing_index_columns
Returns information about database table columns that are missing an index, excluding spatial indexes. sys.dm_db_missing_index_columns is a dynamic management function.
sys.dm_db_missing_index_details
Returns detailed information about missing indexes, excluding spatial indexes.
sys.dm_db_missing_index_group_stats
Returns summary information about groups of missing indexes, excluding spatial indexes.
sys.dm_db_missing_index_groups
Returns information about what missing indexes are contained in a specific missing index group, excluding spatial indexes.
I/O Related
sys.dm_io_backup_tapes
Returns the list of tape devices and the status of mount requests for backups.
sys.dm_io_cluster_shared_drives
This view returns the drive name of each of the shared drives if the current server instance is a clustered server. If the current server instance is not a clustered instance it returns an empty rowset.
sys.dm_io_cluster_valid_path_names
Returns information on all valid shared disks, including clustered shared volumes, for a SQL Server failover cluster instance. If the instance is not clustered, an empty rowset is returned.
sys.dm_io_pending_io_requests
Returns a row for each pending I/O request in SQL Server.
sys.dm_io_virtual_file_stats
Returns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats function.
Memory-Optimized Table
sys.dm_db_xtp_checkpoint_files
Displays information about checkpoint files, including file size, physical location and the transaction ID.
sys.dm_db_xtp_checkpoint_stats
Returns statistics about the In-Memory OLTP checkpoint operations in the current database. If the database has no In-Memory OLTP objects, returns an empty result set.
sys.dm_db_xtp_gc_cycle_stats
Outputs the current state of committed transactions that have deleted one or more rows. The idle garbage collection thread wakes every minute or when the number of committed DML transactions exceeds an internal threshold since the last garbage collection cycle.
sys.dm_db_xtp_hash_index_stats
These statistics are useful for understanding and tuning the bucket counts. It can also be used to detect cases where the index key has many duplicates.
sys.dm_db_xtp_index_stats
Contains index statistics collected since the last database restart.
sys.dm_db_xtp_memory_consumers
Reports the database-level memory consumers in the In-Memory OLTP database engine. The view returns a row for each memory consumer that the database engine uses.
sys.dm_db_xtp_merge_requests
Tracks database merge requests. The merge request may have been generated by SQL Server or the request could have been made by a user withsys.sp_xtp_merge_checkpoint_files.
sys.dm_db_xtp_nonclustered_index_stats
This view includes statistics about operations on nonclustered indexes in memory-optimized tables. It contains one row for each nonclustered index on a memory-optimized table in the current database.
sys.dm_db_xtp_object_stats
Reports the number rows affected by operations on each of the In-Memory OLTP objects since the last database restart. Statistics are updated when the operation executes, regardless of whether the transaction commits or was rolled back.
sys.dm_db_xtp_table_memory_stats
Returns memory usage statistics for each In-Memory OLTP table (user and system) in the current database.
sys.dm_db_xtp_transactions
Reports the active transactions in the In-Memory OLTP database engine.
sys.dm_xtp_gc_queue_stats
Outputs information about each garbage collection worker queue on the server, and various statistics about each. There is one queue per logical CPU.
sys.dm_xtp_gc_stats
Provides information (the overall statistics) about the current behavior of the In-Memory OLTP garbage-collection process.
sys.dm_xtp_system_memory_consumers
Reports system level memory consumers for In-Memory OLTP. The memory for these consumers come either from the default pool (when the allocation is in the context of a user thread) or from internal pool (if the allocation is in the context of a system thread).
sys.dm_xtp_transaction_stats
Reports statistics about transactions that have run since the server started.
Object Related
sys.dm_sql_referenced_entities
Returns one row for each user-defined entity referenced by name in the definition of the specified referencing entity in SQL Server. For example, if a stored procedure is the specified referencing entity, this function returns all user-defined entities that are referenced in the stored procedure such as tables, views, user-defined types (UDTs), or other stored procedures.
sys.dm_sql_referencing_entities
Returns one row for each entity in the current database that references another user-defined entity by name. For example, if a user-defined type (UDT) is specified as the referenced entity, this function returns each user-defined entity that reference that type by name in its definition.
sys.dm_db_stats_properties
Returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database.
Query Notifications
sys.dm_qn_subscriptions
Returns information about the active query notifications subscriptions in the server. You can use this view to check for active subscriptions in the server or a specified database, or to check for a specified server principal.
Replication
sys.dm_repl_articles
Returns information about database objects published as articles in a replication topology.
sys.dm_repl_schemas
Returns information about table columns published by replication.
sys.dm_repl_tranhash
Returns information about transactions being replicated in a transactional publication.
sys.dm_repl_traninfo
Returns information on each replicated or change data capture transaction.
Resource Governor
sys.dm_resource_governor_configuration
Returns a row that contains the current in-memory configuration state of Resource Governor.
sys.dm_resource_governor_resource_pools
Returns information about the current resource pool state, the current configuration of resource pools, and resource pool statistics.
sys.dm_resource_governor_resource_pool_volumes
Returns information about the current resource pool IO statistics for each disk volume. This information is also available at the resource pool level in sys.dm_resource_governor_resource_pools.
sys.dm_resource_governor_workload_groups
Returns workload group statistics and the current in-memory configuration of the workload group. This view can be joined with sys.dm_resource_governor_resource_pools to get the resource pool name.
Security-Related
sys.dm_audit_actions
Returns a row for every audit action that can be reported in the audit log and every audit action group that can be configured as part of SQL Server Audit.
sys.dm_audit_class_type_map
Returns a table that maps the class_type field in the audit log to the class_desc field in sys.dm_audit_actions.
sys.dm_cryptographic_provider_algorithms
Returns the algorithms supported by an Extensible Key Management (EKM) provider.
sys.dm_cryptographic_provider_keys
Returns information about the keys provided by a Extensible Key Management (EKM) provider.
sys.dm_cryptographic_provider_properties
Returns information about registered cryptographic providers.
sys.dm_cryptographic_provider_sessions
Returns information about open sessions for a cryptographic provider.
sys.dm_database_encryption_keys
Returns information about the encryption state of a database and its associated database encryption keys.
sys.dm_server_audit_status
Returns a row for each server audit indicating the current state of the audit.
Service Broker
sys.dm_broker_activated_tasks
Returns a row for each stored procedure activated by Service Broker.
sys.dm_broker_connections
Returns a row for each Service Broker network connection.
sys.dm_broker_forwarded_messages
Returns a row for each Service Broker message that an instance of SQL Server is in the process of forwarding.
sys.dm_broker_queue_monitors
Returns a row for each queue monitor in the instance. A queue monitor manages activation for a queue.
SQL Server Operating System
sys.dm_os_buffer_descriptors
Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type. In SQL Server 2014, this dynamic management view also returns information about the data pages in the buffer pool extension file.
sys.dm_os_buffer_pool_extension_configuration
Returns configuration information about the buffer pool extension in SQL Server. Returns one row for each buffer pool extension file.
sys.dm_os_child_instances
{DEPRECATED} Returns a row for each user instance that has been created from the parent server instance.
sys.dm_os_cluster_nodes
Returns one row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance (formerly "virtual server") has been defined. If the current server instance is not a failover clustered instance, it returns an empty rowset.
sys.dm_os_cluster_properties
Returns one row with the current settings for the SQL Server cluster resource properties identified in this topic. No data is returned if this view is run on a stand-alone instance of SQL Server. These properties are used to set the values that affect failure detection, failure response time, and the logging for monitoring the health status of the SQL Server failover cluster instance.
sys.dm_os_dispatcher_pools
Returns information about session dispatcher pools. Dispatcher pools are thread pools used by system components to perform background processing.
sys.dm_os_hosts
Returns all the hosts currently registered in an instance of SQL Server. This view also returns the resources that are used by these hosts.
sys.dm_os_latch_stats
Returns information about all latch waits organized by class.
sys.dm_os_loaded_modules
Returns a row for each module loaded into the server address space.
sys.dm_os_memory_brokers
Allocations that are internal to SQL Server use the SQL Server memory manager. Tracking the difference between process memory counters from sys.dm_os_process_memory and internal counters can indicate memory use from external components in the SQL Server memory space.
sys.dm_os_memory_cache_clock_hands
Returns the status of each hand for a specific cache clock.
sys.dm_os_memory_cache_counters
Returns a snapshot of the health of a cache in SQL Server. sys.dm_os_memory_cache_counters provides run-time information about the cache entries allocated, their use, and the source of memory for the cache entries.
sys.dm_os_memory_cache_entries
Returns information about all entries in caches in SQL Server. Use this view to trace cache entries to their associated objects. You can also use this view to obtain statistics on cache entries.
sys.dm_os_memory_cache_hash_tables
Returns a row for each active cache in the instance of SQL Server.
sys.dm_os_memory_clerks
Returns the set of all memory clerks that are currently active in the instance of SQL Server. Inside SQL Server, only memory clerks have access to memory nodes. Memory clerks access memory node interfaces to allocate memory. Memory nodes also track the memory allocated by using the clerk for diagnostics. Every component that allocates a significant amount of memory must create its own memory clerk and allocate all its memory by using the clerk interfaces. Frequently, components create their corresponding clerks at the time SQL Server is started.
sys.dm_os_memory_nodes
Allocations that are internal to SQL Server use the SQL Server memory manager. Tracking the difference between process memory counters from sys.dm_os_process_memory and internal counters can indicate memory use from external components in the SQL Server memory space. Nodes are created per physical NUMA memory nodes. These might be different from the CPU nodes in sys.dm_os_nodes. No allocations done directly through Windows memory allocations routines are tracked. The following table provides information about memory allocations done only by using SQL Server memory manager interfaces.
sys.dm_os_memory_objects
Returns memory objects that are currently allocated by SQL Server. You can use sys.dm_os_memory_objects to analyze memory use and to identify possible memory leaks.
sys.dm_os_memory_pools
Returns a row for each object store in the instance of SQL Server. You can use this view to monitor cache memory use and to identify bad caching behavior
sys.dm_os_nodes
An internal component named the SQLOS creates node structures that mimic hardware processor locality. These structures can be changed by using soft-NUMA to create custom node layouts.
sys.dm_os_performance_counters
Returns a row per performance counter maintained by the server.
sys.dm_os_process_memory
This view displays basic memory related information for the server. It provides detail for physical memory, available memory , total page file and available page file and high/low memory status. A low memory status indicates memory pressure.
sys.dm_os_schedulers
Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks.
sys.dm_os_server_diagnostics_log_configurations
Returns one row with the current configuration for the SQL Server failover cluster diagnostic log. These property settings determine whether the diagnostic logging is on or off, and the location, number, and size of the log files.
sys.dm_os_stacks
This dynamic management view is used internally by SQL Server to do the following: 1. Keep track of debug data such as outstanding allocations; 2. Assume or validate logic that is used by SQL Server components in places where the component assumes that a certain call has been made.
sys.dm_os_sys_info
Returns a miscellaneous set of useful information about the computer, and about the resources available to and consumed by SQL Server.
sys.dm_os_sys_memory
Returns memory information from the operating system.
sys.dm_os_tasks
Returns one row for each task that is active in the instance of SQL Server.
sys.dm_os_threads
Returns a list of all SQL Server Operating System threads that are running under the SQL Server process.
sys.dm_os_virtual_address_dump
Returns information about a range of pages in the virtual address space of the calling process.
sys.dm_os_volume_stats
Returns information about the operating system volume (directory) on which the specified databases and files are stored in SQL Server. Use this dynamic management function to check the attributes of the physical disk drive or return available free space information about the directory.
sys.dm_os_wait_stats
Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
sys.dm_os_waiting_tasks
Returns information about the wait queue of tasks that are waiting on some resource.
sys.dm_os_windows_info
Returns one row that displays Windows operating system version information.
sys.dm_os_workers
Returns a row for every worker in the system.
Transaction Related
sys.dm_tran_active_snapshot_database_transactions
Returns a virtual table for all active transactions that generate or potentially access row versions. Transactions are included for one or more of the following conditions: When either or both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT database options are set to ON; When a trigger is fired, there is one row for the transaction under which the trigger is executing; When an online indexing procedure is running, there is one row for the transaction that is creating the index; When Multiple Active Results Sets (MARS) session is enabled, there is one row for each transaction that is accessing row versions.
sys.dm_tran_active_transactions
Returns information about transactions for the instance of SQL Server.
sys.dm_tran_current_snapshot
Returns a virtual table that displays all active transactions at the time when the current snapshot transaction starts. If the current transaction is not a snapshot transaction, this function returns no rows.
sys.dm_tran_current_transaction
Returns a single row that displays the state information of the transaction in the current session.
sys.dm_tran_database_transactions
Returns information about transactions at the database level.
sys.dm_tran_locks
Returns information about currently active lock manager resources in SQL Server 2014. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.
sys.dm_tran_session_transactions
Returns correlation information for associated transactions and sessions.
sys.dm_tran_top_version_generators
Returns a virtual table for the objects that are producing the most versions in the version store. sys.dm_tran_top_version_generators returns the top 256 aggregated record lengths that are grouped by the database_id and rowset_id. sys.dm_tran_top_version_generators retrieves data by querying the dm_tran_version_store virtual table. sys.dm_tran_top_version_generators is an inefficient view to run because this view queries the version store, and the version store can be very large. We recommend that you use this function to find the largest consumers of the version store.
sys.dm_tran_transactions_snapshot
Returns a virtual table for the sequence_number of transactions that are active when each snapshot transaction starts. The information that is returned by this view can you help you do the following: 1. Find the number of currently active snapshot transactions; 2. Identify data modifications that are ignored by a particular snapshot transaction. For a transaction that is active when a snapshot transaction starts, all data modifications by that transaction, even after that transaction commits, are ignored by the snapshot transaction.
sys.dm_tran_version_store
Returns a virtual table that displays all version records in the version store. sys.dm_tran_version_store is inefficient to run because it queries the entire version store, and the version store can be very large.