sys.dm_exec_sql_text (Transact-SQL)

sys.dm_exec_sql_text (Transact-SQL)

Community Content

In this section

Statistics Annotations (1)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (November 2009)

sys.dm_exec_sql_text (Transact-SQL)

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.

Syntax

sys.dm_exec_sql_text(sql_handle | plan_handle)

Arguments

sql_handle

Is the SQL handle of the batch to be looked up. sql_handle is varbinary(64). sql_handle can be obtained from the following dynamic management objects:

sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_cursors
sys.dm_exec_xml_handles
sys.dm_exec_query_memory_grants
sys.dm_exec_connections

plan_handle

Is an identifier for the query plan.

For more information, see sys.dm_exec_text_query_plan (Transact-SQL).

Table Returned

Column name	Data 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). 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.

Remarks

For batches, the SQL handles are hash values based on the SQL text. For database objects such as stored procedures, triggers or functions, the SQL handles are derived from the database ID, object ID, and object number. plan_handle is a hash value derived from the compiled plan of the entire batch.

Permissions

Requires VIEW SERVER STATE permission on the server.

Examples

A. Obtaining information about the top five queries by average CPU time

The following example returns the text of the SQL statement and average CPU time for the top five queries.

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

B. Providing batch-execution statistics

The following example returns the text of SQL queries that are being executed in batches and provides statistical information about them.

SELECT s2.dbid, 
    s1.sql_handle,  
    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
    execution_count, 
    plan_generation_num, 
    last_execution_time,   
    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  
FROM sys.dm_exec_query_stats AS s1 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
WHERE s2.objectid is null 
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

Reference

Dynamic Management Views and Functions (Transact-SQL)
Execution Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
sys.dm_exec_cursors (Transact-SQL)
sys.dm_exec_xml_handles (Transact-SQL)
sys.dm_exec_query_memory_grants (Transact-SQL)

Help and Information

Getting SQL Server 2008 Assistance

Tags

: Add a tag

Community Content

Add new content

Annotations

Not necessary to add 1 on length expression

slh1234 | Edit | Show History

[Microsoft: sthoward] The arguments for the substring function are: (<Value expression>, <start expression>, <length expression>). Since the T-SQL substring function is 1 based, and the statement_start_offset and statement_end_offset are 0 based, you are correct in adding 1 on the start expression. However; the length expression should just be the (statement_end_offset - statement_start_offset)/2. It is not correct to add 1 to the length expression. It might not be noticeable since the next character is probably a white space, but it is not really correct. So the examples would be:

SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

And:

SELECT s2.dbid,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
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
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;