sys.dm_exec_sql_text (Transact-SQL)sys.dm_exec_sql_text (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

傳回指定之sql_handle所識別之 SQL 批次的文字。Returns the text of the SQL batch that is identified by the specified sql_handle. 這個資料表值函式取代系統函數 fn_get_sqlThis table-valued function replaces the system function fn_get_sql.

語法Syntax

sys.dm_exec_sql_text(sql_handle | plan_handle)  

引數Arguments

sql_handlesql_handle
這是一個權杖,可唯一識別已執行或目前正在執行的批次。Is a token that uniquely identifies a batch that has executed or is currently executing. sql_handleVarbinary (64)sql_handle is varbinary(64).

Sql_handle可以從下列動態管理物件取得:The sql_handle can be obtained from the following dynamic management objects:

plan_handleplan_handle
這是一個標記,可唯一識別已執行之批次的查詢執行計畫,且其計畫位於計畫快取中,或目前正在執行。Is a token that uniquely identifies a query execution plan for a batch that has executed and its plan resides in the plan cache, or is currently executing. plan_handleVarbinary (64)plan_handle is varbinary(64).

Plan_handle可以從下列動態管理物件取得:The plan_handle can be obtained from the following dynamic management objects:

傳回的資料表Table Returned

資料行名稱Column name 資料類型Data type 描述Description
dbiddbid smallintsmallint 資料庫的識別碼。ID of database.

對於隨選和準備的 SQL 陳述式而言,則為編譯陳述式的資料庫識別碼。For ad hoc and prepared SQL statements, the ID of the database where the statements were compiled.
objectidobjectid intint 物件的識別碼。ID of object.

特定和準備 SQL 陳述式的這個值是 NULL。Is NULL for ad hoc and prepared SQL statements.
numbernumber smallintsmallint 對於已編號的預存程序,這個資料行會傳回預存程序的編號。For a numbered stored procedure, this column returns the number of the stored procedure. 如需詳細資訊,請參閱numbered_procedures (transact-sql)For more information, see sys.numbered_procedures (Transact-SQL).

特定和準備 SQL 陳述式的這個值是 NULL。Is NULL for ad hoc and prepared SQL statements.
加密encrypted bitbit 1 = SQL 文字已加密。1 = SQL text is encrypted.

0 = SQL 文字未加密。0 = SQL text is not encrypted.
texttext Nvarchar (max nvarchar(max ) SQL 查詢的文字。Text of the SQL query.

加密物件的這個值是 NULL。Is NULL for encrypted objects.

權限Permissions

需要伺服器的 VIEW SERVER STATE 權限。Requires VIEW SERVER STATE permission on the server.

備註Remarks

針對臨機操作查詢,SQL 控制碼是以提交至伺服器的 SQL 文字為基礎的雜湊值,而且可以來自任何資料庫。For ad hoc queries, the SQL handles are hash values based on the SQL text being submitted to the server, and can originate from any database.

針對預存程序、觸發程序或函數之類的資料庫物件,SQL 控制代碼是從資料庫識別碼、物件識別碼和物件編碼衍生而來。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.

注意

無法從特定查詢的sql_handle判斷dbiddbid cannot be determined from sql_handle for ad hoc queries. 若要判斷特定查詢的dbid ,請改用plan_handleTo determine dbid for ad hoc queries, use plan_handle instead.

範例Examples

A.A. 概念範例Conceptual Example

以下是可說明直接或透過CROSS APPLY傳遞sql_handle的基本範例。The following is a basic example to illustrate passing a sql_handle either directly or with CROSS APPLY.

  1. 建立活動。Create activity.
    SQL Server Management StudioSQL Server Management Studio的新查詢視窗中,執行下列 t-sql。Execute the following T-SQL in a new query window in SQL Server Management StudioSQL Server Management Studio.

    -- Identify current spid (session_id)
    SELECT @@SPID;
    GO
    
    -- Create activity
      WAITFOR DELAY '00:02:00';
    
  2. 使用CROSS APPLYUsing CROSS APPLY.
    sys. dm_exec_requests的 sql_handle 將會傳遞至sys。 DM_EXEC_SQL_TEXT使用CROSS APPLYThe sql_handle from sys.dm_exec_requests will be passed to sys.dm_exec_sql_text using CROSS APPLY. 開啟新的查詢視窗,並傳遞步驟1中識別的 spid。Open a new query window and pass the spid identified in step 1. 在此範例中,spid 的結果59會是。In this example the spid happens to be 59.

    SELECT t.*
    FROM sys.dm_exec_requests AS r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
    WHERE session_id = 59 -- modify this value with your actual spid
    
  3. 直接傳遞sql_handlePassing sql_handle directly.
    從 sys.databases **** 取得 sql_handle dm_exec_requestsAcquire the sql_handle from sys.dm_exec_requests. 然後,將sql_handle直接傳遞至sys.databases,dm_exec_sql_textThen, pass the sql_handle directly to sys.dm_exec_sql_text. 開啟新的查詢視窗,並將步驟1中識別的 spid 傳遞至sys. dm_exec_requestsOpen a new query window and pass the spid identified in step 1 to sys.dm_exec_requests. 在此範例中,spid 的結果59會是。In this example the spid happens to be 59. 然後將傳回的sql_handle當做引數傳遞至sys.databases dm_exec_sql_textThen pass the returned sql_handle as an argument to sys.dm_exec_sql_text.

    -- acquire sql_handle
    SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id = 59  -- modify this value with your actual spid
    
    -- pass sql_handle to sys.dm_exec_sql_text
    SELECT * FROM sys.dm_exec_sql_text(0x01000600B74C2A1300D2582A2100000000000000000000000000000000000000000000000000000000000000) -- modify this value with your actual sql_handle
    

B.B. 依平均 CPU 時間取得前五項查詢的相關資訊Obtain information about the top five queries by average CPU time

下列範例會傳回 SQL 陳述式的文字以及前五項查詢的平均 CPU 時間。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;  

C.C. 提供批次執行統計資料Provide batch-execution statistics

下列範例會傳回以批次方式執行的 SQL 查詢之文字並提供有關這些查詢的統計資訊。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;  

另請參閱See also

動態管理檢視與函數 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
執行相關的動態管理檢視和函數 (Transact-sql) Execution Related Dynamic Management Views and Functions (Transact-SQL)
dm_exec_query_stats (Transact-sql) sys.dm_exec_query_stats (Transact-SQL)
sys.dm_exec_requests (Transact-SQL) sys.dm_exec_requests (Transact-SQL)
dm_exec_cursors (Transact-sql) sys.dm_exec_cursors (Transact-SQL)
dm_exec_xml_handles (Transact-sql) sys.dm_exec_xml_handles (Transact-SQL)
dm_exec_query_memory_grants (Transact-sql) sys.dm_exec_query_memory_grants (Transact-SQL)
使用 APPLY   sys.databases dm_exec_text_query_plan (transact-sql)Using APPLY   sys.dm_exec_text_query_plan (Transact-SQL)