TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

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.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.
  
sys.dm_exec_sql_text(sql_handle | plan_handle)  

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).

Column nameData typeDescription
dbidsmallintID of database.

For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.
objectidintID of object.

Is NULL for ad hoc and prepared SQL statements.
numbersmallintFor 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.
encryptedbit1 = SQL text is encrypted.

0 = SQL text is not encrypted.
textnvarchar(max )Text of the SQL query.

Is NULL for encrypted objects.

Requires VIEW SERVER STATE permission on the server.

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.

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;  

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)

Community Additions

ADD
Show:
© 2016 Microsoft