SQL Server如何查看存儲過程的執行計劃

有時候，我們需要查看存儲過程的執行計劃，那麼我們有什麼方式獲取存儲過程的歷史執行計劃或當前的執行計劃呢？ 下麵總結一下獲取存儲過程的執行計劃的方法。

1:我們可以通過下麵腳本查看存儲過程的執行計劃，但是有時候，你會發現這種方式並不總是能夠獲取到存儲過程的執行計劃。

SELECT

        d.object_id ,

        DB_NAME(d.database_id) DBName ,

        OBJECT_NAME(object_id, database_id) 'SPName' ,

        d.cached_time ,

        d.last_execution_time ,

        d.total_elapsed_time/1000000    AS total_elapsed_time,

        d.total_elapsed_time / d.execution_count/1000000 

                                        AS [avg_elapsed_time] ,

        d.last_elapsed_time/1000000     AS last_elapsed_time,

        d.execution_count ,

        d.total_physical_reads ,

        d.last_physical_reads ,

        d.total_logical_writes ,

        d.last_logical_reads ,

        et.text SQLText ,

        eqp.query_plan executionplan

FROM    sys.dm_exec_procedure_stats AS d

CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) et

CROSS APPLY sys.dm_exec_query_plan(d.plan_handle) eqp

WHERE   OBJECT_NAME(object_id, database_id) = 'xxxx'

ORDER BY [total_worker_time] DESC;

有時候使用這種方式並不能獲取存儲過程的執行計劃，腳本查詢出來的結果，query_plan欄位為NULL值，那麼為什麼是NULL值呢？這個是因為有一些限制或條件的緣故，官方文檔的解釋如下：

Under the following conditions, no Showplan output is returned in the query_plan column of the returned table for sys.dm_exec_query_plan:

·         If the query plan that is specified by using plan_handle has been evicted from the plan cache, the query_plan column of the returned table is null. For example, this condition may occur if there is a time delay between when the plan handle was captured and when it was used with sys.dm_exec_query_plan.

·         Some Transact-SQL statements are not cached, such as bulk operation statements or statements containing string literals larger than 8 KB in size. XML Showplans for such statements cannot be retrieved by using sys.dm_exec_query_plan unless the batch is currently executing because they do not exist in the cache.

·         If a Transact-SQL batch or stored procedure contains a call to a user-defined function or a call to dynamic SQL, for example using EXEC (string), the compiled XML Showplan for the user-defined function is not included in the table returned by sys.dm_exec_query_plan for the batch or stored procedure. Instead, you must make a separate call to sys.dm_exec_query_plan for the plan handle that corresponds to the user-defined function.

When an ad hoc query uses simple or forced parameterization, the query_plan column will contain only the statement text and not the actual query plan. To return the query plan, call sys.dm_exec_query_plan for the plan handle of the prepared parameterized query. You can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects view or the text column of the sys.dm_exec_sql_text dynamic management view.

在以下情況下，sys.dm_exec_query_plan的返回表的query_plan列為空值（query_plan列中未返回Showplan輸出）:

·         通過使用plan_handle查詢指定的查詢計劃(query plan)，如果plan_handle已從計劃緩存中踢出（逐出），返回的表的query_plan列為null。 例如，如果在捕獲計劃句柄與將其與sys.dm_exec_query_plan一起使用之間存在時間延遲，則可能會出現這種情況。

·         有些Transact-SQL語句不會cached，例如大容量操作語句(bulk operation statements)或包含大於8 KB的字元串大小的SQL語句。無法使用sys.dm_exec_query_plan檢索此類語句的XML Showplans，除非批處理當前正在執行，因為它們不存在於緩存中。

·         如果Transact-SQL批處理或存儲的過程包含對用戶定義函數的調用或執行動態SQL，例如使用 EXEC (字元串)，則用戶定義函數的已編譯XML Showplan不包含在返回的表中通過sys.dm_exec_query_plan獲取批處理或存儲過程。相反，您必須單獨調用sys.dm_exec_query_plan以獲取與用戶定義函數對應的計劃句柄。

當即席查詢使用簡單或強制參數化時，query_plan列將包含僅語句文本，而不是實際查詢計劃。 若要返回查詢計劃，請調用sys.dm_exec_query_plan準備參數化查詢的計劃句柄。 您可以確定查詢是否已參數化通過引用sql的列sys.syscacheobjects視圖或文本列sys.dm_exec_sql_text動態管理視圖。

註意：sys.dm_exec_query_plan返回的是實際執行計劃。

2：使用SET SHOWPLAN_ALL ON 和SET SHOWPLAN_XML ON獲取存儲過程的執行計劃。

如下所示，在AdventureWorks2014資料庫中，查看存儲過程[dbo].[uspGetEmployeeManagers] 的執行計劃

SET SHOWPLAN_ALL ON

GO

SET FMTONLY ON

GO

EXEC dbo].[uspGetEmployeeManagers] 242;

GO

SET FMTONLY OFF

GO

SET SHOWPLAN_ALL OFF

GO

SET SHOWPLAN_ALL ON

GO

EXEC [dbo].[uspGetEmployeeManagers] 242;

GO

SET SHOWPLAN_ALL OFF;

GO

SET SHOWPLAN_XML ON

GO

EXEC [dbo].[uspGetEmployeeManagers] 242;

GO

SET SHOWPLAN_XML OFF;

GO