December 6, 2012

Transaction Related Dynamic Management Views and Functions

Se more view version maps here: Microsoft Sql System View Version Maps

sys.dm_tran_active_snapshot_database_transactions

In a SQL Server instance, this dynamic management view 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: There is one row for each transaction that is running under snapshot isolation level, or read-committed isolation level that is using row versioning. There is one row for each transaction that causes a row version to be created in the current database. For example, the transaction generates a row version by updating or deleting a row in the current database. 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. This dynamic management view does not include system transactions.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
transaction_id bigint Unique identification number assigned for the transaction. The transaction ID is primarily used to identify the transaction in locking operations.
transaction_sequence_num bigint Transaction sequence number. This is a unique sequence number that is assigned to a transaction when it starts. Transactions that do not generate version records and do not use snapshot scans will not receive a transaction sequence number. For more information, see Understanding Row Versioning-Based Isolation Levels1.
commit_sequence_num bigint Sequence number that indicates when the transaction finishes (commits or stops). For active transactions, the value is NULL.
is_snapshot int 0 = Is not a snapshot isolation transaction.
1 = Is a snapshot isolation transaction.
session_id int ID of the session that started the transaction.
first_snapshot_sequence_num bigint Lowest transaction sequence number of the transactions that were active when a snapshot was taken. On execution, a snapshot transaction takes a snapshot of all of the active transactions at that time. For nonsnapshot transactions, this column shows 0.
max_version_chain_traversed int Maximum length of the version chain that is traversed to find the transactionally consistent version.
average_version_chain_traversed real Average number of row versions in the version chains that are traversed.
elapsed_time_seconds bigint Elapsed time since the transaction obtained its transaction sequence number.

TSQL

Sql 2005
SELECT [transaction_id], [transaction_sequence_num], [commit_sequence_num], [is_snapshot], [session_id], [first_snapshot_sequence_num], [max_version_chain_traversed], [average_version_chain_traversed], [elapsed_time_seconds] FROM sys.dm_tran_active_snapshot_database_transactions
Sql 2008
SELECT [transaction_id], [transaction_sequence_num], [commit_sequence_num], [is_snapshot], [session_id], [first_snapshot_sequence_num], [max_version_chain_traversed], [average_version_chain_traversed], [elapsed_time_seconds] FROM sys.dm_tran_active_snapshot_database_transactions
Sql 2008 R2
SELECT [transaction_id], [transaction_sequence_num], [commit_sequence_num], [is_snapshot], [session_id], [first_snapshot_sequence_num], [max_version_chain_traversed], [average_version_chain_traversed], [elapsed_time_seconds] FROM sys.dm_tran_active_snapshot_database_transactions
Sql 2012
SELECT [transaction_id], [transaction_sequence_num], [commit_sequence_num], [is_snapshot], [session_id], [first_snapshot_sequence_num], [max_version_chain_traversed], [average_version_chain_traversed], [elapsed_time_seconds] FROM sys.dm_tran_active_snapshot_database_transactions

Back to Top


sys.dm_tran_active_transactions

Returns information about transactions for the instance of SQL Server.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
transaction_id bigint ID of the transaction at the instance level, not the database level. It is only unique across all databases within an instance but not unique across all server instances.
name nvarchar(64) Transaction name. This is overwritten if the transaction is marked and the marked name replaces the transaction name.
transaction_begin_time datetime Time that the transaction started.
transaction_type int Type of transaction.
1 = Read/write transaction
2 = Read-only transaction
3 = System transaction
4 = Distributed transaction
transaction_uow uniqueidentifier Transaction unit of work (UOW) identifier for distributed transactions. MS DTC uses the UOW identifier to work with the distributed transaction.
transaction_state int 0 = The transaction has not been completely initialized yet.
1 = The transaction has been initialized but has not started.
2 = The transaction is active.
3 = The transaction has ended. This is used for read-only transactions.
4 = The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.
5 = The transaction is in a prepared state and waiting resolution.
6 = The transaction has been committed.
7 = The transaction is being rolled back.
8 = The transaction has been rolled back.
transaction_status int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
transaction_status2 int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
dtc_state int 1 = ACTIVE
2 = PREPARED
3 = COMMITTED
4 = ABORTED
5 = RECOVERED
dtc_status int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
dtc_isolation_level int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
filestream_transaction_id     varbinary(128) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

TSQL

Sql 2005
SELECT [transaction_id], [name], [transaction_begin_time], [transaction_type], [transaction_uow], [transaction_state], [transaction_status], [transaction_status2], [dtc_state], [dtc_status], [dtc_isolation_level] FROM sys.dm_tran_active_transactions
Sql 2008
SELECT [transaction_id], [name], [transaction_begin_time], [transaction_type], [transaction_uow], [transaction_state], [transaction_status], [transaction_status2], [dtc_state], [dtc_status], [dtc_isolation_level] FROM sys.dm_tran_active_transactions
Sql 2008 R2
SELECT [transaction_id], [name], [transaction_begin_time], [transaction_type], [transaction_uow], [transaction_state], [transaction_status], [transaction_status2], [dtc_state], [dtc_status], [dtc_isolation_level], [filestream_transaction_id] FROM sys.dm_tran_active_transactions
Sql 2012
SELECT [transaction_id], [name], [transaction_begin_time], [transaction_type], [transaction_uow], [transaction_state], [transaction_status], [transaction_status2], [dtc_state], [dtc_status], [dtc_isolation_level], [filestream_transaction_id] FROM sys.dm_tran_active_transactions

Back to Top


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_snapshot is similar to sys.dm_tran_transactions_snapshot, except that sys.dm_tran_current_snapshot returns only the active transactions for the current snapshot transaction.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
transaction_sequence_num bigint Transaction sequence number of the active transaction.

TSQL

Sql 2005
SELECT [transaction_sequence_num] FROM sys.dm_tran_current_snapshot
Sql 2008
SELECT [transaction_sequence_num] FROM sys.dm_tran_current_snapshot
Sql 2008 R2
SELECT [transaction_sequence_num] FROM sys.dm_tran_current_snapshot
Sql 2012
SELECT [transaction_sequence_num] FROM sys.dm_tran_current_snapshot

Back to Top


sys.dm_tran_current_transaction

Returns a single row that displays the state information of the transaction in the current session.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
transaction_id bigint Transaction ID of the current snapshot.
transaction_sequence_num bigint Sequence number of the transaction that generates the record version.
transaction_is_snapshot bit Snapshot isolation state. This value is 1 if the transaction is started under snapshot isolation. Otherwise, the value is 0.
first_snapshot_sequence_num bigint Lowest transaction sequence number of the transactions that were active when a snapshot was taken. On execution, a snapshot transaction takes a snapshot of all of the active transactions at that time. For nonsnapshot transactions, this column shows 0.
last_transaction_sequence_num bigint Global sequence number. This value represents the last transaction sequence number that was generated by the system.
first_useful_sequence_num bigint Global sequence number. This value represents the oldest transaction sequence number of the transaction that has row versions that must be retained in the version store. Row versions that were created by prior transactions can be removed.

TSQL

Sql 2005
SELECT [transaction_id], [transaction_sequence_num], [transaction_is_snapshot], [first_snapshot_sequence_num], [last_transaction_sequence_num], [first_useful_sequence_num] FROM sys.dm_tran_current_transaction
Sql 2008
SELECT [transaction_id], [transaction_sequence_num], [transaction_is_snapshot], [first_snapshot_sequence_num], [last_transaction_sequence_num], [first_useful_sequence_num] FROM sys.dm_tran_current_transaction
Sql 2008 R2
SELECT [transaction_id], [transaction_sequence_num], [transaction_is_snapshot], [first_snapshot_sequence_num], [last_transaction_sequence_num], [first_useful_sequence_num] FROM sys.dm_tran_current_transaction
Sql 2012
SELECT [transaction_id], [transaction_sequence_num], [transaction_is_snapshot], [first_snapshot_sequence_num], [last_transaction_sequence_num], [first_useful_sequence_num] FROM sys.dm_tran_current_transaction

Back to Top


sys.dm_tran_database_transactions

Returns information about transactions at the database level.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
transaction_id bigint ID of the transaction at the instance level, not the database level. It is only unique across all databases within an instance, but not unique across all server instances.
database_id int ID of the database associated with the transaction.
database_transaction_begin_time datetime Time at which the database became involved in the transaction. Specifically, it is the time of the first log record in the database for the transaction.
database_transaction_type int 1 = Read/write transaction
2 = Read-only transaction
3 = System transaction
database_transaction_state int 1 = The transaction has not been initialized.
3 = The transaction has been initialized but has not generated any log records.
4 = The transaction has generated log records.
5 = The transaction has been prepared.
10 = The transaction has been committed.
11 = The transaction has been rolled back.
12 = The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.
database_transaction_status int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
database_transaction_status2 int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
database_transaction_log_record_count int Number of log records generated in the database for the transaction.
database_transaction_replicate_record_count int Number of log records generated in the database for the transaction that will be replicated.
database_transaction_log_bytes_used bigint Number of bytes used so far in the database log for the transaction.
database_transaction_log_bytes_reserved bigint Number of bytes reserved for use in the database log for the transaction.
database_transaction_log_bytes_used_system int Number of bytes used so far in the database log for system transactions on behalf of the transaction.
database_transaction_log_bytes_reserved_system int Number of bytes reserved for use in the database log for system transactions on behalf of the transaction.
database_transaction_begin_lsn numeric(25,0) Log sequence number (LSN) of the begin record for the transaction in the database log.
database_transaction_last_lsn numeric(25,0) LSN of the most recently logged record for the transaction in the database log.
database_transaction_most_recent_savepoint_lsn numeric(25,0) LSN of the most recent savepoint for the transaction in the database log.
database_transaction_commit_lsn numeric(25,0) LSN of the commit log record for the transaction in the database log.
database_transaction_last_rollback_lsn numeric(25,0) LSN that was most recently rolled back to. If no rollback has taken place, the value will be MaxLSN (-1:-1:-1).
database_transaction_next_undo_lsn numeric(25,0) LSN of the next record to undo.

TSQL

