December 6, 2012

Execution Related Dynamic Management Views and Functions

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

sys.dm_exec_background_job_queue

query processor job that is scheduled for asynchronous (background) execution.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
time_queued datetime Time when the job was added to the queue.
job_id int Job identifier.
database_id int Database on which the job is to execute.
object_id1 int Value depends on the job type. For more information, see the Remarks section.
object_id2 int Value depends on the job type. For more information, see the Remarks section.
object_id3 int Value depends on the job type. For more information, see the Remarks section.
object_id4 int Value depends on the job type. For more information, see the Remarks section.
error_code int Error code if the job reinserted due to failure. NULL if suspended, not picked up, or completed.
request_type smallint Type of the job request.
retry_count smallint Number of times the job was picked from the queue and reinserted because of lack of resources or other reasons.
in_progress smallint Indicates whether the job has started execution.
1 = Started
0 = Still waiting
session_id smallint Session identifier.

TSQL

Sql 2005
SELECT [time_queued], [job_id], [database_id], [object_id1], [object_id2], [object_id3], [object_id4], [error_code], [request_type], [retry_count], [in_progress], [session_id] FROM sys.dm_exec_background_job_queue
Sql 2008
SELECT [time_queued], [job_id], [database_id], [object_id1], [object_id2], [object_id3], [object_id4], [error_code], [request_type], [retry_count], [in_progress], [session_id] FROM sys.dm_exec_background_job_queue
Sql 2008 R2
SELECT [time_queued], [job_id], [database_id], [object_id1], [object_id2], [object_id3], [object_id4], [error_code], [request_type], [retry_count], [in_progress], [session_id] FROM sys.dm_exec_background_job_queue
Sql 2012
SELECT [time_queued], [job_id], [database_id], [object_id1], [object_id2], [object_id3], [object_id4], [error_code], [request_type], [retry_count], [in_progress], [session_id] FROM sys.dm_exec_background_job_queue

Back to Top


sys.dm_exec_background_job_queue_stats

Returns a row that provides aggregate statistics for each query processor job submitted for asynchronous (background) execution.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
queue_max_len int Maximum length of the queue.
enqueued_count int Number of requests successfully posted to the queue.
started_count int Number of requests that started execution.
ended_count int Number of requests serviced to either success or failure.
failed_lock_count int Number of requests that failed due to lock contention or deadlock.
failed_other_count int Number of requests that failed due to other reasons.
failed_giveup_count int Number of requests that failed because retry limit has been reached.
enqueue_failed_full_count int Number of failed enqueue attempts because the queue is full.
enqueue_failed_duplicate_count int Number of duplicate enqueue attempts.
elapsed_avg_ms int Average elapsed time of request in milliseconds.
elapsed_max_ms int Elapsed time of the longest request in milliseconds.

TSQL

Sql 2005
SELECT [queue_max_len], [enqueued_count], [started_count], [ended_count], [failed_lock_count], [failed_other_count], [failed_giveup_count], [enqueue_failed_full_count], [enqueue_failed_duplicate_count], [elapsed_avg_ms], [elapsed_max_ms] FROM sys.dm_exec_background_job_queue_stats
Sql 2008
SELECT [queue_max_len], [enqueued_count], [started_count], [ended_count], [failed_lock_count], [failed_other_count], [failed_giveup_count], [enqueue_failed_full_count], [enqueue_failed_duplicate_count], [elapsed_avg_ms], [elapsed_max_ms] FROM sys.dm_exec_background_job_queue_stats
Sql 2008 R2
SELECT [queue_max_len], [enqueued_count], [started_count], [ended_count], [failed_lock_count], [failed_other_count], [failed_giveup_count], [enqueue_failed_full_count], [enqueue_failed_duplicate_count], [elapsed_avg_ms], [elapsed_max_ms] FROM sys.dm_exec_background_job_queue_stats
Sql 2012
SELECT [queue_max_len], [enqueued_count], [started_count], [ended_count], [failed_lock_count], [failed_other_count], [failed_giveup_count], [enqueue_failed_full_count], [enqueue_failed_duplicate_count], [elapsed_avg_ms], [elapsed_max_ms] FROM sys.dm_exec_background_job_queue_stats

Back to Top


sys.dm_exec_cached_plan_dependent_objects

Transact-SQL execution plan, common language runtime (CLR) execution plan, and cursor associated with a plan.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
usecounts int Number of times the execution context or cursor has been used.
Column is not nullable.
memory_object_address varbinary(8) Memory address of the execution context or cursor.
Column is not nullable.
cacheobjtype nvarchar(50) Possible values are
Executable plan

CLR compiled function

CLR compiled procedure

Cursor

Column is not nullable.

TSQL

Sql 2005
SELECT [usecounts], [memory_object_address], [cacheobjtype] FROM sys.dm_exec_cached_plan_dependent_objects
Sql 2008
SELECT [usecounts], [memory_object_address], [cacheobjtype] FROM sys.dm_exec_cached_plan_dependent_objects
Sql 2008 R2
SELECT [usecounts], [memory_object_address], [cacheobjtype] FROM sys.dm_exec_cached_plan_dependent_objects
Sql 2012
SELECT [usecounts], [memory_object_address], [cacheobjtype] FROM sys.dm_exec_cached_plan_dependent_objects

Back to Top


sys.dm_exec_cached_plans

query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
bucketid int ID of the hash bucket in which the entry is cached. The value indicates a range from 0 through the hash table size for the type of cache.
For the SQL Plans and Object Plans caches , the hash table size can be up to 10007 on 32-bit systems and up to 40009 on 64-bit systems. For the Bound Trees cache, the hash table size can be up to 1009 on 32-bit systems and up to 4001 on 64-bit systems. For the Extended Stored Procedures cache the hash table size can be up to 127 on 32-bit and 64-bit systems. For more information about cache types and hash tables, see sys.dm_os_memory_cache_hash_tables1.
refcounts int Number of cache objects that are referencing this cache object. Refcounts must be at least 1 for an entry to be in the cache.
usecounts int Number of times this cache object has been used since its inception.
size_in_bytes int Number of bytes consumed by the cache object.
memory_object_address varbinary(8) Memory address of the cached entry. This value can be used with sys.dm_os_memory_objects2 to get the memory breakdown of the cached plan and with sys.dm_os_memory_cache_entries3_entries to obtain the cost of caching the entry.
cacheobjtype nvarchar(34) Type of object in the cache. The value can be one of the following:
Compiled Plan

Parse Tree

Extended Proc

CLR Compiled Func

CLR Compiled Proc
objtype nvarchar(16) Type of object. The value can be one of the following:
Value Description Proc Stored procedure
Prepared Prepared statement
Adhoc Ad hoc query1
ReplProc Replication-filter-procedure
Trigger Trigger
View View
Default Default
UsrTab User table
SysTab System table
Check CHECK constraint
Rule Rule
plan_handle varbinary(64) Identifier for the in-memory plan. This identifier is transient and remains constant only while the plan remains in the cache. This value may be used with the following dynamic management functions:
sys.dm_exec_sql_text4
sys.dm_exec_query_plan5
sys.dm_exec_plan_attributes6
pool_id   int The ID of the resource pool against which this plan memory usage is accounted for.

TSQL

Sql 2005
SELECT [bucketid], [refcounts], [usecounts], [size_in_bytes], [memory_object_address], [cacheobjtype], [objtype], [plan_handle] FROM sys.dm_exec_cached_plans
Sql 2008
SELECT [bucketid], [refcounts], [usecounts], [size_in_bytes], [memory_object_address], [cacheobjtype], [objtype], [plan_handle], [pool_id] FROM sys.dm_exec_cached_plans
Sql 2008 R2
SELECT [bucketid], [refcounts], [usecounts], [size_in_bytes], [memory_object_address], [cacheobjtype], [objtype], [plan_handle], [pool_id] FROM sys.dm_exec_cached_plans
Sql 2012
SELECT [bucketid], [refcounts], [usecounts], [size_in_bytes], [memory_object_address], [cacheobjtype], [objtype], [plan_handle], [pool_id] FROM sys.dm_exec_cached_plans

Back to Top


sys.dm_exec_connections

