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
Indsendt af kl.
Etiketter: , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Total Pageviews