Sql 2005
SELECT [transaction_id], [database_id], [database_transaction_begin_time], [database_transaction_type], [database_transaction_state], [database_transaction_status], [database_transaction_status2], [database_transaction_log_record_count], [database_transaction_replicate_record_count], [database_transaction_log_bytes_used], [database_transaction_log_bytes_reserved], [database_transaction_log_bytes_used_system], [database_transaction_log_bytes_reserved_system], [database_transaction_begin_lsn], [database_transaction_last_lsn], [database_transaction_most_recent_savepoint_lsn], [database_transaction_commit_lsn], [database_transaction_last_rollback_lsn], [database_transaction_next_undo_lsn] FROM sys.dm_tran_database_transactions
Sql 2008
SELECT [transaction_id], [database_id], [database_transaction_begin_time], [database_transaction_type], [database_transaction_state], [database_transaction_status], [database_transaction_status2], [database_transaction_log_record_count], [database_transaction_replicate_record_count], [database_transaction_log_bytes_used], [database_transaction_log_bytes_reserved], [database_transaction_log_bytes_used_system], [database_transaction_log_bytes_reserved_system], [database_transaction_begin_lsn], [database_transaction_last_lsn], [database_transaction_most_recent_savepoint_lsn], [database_transaction_commit_lsn], [database_transaction_last_rollback_lsn], [database_transaction_next_undo_lsn] FROM sys.dm_tran_database_transactions
Sql 2008 R2
SELECT [transaction_id], [database_id], [database_transaction_begin_time], [database_transaction_type], [database_transaction_state], [database_transaction_status], [database_transaction_status2], [database_transaction_log_record_count], [database_transaction_replicate_record_count], [database_transaction_log_bytes_used], [database_transaction_log_bytes_reserved], [database_transaction_log_bytes_used_system], [database_transaction_log_bytes_reserved_system], [database_transaction_begin_lsn], [database_transaction_last_lsn], [database_transaction_most_recent_savepoint_lsn], [database_transaction_commit_lsn], [database_transaction_last_rollback_lsn], [database_transaction_next_undo_lsn] FROM sys.dm_tran_database_transactions
Sql 2012
SELECT [transaction_id], [database_id], [database_transaction_begin_time], [database_transaction_type], [database_transaction_state], [database_transaction_status], [database_transaction_status2], [database_transaction_log_record_count], [database_transaction_replicate_record_count], [database_transaction_log_bytes_used], [database_transaction_log_bytes_reserved], [database_transaction_log_bytes_used_system], [database_transaction_log_bytes_reserved_system], [database_transaction_begin_lsn], [database_transaction_last_lsn], [database_transaction_most_recent_savepoint_lsn], [database_transaction_commit_lsn], [database_transaction_last_rollback_lsn], [database_transaction_next_undo_lsn] FROM sys.dm_tran_database_transactions

Back to Top


sys.dm_tran_locks

Returns information about currently active lock manager resources in SQL Server 2012. 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.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
resource_type nvarchar(120) Represents the resource type. The value can be one of the following: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT, or ALLOCATION_UNIT.
resource_subtype nvarchar(120) Represents a subtype of resource_type. Acquiring a subtype lock without holding a nonsubtyped lock of the parent type is technically valid. Different subtypes do not conflict with each other or with the nonsubtyped parent type. Not all resource types have subtypes.
resource_database_id int ID of the database under which this resource is scoped. All resources handled by the lock manager are scoped by the database ID.
resource_description nvarchar(512) Description of the resource that contains only information that is not available from other resource columns.
resource_associated_entity_id bigint ID of the entity in a database with which a resource is associated. This can be an object ID, Hobt ID, or an Allocation Unit ID, depending on the resource type.
resource_lock_partition int ID of the lock partition for a partitioned lock resource. The value for nonpartitioned lock resources is 0.
request_mode nvarchar(120) Mode of the request. For granted requests, this is the granted mode; for waiting requests, this is the mode being requested.
request_type nvarchar(120) Request type. The value is LOCK.
request_status nvarchar(120) Current status of this request. Possible values are GRANTED, CONVERT, or WAIT.
request_reference_count smallint Returns an approximate number of times the same requestor has requested this resource.
request_lifetime int Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
request_session_id int Session ID that currently owns this request. The owning session ID can change for distributed and bound transactions. A value of -2 indicates that the request belongs to an orphaned distributed transaction. A value of -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.
request_exec_context_id int Execution context ID of the process that currently owns this request.
request_request_id int Request ID (batch ID) of the process that currently owns this request. This value will change every time that the active Multiple Active Result Set (MARS) connection for a transaction changes.
request_owner_type nvarchar(120) Entity type that owns the request. Lock manager requests can be owned by a variety of entities. Possible values are:
TRANSACTION = The request is owned by a transaction.
CURSOR = The request is owned by a cursor.
SESSION = The request is owned by a user session.
SHARED_TRANSACTION_WORKSPACE = The request is owned by the shared part of the transaction workspace.
EXCLUSIVE_TRANSACTION_WORKSPACE = The request is owned by the exclusive part of the transaction workspace.
request_owner_id bigint ID of the specific owner of this request. This value is only used for transactions for which this is the transaction ID.
request_owner_guid uniqueidentifier GUID of the specific owner of this request. This value is only used by a distributed transaction where the value corresponds to the MS DTC GUID for that transaction.
request_owner_lockspace_id nvarchar(64) Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. This value represents the lockspace ID of the requestor. The lockspace ID determines whether two requestors are compatible with each other and can be granted locks in modes that would otherwise conflict with one another.
lock_owner_address varbinary(8) Memory address of the internal data structure that is used to track this request. This column can be joined the with resource_address column in sys.dm_os_waiting_tasks.

TSQL

Sql 2005
SELECT [resource_type], [resource_subtype], [resource_database_id], [resource_description], [resource_associated_entity_id], [resource_lock_partition], [request_mode], [request_type], [request_status], [request_reference_count], [request_lifetime], [request_session_id], [request_exec_context_id], [request_request_id], [request_owner_type], [request_owner_id], [request_owner_guid], [request_owner_lockspace_id], [lock_owner_address] FROM sys.dm_tran_locks
Sql 2008
SELECT [resource_type], [resource_subtype], [resource_database_id], [resource_description], [resource_associated_entity_id], [resource_lock_partition], [request_mode], [request_type], [request_status], [request_reference_count], [request_lifetime], [request_session_id], [request_exec_context_id], [request_request_id], [request_owner_type], [request_owner_id], [request_owner_guid], [request_owner_lockspace_id], [lock_owner_address] FROM sys.dm_tran_locks
Sql 2008 R2
SELECT [resource_type], [resource_subtype], [resource_database_id], [resource_description], [resource_associated_entity_id], [resource_lock_partition], [request_mode], [request_type], [request_status], [request_reference_count], [request_lifetime], [request_session_id], [request_exec_context_id], [request_request_id], [request_owner_type], [request_owner_id], [request_owner_guid], [request_owner_lockspace_id], [lock_owner_address] FROM sys.dm_tran_locks
Sql 2012
SELECT [resource_type], [resource_subtype], [resource_database_id], [resource_description], [resource_associated_entity_id], [resource_lock_partition], [request_mode], [request_type], [request_status], [request_reference_count], [request_lifetime], [request_session_id], [request_exec_context_id], [request_request_id], [request_owner_type], [request_owner_id], [request_owner_guid], [request_owner_lockspace_id], [lock_owner_address] FROM sys.dm_tran_locks

Back to Top


sys.dm_tran_session_transactions

Returns correlation information for associated transactions and sessions.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
session_id int ID of the session under which the transaction is running.
transaction_id bigint ID of the transaction.
transaction_descriptor binary(8) Transaction identifier used by SQL Server when communicating with the client driver.
enlist_count int Number of active requests in the session working on the transaction.
is_user_transaction bit 1 = The transaction was initiated by a user request.
0 = System transaction.
is_local bit 1 = Local transaction.
0 = Distributed transaction or an enlisted bound session transaction.
is_enlisted bit 1 = Enlisted distributed transaction.
0 = Not an enlisted distributed transaction.
is_bound bit 1 = The transaction is active on the session via bound sessions.
0 = The transaction is not active on the session via bound sessions.

TSQL

Sql 2005
SELECT [session_id], [transaction_id], [transaction_descriptor], [enlist_count], [is_user_transaction], [is_local], [is_enlisted], [is_bound] FROM sys.dm_tran_session_transactions
Sql 2008
SELECT [session_id], [transaction_id], [transaction_descriptor], [enlist_count], [is_user_transaction], [is_local], [is_enlisted], [is_bound] FROM sys.dm_tran_session_transactions
Sql 2008 R2
SELECT [session_id], [transaction_id], [transaction_descriptor], [enlist_count], [is_user_transaction], [is_local], [is_enlisted], [is_bound] FROM sys.dm_tran_session_transactions
Sql 2012
SELECT [session_id], [transaction_id], [transaction_descriptor], [enlist_count], [is_user_transaction], [is_local], [is_enlisted], [is_bound] FROM sys.dm_tran_session_transactions

Back to Top


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.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
database_id int Database ID.
rowset_id bigint Rowset ID.
aggregated_record_length_in_bytes int Sum of the record lengths for each database_id and rowset_id pair in the version store.

TSQL

Sql 2005
SELECT [database_id], [rowset_id], [aggregated_record_length_in_bytes] FROM sys.dm_tran_top_version_generators
Sql 2008
SELECT [database_id], [rowset_id], [aggregated_record_length_in_bytes] FROM sys.dm_tran_top_version_generators
Sql 2008 R2
SELECT [database_id], [rowset_id], [aggregated_record_length_in_bytes] FROM sys.dm_tran_top_version_generators
Sql 2012
SELECT [database_id], [rowset_id], [aggregated_record_length_in_bytes] FROM sys.dm_tran_top_version_generators

Back to Top


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: Find the number of currently active snapshot transactions. 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. For example, consider the following output from sys.dm_tran_transactions_snapshot: transaction_sequence_num snapshot_id snapshot_sequence_num ------------------------ ----------- --------------------- 59 0 57 59 0 58 60 0 57 60 0 58 60 0 59 60 3 57 60 3 58 60 3 59 60 3 60 The transaction_sequence_num column identifies the transaction sequence (XSN) number of the current snapshot transactions. The output shows two: 59 and 60. The snapshot_sequence_num column identifies the transaction sequence number of the transactions that are active when each snapshot transaction starts. The output shows that snapshot transaction XSN-59 starts while two active transactions, XSN-57 and XSN-58, are running. If XSN-57 or XSN-58 makes data modifications, XSN-59 ignores the changes and uses row versioning to maintain a transactionally consistent view of the database. Snapshot transaction XSN-60 ignores data modifications made by XSN-57 and XSN-58 and also XSN 59.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
transaction_sequence_num bigint Transaction sequence number (XSN) of a snapshot transaction.
snapshot_id int Snapshot ID for each Transact-SQL statement started under read-committed using row versioning. This value is used to generate a transactionally consistent view of the database supporting each query that is being run under read-committed using row versioning.
snapshot_sequence_num bigint Transaction sequence number of a transaction that was active when the snapshot transaction started.

TSQL

Sql 2005
SELECT [transaction_sequence_num], [snapshot_id], [snapshot_sequence_num] FROM sys.dm_tran_transactions_snapshot
Sql 2008
SELECT [transaction_sequence_num], [snapshot_id], [snapshot_sequence_num] FROM sys.dm_tran_transactions_snapshot
Sql 2008 R2
SELECT [transaction_sequence_num], [snapshot_id], [snapshot_sequence_num] FROM sys.dm_tran_transactions_snapshot
Sql 2012
SELECT [transaction_sequence_num], [snapshot_id], [snapshot_sequence_num] FROM sys.dm_tran_transactions_snapshot

Back to Top


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. Each versioned record is stored as binary data together with some tracking or status information. Similar to records in database tables, version-store records are stored in 8192-byte pages. If a record exceeds 8192 bytes, the record will be split across two different records. Because the versioned record is stored as binary, there are no problems with different collations from different databases. Use sys.dm_tran_version_store to find the previous versions of the rows in binary representation as they exist in the version store.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
transaction_sequence_num bigint Sequence number of the transaction that generates the record version.
version_sequence_num bigint Version record sequence number. This value is unique within the version-generating transaction.
database_id int Database ID of the versioned record.
rowset_id bigint Rowset ID of the record.
status tinyint Indicates whether a versioned record has been split across two records. If the value is 0, the record is stored in one page. If the value is 1, the record is split into two records that are stored on two different pages.
min_length_in_bytes smallint Minimum length of the record in bytes.
record_length_first_part_in_bytes smallint Length of the first part of the versioned record in bytes.
record_image_first_part varbinary(8000) Binary image of the first part of version record.
record_length_second_part_in_bytes smallint Length of the second part of version record in bytes.
record_image_second_part varbinary(8000) Binary image of the second part of the version record.