Returns information about the connections established to this instance of SQL Server and the details of each connection.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
session_id int Identifies the session associated with this connection. Is nullable.
most_recent_session_id int Represents the session ID for the most recent request associated with this connection. (SOAP connections can be reused by another session.) Is nullable.
connect_time datetime Timestamp when connection was established. Is not nullable.
net_transport nvarchar(40) Describes the physical transport protocol that is used by this connection. Is not nullable.
Note: Always returns Session when a connection has multiple active result sets (MARS) enabled.
protocol_type nvarchar(40) Specifies the protocol type of the payload. It currently distinguishes between TDS (TSQL) and SOAP. Is nullable.
protocol_version int Version of the data access protocol associated with this connection. Is nullable.
endpoint_id int An identifier that describes what type of connection it is. This endpoint_id can be used to query the sys.endpoints view. Is nullable.
encrypt_option nvarchar(40) Boolean value to describe whether encryption is enabled for this connection. Is not nullable.
auth_scheme nvarchar(40) The SQL Server authentication scheme for a connection. The mechanism for Windows authentication (NTLM, KERBEROS, DIGEST, BASIC, NEGOTIATE) or SQL for SQL Server authentication. Is not nullable.
node_affinity smallint Identifies the memory node to which this connection has affinity. Is not nullable.
num_reads int Number of packet reads that have occurred over this connection. Is nullable.
num_writes int Number of data packet writes that have occurred over this connection. Is nullable.
last_read datetime Timestamp when last read occurred over this connection. Is nullable.
last_write datetime Timestamp when last write occurred over this connection. Not Is nullable.
net_packet_size int Network packet size used for information and data transfer. Is nullable.
client_net_address varchar(40) Host address of the client connecting to this server. Is nullable.
client_tcp_port int Port number on the client computer that is associated with this connection. Is nullable.
local_net_address varchar(40) Represents the IP address on the server that this connection targeted. Available only for connections using the TCP transport provider. Is nullable.
local_tcp_port int Represents the server TCP port that this connection targeted if it were a connection using the TCP transport. Is nullable.
connection_id uniqueidentifier Identifies each connection uniquely. Is not nullable.
parent_connection_id uniqueidentifier Identifies the primary connection that the MARS session is using. Is nullable.
most_recent_sql_handle varbinary(64) The SQL handle of the last request executed on this connection. The most_recent_sql_handle column is always in sync with the most_recent_session_id column. Is nullable.

TSQL

Sql 2005
SELECT [session_id], [most_recent_session_id], [connect_time], [net_transport], [protocol_type], [protocol_version], [endpoint_id], [encrypt_option], [auth_scheme], [node_affinity], [num_reads], [num_writes], [last_read], [last_write], [net_packet_size], [client_net_address], [client_tcp_port], [local_net_address], [local_tcp_port], [connection_id], [parent_connection_id], [most_recent_sql_handle] FROM sys.dm_exec_connections
Sql 2008
SELECT [session_id], [most_recent_session_id], [connect_time], [net_transport], [protocol_type], [protocol_version], [endpoint_id], [encrypt_option], [auth_scheme], [node_affinity], [num_reads], [num_writes], [last_read], [last_write], [net_packet_size], [client_net_address], [client_tcp_port], [local_net_address], [local_tcp_port], [connection_id], [parent_connection_id], [most_recent_sql_handle] FROM sys.dm_exec_connections
Sql 2008 R2
SELECT [session_id], [most_recent_session_id], [connect_time], [net_transport], [protocol_type], [protocol_version], [endpoint_id], [encrypt_option], [auth_scheme], [node_affinity], [num_reads], [num_writes], [last_read], [last_write], [net_packet_size], [client_net_address], [client_tcp_port], [local_net_address], [local_tcp_port], [connection_id], [parent_connection_id], [most_recent_sql_handle] FROM sys.dm_exec_connections
Sql 2012
SELECT [session_id], [most_recent_session_id], [connect_time], [net_transport], [protocol_type], [protocol_version], [endpoint_id], [encrypt_option], [auth_scheme], [node_affinity], [num_reads], [num_writes], [last_read], [last_write], [net_packet_size], [client_net_address], [client_tcp_port], [local_net_address], [local_tcp_port], [connection_id], [parent_connection_id], [most_recent_sql_handle] FROM sys.dm_exec_connections

Back to Top


sys.dm_exec_cursors

Returns information about the cursors that are open in various databases.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
session_id int ID of the session that holds this cursor.
cursor_id int ID of the cursor object.
name nvarchar(256) Name of the cursor as defined by the user.
properties nvarchar(256) Specifies the properties of the cursor. The values of the following properties are concatenated to form the value of this column:
Declaration Interface

Cursor Type

Cursor Concurrency

Cursor scope

Cursor nesting level

For example, the value returned in this column might be "TSQL | Dynamic | Optimistic | Global (0)".
sql_handle varbinary(64) Handle to the text of the batch that declared the cursor.
statement_start_offset int Number of characters into the currently executing batch or stored procedure at which the currently executing statement starts. Can be used together with the sql_handle, the statement_end_offset, and the sys.dm_exec_sql_text1 dynamic management function to retrieve the currently executing statement for the request.
statement_end_offset int Number of characters into the currently executing batch or stored procedure at which the currently executing statement ends. Can be used together with the sql_handle, the statement_start_offset, and the sys.dm_exec_sql_text dynamic management function to retrieve the currently executing statement for the request.
plan_generation_num bigint A sequence number that can be used to distinguish between instances of plans after recompilation.
creation_time datetime Timestamp when this cursor was created.
is_open bit Specifies whether the cursor is open.
is_async_population bit Specifies whether the background thread is still asynchronously populating a KEYSET or STATIC cursor.
is_close_on_commit bit Specifies whether the cursor was declared by using CURSOR_CLOSE_ON_COMMIT.
1 = Cursor will be closed when the transaction ends.
fetch_status int Returns last fetch status of the cursor. This is the last returned @@FETCH_STATUS value.
fetch_buffer_size int Returns information about the size of the fetch buffer.
1 = Transact-SQL cursors. This can be set to a higher value for API cursors.
fetch_buffer_start int For FAST_FORWARD and DYNAMIC cursors, it returns 0 if the cursor is not open or if it is positioned before the first row. Otherwise, it returns -1.
For STATIC and KEYSET cursors, it returns 0 if the cursor is not open, and -1 if the cursor is positioned beyond the last row.
Otherwise, it returns the row number in which it is positioned.
ansi_position int Cursor position within the fetch buffer.
worker_time bigint Time spent, in microseconds, by the workers executing this cursor.
reads bigint Number of reads performed by the cursor.
writes bigint Number of writes performed by the cursor.
dormant_duration bigint Milliseconds since the last query (open or fetch) on this cursor was started.

TSQL

Sql 2005
SELECT [session_id], [cursor_id], [name], [properties], [sql_handle], [statement_start_offset], [statement_end_offset], [plan_generation_num], [creation_time], [is_open], [is_async_population], [is_close_on_commit], [fetch_status], [fetch_buffer_size], [fetch_buffer_start], [ansi_position], [worker_time], [reads], [writes], [dormant_duration] FROM sys.dm_exec_cursors
Sql 2008
SELECT [session_id], [cursor_id], [name], [properties], [sql_handle], [statement_start_offset], [statement_end_offset], [plan_generation_num], [creation_time], [is_open], [is_async_population], [is_close_on_commit], [fetch_status], [fetch_buffer_size], [fetch_buffer_start], [ansi_position], [worker_time], [reads], [writes], [dormant_duration] FROM sys.dm_exec_cursors
Sql 2008 R2
SELECT [session_id], [cursor_id], [name], [properties], [sql_handle], [statement_start_offset], [statement_end_offset], [plan_generation_num], [creation_time], [is_open], [is_async_population], [is_close_on_commit], [fetch_status], [fetch_buffer_size], [fetch_buffer_start], [ansi_position], [worker_time], [reads], [writes], [dormant_duration] FROM sys.dm_exec_cursors
Sql 2012
SELECT [session_id], [cursor_id], [name], [properties], [sql_handle], [statement_start_offset], [statement_end_offset], [plan_generation_num], [creation_time], [is_open], [is_async_population], [is_close_on_commit], [fetch_status], [fetch_buffer_size], [fetch_buffer_start], [ansi_position], [worker_time], [reads], [writes], [dormant_duration] FROM sys.dm_exec_cursors

Back to Top


sys.dm_exec_plan_attributes

