Get parse, compile, and execute time for SQL statement. 取得 剖析、編譯 和 執行 SQL 查詢 所耗用的時間

若取得 剖析、編譯 和 執行 SQL 查詢 所耗用的時間，可以使用以下方式：

• 使用 SET STATISTICS IO, TIME ON 取得。
• 檢視 Execution Plan 取得。
• 使用 DMV 直接上 記憶體 Cache 區取得。

---

Get parse, compile, and execute time for SQL statement. 
取得 剖析、編譯 和 執行 SQL 查詢 所耗用的時間



01. 依序執行以下 SQL 陳述式：

• SET STATISTICS IO, TIME ON: 是用於取得 剖析、編譯和執行的 時間與 Disk IO 資訊。 
• sp_recompile: 是用於要求 SQL Server 重新編譯 Stored Procedure。

以下是各個的功用說明。

SET STATISTICS IO, TIME ON: 

• TIME: 
• 顯示剖析、編譯和執行每個陳述式所需要的毫秒數。 
• Displays the number of milliseconds required to parse, compile, and execute each statement.
• IO: 
• 顯示 Transact-SQL 陳述式所產生之磁碟活動量的相關資訊。 
• Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.

sp_recompile: 

• 在下次執行預存程序、觸發程序以及使用者定義函數時，重新編譯它們。 
• 其運作方式是從程序快取中卸除現有的計畫，以便強制在下次執行程序或觸發程序時建立新的計畫。

-- figure 01_SET STATISTICS IO TIME ON





02. 執行 Stored Procedure。

• EXEC dbo.GetSalesList 43659

-- figure 02_Execute_Stored_Procedure




03. 檢視輸出，可以看到 parse and compile time:

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 15 ms, elapsed time = 43 ms.

-- figure 03_parse and compile time






04. 也可以由 Execution Plan 取得相關的資訊:

• CompileCPU: 12
• CompileMemory: 1008
• CompileTime: 35

-- figure 04_Actual_Execution_Plan_Complie_Time_CPU





05. 想要知道 已經執行過 SQL Statement 的 編譯相關資源嗎？

使用 DMV 直接上 記憶體 Cache 區，自行取用 :)

• CompileTime(ms): 35
• CompileCPU(ms): 12
• CompileMemory(KB): 1008

-- figure 05_Get execution plan for cached query

-- Get execution plan for cached query: Execution Count, Plan Recompile, ParameterList, Query Plan, CompileTime, CompileMemory, SubTreeCost, etc.
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns)

SELECT TOP 10 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 TRY_CAST(qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@StatementSubTreeCost)[1]', 'float') 
 AS decimal(38,7)) [StatementSubTreeCost],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileTime)[1]', 'int') [CompileTime(ms)], 
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileCPU)[1]', 'int') [CompileCPU(ms)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/@CompileMemory)[1]', 'int') [CompileMemory(KB)],
 qp.query_plan.value('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/@RetrievedFromCache)[1]', 'nvarchar(50)') [RetrievedFromCache],
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('(/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
         ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('AdventureWorks2014') 
-- AND cp.objtype = 'Proc' CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY StatementSubTreeCost DESC;
GO

---

Sample Code

20180825_Get_parse_compile_execute_time_SQL
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

Reference


[Case Study] Parameter Sniffing with OR Operator issue, 當 Parameter Sniffing 遇上 OR 運算子 (1)
http://sharedderrick.blogspot.com/2018/08/case-study-parameter-sniffing-with-or.html

[SQL Server] Get SubTreeCost, CompileTime, CompileMemory, Execution Count, Plan Recompile from Plan Cache - 子樹成本
http://sharedderrick.blogspot.com/2017/12/sql-server-get-subtreecost-compiletime.html