TSQL

Sql 2005
SELECT [transaction_sequence_num], [version_sequence_num], [database_id], [rowset_id], [status], [min_length_in_bytes], [record_length_first_part_in_bytes], [record_image_first_part], [record_length_second_part_in_bytes], [record_image_second_part] FROM sys.dm_tran_version_store
Sql 2008
SELECT [transaction_sequence_num], [version_sequence_num], [database_id], [rowset_id], [status], [min_length_in_bytes], [record_length_first_part_in_bytes], [record_image_first_part], [record_length_second_part_in_bytes], [record_image_second_part] FROM sys.dm_tran_version_store
Sql 2008 R2
SELECT [transaction_sequence_num], [version_sequence_num], [database_id], [rowset_id], [status], [min_length_in_bytes], [record_length_first_part_in_bytes], [record_image_first_part], [record_length_second_part_in_bytes], [record_image_second_part] FROM sys.dm_tran_version_store
Sql 2012
SELECT [transaction_sequence_num], [version_sequence_num], [database_id], [rowset_id], [status], [min_length_in_bytes], [record_length_first_part_in_bytes], [record_image_first_part], [record_length_second_part_in_bytes], [record_image_second_part] FROM sys.dm_tran_version_store

Back to Top

SQL Server Operating System Related Dynamic Management Views

Se more view version maps here: Microsoft Sql System View Version Maps

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. When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server. sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. This includes pages that are associated with the Resource database.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
database_id int ID of database associated with the page in the buffer pool. Is nullable.
file_id int ID of the file that stores the persisted image of the page. Is nullable
page_id int ID of the page within the file. Is nullable.
page_level int Index level of the page. Is nullable
allocation_unit_id bigint ID of the allocation unit of the page. This value can be used to join sys.allocation_units. Is nullable.
Note sys.dm_os_buffer_descriptors might show nonexistent values in allocation_unit_id for clustered indexes that are created in versions of SQL Server earlier than SQL Server 2005.
page_type nvarchar(60) Type of the page, such as: Data page or Index page. Is nullable. For more information, see Pages and Extents1.
row_count int Number of rows on the page. Is nullable.
free_space_in_bytes int Amount of available free space, in bytes, on the page. Is nullable.
is_modified bit 1 = Page has been modified after it was read from the disk. Is nullable.
numa_mode       int Nonuniform Memory Access node for the buffer.
numa_node     int Nonuniform Memory Access node for the buffer.
read_microsec       bigint The actual time (in microseconds) required to read the page into the buffer. This number is reset when the buffer is reused. Is nullable.

TSQL

Sql 2005
SELECT [database_id], [file_id], [page_id], [page_level], [allocation_unit_id], [page_type], [row_count], [free_space_in_bytes], [is_modified] FROM sys.dm_os_buffer_descriptors
Sql 2008
SELECT [database_id], [file_id], [page_id], [page_level], [allocation_unit_id], [page_type], [row_count], [free_space_in_bytes], [is_modified], [numa_mode] FROM sys.dm_os_buffer_descriptors
Sql 2008 R2
SELECT [database_id], [file_id], [page_id], [page_level], [allocation_unit_id], [page_type], [row_count], [free_space_in_bytes], [is_modified], [numa_node] FROM sys.dm_os_buffer_descriptors
Sql 2012
SELECT [database_id], [file_id], [page_id], [page_level], [allocation_unit_id], [page_type], [row_count], [free_space_in_bytes], [is_modified], [numa_node], [read_microsec] FROM sys.dm_os_buffer_descriptors

Back to Top


sys.dm_os_child_instances

user instance that has been created from the parent server instance. Important This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. The information returned from sys.dm_os_child_instances can be used to determine the state of each User Instance (heart_beat) and to obtain the pipe name (instance_pipe_name) that can be used to create a connection to the User Instance using SQL Server Management Studio or SQLCmd. You can only connect to a User Instance after it has been started by an external process, such as a client application. SQL management tools cannot start a User Instance. Note User Instances are a feature of SQL Server 2012 Express only. ColumnData typeDescription owning_principal_name nvarchar(256) The name of the user that this user instance was created for. owning_principal_sidnvarchar(256)SID (Security-Identifier) of the principal who owns this user instance. This matches Windows SID. owning_principal_sid_binary varbinary(85)Binary version of the SID for the user who owns the user Instance instance_name nvarchar(128) The name of this user instance. instance_pipe_name nvarchar(260) When a user instance is created, a named pipe is created for applications to connect to. This name can be used in a connect string to connect to this user instance. os_process_id Int The process number of the Windows process for this user instance. os_process_creation_date Datetime The date and time when this user instance process was last started. heart_beat nvarchar(5) Current state of this user instance; either ALIVE or DEAD.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
owning_principal_name nvarchar(256) The name of the user that this user instance was created for.
owning_principal_sid nvarchar(256) SID (Security-Identifier) of the principal who owns this user instance. This matches Windows SID.
owning_principal_sid_binary_ varbinary(85) Binary version of the SID for the user who owns the user Instance
instance_name nvarchar(128) The name of this user instance.
instance_pipe_name nvarchar(260) When a user instance is created, a named pipe is created for applications to connect to. This name can be used in a connect string to connect to this user instance.
os_process_id int The process number of the Windows process for this user instance.
os_process_creation_date datetime The date and time when this user instance process was last started.
heart_beat nvarchar(5) Current state of this user instance; either ALIVE or DEAD.

TSQL

Sql 2005
SELECT [owning_principal_name], [owning_principal_sid], [owning_principal_sid_binary_], [instance_name], [instance_pipe_name], [os_process_id], [os_process_creation_date], [heart_beat] FROM sys.dm_os_child_instances
Sql 2008
SELECT [owning_principal_name], [owning_principal_sid], [owning_principal_sid_binary_], [instance_name], [instance_pipe_name], [os_process_id], [os_process_creation_date], [heart_beat] FROM sys.dm_os_child_instances
Sql 2008 R2
SELECT [owning_principal_name], [owning_principal_sid], [owning_principal_sid_binary_], [instance_name], [instance_pipe_name], [os_process_id], [os_process_creation_date], [heart_beat] FROM sys.dm_os_child_instances
Sql 2012
SELECT [owning_principal_name], [owning_principal_sid], [owning_principal_sid_binary_], [instance_name], [instance_pipe_name], [os_process_id], [os_process_creation_date], [heart_beat] FROM sys.dm_os_child_instances

Back to Top


sys.dm_os_cluster_nodes

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.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
nodename sysname Name of a node in the SQL Server failover cluster instance (virtual server) configuration.
status       int Status of the node in a SQL Server failover cluster instance. For more information, see GetClusterNodeState Function1.
0
1
2
3
-1
status_description       nvarchar(20) Description of the status of the SQL Server failover cluster node.
0 = up
1 = down
2 = paused
3 = joining
-1 = unknown
is_current_owner       bit 1 means this node is the current owner of the SQL Server failover cluster resource.

TSQL

Sql 2005
SELECT [nodename] FROM sys.dm_os_cluster_nodes
Sql 2008
SELECT [nodename] FROM sys.dm_os_cluster_nodes
Sql 2008 R2
SELECT [nodename] FROM sys.dm_os_cluster_nodes
Sql 2012
SELECT [nodename], [status], [status_description], [is_current_owner] FROM sys.dm_os_cluster_nodes

Back to Top


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.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
host_address varbinary(8) Internal memory address of the host object.
type nvarchar(60) Type of hosted component. For example,
SOSHOST_CLIENTID_SERVERSNI= SQL Native Client Interface
SOSHOST_CLIENTID_SQLOLEDB = SQL Native Client OLE DB Provider
SOSHOST_CLIENTID_MSDART = Microsoft Data Access Run Time
name nvarchar(32) Name of the host.
enqueued_tasks_count int Total number of tasks that this host has placed onto queues in SQL Server.
active_tasks_count int Number of currently running tasks that this host has placed onto queues.
completed_ios_count int Total number of I/Os issued and completed through this host.
completed_ios_in_bytes bigint Total byte count of the I/Os completed through this host.
active_ios_count int Total number of I/O requests related to this host that are currently waiting to complete.
default_memory_clerk_address varbinary(8) Memory address of the memory clerk object associated with this host. For more information, see sys.dm_os_memory_clerks1.

TSQL

Sql 2005
SELECT [host_address], [type], [name], [enqueued_tasks_count], [active_tasks_count], [completed_ios_count], [completed_ios_in_bytes], [active_ios_count], [default_memory_clerk_address] FROM sys.dm_os_hosts
Sql 2008
SELECT [host_address], [type], [name], [enqueued_tasks_count], [active_tasks_count], [completed_ios_count], [completed_ios_in_bytes], [active_ios_count], [default_memory_clerk_address] FROM sys.dm_os_hosts
Sql 2008 R2
SELECT [host_address], [type], [name], [enqueued_tasks_count], [active_tasks_count], [completed_ios_count], [completed_ios_in_bytes], [active_ios_count], [default_memory_clerk_address] FROM sys.dm_os_hosts
Sql 2012
SELECT [host_address], [type], [name], [enqueued_tasks_count], [active_tasks_count], [completed_ios_count], [completed_ios_in_bytes], [active_ios_count], [default_memory_clerk_address] FROM sys.dm_os_hosts

Back to Top


sys.dm_os_latch_stats

Returns information about all latch waits organized by class.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
latch_class nvarchar(120 ) Name of the latch class.
waiting_requests_count bigint Number of waits on latches in this class. This counter is incremented at the start of a latch wait.
wait_time_ms bigint Total wait time, in milliseconds, on latches in this class.
Note: This column is updated every five minutes during a latch wait and at the end of a latch wait.
max_wait_time_ms bigint Maximum time a memory object has waited on this latch. If this value is unusually high, it might indicate an internal deadlock.

TSQL

Sql 2005
SELECT [latch_class], [waiting_requests_count], [wait_time_ms], [max_wait_time_ms] FROM sys.dm_os_latch_stats
Sql 2008
SELECT [latch_class], [waiting_requests_count], [wait_time_ms], [max_wait_time_ms] FROM sys.dm_os_latch_stats
Sql 2008 R2
SELECT [latch_class], [waiting_requests_count], [wait_time_ms], [max_wait_time_ms] FROM sys.dm_os_latch_stats
Sql 2012
SELECT [latch_class], [waiting_requests_count], [wait_time_ms], [max_wait_time_ms] FROM sys.dm_os_latch_stats

Back to Top


sys.dm_os_loaded_modules