Returns one row per plan attribute for the plan specified by the plan handle. You can use this table-valued function to get details about a particular plan, such as the cache key values or the number of current simultaneous executions of the plan. Note Some of the information returned through this function maps to the sys.syscacheobjects1 backward compatibility view.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
attribute varchar(128) Name of the attribute associated with this plan. One of the following:
Attribute Data type Description set_optionsintIndicates the option values that the plan was compiled with.
objectidintOne of the main keys used for looking up an object in the cache. This is the object ID stored in sys.objects3 for database objects (procedures, views, triggers, and so on). For plans of type "Adhoc" or "Prepared", it is an internal hash of the batch text.
dbidintIs the ID of the database where the entity for which we have the plan resides. For ad hoc or prepared plans, it is the database ID from which the batch is executed.
dbid_executeintFor system objects stored in the Resource database, the database ID from which the cached plan is executed. For all other cases it is 0.
user_idintID of the schema that contains the object. A value of -2 indicates that the batch submitted does not depend on implicit name resolution and can be shared among different users. This is the preferred method.
language_idsmallintID of the language of the connection that created the cache object. For more information, see sys.syslanguages (Transact-SQL)4.
date_formatsmallintDate format of the connection that created the cache object. For more information, see SET DATEFORMAT (Transact-SQL)5.
date_firsttinyintDate first value. For more information, see SET DATEFIRST (Transact-SQL)6.
statusintInternal status bits that are part of the cache lookup key.
required_cursor_optionsintCursor options specified by the user such as the cursor type.
acceptable_cursor_optionsintCursor options that SQL Server may implicitly convert to in order to support the execution of the statement. For example, the user may specify a dynamic cursor, but the query optimizer is permitted to convert this cursor type to a static cursor. For more information, see Using Implicit Cursor Conversions7.
inuse_exec_contextintNumber of currently executing batches that are using the query plan.For more information about execution context and query plans, see Execution Plan Caching and Reuse8.
free_exec_contextintNumber of cached execution contexts for the query plan that are not being currently used.
hits_exec_contextintNumber of times the execution context was obtained from the plan cache and reused, saving the overhead of recompiling the SQL statement. The value is an aggregate for all batch executions so far.
misses_exec_contextintNumber of times that an execution context could not be found in the plan cache, resulting in the creation of a new execution context for the batch execution.
removed_exec_contextintNumber of execution contexts that have been removed because of memory pressure on the cached plan.
inuse_cursorsintNumber of currently executing batches containing one or more cursors that are using the cached plan.
free_cursorsintNumber of idle or free cursors for the cached plan.
hits_cursorsintNumber of times that an inactive cursor was obtained from the cached plan and reused. The value is an aggregate for all batch executions so far.
misses_cursorsintNumber of times that an inactive cursor could not be found in the cache.
removed_cursorsintNumber of cursors that have been removed because of memory pressure on the cached plan.
sql_handlevarbinary(64)The SQL handle for the batch.
value sql_variant Value of the attribute that is associated with this plan.
is_cache_key bit Indicates whether the attribute is used as part of the cache lookup key for the plan.
set_options       int Indicates the option values that the plan was compiled with.
objectid       int One of the main keys used for looking up an object in the cache. This is the object ID stored in sys.objects3 for database objects (procedures, views, triggers, and so on). For plans of type "Adhoc" or "Prepared", it is an internal hash of the batch text.
dbid       int Is the ID of the database containing the entity the plan refers to.
For ad hoc or prepared plans, it is the database ID from which the batch is executed.
dbid_execute       int For system objects stored in the Resource database, the database ID from which the cached plan is executed. For all other cases, it is 0.
user_id       int Value of -2 indicates that the batch submitted does not depend on implicit name resolution and can be shared among different users. This is the preferred method. Any other value represents the user ID of the user submitting the query in the database.
language_id       smallint ID of the language of the connection that created the cache object. For more information, see sys.syslanguages (Transact-SQL)4.
date_format       smallint Date format of the connection that created the cache object. For more information, see SET DATEFORMAT (Transact-SQL)5.
date_first       tinyint Date first value. For more information, see SET DATEFIRST (Transact-SQL)6.
status       int Internal status bits that are part of the cache lookup key.
required_cursor_options       int Cursor options specified by the user such as the cursor type.
acceptable_cursor_options       int Cursor options that SQL Server may implicitly convert to in order to support the execution of the statement. For example, the user may specify a dynamic cursor, but the query optimizer is permitted to convert this cursor type to a static cursor.
inuse_exec_context       int Number of currently executing batches that are using the query plan.
free_exec_context       int Number of cached execution contexts for the query plan that are not being currently used.
hits_exec_context       int Number of times the execution context was obtained from the plan cache and reused, saving the overhead of recompiling the SQL statement. The value is an aggregate for all batch executions so far.
misses_exec_context       int Number of times that an execution context could not be found in the plan cache, resulting in the creation of a new execution context for the batch execution.
removed_exec_context       int Number of execution contexts that have been removed because of memory pressure on the cached plan.
inuse_cursors       int Number of currently executing batches containing one or more cursors that are using the cached plan.
free_cursors       int Number of idle or free cursors for the cached plan.
hits_cursors       int Number of times that an inactive cursor was obtained from the cached plan and reused. The value is an aggregate for all batch executions so far.
misses_cursors       int Number of times that an inactive cursor could not be found in the cache.
removed_cursors       int Number of cursors that have been removed because of memory pressure on the cached plan.
sql_handle       varbinary(64) The SQL handle for the batch.
merge_action_type       smallint The type of trigger execution plan used as the result of a MERGE statement.
0 indicates a non-trigger plan, a trigger plan that does not execute as the result of a MERGE statement, or a trigger plan that executes as the result of a MERGE statement that only specifies a DELETE action.
1 indicates an INSERT trigger plan that runs as the result of a MERGE statement.
2 indicates an UPDATE trigger plan that runs as the result of a MERGE statement.
3 indicates a DELETE trigger plan that runs as the result of a MERGE statement containing a corresponding INSERT or UPDATE action.
For nested triggers run by cascading actions, this value is the action of the MERGE statement that caused the cascade.

TSQL

Sql 2005
SELECT [attribute], [value], [is_cache_key] FROM sys.dm_exec_plan_attributes
Sql 2008
SELECT [attribute], [value], [is_cache_key] FROM sys.dm_exec_plan_attributes
Sql 2008 R2
SELECT [attribute], [value], [is_cache_key] FROM sys.dm_exec_plan_attributes
Sql 2012
SELECT [attribute], [set_options], [objectid], [dbid], [dbid_execute], [user_id], [language_id], [date_format], [date_first], [status], [required_cursor_options], [acceptable_cursor_options], [inuse_exec_context], [free_exec_context], [hits_exec_context], [misses_exec_context], [removed_exec_context], [inuse_cursors], [free_cursors], [hits_cursors], [misses_cursors], [removed_cursors], [sql_handle], [merge_action_type], [value], [is_cache_key] FROM sys.dm_exec_plan_attributes

Back to Top


sys.dm_exec_query_memory_grants

Returns information about the queries that have acquired a memory grant or that still require a memory grant to execute. Queries that do not have to wait on a memory grant will not appear in this view.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
session_id smallint ID (SPID) of the session where this query is running.
request_id int ID of the request. Unique in the context of the session.
scheduler_id int ID of the scheduler that is scheduling this query.
dop smallint Degree of parallelism of this query.
request_time datetime Date and time when this query requested the memory grant.
grant_time datetime Date and time when memory was granted for this query. NULL if memory is not granted yet.
requested_memory_kb bigint Total requested amount of memory in kilobytes.
granted_memory_kb bigint Total amount of memory actually granted in kilobytes. Can be NULL if the memory is not granted yet. For a typical situation, this value should be the same as requested_memory_kb. For index creation, the server may allow additional on-demand memory beyond initially granted memory.
required_memory_kb bigint Minimum memory required to run this query in kilobytes. requested_memory_kb is the same or larger than this amount.
used_memory_kb bigint Physical memory used at this moment in kilobytes.
max_used_memory_kb bigint Maximum physical memory used up to this moment in kilobytes.
query_cost float Estimated query cost.
timeout_sec int Time-out in seconds before this query gives up the memory grant request.
resource_semaphore_id smallint ID of the resource semaphore on which this query is waiting.
queue_id smallint ID of waiting queue where this query waits for memory grants. NULL if the memory is already granted.
wait_order int Sequential order of waiting queries within the specified queue_id. This value can change for a given query if other queries get memory grants or time out. NULL if memory is already granted.
is_next_candidate bit Candidate for next memory grant.
1 = Yes
0 = No
NULL = Memory is already granted.
wait_time_ms bigint Wait time in milliseconds. NULL if the memory is already granted.
plan_handle varbinary(64) Identifier for this query plan. Use sys.dm_exec_query_plan to extract the actual XML plan.
sql_handle varbinary(64) Identifier for Transact-SQL text for this query. Use sys.dm_exec_sql_text to get the actual Transact-SQL text.
group_id   int ID for the workload group where this query is running.
pool_id   int ID of the resource pool that this workload group belongs to.
is_small   tinyint When set to 1, indicates that this grant uses the small resource semaphore. When set to 0, indicates that a regular semaphore is used.
ideal_memory_kb   bigint Size, in kilobytes (KB), of the memory grant to fit everything into physical memory. This is based on the cardinality estimate.

