SET SHOWPLAN_XML (Transact-SQL)
適用於:SQL Server
Azure SQL 資料庫
Azure SQL 受控執行個體
Azure Synapse Analytics(僅限專用 SQL 集區)
使 SQL Server 不執行 Transact-SQL 陳述式。 相反地,SQL Server 會以妥善定義的 XML 文件格式傳回詳細資訊,說明這些陳述式會如何執行。
Syntax
SET SHOWPLAN_XML { ON | OFF }
注意
若要檢視 SQL Server 2014 (12.x) 和更早版本的 Transact-SQL 語法,請參閱舊版文件。
備註
SET SHOWPLAN_XML 的設定是在執行階段進行設定,而不是在剖析階段進行設定。
當 SET SHOWPLAN_XML 為 ON 時,SQL Server 會在未執行陳述式的情況下,傳回每個陳述式的執行計劃資訊,且不會執行 Transact-SQL 陳述式。 將此選項設定為 ON 之後,即會傳回所有後續 Transact-SQL 陳述式的執行計劃相關資訊,直到將此選項設定為 OFF 為止。 例如,如果執行 CREATE TABLE 陳述式時 SET SHOWPLAN_XML 為 ON,SQL Server 會從涉及相同資料表的後續 SELECT 陳述式傳回錯誤訊息:指定的資料表不存在。 因此,後來參考這份資料表都會失敗。 當 SET SHOWPLAN_XML 為 OFF 時,SQL Server 會執行陳述式而不產生報表。
SET SHOWPLAN_XML 用來傳回應用程式 (如 sqlcmd 公用程式) 的輸出為 nvarchar(max),其中 XML 輸出後續可供其他工具顯示和處理查詢計劃資訊。
注意
動態管理檢視 sys.dm_exec_query_plan
會以 xml 資料類型傳回 SET SHOWPLAN XML 的相同資訊。 這項資訊是從 query_plan
的 sys.dm_exec_query_plan
資料行傳回。 如需詳細資訊,請參閱 sys.dm_exec_query_plan (Transact-SQL)。
在預存程序內,不能指定 SET SHOWPLAN_XML。 它必須是批次中的唯一陳述式。
SET SHOWPLAN_XML 會將資訊當作一組 XML 文件傳回。 SET SHOWPLAN_XML ON 陳述式之後的每個批次都會反映在單一文件的輸出中。 每份文件都包含批次內各陳述式的文字,後面接著執行步驟的詳細資料。 文件會顯示估計的成本、資料列數、存取的索引、執行的運算子類型、聯結順序,以及執行計畫的詳細資訊。
注意
如果在 SQL Server Management Studio 中選取了 [包括實際執行計劃],則此 SET 選項不會產生 XML 執行程序表輸出。 在使用這個 SET 選項之前,請清除 [包括實際執行計畫] 按鈕。
透過 SSMS 和 SET SHOWPLAN_XML 估計的執行計劃適用於專用 SQL 集區 (先前稱為 SQL DW) 和 Azure Synapse Analytics 中的專用 SQL 集區。 若要擷取專用 SQL 集區 (先前稱為 SQL DW) 和 Azure Synapse Analytics 中的專用 SQL 集區的實際執行計劃,應使用不同的命令。 如需詳細資訊,請參閱使用 DMV 監視 Azure Synapse Analytics 專用 SQL 集區工作負載。
SHOWPLAN 輸出的位置
在安裝期間,會將包含 SET SHOWPLAN_XML 所輸出 XML 之 XML 結構描述的文件,複製到 Microsoft SQL Server 安裝所在電腦上的本機目錄。 您可以在包含 SQL Server 安裝檔案的磁碟機中找到該文件,路徑類似如下:
\Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd
在上述路徑中,SQL Server 2016 會使用節點 130\
。 數字 130 衍生自 SELECT @@VERSION
所傳回的值第一個節點,也就是 13。 若是 SQL Server 2017,則路徑會使用 140\
,因為其 @@VERSION
值的第一個節點是 14。 若是 SQL Server 2019,@@VERSION
的第一個值會是 15。 若是 SQL Server 2022,@@VERSION
的第一個值會是 16。
您也可以在 Microsoft SQL Server XML 結構描述中找到此執行程序表結構描述。
權限
若要使用 SET SHOWPLAN_XML,您必須有執行 SET SHOWPLAN_XML 所針對的陳述式之充份權限,且您必須有包含所參考物件的所有資料庫之 SHOWPLAN 權限。
針對 SELECT
、INSERT
、UPDATE
、DELETE
、EXEC *stored_procedure*
和 EXEC *user_defined_function*
陳述式,若要產生執行程序表,使用者必須:
具備用來執行 Transact-SQL 陳述式的適當權限。
有包含 Transact-SQL 陳述式所參考的物件 (如資料表、檢視等等) 之所有資料庫的 SHOWPLAN 權限。
針對所有其他陳述式 (例如 DDL、USE *database_name*
、SET
、DECLARE
、動態 SQL 等,只需要有執行 Transact-SQL 陳述式的適當權限。
範例
下面兩個陳述式利用 SET SHOWPLAN_XML 設定來顯示 SQL Server 分析和最佳化查詢中之索引使用情況的方式。
第一個查詢在索引資料行的 WHERE 子句中,使用等於比較運算子 (=
)。 第二個查詢在 WHERE 子句中使用 LIKE 運算子。 這會強制 SQL Server 使用叢集索引掃描,以及尋找符合 WHERE 子句條件的資料。 第一個索引查詢的 EstimateRows
和 EstimatedTotalSubtreeCost
屬性中的值比較小,這表示它的處理速度比較快,使用的資源比非索引查詢少。
USE AdventureWorks2022;
GO
SET SHOWPLAN_XML ON;
GO
-- First query.
SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE NationalIDNumber = '509647174';
GO
-- Second query.
SELECT BusinessEntityID, JobTitle
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';
GO
SET SHOWPLAN_XML OFF;