module loaded into the server address space.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
base_address varbinary(8) Address of the module in the process.
file_version varchar(23) Version of the file. Appears in the following format:
x.x:x.x
product_version varchar(23) Version of the product. Appears in the following format:
x.x:x.x
debug bit 1 = Module is a debug version of the loaded module.
patched bit 1 = Module has been patched.
prerelease bit 1 = Module is a pre-release version of the loaded module.
private_build bit 1 = Module is a private build of the loaded module.
special_build bit 1 = Module is a special build of the loaded module.
language int Language of version information of the module.
company nvarchar(256) Name of company that created the module.
description nvarchar(256) Description of the module.
name nvarchar(255) Name of module. Includes the full path of the module.

TSQL

Sql 2005
SELECT [base_address], [file_version], [product_version], [debug], [patched], [prerelease], [private_build], [special_build], [language], [company], [description], [name] FROM sys.dm_os_loaded_modules
Sql 2008
SELECT [base_address], [file_version], [product_version], [debug], [patched], [prerelease], [private_build], [special_build], [language], [company], [description], [name] FROM sys.dm_os_loaded_modules
Sql 2008 R2
SELECT [base_address], [file_version], [product_version], [debug], [patched], [prerelease], [private_build], [special_build], [language], [company], [description], [name] FROM sys.dm_os_loaded_modules
Sql 2012
SELECT [base_address], [file_version], [product_version], [debug], [patched], [prerelease], [private_build], [special_build], [language], [company], [description], [name] FROM sys.dm_os_loaded_modules

Back to Top


sys.dm_os_memory_cache_clock_hands

Returns the status of each hand for a specific cache clock.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
cache_address varbinary(8) Address of the cache associated with the clock. Is not nullable.
name nvarchar(256) Name of the cache. Is not nullable.
type nvarchar(60) Type of cache store. There can be several caches of the same type. Is not nullable.
clock_hand nvarchar(60) Type of hand. This is one of the following:
External

Internal

Is not nullable.
clock_status nvarchar(60) Status of the clock. This is one of the following:
Suspended

Running

Is not nullable.
rounds_count bigint Number of sweeps made through the cache to remove entries. Is not nullable.
removed_all_rounds_count bigint Number of entries removed by all sweeps. Is not nullable.
updated_last_round_count bigint Number of entries updated during the last sweep. Is not nullable.
removed_last_round_count bigint Number of entries removed during the last sweep. Is not nullable.
last_tick_time bigint Last time, in CPU ticks, that the clock hand moved. Is not nullable.
round_start_time bigint Time, in CPU ticks, of the previous sweep. Is not nullable.
last_round_start_time bigint Total time, in CPU ticks, taken by the clock to complete the previous round. Is not nullable.

TSQL

Sql 2005
SELECT [cache_address], [name], [type], [clock_hand], [clock_status], [rounds_count], [removed_all_rounds_count], [updated_last_round_count], [removed_last_round_count], [last_tick_time], [round_start_time], [last_round_start_time] FROM sys.dm_os_memory_cache_clock_hands
Sql 2008
SELECT [cache_address], [name], [type], [clock_hand], [clock_status], [rounds_count], [removed_all_rounds_count], [updated_last_round_count], [removed_last_round_count], [last_tick_time], [round_start_time], [last_round_start_time] FROM sys.dm_os_memory_cache_clock_hands
Sql 2008 R2
SELECT [cache_address], [name], [type], [clock_hand], [clock_status], [rounds_count], [removed_all_rounds_count], [updated_last_round_count], [removed_last_round_count], [last_tick_time], [round_start_time], [last_round_start_time] FROM sys.dm_os_memory_cache_clock_hands
Sql 2012
SELECT [cache_address], [name], [type], [clock_hand], [clock_status], [rounds_count], [removed_all_rounds_count], [updated_last_round_count], [removed_last_round_count], [last_tick_time], [round_start_time], [last_round_start_time] FROM sys.dm_os_memory_cache_clock_hands

Back to Top


sys.dm_os_memory_cache_counters

Returns a snapshot of the health of a cache in SQL Server 2012. 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.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
cache_address varbinary(8) Address (primary key) of the counters associated with a specific cache. Is not nullable.
name nvarchar(256) Name of the cache. Is not nullable.
type nvarchar(60) Type of cache that is associated with this entry. Is not nullable.
single_pages_kb   bigint Amount, in kilobytes, of the single-page memory allocated. This is the amount of memory allocated by using the single-page allocator. This refers to the 8-KB pages that are taken directly from the buffer pool for this cache. Is not nullable.
multi_pages_kb   bigint Amount, in kilobytes, of the multipage memory allocated. This is the amount of memory allocated by using the multiple-page allocator of the memory node. This memory is allocated outside the buffer pool and takes advantage of the virtual allocator of the memory nodes. Is not nullable.
single_pages_in_use_kb   bigint Amount, in kilobytes, of the single-page memory that is being used. Is nullable. This information is not tracked for objects of type USERSTORE_<*> and these values will be NULL.
multi_pages_in_use_kb   bigint Amount, in kilobytes, of the multipage memory that is being used. NULLABLE. This information is not tracked for objects of type USERSTORE_<*>, and these values will be NULL.
entries_count bigint Number of entries in the cache. Is not nullable.
entries_in_use_count bigint Number of entries in the cache that is being used. Is not nullable.
pages_kb       bigint Specifies the amount, in kilobytes, of the memory allocated in the cache. Is not nullable.
pages_in_use_kb       bigint Specifies the amount, in kilobytes, of the memory that is allocated and in use in the cache. Is nullable. Values for objects of type USERSTORE_<*> are not tracked. NULL is reported for them.

TSQL

Sql 2005
SELECT [cache_address], [name], [type], [single_pages_kb], [multi_pages_kb], [single_pages_in_use_kb], [multi_pages_in_use_kb], [entries_count], [entries_in_use_count] FROM sys.dm_os_memory_cache_counters
Sql 2008
SELECT [cache_address], [name], [type], [single_pages_kb], [multi_pages_kb], [single_pages_in_use_kb], [multi_pages_in_use_kb], [entries_count], [entries_in_use_count] FROM sys.dm_os_memory_cache_counters
Sql 2008 R2
SELECT [cache_address], [name], [type], [single_pages_kb], [multi_pages_kb], [single_pages_in_use_kb], [multi_pages_in_use_kb], [entries_count], [entries_in_use_count] FROM sys.dm_os_memory_cache_counters
Sql 2012
SELECT [cache_address], [name], [type], [pages_kb], [pages_in_use_kb], [entries_count], [entries_in_use_count] FROM sys.dm_os_memory_cache_counters

Back to Top


sys.dm_os_memory_cache_entries

Returns information about all entries in caches in SQL Server 2012. Use this view to trace cache entries to their associated objects. You can also use this view to obtain statistics on cache entries.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
cache_address varbinary(8) Address of the cache. Is not nullable.
name nvarchar(256) Name of the cache. Is not nullable.
type varchar(60) Type of cache. Is not nullable.
in_use_count int Number of concurrent users of this cache entry. Is not nullable.
is_dirty bit 1 = This cache entry has changes that have not been persisted.
Is not nullable.
entry_address varbinary(8) Address of the descriptor of the cache entry. Is not nullable.
entry_data_address varbinary(8) Address of the user data in the cache entry.
0x00000000 = Entry data address is not available.
Is not nullable.
disk_ios_count int Number of I/Os incurred because of this entry. Is not nullable.
context_switches_count int Number of context switches incurred because of this entry. Is not nullable.
original_cost int Original cost of the entry. This value is an approximation of the number of I/Os incurred, CPU instruction cost, and the context switch count. The greater the cost, the lower the chance that the item will be removed from the cache. Is not nullable.
current_cost int Current cost of the cache entry. This value is updated during the process of entry purging. Current cost is reset to its original value on entry reuse. Is not nullable.
memory_object_address varbinary(8) Address of the associated memory object. Is nullable.
pages_allocated_count   bigint Number of 8-KB pages to store this cache entry. Is not nullable.
entry_data nvarchar(2048) Serialized representation of the cached entry. This information is cache implementation dependant. Is nullable.
pool_id     int The ID of the resource pool.
pages_kb       bigint Amount of memory in kilobytes (KB) used by this cache entry. Is not nullable.

TSQL

Sql 2005
SELECT [cache_address], [name], [type], [in_use_count], [is_dirty], [entry_address], [entry_data_address], [disk_ios_count], [context_switches_count], [original_cost], [current_cost], [memory_object_address], [pages_allocated_count], [entry_data] FROM sys.dm_os_memory_cache_entries
Sql 2008
SELECT [cache_address], [name], [type], [in_use_count], [is_dirty], [entry_address], [entry_data_address], [disk_ios_count], [context_switches_count], [original_cost], [current_cost], [memory_object_address], [pages_allocated_count], [entry_data] FROM sys.dm_os_memory_cache_entries
Sql 2008 R2
SELECT [cache_address], [name], [type], [entry_address], [entry_data_address], [in_use_count], [is_dirty], [disk_ios_count], [context_switches_count], [original_cost], [current_cost], [memory_object_address], [pages_allocated_count], [entry_data], [pool_id] FROM sys.dm_os_memory_cache_entries
Sql 2012
SELECT [cache_address], [name], [type], [entry_address], [entry_data_address], [in_use_count], [is_dirty], [disk_ios_count], [context_switches_count], [original_cost], [current_cost], [memory_object_address], [pages_kb], [entry_data], [pool_id] FROM sys.dm_os_memory_cache_entries

Back to Top


sys.dm_os_memory_cache_hash_tables

active cache in the instance of SQL Server.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
cache_address varbinary(8) Address (primary key) of the cache entry. Is not nullable.
name nvarchar(256) Name of the cache. Is not nullable.
type nvarchar(60) Type of cache. Is not nullable.
table_level int Hash table number. A particular cache may have multiple hash tables that correspond to different hash functions. Is not nullable.
buckets_count int Number of buckets in the hash table. Is not nullable.
buckets_in_use_count int Number of buckets that are currently being used. Is not nullable.
buckets_min_length int Minimum number of cache entries in a bucket. Is not nullable.
buckets_max_length int Maximum number of cache entries in a bucket. Is not nullable.
buckets_avg_length int Average number of cache entries in each bucket. Is not nullable.
buckets_max_length_ever int Maximum number of cached entries in a hash bucket for this hash table since the server was started. Is not nullable.
hits_count bigint Number of cache hits. Is not nullable.
misses_count bigint Number of cache misses. Is not nullable.
buckets_avg_scan_hit_length int Average number of examined entries in a bucket before the searched for an item was found. Is not nullable.
buckets_avg_scan_miss_length int Average number of examined entries in a bucket before the search ended unsuccessfully. Is not nullable.

TSQL

Sql 2005
SELECT [cache_address], [name], [type], [table_level], [buckets_count], [buckets_in_use_count], [buckets_min_length], [buckets_max_length], [buckets_avg_length], [buckets_max_length_ever], [hits_count], [misses_count], [buckets_avg_scan_hit_length], [buckets_avg_scan_miss_length] FROM sys.dm_os_memory_cache_hash_tables
Sql 2008
SELECT [cache_address], [name], [type], [table_level], [buckets_count], [buckets_in_use_count], [buckets_min_length], [buckets_max_length], [buckets_avg_length], [buckets_max_length_ever], [hits_count], [misses_count], [buckets_avg_scan_hit_length], [buckets_avg_scan_miss_length] FROM sys.dm_os_memory_cache_hash_tables
Sql 2008 R2
SELECT [cache_address], [name], [type], [table_level], [buckets_count], [buckets_in_use_count], [buckets_min_length], [buckets_max_length], [buckets_avg_length], [buckets_max_length_ever], [hits_count], [misses_count], [buckets_avg_scan_hit_length], [buckets_avg_scan_miss_length] FROM sys.dm_os_memory_cache_hash_tables
Sql 2012
SELECT [cache_address], [name], [type], [table_level], [buckets_count], [buckets_in_use_count], [buckets_min_length], [buckets_max_length], [buckets_avg_length], [buckets_max_length_ever], [hits_count], [misses_count], [buckets_avg_scan_hit_length], [buckets_avg_scan_miss_length] FROM sys.dm_os_memory_cache_hash_tables