TSQL

Sql 2005
SELECT [session_id], [request_id], [scheduler_id], [dop], [request_time], [grant_time], [requested_memory_kb], [granted_memory_kb], [required_memory_kb], [used_memory_kb], [max_used_memory_kb], [query_cost], [timeout_sec], [resource_semaphore_id], [queue_id], [wait_order], [is_next_candidate], [wait_time_ms], [plan_handle], [sql_handle] FROM sys.dm_exec_query_memory_grants
Sql 2008
SELECT [session_id], [request_id], [scheduler_id], [dop], [request_time], [grant_time], [requested_memory_kb], [granted_memory_kb], [required_memory_kb], [used_memory_kb], [max_used_memory_kb], [query_cost], [timeout_sec], [resource_semaphore_id], [queue_id], [wait_order], [is_next_candidate], [wait_time_ms], [plan_handle], [sql_handle], [group_id], [pool_id], [is_small], [ideal_memory_kb] FROM sys.dm_exec_query_memory_grants
Sql 2008 R2
SELECT [session_id], [request_id], [scheduler_id], [dop], [request_time], [grant_time], [requested_memory_kb], [granted_memory_kb], [required_memory_kb], [used_memory_kb], [max_used_memory_kb], [query_cost], [timeout_sec], [resource_semaphore_id], [queue_id], [wait_order], [is_next_candidate], [wait_time_ms], [plan_handle], [sql_handle], [group_id], [pool_id], [is_small], [ideal_memory_kb] FROM sys.dm_exec_query_memory_grants
Sql 2012
SELECT [session_id], [request_id], [scheduler_id], [dop], [request_time], [grant_time], [requested_memory_kb], [granted_memory_kb], [required_memory_kb], [used_memory_kb], [max_used_memory_kb], [query_cost], [timeout_sec], [resource_semaphore_id], [queue_id], [wait_order], [is_next_candidate], [wait_time_ms], [plan_handle], [sql_handle], [group_id], [pool_id], [is_small], [ideal_memory_kb] FROM sys.dm_exec_query_memory_grants

Back to Top


sys.dm_exec_query_optimizer_info

Returns detailed statistics about the operation of the SQL Server query optimizer. You can use this view when tuning a workload to identify query optimization problems or improvements. For example, you can use the total number of optimizations, the elapsed time value, and the final cost value to compare the query optimizations of the current workload and any changes observed during the tuning process. Some counters provide data that is relevant only for SQL Server internal diagnostic use. These counters are marked as "Internal only." NameData typeDescription counter nvarchar(4000) Name of optimizer statistics event. occurrence bigint Number of occurrences of optimization event for this counter. value float Average property value per event occurrence.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
counter nvarchar(8000) Name of optimizer statistics event.
occurrence bigint Number of occurrences of optimization event for this counter.
value float Average property value per event occurrence.

TSQL

Sql 2005
SELECT [counter], [occurrence], [value] FROM sys.dm_exec_query_optimizer_info
Sql 2008
SELECT [counter], [occurrence], [value] FROM sys.dm_exec_query_optimizer_info
Sql 2008 R2
SELECT [counter], [occurrence], [value] FROM sys.dm_exec_query_optimizer_info
Sql 2012
SELECT [counter], [occurrence], [value] FROM sys.dm_exec_query_optimizer_info

Back to Top


sys.dm_exec_query_plan

Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing. The XML schema for the Showplan is published and available at this Microsoft Web site1. It is also available in the directory where SQL Server is installed. Transact-SQL Syntax Conventions2

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
dbid smallint ID of the context database that was in effect when the Transact-SQL statement corresponding to this plan was compiled. For ad hoc and prepared batches, this column is null.
Column is nullable.
objectid int ID of the object (for example, stored procedure or user-defined function) for this query plan. For ad hoc and prepared batches, this column is null.
Column is nullable.
number smallint Numbered stored procedure integer. For example, a group of procedures for the orders application may be named orderproc;1, orderproc;2, and so on. For ad hoc and prepared batches, this column is null.
Column is nullable.
encrypted bit Indicates whether the corresponding stored procedure is encrypted.
0 = not encrypted
1 = encrypted
Column is not nullable.
query_plan xml Contains the compile-time Showplan representation of the query execution plan that is specified with plan_handle. The Showplan is in XML format. One plan is generated for each batch that contains, for example ad hoc Transact-SQL statements, stored procedure calls, and user-defined function calls.
Column is nullable.

TSQL

Sql 2005
SELECT [dbid], [objectid], [number], [encrypted], [query_plan] FROM sys.dm_exec_query_plan
Sql 2008
SELECT [dbid], [objectid], [number], [encrypted], [query_plan] FROM sys.dm_exec_query_plan
Sql 2008 R2
SELECT [dbid], [objectid], [number], [encrypted], [query_plan] FROM sys.dm_exec_query_plan
Sql 2012
SELECT [dbid], [objectid], [number], [encrypted], [query_plan] FROM sys.dm_exec_query_plan

Back to Top


sys.dm_exec_query_resource_semaphores

Returns the information about the current query-resource semaphore status. sys.dm_exec_query_resource_semaphores provides general query-execution memory status and allows you to determine whether the system can access enough memory. This view complements memory information obtained from sys.dm_os_memory_clerks1 to provide a complete picture of server memory status. sys.dm_exec_query_resource_semaphores returns one row for the regular resource semaphore and another row for the small-query resource semaphore.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
resource_semaphore_id smallint ID of the resource semaphore. 0 for the regular resource semaphore and 1 for the small-query resource semaphore.
target_memory_kb bigint Grant usage target in kilobytes.
max_target_memory_kb bigint Maximum potential target in kilobytes. NULL for the small-query resource semaphore.
total_memory_kb bigint Memory held by the resource semaphore in kilobytes. If the system is under memory pressure or if forced minimum memory is granted frequently, this value can be larger than the target_memory_kb or max_target_memory_kb values. Total memory is a sum of available and granted memory.
available_memory_kb bigint Memory available for a new grant in kilobytes.
granted_memory_kb bigint Total granted memory in kilobytes.
used_memory_kb bigint Physically used part of granted memory in kilobytes.
grantee_count int Number of active queries that have their grants satisfied.
waiter_count int Number of queries waiting for grants to be satisfied.
timeout_error_count bigint Total number of time-out errors since server startup. NULL for the small-query resource semaphore.
forced_grant_count bigint Total number of forced minimum-memory grants since server startup. NULL for the small-query resource semaphore.
pool_id   int ID of the resource pool to which this resource semaphore belongs.

TSQL

Sql 2005
SELECT [resource_semaphore_id], [target_memory_kb], [max_target_memory_kb], [total_memory_kb], [available_memory_kb], [granted_memory_kb], [used_memory_kb], [grantee_count], [waiter_count], [timeout_error_count], [forced_grant_count] FROM sys.dm_exec_query_resource_semaphores
Sql 2008
SELECT [resource_semaphore_id], [target_memory_kb], [max_target_memory_kb], [total_memory_kb], [available_memory_kb], [granted_memory_kb], [used_memory_kb], [grantee_count], [waiter_count], [timeout_error_count], [forced_grant_count], [pool_id] FROM sys.dm_exec_query_resource_semaphores
Sql 2008 R2
SELECT [resource_semaphore_id], [target_memory_kb], [max_target_memory_kb], [total_memory_kb], [available_memory_kb], [granted_memory_kb], [used_memory_kb], [grantee_count], [waiter_count], [timeout_error_count], [forced_grant_count], [pool_id] FROM sys.dm_exec_query_resource_semaphores
Sql 2012
SELECT [resource_semaphore_id], [target_memory_kb], [max_target_memory_kb], [total_memory_kb], [available_memory_kb], [granted_memory_kb], [used_memory_kb], [grantee_count], [waiter_count], [timeout_error_count], [forced_grant_count], [pool_id] FROM sys.dm_exec_query_resource_semaphores

