- sys.dm_exec_background_job_queue
- sys.dm_exec_background_job_queue_stats
- sys.dm_exec_cached_plan_dependent_objects
- sys.dm_exec_cached_plans
- sys.dm_exec_connections
- sys.dm_exec_cursors
- sys.dm_exec_plan_attributes
- sys.dm_exec_query_memory_grants
- sys.dm_exec_query_optimizer_info
- sys.dm_exec_query_plan
- sys.dm_exec_query_resource_semaphores
- sys.dm_exec_query_stats
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.dm_exec_sql_text
- sys.dm_exec_text_query_plan
- sys.dm_exec_xml_handles
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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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