Back to Top


sys.dm_os_memory_clerks

Returns the set of all memory clerks that are currently active in the instance of SQL Server.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
memory_clerk_address varbinary(8) Unique memory address of the memory clerk. This is the primary key column. Is not nullable.
type nvarchar(60) Type of memory clerk. Every clerk has a specific type, such as CLR Clerks MEMORYCLERK_SQLCLR. Is not nullable.
name nvarchar(256) Internally assigned name of this memory clerk. Every component can have several memory clerks of a specific type. A component might choose to use specific names to identify memory clerks of the same type. Is not nullable.
memory_node_id smallint ID of the memory node. Is not nullable.
single_pages_kb   bigint Amount of single page memory allocated in kilobytes (KB). This is the amount of memory allocated by using the single page allocator of a memory node. This single page allocator steals pages directly from the buffer pool. Is not nullable.
multi_pages_kb   bigint Amount of multipage memory allocated in KB. This is the amount of memory allocated by using the multiple page allocator of the memory nodes. This memory is allocated outside the buffer pool and takes advantage of the virtual allocator of the memory nodes. Is not nullable.
virtual_memory_reserved_kb bigint Amount of virtual memory that is reserved by a memory clerk. This is the amount of memory reserved directly by the component that uses this clerk. In most situations, only the buffer pool reserves virtual address space directly by using its memory clerk. Is not nullable.
virtual_memory_committed_kb bigint Amount of virtual memory that is committed by a memory clerk. This is the amount of memory committed by the clerk. The amount of committed memory should always be less than the amount of reserved memory. Not NULLABLE.
awe_allocated_kb bigint Amount of memory that is allocated by the memory clerk by using Address Windowing Extensions (AWE). In SQL Server, only buffer pool clerks (MEMORYCLERK_SQLBUFFERPOOL) use this mechanism, and only when AWE is enabled. Is not nullable.
shared_memory_reserved_kb bigint Amount of shared memory that is reserved by a memory clerk. The amount of memory reserved for use by shared memory and file mapping. Is not nullable.
shared_memory_committed_kb bigint Amount of shared memory that is committed by the memory clerk. Is not nullable.
page_size_bytes   bigint Size of the page that can be allocated by a memory clerk. Only one size, 8192 bytes, is supported. Is not nullable.
page_allocator_address varbinary(8) Address of the page allocator. This address is unique for a memory clerk and can be used in sys.dm_os_memory_objects to locate memory objects that are bound to this clerk. Is not nullable.
host_address varbinary(8) Memory address of the host for this memory clerk. For more information, see sys.dm_os_hosts1. Components, such as Microsoft SQL Native Client, access SQL Server memory resources through the host interface.
0x00000000 = Memory clerk belongs to SQL Server.
Is not nullable.
pages_kb       bigint Specifies the amount of page memory allocated in kilobytes (KB) for this memory clerk. Is not nullable.
page_size_in_bytes       bigint Specifies the granularity of the page allocation for this memory clerk. Is not nullable.

TSQL

Sql 2005
SELECT [memory_clerk_address], [type], [name], [memory_node_id], [single_pages_kb], [multi_pages_kb], [virtual_memory_reserved_kb], [virtual_memory_committed_kb], [awe_allocated_kb], [shared_memory_reserved_kb], [shared_memory_committed_kb], [page_size_bytes], [page_allocator_address], [host_address] FROM sys.dm_os_memory_clerks
Sql 2008
SELECT [memory_clerk_address], [type], [name], [memory_node_id], [single_pages_kb], [multi_pages_kb], [virtual_memory_reserved_kb], [virtual_memory_committed_kb], [awe_allocated_kb], [shared_memory_reserved_kb], [shared_memory_committed_kb], [page_size_bytes], [page_allocator_address], [host_address] FROM sys.dm_os_memory_clerks
Sql 2008 R2
SELECT [memory_clerk_address], [type], [name], [memory_node_id], [single_pages_kb], [multi_pages_kb], [virtual_memory_reserved_kb], [virtual_memory_committed_kb], [awe_allocated_kb], [shared_memory_reserved_kb], [shared_memory_committed_kb], [page_size_bytes], [page_allocator_address], [host_address] FROM sys.dm_os_memory_clerks
Sql 2012
SELECT [memory_clerk_address], [type], [name], [memory_node_id], [pages_kb], [virtual_memory_reserved_kb], [virtual_memory_committed_kb], [awe_allocated_kb], [shared_memory_reserved_kb], [shared_memory_committed_kb], [page_size_in_bytes], [page_allocator_address], [host_address] FROM sys.dm_os_memory_clerks

Back to Top


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.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
memory_object_address varbinary(8) Address of the memory object. Is not nullable.
parent_address varbinary(8) Address of the parent memory object. Is nullable.
pages_allocated_count   int Number of pages that are allocated by this object. Is not nullable.
creation_options int Internal use only. Is nullable.
bytes_used bigint Internal use only. Is nullable.
type nvarchar(60) Type of memory object.
This indicates a component that this memory object belongs to, or the function of the memory object. Is nullable.
name varchar(128) Internal use only. Is nullable.
memory_node_id smallint ID of a memory node that is being used by this memory object. Is not nullable.
creation_time datetime Internal only. NULLABLE.
page_size_in_bytes int Size of pages allocated by this object. Is not nullable.
max_pages_allocated_count   int Maximum number of pages allocated by this memory object. Is not nullable.
page_allocator_address varbinary(8) Memory address of page allocator. Is not nullable. For more information, see sys.dm_os_memory_clerks1.
creation_stack_address varbinary(8) Internal use only. Is nullable.
sequence_num int Internal use only. Is nullable.
pages_in_bytes       bigint Amount of memory in bytes that is allocated by this instance of the memory object. Is not nullable.
max_pages_in_bytes       bigint Maximum amount of memory ever used by this memory object. Is not nullable.

TSQL

Sql 2005
SELECT [memory_object_address], [parent_address], [pages_allocated_count], [creation_options], [bytes_used], [type], [name], [memory_node_id], [creation_time], [page_size_in_bytes], [max_pages_allocated_count], [page_allocator_address], [creation_stack_address], [sequence_num] FROM sys.dm_os_memory_objects
Sql 2008
SELECT [memory_object_address], [parent_address], [pages_allocated_count], [creation_options], [bytes_used], [type], [name], [memory_node_id], [creation_time], [page_size_in_bytes], [max_pages_allocated_count], [page_allocator_address], [creation_stack_address], [sequence_num] FROM sys.dm_os_memory_objects
Sql 2008 R2
SELECT [memory_object_address], [parent_address], [pages_allocated_count], [creation_options], [bytes_used], [type], [name], [memory_node_id], [creation_time], [page_size_in_bytes], [max_pages_allocated_count], [page_allocator_address], [creation_stack_address], [sequence_num] FROM sys.dm_os_memory_objects
Sql 2012
SELECT [memory_object_address], [parent_address], [pages_in_bytes], [creation_options], [bytes_used], [type], [name], [memory_node_id], [creation_time], [page_size_in_bytes], [max_pages_in_bytes], [page_allocator_address], [creation_stack_address], [sequence_num] FROM sys.dm_os_memory_objects

Back to Top


sys.dm_os_memory_pools

object store in the instance of SQL Server. You can use this view to monitor cache memory use and to identify bad caching behavior

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
memory_pool_address varbinary(8) Memory address of the entry that represents the memory pool. Is not nullable.
pool_id int ID of a specific pool within a set of pools. Is not nullable.
type nvarchar(60) Type of object pool. Is not nullable. For more information, see sys.dm_os_memory_clerks1.
name nvarchar(256) System-assigned name of this memory object. Is not nullable.
max_free_entries_count bigint Maximum number of free entries that a pool can have. Is not nullable.
free_entries_count bigint Number of free entries currently in the pool. Is not nullable.
removed_in_all_rounds_count bigint Number of entries removed from the pool since the instance of SQL Server was started. Is not nullable.

TSQL

Sql 2005
SELECT [memory_pool_address], [pool_id], [type], [name], [max_free_entries_count], [free_entries_count], [removed_in_all_rounds_count] FROM sys.dm_os_memory_pools
Sql 2008
SELECT [memory_pool_address], [pool_id], [type], [name], [max_free_entries_count], [free_entries_count], [removed_in_all_rounds_count] FROM sys.dm_os_memory_pools
Sql 2008 R2
SELECT [memory_pool_address], [pool_id], [type], [name], [max_free_entries_count], [free_entries_count], [removed_in_all_rounds_count] FROM sys.dm_os_memory_pools
Sql 2012
SELECT [memory_pool_address], [pool_id], [type], [name], [max_free_entries_count], [free_entries_count], [removed_in_all_rounds_count] FROM sys.dm_os_memory_pools

Back to Top


sys.dm_os_performance_counters

Returns a row per performance counter maintained by the server. For information about each performance counter, see Use SQL Server Objects1.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
object_name nchar(128) Category to which this counter belongs.
counter_name nchar(128) Name of the counter.
instance_name nchar(128) Name of the specific instance of the counter. Often contains the database name.
cntr_value bigint Current value of the counter.
Note: For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used.
cntr_type int Type of counter as defined by the Windows performance architecture. See WMI Performance Counter2 Types or your Windows Server documentation for more information on performance counter types.

TSQL

Sql 2005
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.dm_os_performance_counters
Sql 2008
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.dm_os_performance_counters
Sql 2008 R2
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.dm_os_performance_counters
Sql 2012
SELECT [object_name], [counter_name], [instance_name], [cntr_value], [cntr_type] FROM sys.dm_os_performance_counters

Back to Top


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.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
scheduler_address varbinary(8) Memory address of the scheduler. Is not nullable.
parent_node_id int ID of the node that the scheduler belongs to, also known as the parent node. This represents a nonuniform memory access (NUMA) node. Is not nullable.
scheduler_id int ID of the scheduler. All schedulers that are used to run regular queries have ID numbers less than 255. Those schedulers that have IDs greater than or equal to 255 are used internally by SQL Server, such as the dedicated administrator connection scheduler. Is not nullable.
cpu_id smallint ID of the CPU with which this scheduler is associated. If SQL Server is configured to run with affinity, the value is the ID of the CPU on which the scheduler is supposed to be running.
255 = Affinity mask is not specified.
Is not nullable.
status nvarchar(60) Indicates the status of the scheduler. Can be one of the following values:
HIDDEN ONLINE

HIDDEN OFFLINE

VISIBLE ONLINE

VISIBLE OFFLINE

VISIBLE ONLINE (DAC)