Back to Top


sys.dm_exec_query_stats

Returns aggregate performance statistics for cached query plans in SQL Server 2012. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view. Note An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server. More accurate results may be determined by rerunning the query.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
sql_handle varbinary(64) Is a token that refers to the batch or stored procedure that the query is part of.
sql_handle, together with statement_start_offset and statement_end_offset, can be used to retrieve the SQL text of the query by calling the sys.dm_exec_sql_text dynamic management function.
statement_start_offset int Indicates, in bytes, beginning with 0, the starting position of the query that the row describes within the text of its batch or persisted object.
statement_end_offset int Indicates, in bytes, starting with 0, the ending position of the query that the row describes within the text of its batch or persisted object. A value of -1 indicates the end of the batch.
plan_generation_num bigint A sequence number that can be used to distinguish between instances of plans after a recompile.
plan_handle varbinary(64) A token that refers to the compiled plan that the query is part of. This value can be passed to the sys.dm_exec_query_plan1 dynamic management function to obtain the query plan.
creation_time datetime Time at which the plan was compiled.
last_execution_time datetime Last time at which the plan was executed.
execution_count bigint Number of times that the plan has been executed since it was last compiled.
total_worker_time bigint Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled.
last_worker_time bigint CPU time, in microseconds, that was consumed the last time the plan was executed.
min_worker_time bigint Minimum CPU time, in microseconds, that this plan has ever consumed during a single execution.
max_worker_time bigint Maximum CPU time, in microseconds, that this plan has ever consumed during a single execution.
total_physical_reads bigint Total number of physical reads performed by executions of this plan since it was compiled.
last_physical_reads bigint Number of physical reads performed the last time the plan was executed.
min_physical_reads bigint Minimum number of physical reads that this plan has ever performed during a single execution.
max_physical_reads bigint Maximum number of physical reads that this plan has ever performed during a single execution.
total_logical_writes bigint Total number of logical writes performed by executions of this plan since it was compiled.
last_logical_writes bigint Number of logical writes performed the last time the plan was executed.
min_logical_writes bigint Minimum number of logical writes that this plan has ever performed during a single execution.
max_logical_writes bigint Maximum number of logical writes that this plan has ever performed during a single execution.
total_logical_reads bigint Total number of logical reads performed by executions of this plan since it was compiled.
last_logical_reads bigint Number of logical reads performed the last time the plan was executed.
min_logical_reads bigint Minimum number of logical reads that this plan has ever performed during a single execution.
max_logical_reads bigint Maximum number of logical reads that this plan has ever performed during a single execution.
total_clr_time bigint Time, in microseconds, consumed inside Microsoft .NET Framework common language runtime (CLR) objects by executions of this plan since it was compiled. The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
last_clr_time bigint Time consumed by execution inside .NET Framework CLR objects during the last execution of this plan. The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
min_clr_time bigint Minimum time, in microseconds, that this plan has ever consumed inside .NET Framework CLR objects during a single execution. The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
max_clr_time bigint Maximum time, in microseconds, that this plan has ever consumed inside the .NET Framework CLR during a single execution. The CLR objects can be stored procedures, functions, triggers, types, and aggregates.
total_elapsed_time bigint Total elapsed time, in microseconds, for completed executions of this plan.
last_elapsed_time bigint Elapsed time, in microseconds, for the most recently completed execution of this plan.
min_elapsed_time bigint Minimum elapsed time, in microseconds, for any completed execution of this plan.
max_elapsed_time bigint Maximum elapsed time, in microseconds, for any completed execution of this plan.
query_hash   Binary(8) Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values. For more information, see Finding and Tuning Similar Queries by Using Query and Query Plan Hashes2.
query_plan_hash   binary(8) Binary hash value calculated on the query execution plan and used to identify similar query execution plans. You can use query plan hash to find the cumulative cost of queries with similar execution plans. For more information, see Finding and Tuning Similar Queries by Using Query and Query Plan Hashes2.
total_rows     bigint Total number of rows returned by the query. Cannot be null.
last_rows     bigint Number of rows returned by the last execution of the query. Cannot be null.
min_rows     bigint Minimum number of rows returned by the query over the number of times that the plan has been executed since it was last compiled. Cannot be null.
max_rows     bigint Maximum number of rows returned by the query over the number of times that the plan has been executed since it was last compiled. Cannot be null.

TSQL

Sql 2005
SELECT [sql_handle], [statement_start_offset], [statement_end_offset], [plan_generation_num], [plan_handle], [creation_time], [last_execution_time], [execution_count], [total_worker_time], [last_worker_time], [min_worker_time], [max_worker_time], [total_physical_reads], [last_physical_reads], [min_physical_reads], [max_physical_reads], [total_logical_writes], [last_logical_writes], [min_logical_writes], [max_logical_writes], [total_logical_reads], [last_logical_reads], [min_logical_reads], [max_logical_reads], [total_clr_time], [last_clr_time], [min_clr_time], [max_clr_time], [total_elapsed_time], [last_elapsed_time], [min_elapsed_time], [max_elapsed_time] FROM sys.dm_exec_query_stats
Sql 2008
SELECT [sql_handle], [statement_start_offset], [statement_end_offset], [plan_generation_num], [plan_handle], [creation_time], [last_execution_time], [execution_count], [total_worker_time], [last_worker_time], [min_worker_time], [max_worker_time], [total_physical_reads], [last_physical_reads], [min_physical_reads], [max_physical_reads], [total_logical_writes], [last_logical_writes], [min_logical_writes], [max_logical_writes], [total_logical_reads], [last_logical_reads], [min_logical_reads], [max_logical_reads], [total_clr_time], [last_clr_time], [min_clr_time], [max_clr_time], [total_elapsed_time], [last_elapsed_time], [min_elapsed_time], [max_elapsed_time], [query_hash], [query_plan_hash] FROM sys.dm_exec_query_stats
Sql 2008 R2
SELECT [sql_handle], [statement_start_offset], [statement_end_offset], [plan_generation_num], [plan_handle], [creation_time], [last_execution_time], [execution_count], [total_worker_time], [last_worker_time], [min_worker_time], [max_worker_time], [total_physical_reads], [last_physical_reads], [min_physical_reads], [max_physical_reads], [total_logical_writes], [last_logical_writes], [min_logical_writes], [max_logical_writes], [total_logical_reads], [last_logical_reads], [min_logical_reads], [max_logical_reads], [total_clr_time], [last_clr_time], [min_clr_time], [max_clr_time], [total_elapsed_time], [last_elapsed_time], [min_elapsed_time], [max_elapsed_time], [query_hash], [query_plan_hash], [total_rows], [last_rows], [min_rows], [max_rows] FROM sys.dm_exec_query_stats
Sql 2012
SELECT [sql_handle], [statement_start_offset], [statement_end_offset], [plan_generation_num], [plan_handle], [creation_time], [last_execution_time], [execution_count], [total_worker_time], [last_worker_time], [min_worker_time], [max_worker_time], [total_physical_reads], [last_physical_reads], [min_physical_reads], [max_physical_reads], [total_logical_writes], [last_logical_writes], [min_logical_writes], [max_logical_writes], [total_logical_reads], [last_logical_reads], [min_logical_reads], [max_logical_reads], [total_clr_time], [last_clr_time], [min_clr_time], [max_clr_time], [total_elapsed_time], [last_elapsed_time], [min_elapsed_time], [max_elapsed_time], [query_hash], [query_plan_hash], [total_rows], [last_rows], [min_rows], [max_rows] FROM sys.dm_exec_query_stats

Back to Top


sys.dm_exec_requests

Returns information about each request that is executing within SQL Server. Note 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. Time values returned by this dynamic management view do not include time spent in preemptive mode.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
session_id smallint ID of the session to which this request is related. Is not nullable.
request_id int ID of the request. Unique in the context of the session. Is not nullable.
start_time datetime Time when the request is scheduled to run. Is not nullable.
status nvarchar(60) Status of the request. Possible values are as follows:
Background. The request is a background thread such as Resource Monitor or Deadlock Monitor.

Running. The request is running.

Runnable. The request is running and temporarily scheduled out because it is running out of quorum.

Sleeping. There is no work to be done.

Pending. The request is waiting for a worker to pick it up.

Suspended. The request is waiting for some event.

Is not nullable.
command nvarchar(32) Identifies the type of command that is being processed. Common command types include the following:
SELECT

INSERT

UPDATE

DELETE

BACKUP LOG

BACKUP DB

DBCC

WAITFOR

