Eksportér (0) Udskriv
Udvid alt
Expand Minimize
EN
Dette indhold er ikke tilgængeligt på dit sprog, men her er den engelske version.

sys.dm_exec_sql_text

Updated: April 2, 2011

This topic is OBSOLETE. You can find the most current version in the SQL 14 Transact-SQL Reference.

ImportantImportant
This topic is not maintained. For the current version, see 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.


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.

 

Column name Data type Description

dbid

smallint

ID of database.

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

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

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.

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;

Fællesskabsindhold

Tilføj
Vis:
© 2014 Microsoft