Is not nullable.
HIDDEN schedulers are used to process requests that are internal to the Database Engine. VISIBLE schedulers are used to process user requests.
OFFLINE schedulers map to processors that are offline in the affinity mask and are, therefore, not being used to process any requests. ONLINE schedulers map to processors that are online in the affinity mask and are available to process threads.
DAC indicates the scheduler is running under a dedicated administrator connection.
is_online bit If SQL Server is configured to use only some of the available processors on the server, this configuration can mean that some schedulers are mapped to processors that are not in the affinity mask. If that is the case, this column returns 0. This value means that the scheduler is not being used to process queries or batches.
Is not nullable.
is_idle bit 1 = Scheduler is idle. No workers are currently running. Is not nullable.
preemptive_switches_count int Number of times that workers on this scheduler have switched to the preemptive mode.
To execute code that is outside SQL Server (for example, extended stored procedures and distributed queries), a thread has to execute outside the control of the non-preemptive scheduler. To do this, a worker switches to preemptive mode.
context_switches_count int Number of context switches that have occurred on this scheduler. Is not nullable.
To allow for other workers to run, the current running worker has to relinquish control of the scheduler or switch context.
Note: If a worker yields the scheduler and puts itself into the runnable queue and then finds no other workers, the worker will select itself. In this case, the context_switches_count is not updated, but the yield_count is updated.
idle_switches_count int Number of times the scheduler has been waiting for an event while idle. This column is similar to context_switches_count. Is not nullable.
current_tasks_count int Number of current tasks that are associated with this scheduler. This count includes the following:
Tasks that are waiting for a worker to execute them.

Tasks that are currently waiting or running (in SUSPENDED or RUNNABLE state).

When a task is completed, this count is decremented. Is not nullable.
runnable_tasks_count int Number of workers, with tasks assigned to them, that are waiting to be scheduled on the runnable queue. Is not nullable.
current_workers_count int Number of workers that are associated with this scheduler. This count includes workers that are not assigned any task. Is not nullable.
active_workers_count int Number of workers that are active. An active worker is always nonpreemptive, must have an associated task, and is either running, runnable, or suspended. Is not nullable.
work_queue_count bigint Number of tasks in the pending queue. These tasks are waiting for a worker to pick them up. Is not nullable.
pending_disk_io_count int Number of pending I/Os that are waiting to be completed. Each scheduler has a list of pending I/Os that are checked to determine whether they have been completed every time there is a context switch. The count is incremented when the request is inserted. This count is decremented when the request is completed. This number does not indicate the state of the I/Os. Is not nullable.
load_factor int Internal value that indicates the perceived load on this scheduler. This value is used to determine whether a new task should be put on this scheduler or another scheduler. This value is useful for debugging purposes when it appears that schedulers are not evenly loaded. In SQL Server 2000, a task is routed to a particular scheduler. However, in SQL Server 2005, the routing decision is made based on the load on the scheduler. SQL Server 2005 also uses a load factor of nodes and schedulers to help determine the best location to acquire resources. When a task is enqueued, the load factor is increased. When a task is completed, the load factor is decreased. Using the load factors helps SQL Server OS balance the work load better. Is not nullable.
yield_count int Internal value that is used to indicate progress on this scheduler. This value is used by the Scheduler Monitor to determine whether a worker on the scheduler is not yielding to other workers on time. This value does not indicate that the worker or task transitioned to a new worker. Is not nullable.
last_timer_activity bigint In CPU ticks, the last time that the scheduler timer queue was checked by the scheduler. Is not nullable.
failed_to_create_worker bit Set to 1 if a new worker could not be created on this scheduler. This generally occurs because of memory constraints. Is nullable.
active_worker_address varbinary(8) Memory address of the worker that is currently active. Is nullable. For more information, see sys.dm_os_workers1.
memory_object_address varbinary(8) Memory address of the scheduler memory object. Not NULLABLE.
task_memory_object_address varbinary(8) Memory address of the task memory object. Is not nullable. For more information, see sys.dm_os_memory_objects2.
quantum_length_us     bigint Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. Exposes the scheduler quantum used by SQLOS.

TSQL

Sql 2005
SELECT [scheduler_address], [parent_node_id], [scheduler_id], [cpu_id], [status], [is_online], [is_idle], [preemptive_switches_count], [context_switches_count], [idle_switches_count], [current_tasks_count], [runnable_tasks_count], [current_workers_count], [active_workers_count], [work_queue_count], [pending_disk_io_count], [load_factor], [yield_count], [last_timer_activity], [failed_to_create_worker], [active_worker_address], [memory_object_address], [task_memory_object_address] FROM sys.dm_os_schedulers
Sql 2008
SELECT [scheduler_address], [parent_node_id], [scheduler_id], [cpu_id], [status], [is_online], [is_idle], [preemptive_switches_count], [context_switches_count], [idle_switches_count], [current_tasks_count], [runnable_tasks_count], [current_workers_count], [active_workers_count], [work_queue_count], [pending_disk_io_count], [load_factor], [yield_count], [last_timer_activity], [failed_to_create_worker], [active_worker_address], [memory_object_address], [task_memory_object_address] FROM sys.dm_os_schedulers
Sql 2008 R2
SELECT [scheduler_address], [parent_node_id], [scheduler_id], [cpu_id], [status], [is_online], [is_idle], [preemptive_switches_count], [context_switches_count], [idle_switches_count], [current_tasks_count], [runnable_tasks_count], [current_workers_count], [active_workers_count], [work_queue_count], [pending_disk_io_count], [load_factor], [yield_count], [last_timer_activity], [failed_to_create_worker], [active_worker_address], [memory_object_address], [task_memory_object_address], [quantum_length_us] FROM sys.dm_os_schedulers
Sql 2012
SELECT [scheduler_address], [parent_node_id], [scheduler_id], [cpu_id], [status], [is_online], [is_idle], [preemptive_switches_count], [context_switches_count], [idle_switches_count], [current_tasks_count], [runnable_tasks_count], [current_workers_count], [active_workers_count], [work_queue_count], [pending_disk_io_count], [load_factor], [yield_count], [last_timer_activity], [failed_to_create_worker], [active_worker_address], [memory_object_address], [task_memory_object_address], [quantum_length_us] FROM sys.dm_os_schedulers

Back to Top


sys.dm_os_stacks

This dynamic management view is used internally by SQL Server to do the following: Keep track of debug data such as outstanding allocations. Assume or validate logic that is used by SQL Server components in places where the component assumes that a certain call has been made.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
stack_address varbinary(8) Unique address for this stack allocation. Is not nullable.
frame_index int Each line represents a function call that, when sorted in ascending order by frame index for a particular stack_address, returns the full call stack. Is not nullable.
frame_address varbinary(8) Address of the function call. Is not nullable.

TSQL

Sql 2005
SELECT [stack_address], [frame_index], [frame_address] FROM sys.dm_os_stacks
Sql 2008
SELECT [stack_address], [frame_index], [frame_address] FROM sys.dm_os_stacks
Sql 2008 R2
SELECT [stack_address], [frame_index], [frame_address] FROM sys.dm_os_stacks
Sql 2012
SELECT [stack_address], [frame_index], [frame_address] FROM sys.dm_os_stacks

Back to Top


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.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
cpu_ticks bigint Current CPU tick count. CPU ticks are obtained from the processor's RDTSC counter. It is a monotonically increasing number.
ms_ticks bigint Number of milliseconds since the computer was started.
cpu_count int Number of logical CPUs on the system.
cpu_ticks_in_ms       bigint Number of CPU ticks in milliseconds.
hyperthread_ratio int Ratio of the number of logical and physical processors.
physical_memory_in_bytes   bigint Amount of physical memory available.
virtual_memory_in_bytes   bigint Amount of virtual memory available to the process in user mode. This can be used to determine whether SQL Server was started by using a 3-GB switch.
bpool_committed   int Number of 8-KB buffers in the buffer pool. This amount represents committed physical memory in the buffer pool. Does not include reserved memory in the buffer pool.
bpool_commit_target   int Number of 8-KB buffers needed by the buffer pool. The target amount is calculated using a variety of inputs such as the current state of the system, including its load, the memory requested by current processes, the amount of memory installed on the computer, and configuration parameters. If the bpool_commit_target is larger than the bpool_committed value, the buffer pool will try to obtain additional memory. If the bpool_commit_target is smaller than the bpool_committed value, the buffer pool will shrink.
bpool_visible   int Number of 8-KB buffers in the buffer pool that are directly accessible in the process virtual address space. When not using the Address Windowing Extensions (AWE), when the buffer pool has obtained its memory target (bpool_committed = bpool_commit_target), the value of bpool_visible equals the value of bpool_committed.
When using AWE on a 32-bit version of SQL Server, bpool_visible represents the size of the AWE mapping window used to access physical memory allocated by the buffer pool. The size of this mapping window is bound by the process address space and, therefore, the visible amount will be smaller than the committed amount, and can be further reduced by internal components consuming memory for purposes other than database pages. If the value of bpool_visible is too low, you might receive out of memory errors.
stack_size_in_bytes int Size of the call stack for each thread created by SQL Server.
os_quantum bigint Quantum for a non-preemptive task, measured in CPU ticks. Quantum (in seconds) = os_quantum / CPU clock speed.
os_error_mode int Error mode for the SQL Server process.
os_priority_class int Priority class for the SQL Server process.
max_workers_count int Maximum number of workers that can be created.
scheduler_count int Number of user schedulers configured in the SQL Server process.
scheduler_total_count int Total number of schedulers in SQL Server.
deadlock_monitor_serial_number int ID of the current deadlock monitor sequence.
sqlserver_start_time_ms_ticks   bigint ms_tick number when SQL Server last started. Compare to the current ms_ticks column.
sqlserver_start_time   datetime Date and time SQL Server last started.
affinity_type     int Describes the affinity type that SQL Server uses.

ValueDescription
1Manual
2Auto
affinity_type_description       varchar(60) Describes the value in the affinity_type column. For more information, see ALTER SERVER CONFIGURATION (Transact-SQL)2.

ValueDescription
MANUALAffinity has been set for at least one CPU.
AUTOSQL Server moves threads between CPUs as required.
process_kernel_time_ms     bigint Total time in milliseconds spent by all SQL Server threads in kernel mode.
Note
This value can be larger than a single processor clock because it includes the time for all processors on the server.
process_user_time_ms     bigint Total time in milliseconds spent by all SQL Server threads in user mode.
Note
This value can be larger than a single processor clock because it includes the time for all processors on the server.
time_source     int Describes the API that SQL Server is using to retrieve wall clock time.

ValueDescription
0Indicates the QueryPerformanceCounter3 API.
1Indicates this multimedia timer4 API.
time_source_desc     nvarchar(60) Describes the value in the time_source column.

ValueDescription
QUERY_PERFORMANCE_COUNTERThe QueryPerformanceCounter3 API retrieves wall clock time.
MULTIMEDIA_TIMERThe multimedia timer4 API that retrieves wall clock time.
virtual_machine_type     int Indicates whether SQL Server is running in a virtualized environment. Not nullable.

ValueDescription
0NONE
1HYPERVISOR
2Other
virtual_machine_type_desc     nvarchar(60) Describes the virtual_machine_type column. Not nullable.