The text of the request can be retrieved by using the sys.dm_exec_sql_text dynamic management function with the corresponding sql_handle for the request. Internal system processes set the command, depending on the type of task that they perform. Tasks can include the following:
LOCK MONITOR

CHECKPOINTLAZY

WRITER

Is not nullable.
sql_handle varbinary(64) The handle to the request's SQL statement. This handle can be used to retrieve the actual statement text from the sys.dm_exec_sql_text2dynamic management function. Is not nullable.
statement_start_offset int Starting character position of the executing statement in the executing batch or stored procedure. Can be used together with the statement_end_offset, the sys.dm_exec_sql_text dynamic management function, and the sql_handle to retrieve the executing statement for the request. Is nullable.
statement_end_offset int Ending character position of the executing statement in the executing batch or stored procedure. Can be used together with the statement_start_offset, the sys.dm_exec_sql_text dynamic management function, and the sql_handle to retrieve the executing statement for the request. Is nullable.
plan_handle varbinary(64) The handle to the query plan of the request. To see the query plan, use with the sys.dm_exec_query_plan dynamic management function. To query the plan cache, use the sys.dm_exec_cached_plans dynamic management view. To see the plan attributes, use the sys.dm_exec_plan_attributes function. Is nullable.
database_id smallint ID of the database the request is running under. For more database information, query the sys.databases catalog view; or to obtain the database name, use the db_name()intrinsic function. Is not nullable.
user_id int User ID the request is running under. For more user information, query the sys.database_principals catalog view. Is not nullable.
connection_id uniqueidentifier ID of the connection on which the request arrived. For more information about the physical or logical connection, query the sys.dm_exec_connections dynamic management view. Is nullable.
blocking_session_id smallint ID of the session that is blocking the request. If this column is 0, the request is not blocked, or information for 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 because of internal latch state transitions.
wait_type nvarchar(60) If the request is blocked, this column returns the type of wait. Is nullable.
wait_time int If the request is blocked, this column returns the duration in milliseconds, of the current wait. Is not nullable.
last_wait_type nvarchar(64) If this request has previously been blocked, this column returns the type of the last wait. Is not nullable.
wait_resource nvarchar(512) If the request is blocked, this column returns the resource for which the request is waiting. Is not nullable.
open_transaction_count int Number of transactions that are open for this request. Is not nullable.
open_resultset_count int Number of result sets that are open for this request. Is not nullable.
transaction_id bigint ID of the transaction in which this request executes. This ID is unique for an instance of SQL Server. Use to query the sys.dm_tran_active_transactions, sys.dm_tran_locks, or sys.dm_tran_database_transactions dynamic management views. Is not nullable.
context_info varbinary(128) Value from the SET CONTEXT_INFO statement for the request. Is nullable.
percent_complete real Percent of work completed for certain operations, rollbacks included.
Note: This does not provide progress data for queries.

Is not nullable.
estimated_completion_time bigint Internal only. Is not nullable.
cpu_time int CPU time in milliseconds that is used by the request. Is not nullable.
total_elapsed_time int Total time elapsed in milliseconds since the request arrived. Is not nullable.
scheduler_id int ID of the scheduler scheduling this request. For more information about this scheduler, query the sys.dm_os_schedulers dynamic management view. Is not nullable.
task_address varbinary(8) Memory address allocated to the task that is associated with this request. For more information about this task, query the sys.dm_os_tasks dynamic management view. Is nullable.
reads bigint Number of reads performed by this request. Is not nullable.
writes bigint Number of writes performed by this request. Is not nullable.
logical_reads bigint Number of logical reads that have been performed by the request. Is not nullable.
text_size int TEXTSIZE setting for this request. Is not nullable.
language nvarchar(256) Language setting for the request. Is nullable.
date_format nvarchar(3) DATEFORMAT setting for the request. Is nullable.
date_first smallint DATEFIRST setting for the request. Is not nullable.
quoted_identifier bit 1 = QUOTED_IDENTIFIER is ON for the request. Otherwise, it is 0.
Is not nullable.
arithabort bit 1 = ARITHABORT setting is ON for the request. Otherwise, it is 0.
Is not nullable.
ansi_null_dflt_on bit 1 = ANSI_NULL_DFLT_ON setting is ON for the request. Otherwise, it is 0.
Is not nullable.
ansi_defaults bit 1 = ANSI_DEFAULTS setting is ON for the request. Otherwise, it is 0.
Is not nullable.
ansi_warnings bit 1 = ANSI_WARNINGS setting is ON for the request. Otherwise, it is 0.
Is not nullable.
ansi_padding bit 1 = ANSI_PADDING setting is ON for the request.
Otherwise, it is 0.
Is not nullable.
ansi_nulls bit 1 = ANSI_NULLS setting is ON for the request. Otherwise, it is 0.
Is not nullable.
concat_null_yields_null bit 1 = CONCAT_NULL_YIELDS_NULL setting is ON for the request. Otherwise, it is 0.
Is not nullable.
transaction_isolation_level smallint Transaction isolation level of this request. Possible values are as follows:
0 = Unspecified
1 = ReadUncomitted
2 = ReadCommitted
3 = Repeatable
4 = Serializable
5 = Snapshot
Is not nullable.
lock_timeout int Lock time-out period in milliseconds for this request. Is not nullable.
deadlock_priority int DEADLOCK_PRIORITY setting for the request. Is not nullable.
row_count bigint Number of rows that have been returned to the client by this request. Is not nullable.
prev_error int Last error that occurred during the execution of the request. Is not nullable.
nest_level int Nesting level of code that is executing on the request. Is not nullable.
granted_query_memory int Number of pages allocated to the execution of a query on the request. Is not nullable.
executing_managed_code bit Indicates whether this request is executing common language runtime objects, such as routines, types, and triggers. It is set for the full-time a common language runtime object is on the stack, even when it runs Transact-SQL from common language runtime. Is not nullable.
group_id   int ID of the workload group to which this query belongs. Is not nullable.
query_hash   binary(8) Binary hash value calculated on the query and used to identify queries with similar logic. You can use the query hash to determine the aggregate resource usage for queries that differ only by literal values. For more information, see Finding and Tuning Similar Queries by Using Query and Query Plan Hashes1.
query_plan_hash   binary(8) Binary hash value calculated on the query execution plan and used to identify similar query execution plans. You can use query plan hash to find the cumulative cost of queries with similar execution plans. For more information, see Finding and Tuning Similar Queries by Using Query and Query Plan Hashes1.

TSQL

Sql 2005
SELECT [session_id], [request_id], [start_time], [status], [command], [sql_handle], [statement_start_offset], [statement_end_offset], [plan_handle], [database_id], [user_id], [connection_id], [blocking_session_id], [wait_type], [wait_time], [last_wait_type], [wait_resource], [open_transaction_count], [open_resultset_count], [transaction_id], [context_info], [percent_complete], [estimated_completion_time], [cpu_time], [total_elapsed_time], [scheduler_id], [task_address], [reads], [writes], [logical_reads], [text_size], [language], [date_format], [date_first], [quoted_identifier], [arithabort], [ansi_null_dflt_on], [ansi_defaults], [ansi_warnings], [ansi_padding], [ansi_nulls], [concat_null_yields_null], [transaction_isolation_level], [lock_timeout], [deadlock_priority], [row_count], [prev_error], [nest_level], [granted_query_memory], [executing_managed_code] FROM sys.dm_exec_requests
Sql 2008
SELECT [session_id], [request_id], [start_time], [status], [command], [sql_handle], [statement_start_offset], [statement_end_offset], [plan_handle], [database_id], [user_id], [connection_id], [blocking_session_id], [wait_type], [wait_time], [last_wait_type], [wait_resource], [open_transaction_count], [open_resultset_count], [transaction_id], [context_info], [percent_complete], [estimated_completion_time], [cpu_time], [total_elapsed_time], [scheduler_id], [task_address], [reads], [writes], [logical_reads], [text_size], [language], [date_format], [date_first], [quoted_identifier], [arithabort], [ansi_null_dflt_on], [ansi_defaults], [ansi_warnings], [ansi_padding], [ansi_nulls], [concat_null_yields_null], [transaction_isolation_level], [lock_timeout], [deadlock_priority], [row_count], [prev_error], [nest_level], [granted_query_memory], [executing_managed_code], [group_id], [query_hash], [query_plan_hash] FROM sys.dm_exec_requests
Sql 2008 R2
SELECT [session_id], [request_id], [start_time], [status], [command], [sql_handle], [statement_start_offset], [statement_end_offset], [plan_handle], [database_id], [user_id], [connection_id], [blocking_session_id], [wait_type], [wait_time], [last_wait_type], [wait_resource], [open_transaction_count], [open_resultset_count], [transaction_id], [context_info], [percent_complete], [estimated_completion_time], [cpu_time], [total_elapsed_time], [scheduler_id], [task_address], [reads], [writes], [logical_reads], [text_size], [language], [date_format], [date_first], [quoted_identifier], [arithabort], [ansi_null_dflt_on], [ansi_defaults], [ansi_warnings], [ansi_padding], [ansi_nulls], [concat_null_yields_null], [transaction_isolation_level], [lock_timeout], [deadlock_priority], [row_count], [prev_error], [nest_level], [granted_query_memory], [executing_managed_code], [group_id], [query_hash], [query_plan_hash] FROM sys.dm_exec_requests
Sql 2012
SELECT [session_id], [request_id], [start_time], [status], [command], [sql_handle], [statement_start_offset], [statement_end_offset], [plan_handle], [database_id], [user_id], [connection_id], [blocking_session_id], [wait_type], [wait_time], [last_wait_type], [wait_resource], [open_transaction_count], [open_resultset_count], [transaction_id], [context_info], [percent_complete], [estimated_completion_time], [cpu_time], [total_elapsed_time], [scheduler_id], [task_address], [reads], [writes], [logical_reads], [text_size], [language], [date_format], [date_first], [quoted_identifier], [arithabort], [ansi_null_dflt_on], [ansi_defaults], [ansi_warnings], [ansi_padding], [ansi_nulls], [concat_null_yields_null], [transaction_isolation_level], [lock_timeout], [deadlock_priority], [row_count], [prev_error], [nest_level], [granted_query_memory], [executing_managed_code], [group_id], [query_hash], [query_plan_hash] FROM sys.dm_exec_requests