ValueDescription
NoneSQL Server is not running inside a virtual machine.
HYPERVISORSQL Server is running inside a hypervisor, which implies a hardware-assisted virtualization. If the instance is running on the host OS, the description will still return HYPERVISOR.
OtherSQL Server is running inside a virtual machine that does not employ hardware assistant such as Microsoft Virtual PC.
physical_memory_kb       bigint Specifies the total amount of physical memory on the machine. Not nullable.
virtual_memory_kb       bigint Specifies the total amount of virtual address space available to the process in user mode. Not nullable.
committed_kb       int Represents the committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager. Not nullable.
committed_target_kb       int Represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager. The target amount is calculated using a variety of inputs like:
the current state of the system including its load
the memory requested by current processes
the amount of memory installed on the computer
configuration parameters
If committed_target_kb is larger than committed_kb, the memory manager will try to obtain additional memory. If committed_target_kb is smaller than committed_kb, the memory manager will try to shrink the amount of memory committed. The committed_target_kb always includes stolen and reserved memory. Not nullable.
visible_target_kb       int Is the same as committed_target_kb. Not nullable.
affinity_type_desc       varchar(60) Describes the affinity_type column. Not nullable.
MANUAL = affinity has been set for at least one CPU.
AUTO = SQL Server can freely move threads between CPUs.

TSQL

Sql 2005
SELECT [cpu_ticks], [ms_ticks], [cpu_count], [cpu_ticks_in_ms], [hyperthread_ratio], [physical_memory_in_bytes], [virtual_memory_in_bytes], [bpool_committed], [bpool_commit_target], [bpool_visible], [stack_size_in_bytes], [os_quantum], [os_error_mode], [os_priority_class], [max_workers_count], [scheduler_count], [scheduler_total_count], [deadlock_monitor_serial_number] FROM sys.dm_os_sys_info
Sql 2008
SELECT [cpu_ticks], [ms_ticks], [cpu_count], [hyperthread_ratio], [physical_memory_in_bytes], [virtual_memory_in_bytes], [bpool_committed], [bpool_commit_target], [bpool_visible], [stack_size_in_bytes], [os_quantum], [os_error_mode], [os_priority_class], [max_workers_count], [scheduler_count], [scheduler_total_count], [deadlock_monitor_serial_number], [sqlserver_start_time_ms_ticks], [sqlserver_start_time] FROM sys.dm_os_sys_info
Sql 2008 R2
SELECT [cpu_ticks], [ms_ticks], [cpu_count], [hyperthread_ratio], [physical_memory_in_bytes], [virtual_memory_in_bytes], [bpool_committed], [bpool_commit_target], [bpool_visible], [stack_size_in_bytes], [os_quantum], [os_error_mode], [os_priority_class], [max_workers_count], [scheduler_count], [scheduler_total_count], [deadlock_monitor_serial_number], [sqlserver_start_time_ms_ticks], [sqlserver_start_time], [affinity_type], [affinity_type_description], [process_kernel_time_ms], [process_user_time_ms], [time_source], [time_source_desc], [virtual_machine_type], [virtual_machine_type_desc] FROM sys.dm_os_sys_info
Sql 2012
SELECT [cpu_ticks], [ms_ticks], [cpu_count], [hyperthread_ratio], [physical_memory_kb], [virtual_memory_kb], [committed_kb], [committed_target_kb], [visible_target_kb], [stack_size_in_bytes], [os_quantum], [os_error_mode], [os_priority_class], [max_workers_count], [scheduler_count], [scheduler_total_count], [deadlock_monitor_serial_number], [sqlserver_start_time_ms_ticks], [sqlserver_start_time], [affinity_type], [affinity_type_desc], [process_kernel_time_ms], [process_user_time_ms], [time_source], [time_source_desc], [virtual_machine_type], [virtual_machine_type_desc] FROM sys.dm_os_sys_info

Back to Top


sys.dm_os_tasks

task that is active in the instance of SQL Server.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
task_address varbinary(8) Memory address of the object.
task_state nvarchar(60) State of the task. This can be one of the following:
PENDING: Waiting for a worker thread.
RUNNABLE: Runnable, but waiting to receive a quantum.
RUNNING: Currently running on the scheduler.
SUSPENDED: Has a worker, but is waiting for an event.
DONE: Completed.
SPINLOOP: Stuck in a spinlock.
context_switches_count int Number of scheduler context switches that this task has completed.
pending_io_count int Number of physical I/Os that are performed by this task.
pending_io_byte_count bigint Total byte count of I/Os that are performed by this task.
pending_io_byte_average int Average byte count of I/Os that are performed by this task.
scheduler_id int ID of the parent scheduler. This is a handle to the scheduler information for this task. For more information, see sys.dm_os_schedulers1.
session_id smallint ID of the session that is associated with the task.
exec_context_id int Execution context ID that is associated with the task.
request_id int ID of the request of the task. For more information, see sys.dm_exec_requests2.
worker_address varbinary(8) Memory address of the worker that is running the task.
NULL = Task is either waiting for a worker to be able to run, or the task has just finished running.
For more information, see sys.dm_os_workers3.
host_address varbinary(8) Memory address of the host.
0 = Hosting was not used to create the task. This helps identify the host that was used to create this task.
For more information, see sys.dm_os_hosts4.
parent_task_address     varbinary(8) Memory address of the task that is the parent of the object.

TSQL

Sql 2005
SELECT [task_address], [task_state], [context_switches_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [scheduler_id], [session_id], [exec_context_id], [request_id], [worker_address], [host_address] FROM sys.dm_os_tasks
Sql 2008
SELECT [task_address], [task_state], [context_switches_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [scheduler_id], [session_id], [exec_context_id], [request_id], [worker_address], [host_address] FROM sys.dm_os_tasks
Sql 2008 R2
SELECT [task_address], [task_state], [context_switches_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [scheduler_id], [session_id], [exec_context_id], [request_id], [worker_address], [host_address], [parent_task_address] FROM sys.dm_os_tasks
Sql 2012
SELECT [task_address], [task_state], [context_switches_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [scheduler_id], [session_id], [exec_context_id], [request_id], [worker_address], [host_address], [parent_task_address] FROM sys.dm_os_tasks

Back to Top


sys.dm_os_threads

Returns a list of all SQL Server Operating System threads that are running under the SQL Server process.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
thread_address varbinary(8) Memory address (Primary Key) of the thread.
started_by_sqlservr bit Indicates the thread initiator.
1 = SQL Server started the thread.
0 = Another component started the thread, such as an extended stored procedure from within SQL Server.
os_thread_id int ID of the thread that is assigned by the operating system.
status int Internal status flag.
instruction_address varbinary(8) Address of the instruction that is currently being executed.
creation_time datetime Time when this thread was created.
kernel_time bigint Amount of kernel time that is used by this thread.
usermode_time bigint Amount of user time that is used by this thread.
stack_base_address varbinary(8) Memory address of the highest stack address for this thread.
stack_end_address varbinary(8) Memory address of the lowest stack address of this thread.
stack_bytes_committed int Number of bytes that are committed in the stack.
stack_bytes_used int Number of bytes that are actively being used on the thread.
affinity bigint CPU mask on which this thread is supposed to be running. This depends on the value in sp_configure affinity mask configuration option.
locale int Cached locale LCID for the thread.
priority int Priority value of this thread.
token varbinary(8) Cached impersonation token handle for the thread.
is_impersonating int Indicates whether this thread is using Win32 impersonation.
1 = The thread is using security credentials that are different from the default of the process. This indicates that the thread is impersonating an entity other than the one that created the process.
is_waiting_on_loader_lock int Operating system status of whether the thread is waiting on the loader lock.
fiber_data varbinary(8) Current Win32 fiber that is running on the thread. This is only applicable when SQL Server is configured for lightweight pooling.
thread_handle varbinary(8) Internal use only.
event_handle varbinary(8) Internal use only.
scheduler_address varbinary(8) Memory address of the scheduler that is associated with this thread. For more information, see sys.dm_os_schedulers1.
worker_address varbinary(8) Memory address of the worker that is bound to this thread. For more information, see sys.dm_os_workers2.
fiber_context_address varbinary(8) Internal fiber context address. This is only applicable when SQL Server is configured for lightweight pooling.
self_address varbinary(8) Internal consistency pointer.
processor_group     smallint Stores the processor group ID.

TSQL

Sql 2005
SELECT [thread_address], [started_by_sqlservr], [os_thread_id], [status], [instruction_address], [creation_time], [kernel_time], [usermode_time], [stack_base_address], [stack_end_address], [stack_bytes_committed], [stack_bytes_used], [affinity], [locale], [priority], [token], [is_impersonating], [is_waiting_on_loader_lock], [fiber_data], [thread_handle], [event_handle], [scheduler_address], [worker_address], [fiber_context_address], [self_address] FROM sys.dm_os_threads
Sql 2008
SELECT [thread_address], [started_by_sqlservr], [os_thread_id], [status], [instruction_address], [creation_time], [kernel_time], [usermode_time], [stack_base_address], [stack_end_address], [stack_bytes_committed], [stack_bytes_used], [affinity], [locale], [priority], [token], [is_impersonating], [is_waiting_on_loader_lock], [fiber_data], [thread_handle], [event_handle], [scheduler_address], [worker_address], [fiber_context_address], [self_address] FROM sys.dm_os_threads
Sql 2008 R2
SELECT [thread_address], [started_by_sqlservr], [os_thread_id], [status], [instruction_address], [creation_time], [kernel_time], [usermode_time], [stack_base_address], [stack_end_address], [stack_bytes_committed], [stack_bytes_used], [affinity], [priority], [locale], [token], [is_impersonating], [is_waiting_on_loader_lock], [fiber_data], [thread_handle], [event_handle], [scheduler_address], [worker_address], [fiber_context_address], [self_address], [processor_group] FROM sys.dm_os_threads
Sql 2012
SELECT [thread_address], [started_by_sqlservr], [os_thread_id], [status], [instruction_address], [creation_time], [kernel_time], [usermode_time], [stack_base_address], [stack_end_address], [stack_bytes_committed], [stack_bytes_used], [affinity], [priority], [locale], [token], [is_impersonating], [is_waiting_on_loader_lock], [fiber_data], [thread_handle], [event_handle], [scheduler_address], [worker_address], [fiber_context_address], [self_address], [processor_group] FROM sys.dm_os_threads

Back to Top


sys.dm_os_virtual_address_dump

Returns information about a range of pages in the virtual address space of the calling process. Note This information is also returned by the VirtualQuery Windows API.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
region_base_address varbinary(8) Pointer to the base address of the region of pages. Is not nullable.
region_allocation_base_address varbinary(8) Pointer to the base address of a range of pages allocated by the VirtualAlloc Windows API function. The page pointed to by the BaseAddress member is contained within this allocation range. Is not nullable.
region_allocation_protection varbinary(8) Protection attributes when the region was first allocated. The value is one of the following:
PAGE_READONLY

PAGE_READWRITE

PAGE_NOACCESS

PAGE_WRITECOPY

PAGE_EXECUTE

PAGE_EXECUTE_READ

PAGE_EXECUTE_READWRITE

PAGE_EXECUTE_WRITECOPY

PAGE_GUARD

PAGE_NOCACHE

Is not nullable.
region_size_in_bytes bigint Size of the region, in bytes, starting at the base address in which all the pages have the same attributes. Is not nullable.
region_state varbinary(8) Current state of the region. This is one of the following:
MEM_COMMIT

MEM_RESERVE

MEM_FREE

Is not nullable.
region_current_protection varbinary(8) Protection attributes. The value is one of the following:
PAGE_READONLY

PAGE_READWRITE

PAGE_NOACCESS

PAGE_WRITECOPY

PAGE_EXECUTE

PAGE_EXECUTE_READ

PAGE_EXECUTE_READWRITE

PAGE_EXECUTE_WRITECOPY

PAGE_GUARD

PAGE_NOCACHE

Is not nullable.
region_type varbinary(8) Identifies the types of pages in the region. The value can be one of the following:
MEM_PRIVATE

MEM_MAPPED

MEM_IMAGE

Is not nullable.

TSQL

Sql 2005
SELECT [region_base_address], [region_allocation_base_address], [region_allocation_protection], [region_size_in_bytes], [region_state], [region_current_protection], [region_type] FROM sys.dm_os_virtual_address_dump
Sql 2008
SELECT [region_base_address], [region_allocation_base_address], [region_allocation_protection], [region_size_in_bytes], [region_state], [region_current_protection], [region_type] FROM sys.dm_os_virtual_address_dump
Sql 2008 R2
SELECT [region_base_address], [region_allocation_base_address], [region_allocation_protection], [region_size_in_bytes], [region_state], [region_current_protection], [region_type] FROM sys.dm_os_virtual_address_dump
Sql 2012
SELECT [region_base_address], [region_allocation_base_address], [region_allocation_protection], [region_size_in_bytes], [region_state], [region_current_protection], [region_type] FROM sys.dm_os_virtual_address_dump

Back to Top


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.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
wait_type nvarchar(60) Name of the wait type.
waiting_tasks_count bigint Number of waits on this wait type. This counter is incremented at the start of each wait.
wait_time_ms bigint Total wait time for this wait type in milliseconds. This time is inclusive of signal_wait_time_ms.
max_wait_time_ms bigint Maximum wait time on this wait type.
signal_wait_time_ms bigint Difference between the time the waiting thread was signaled and when it started running.

TSQL

Sql 2005
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] FROM sys.dm_os_wait_stats
Sql 2008
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] FROM sys.dm_os_wait_stats
Sql 2008 R2
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] FROM sys.dm_os_wait_stats
Sql 2012
SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms] FROM sys.dm_os_wait_stats