Back to Top


sys.dm_exec_sessions

Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. Use sys.dm_exec_sessions to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions. The sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests dynamic management views map to the sys.sysprocesses1 system table.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
session_id smallint Identifies the session associated with each active primary connection. Not NULLABLE.
login_time datetime Time when session was established. Not NULLABLE.
host_name nvarchar(128) Name of the client workstation that is specific to a session. The value is NULL for internal sessions. NULLABLE.
program_name nvarchar(128) Name of client program that initiated the session. The value is NULL for internal sessions. NULLABLE.
host_process_id int Process ID of the client program that initiated the session. The value is NULL for internal sessions. NULLABLE.
client_version int TDS protocol version of the interface that is used by the client to connect to the server. The value is NULL for internal sessions. NULLABLE.
client_interface_name nvarchar(32) Protocol name that is used by the client to connect to the server. The value is NULL for internal sessions. NULLABLE.
security_id varbinary(85) Microsoft Windows security ID associated with the login. Not NULLABLE.
login_name nvarchar(128) SQL Server login name under which the session is currently executing. For the original login name that created the session, see original_login_name. Can be a SQL Server authenticated login name or a Windows authenticated domain user name. Not NULLABLE.
nt_domain nvarchar(128) Windows domain for the client if the session is using Windows Authentication or a trusted connection. This value is NULL for internal sessions and nondomain users. NULLABLE.
nt_user_name nvarchar(128) Windows user name for the client if the session is using Windows Authentication or a trusted connection. This value is NULL for internal sessions and non-domain users. NULLABLE.
status nvarchar(30) Status of the session. Possible values:
Running - Currently running one or more requests

Sleeping - Currently running no requests

Dormant €“ Session has been reset because of connection pooling and is now in prelogin state.

Not NULLABLE.
context_info varbinary(128) CONTEXT_INFO value for the session. The context information is set by the user by using the SET CONTEXT_INFO2 statement. NULLABLE.
cpu_time int CPU time, in milliseconds, that was used by this session. Not NULLABLE.
memory_usage int Number of 8-KB pages of memory used by this session. Not NULLABLE.
total_scheduled_time int Total time, in milliseconds, for which the session (requests within) were scheduled for execution. Not NULLABLE.
total_elapsed_time int Time, in milliseconds, since the session was established. Not NULLABLE.
endpoint_id int ID of the Endpoint associated with the session. Not NULLABLE.
last_request_start_time datetime Time at which the last request on the session began. This includes the currently executing request. Not NULLABLE.
last_request_end_time datetime Time of the last completion of a request on the session. NULLABLE.
reads bigint Number of reads performed, by requests in this session, during this session. Not NULLABLE.
writes bigint Number of writes performed, by requests in this session, during this session. Not NULLABLE.
logical_reads bigint Number of logical reads that have been performed on the session. Not NULLABLE.
is_user_process bit 0 if the session is a system session. Otherwise, it is 1. Not NULLABLE.
text_size int TEXTSIZE setting for the session. Not NULLABLE.
language nvarchar(128) LANGUAGE setting for the session. NULLABLE.
date_format nvarchar(3) DATEFORMAT setting for the session. NULLABLE.
date_first smallint DATEFIRST setting for the session. Not NULLABLE.
quoted_identifier bit QUOTED_IDENTIFIER setting for the session. Not NULLABLE.
arithabort bit ARITHABORT setting for the session. Not NULLABLE.
ansi_null_dflt_on bit ANSI_NULL_DFLT_ON setting for the session. Not NULLABLE.
ansi_defaults bit ANSI_DEFAULTS setting for the session. Not NULLABLE.
ansi_warnings bit ANSI_WARNINGS setting for the session. Not NULLABLE.
ansi_padding bit ANSI_PADDING setting for the session. Not NULLABLE.
ansi_nulls bit ANSI_NULLS setting for the session. Not NULLABLE.
concat_null_yields_null bit CONCAT_NULL_YIELDS_NULL setting for the session. Not NULLABLE.
transaction_isolation_level smallint Transaction isolation level of the session.
0 = Unspecified
1 = ReadUncomitted
2 = ReadCommitted
3 = Repeatable
4 = Serializable
5 = Snapshot
Not NULLABLE.
lock_timeout int LOCK_TIMEOUT setting for the session. The value is in milliseconds. Not NULLABLE.
deadlock_priority int DEADLOCK_PRIORITY setting for the session. Not NULLABLE.
row_count bigint Number of rows returned on the session up to this point. Not NULLABLE.
prev_error int ID of the last error returned on the session. Not NULLABLE.
original_security_id varbinary(85) Microsoft Windows security ID that is associated with the original_login_name. Not NULLABLE.
original_login_name nvarchar(128) SQL Server login name that the client used to create this session. Can be a SQL Server authenticated login name or a Windows authenticated domain user name. Note that the session could have gone through many implicit or explicit context switches after the initial connection. For example, if EXECUTE AS3 is used. Not NULLABLE.
last_successful_logon datetime Time of the last successful logon for the original_login_name before the current session started.
last_unsuccessful_logon datetime Time of the last unsuccessful logon attempt for the original_login_name before the current session started.
unsuccessful_logons bigint Number of unsuccessful logon attempts for the original_login_name between the last_successful_logon and login_time.
group_id   int ID of the workload group to which this session belongs. Is not nullable.
authenticating_database_id       int ID of the database authenticating the principal. For Logins, the value will be 0. For contained database users, the value will be the database ID of the contained database.

TSQL

Sql 2005
SELECT [session_id], [login_time], [host_name], [program_name], [host_process_id], [client_version], [client_interface_name], [security_id], [login_name], [nt_domain], [nt_user_name], [status], [context_info], [cpu_time], [memory_usage], [total_scheduled_time], [total_elapsed_time], [endpoint_id], [last_request_start_time], [last_request_end_time], [reads], [writes], [logical_reads], [is_user_process], [text_size], [language], [date_format], [date_first], [quoted_identifier], [arithabort], [ansi_null_dflt_on], [ansi_defaults], [ansi_warnings], [ansi_padding], [ansi_nulls], [concat_null_yields_null], [transaction_isolation_level], [lock_timeout], [deadlock_priority], [row_count], [prev_error], [original_security_id], [original_login_name], [last_successful_logon], [last_unsuccessful_logon], [unsuccessful_logons] FROM sys.dm_exec_sessions
Sql 2008
SELECT [session_id], [login_time], [host_name], [program_name], [host_process_id], [client_version], [client_interface_name], [security_id], [login_name], [nt_domain], [nt_user_name], [status], [context_info], [cpu_time], [memory_usage], [total_scheduled_time], [total_elapsed_time], [endpoint_id], [last_request_start_time], [last_request_end_time], [reads], [writes], [logical_reads], [is_user_process], [text_size], [language], [date_format], [date_first], [quoted_identifier], [arithabort], [ansi_null_dflt_on], [ansi_defaults], [ansi_warnings], [ansi_padding], [ansi_nulls], [concat_null_yields_null], [transaction_isolation_level], [lock_timeout], [deadlock_priority], [row_count], [prev_error], [original_security_id], [original_login_name], [last_successful_logon], [last_unsuccessful_logon], [unsuccessful_logons], [group_id] FROM sys.dm_exec_sessions
Sql 2008 R2
SELECT [session_id], [login_time], [host_name], [program_name], [host_process_id], [client_version], [client_interface_name], [security_id], [login_name], [nt_domain], [nt_user_name], [status], [context_info], [cpu_time], [memory_usage], [total_scheduled_time], [total_elapsed_time], [endpoint_id], [last_request_start_time], [last_request_end_time], [reads], [writes], [logical_reads], [is_user_process], [text_size], [language], [date_format], [date_first], [quoted_identifier], [arithabort], [ansi_null_dflt_on], [ansi_defaults], [ansi_warnings], [ansi_padding], [ansi_nulls], [concat_null_yields_null], [transaction_isolation_level], [lock_timeout], [deadlock_priority], [row_count], [prev_error], [original_security_id], [original_login_name], [last_successful_logon], [last_unsuccessful_logon], [unsuccessful_logons], [group_id] FROM sys.dm_exec_sessions
Sql 2012
SELECT [session_id], [login_time], [host_name], [program_name], [host_process_id], [client_version], [client_interface_name], [security_id], [login_name], [nt_domain], [nt_user_name], [status], [context_info], [cpu_time], [memory_usage], [total_scheduled_time], [total_elapsed_time], [endpoint_id], [last_request_start_time], [last_request_end_time], [reads], [writes], [logical_reads], [is_user_process], [text_size], [language], [date_format], [date_first], [quoted_identifier], [arithabort], [ansi_null_dflt_on], [ansi_defaults], [ansi_warnings], [ansi_padding], [ansi_nulls], [concat_null_yields_null], [transaction_isolation_level], [lock_timeout], [deadlock_priority], [row_count], [prev_error], [original_security_id], [original_login_name], [last_successful_logon], [last_unsuccessful_logon], [unsuccessful_logons], [group_id], [authenticating_database_id] FROM sys.dm_exec_sessions

Back to Top


sys.dm_exec_sql_text

Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
dbid smallint ID of database.
Is NULL for ad hoc and prepared SQL statements.
objectid int ID of object.
Is NULL for ad hoc and prepared SQL statements.
number smallint For a numbered stored procedure, this column returns the number of the stored procedure. For more information, see sys.numbered_procedures (Transact-SQL)1.
Is NULL for ad hoc and prepared SQL statements.
encrypted bit 1 = SQL text is encrypted.
0 = SQL text is not encrypted.
text nvarchar(max) Text of the SQL query.
Is NULL for encrypted objects.

TSQL

Sql 2005
SELECT [dbid], [objectid], [number], [encrypted], [text] FROM sys.dm_exec_sql_text
Sql 2008
SELECT [dbid], [objectid], [number], [encrypted], [text] FROM sys.dm_exec_sql_text
Sql 2008 R2
SELECT [dbid], [objectid], [number], [encrypted], [text] FROM sys.dm_exec_sql_text
Sql 2012
SELECT [dbid], [objectid], [number], [encrypted], [text] FROM sys.dm_exec_sql_text

Back to Top


sys.dm_exec_text_query_plan

Returns the Showplan in text format for a Transact-SQL batch or for a specific statement within the batch. The query plan specified by the plan handle can either be cached or currently executing. This table-valued function is similar to sys.dm_exec_query_plan (Transact-SQL)1, but has the following differences: The output of the query plan is returned in text format. The output of the query plan is not limited in size. Individual statements within the batch can be specified. Transact-SQL Syntax Conventions2

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
dbid smallint ID of the context database that was in effect when the Transact-SQL statement corresponding to this plan was compiled. For ad hoc and prepared batches, this column is null.
Column is nullable.
objectid int ID of the object (for example, stored procedure or user-defined function) for this query plan. For ad hoc and prepared batches, this column is null.
Column is nullable.
number smallint Numbered stored procedure integer. For example, a group of procedures for the orders application may be named orderproc;1, orderproc;2, and so on. For ad hoc and prepared batches, this column is null.
Column is nullable.
encrypted bit Indicates whether the corresponding stored procedure is encrypted.
0 = not encrypted
1 = encrypted
Column is not nullable.
query_plan nvarchar(max) Contains the compile-time Showplan representation of the query execution plan that is specified with plan_handle. The Showplan is in text format. One plan is generated for each batch that contains, for example ad hoc Transact-SQL statements, stored procedure calls, and user-defined function calls.
Column is nullable.

TSQL

Sql 2005
SELECT [dbid], [objectid], [number], [encrypted], [query_plan] FROM sys.dm_exec_text_query_plan
Sql 2008
SELECT [dbid], [objectid], [number], [encrypted], [query_plan] FROM sys.dm_exec_text_query_plan
Sql 2008 R2
SELECT [dbid], [objectid], [number], [encrypted], [query_plan] FROM sys.dm_exec_text_query_plan
Sql 2012
SELECT [dbid], [objectid], [number], [encrypted], [query_plan] FROM sys.dm_exec_text_query_plan

Back to Top


sys.dm_exec_xml_handles

Returns information about active handles that have been opened by sp_xml_preparedocument.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
session_id int Session ID of the session that holds this XML document handle.
document_id int XML document handle ID returned by sp_xml_preparedocument.
namespace_document_id int Internal handle ID used for the associated namespace document that has been passed as the third parameter to sp_xml_preparedocument. NULL if there is no namespace document.
sql_handle varbinary(64) Handle to the text of the SQL code where the handle has been defined.
statement_start_offset int Number of characters into the currently executing batch or stored procedure at which the sp_xml_preparedocument call occurs. Can be used together with the sql_handle, the statement_end_offset, and the sys.dm_exec_sql_text dynamic management function to retrieve the currently executing statement for the request.
statement_end_offset int Number of characters into the currently executing batch or stored procedure at which the sp_xml_preparedocument call occurs. Can be used together with the sql_handle, the statement_start_offset, and the sys.dm_exec_sql_text dynamic management function to retrieve the currently executing statement for the request.
creation_time datetime Timestamp when sp_xml_preparedocument was called.
original_document_size_bytes bigint Size of the unparsed XML document in bytes.
original_namespace_document_size_bytes bigint Size of the unparsed XML namespace document, in bytes. NULL if there is no namespace document.
num_openxml_calls bigint Number of OPENXML calls with this document handle.
row_count bigint Number of rows returned by all previous OPENXML calls for this document handle.
dormant_duration_ms bigint Milliseconds since the last OPENXML call. If OPENXML has not been called, returns milliseconds since the sp_xml_preparedocument call.

TSQL

Sql 2005
SELECT [session_id], [document_id], [namespace_document_id], [sql_handle], [statement_start_offset], [statement_end_offset], [creation_time], [original_document_size_bytes], [original_namespace_document_size_bytes], [num_openxml_calls], [row_count], [dormant_duration_ms] FROM sys.dm_exec_xml_handles
Sql 2008
SELECT [session_id], [document_id], [namespace_document_id], [sql_handle], [statement_start_offset], [statement_end_offset], [creation_time], [original_document_size_bytes], [original_namespace_document_size_bytes], [num_openxml_calls], [row_count], [dormant_duration_ms] FROM sys.dm_exec_xml_handles
Sql 2008 R2
SELECT [session_id], [document_id], [namespace_document_id], [sql_handle], [statement_start_offset], [statement_end_offset], [creation_time], [original_document_size_bytes], [original_namespace_document_size_bytes], [num_openxml_calls], [row_count], [dormant_duration_ms] FROM sys.dm_exec_xml_handles
Sql 2012
SELECT [session_id], [document_id], [namespace_document_id], [sql_handle], [statement_start_offset], [statement_end_offset], [creation_time], [original_document_size_bytes], [original_namespace_document_size_bytes], [num_openxml_calls], [row_count], [dormant_duration_ms] FROM sys.dm_exec_xml_handles

Back to Top

No comments:

Post a Comment

Total Pageviews