Back to Top


sys.dm_os_waiting_tasks

Returns information about the wait queue of tasks that are waiting on some resource.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
waiting_task_address varbinary(8) Address of the waiting task.
session_id smallint ID of the session associated with the task.
exec_context_id int ID of the execution context associated with the task.
wait_duration_ms int Total wait time for this wait type, in milliseconds. This time is inclusive of signal_wait_time.
wait_type nvarchar(60) Name of the wait type.
resource_address varbinary(8) Address of the resource for which the task is waiting.
blocking_task_address varbinary(8) Task that is currently holding this resource
blocking_session_id smallint ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).
-2 = The blocking resource is owned by an orphaned distributed transaction.
-3 = The blocking resource is owned by a deferred recovery transaction.
-4 = Session ID of the blocking latch owner could not be determined due to internal latch state transitions.
blocking_exec_context_id int ID of the execution context of the blocking task.
resource_description nvarchar(1024) Description of the resource that is being consumed.

TSQL

Sql 2005
SELECT [waiting_task_address], [session_id], [exec_context_id], [wait_duration_ms], [wait_type], [resource_address], [blocking_task_address], [blocking_session_id], [blocking_exec_context_id], [resource_description] FROM sys.dm_os_waiting_tasks
Sql 2008
SELECT [waiting_task_address], [session_id], [exec_context_id], [wait_duration_ms], [wait_type], [resource_address], [blocking_task_address], [blocking_session_id], [blocking_exec_context_id], [resource_description] FROM sys.dm_os_waiting_tasks
Sql 2008 R2
SELECT [waiting_task_address], [session_id], [exec_context_id], [wait_duration_ms], [wait_type], [resource_address], [blocking_task_address], [blocking_session_id], [blocking_exec_context_id], [resource_description] FROM sys.dm_os_waiting_tasks
Sql 2012
SELECT [waiting_task_address], [session_id], [exec_context_id], [wait_duration_ms], [wait_type], [resource_address], [blocking_task_address], [blocking_session_id], [blocking_exec_context_id], [resource_description] FROM sys.dm_os_waiting_tasks

Back to Top


sys.dm_os_workers

Returns a row for every worker in the system.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
worker_address varbinary(8) Memory address of the worker.
status int Internal use only.
is_preemptive bit 1 = Worker is running with preemptive scheduling. Any worker that is running external code is run under preemptive scheduling.
is_fiber bit 1 = Worker is running with lightweight pooling. For more information, see sp_configure (Transact-SQL)1.
is_sick bit 1 = Worker is stuck trying to obtain a spin lock. If this bit is set, this might indicate a problem with contention on a frequently accessed object.
is_in_cc_exception bit 1 = Worker is currently handling a non-SQL Server exception.
is_fatal_exception bit Specifies whether this worker received a fatal exception.
is_inside_catch bit 1 = Worker is currently handling an exception.
is_in_polling_io_completion_routine bit 1 = Worker is currently running an I/O completion routine for a pending I/O. For more information, see sys.dm_io_pending_io_requests2.
context_switch_count int The number of scheduler context switches that are performed by this worker.
pending_io_count int The number of physical I/Os that are performed by this worker.
pending_io_byte_count bigint Total number of bytes for all pending physical I/Os for this worker.
pending_io_byte_average int Average number of bytes for physical I/Os for this worker.
wait_started_ms_ticks int Point in time, in ms_ticks3, when this worker entered the SUSPENDED state. Subtracting this value from ms_ticks in sys.dm_os_sys_info3 returns the number of milliseconds that the worker has been waiting.
wait_resumed_ms_ticks int Point in time, in ms_ticks3, when this worker entered the RUNNABLE state. Subtracting this value from ms_ticks in sys.dm_os_sys_info3 returns the number of milliseconds that the worker has been in the runnable queue.
task_bound_ms_ticks bigint Point in time, in ms_ticks3, when a task is bound to this worker.
worker_created_ms_ticks bigint Point in time, in ms_ticks3, when a worker is created.
exception_num int The error number of the last exception that this worker encountered.
exception_severity int The severity of the last exception that this worker encountered.
exception_address varbinary(8) The code address that threw the exception
locale   int Locale LCID setting for the worker.
affinity bigint Affinity setting for the worker. For more information, see sys.dm_os_schedulers4.
state nvarchar(60) Worker state. Can be one of the following values:
INIT = Worker is currently being initialized.
RUNNING = Worker is currently running either nonpreemptively or preemptively.
RUNNABLE = The worker is ready to run on the scheduler.
SUSPENDED = The worker is currently suspended, waiting for an event to send it a signal.
start_quantum bigint Time, in cpu_ticks3, at the start of the current run of this worker.
end_quantum bigint Time, in cpu_ticks3, at the end of the current run of this worker.
last_wait_type nvarchar(60) Type of last wait. For a list of wait types, see sys.dm_os_wait_stats5.
return_code int Return value from last wait. Can be one of the following values:
0 =SUCCESS
3 = DEADLOCK
4 = PREMATURE_WAKEUP
258 = TIMEOUT
quantum_used bigint Internal use only.
max_quantum bigint Internal use only.
boost_count int Internal use only.
tasks_processed_count int Number of tasks that this worker processed.
fiber_address varbinary(8) Memory address of the fiber with which this worker is associated.
NULL = SQL Server is not configured for lightweight pooling.
task_address varbinary(8) Memory address of the current task. For more information, see sys.dm_os_tasks6.
memory_object_address varbinary(8) Memory address of the worker memory object. For more information, see sys.dm_os_memory_objects7.
thread_address varbinary(8) Memory address of the thread associated with this worker. For more information, see sys.dm_os_threads8.
signal_worker_address varbinary(8) Memory address of the worker that last signaled this object. For more information, see sys.dm_os_workers9.
scheduler_address varbinary(8) Memory address of the scheduler. For more information, see sys.dm_os_schedulers4.
processor_group     smallint Stores the processor group ID that is assigned to this thread.

TSQL

Sql 2005
SELECT [worker_address], [status], [is_preemptive], [is_fiber], [is_sick], [is_in_cc_exception], [is_fatal_exception], [is_inside_catch], [is_in_polling_io_completion_routine], [context_switch_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [wait_started_ms_ticks], [wait_resumed_ms_ticks], [task_bound_ms_ticks], [worker_created_ms_ticks], [exception_num], [exception_severity], [exception_address], [locale], [affinity], [state], [start_quantum], [end_quantum], [last_wait_type], [return_code], [quantum_used], [max_quantum], [boost_count], [tasks_processed_count], [fiber_address], [task_address], [memory_object_address], [thread_address], [signal_worker_address], [scheduler_address] FROM sys.dm_os_workers
Sql 2008
SELECT [worker_address], [status], [is_preemptive], [is_fiber], [is_sick], [is_in_cc_exception], [is_fatal_exception], [is_inside_catch], [is_in_polling_io_completion_routine], [context_switch_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [wait_started_ms_ticks], [wait_resumed_ms_ticks], [task_bound_ms_ticks], [worker_created_ms_ticks], [exception_num], [exception_severity], [exception_address], [locale], [affinity], [state], [start_quantum], [end_quantum], [last_wait_type], [return_code], [quantum_used], [max_quantum], [boost_count], [tasks_processed_count], [fiber_address], [task_address], [memory_object_address], [thread_address], [signal_worker_address], [scheduler_address] FROM sys.dm_os_workers
Sql 2008 R2
SELECT [worker_address], [status], [is_preemptive], [is_fiber], [is_sick], [is_in_cc_exception], [is_fatal_exception], [is_inside_catch], [is_in_polling_io_completion_routine], [context_switch_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [wait_started_ms_ticks], [wait_resumed_ms_ticks], [task_bound_ms_ticks], [worker_created_ms_ticks], [exception_num], [exception_severity], [exception_address], [locale], [affinity], [state], [start_quantum], [end_quantum], [last_wait_type], [return_code], [quantum_used], [max_quantum], [boost_count], [tasks_processed_count], [fiber_address], [task_address], [memory_object_address], [thread_address], [signal_worker_address], [scheduler_address], [processor_group] FROM sys.dm_os_workers
Sql 2012
SELECT [worker_address], [status], [is_preemptive], [is_fiber], [is_sick], [is_in_cc_exception], [is_fatal_exception], [is_inside_catch], [is_in_polling_io_completion_routine], [context_switch_count], [pending_io_count], [pending_io_byte_count], [pending_io_byte_average], [wait_started_ms_ticks], [wait_resumed_ms_ticks], [task_bound_ms_ticks], [worker_created_ms_ticks], [exception_num], [exception_severity], [exception_address], [affinity], [state], [start_quantum], [end_quantum], [last_wait_type], [return_code], [quantum_used], [max_quantum], [boost_count], [tasks_processed_count], [fiber_address], [task_address], [memory_object_address], [thread_address], [signal_worker_address], [scheduler_address], [processor_group] FROM sys.dm_os_workers

Back to Top

Total Pageviews