積沙成塔

【情境說明】

相信有使用 SQL Server 的朋友都知道，SQL Server 提供圖形執行計畫可以讓開發人員或 DBA 分析查詢的成本，來做為 T-SQL 指令碼效能調校的參考。

由於圖形執行計畫提供相當完整，當您將滑鼠游標停留在每個圖示代表的運算子上，可以看到更詳盡的陳述式執行時期的資訊（如下圖）。

當您的查詢陳述式相當複雜時，圖形執行計畫會變得相當複雜，如果要提供給 DBA 或他人來協助找到查詢效能不彰的問題時，就會顯得不容易閱讀。此時，您可以改用 SET SHOWPLAN_* 來取得執行資訊，請見下一節的說明。

【實作步驟】

使用 SET SHOWPLAN_* 選項來取得執行資訊，會在執行時期才會生效，使用之後並不會回傳查詢陳述式的結果，而是回傳陳述式的執行資訊。您可以利用 SET SHOWPLAN_TEXT 選項來顯示文字執行計畫。

下列指令碼利用 SET SHOWPLAN_TEXT 來呈現查詢陳述式的執行資訊的樹狀結構：

use Northwind

go

SET SHOWPLAN_TEXT ON

go

select *

from Orders a

join [Order Details] b

on a.OrderID = b.OrderID

left join Employees c

on a.EmployeeID = c.EmployeeID

left join Customers d

on a.CustomerID = d.CustomerID

go

SET SHOWPLAN_TEXT OFF

go

執行結果如下，第一個查詢結果為您的原始陳述式，第二個查詢結果則為 SQL Server 執行陳述式的經過，與圖形執行計畫的閱讀方式是以由右向左稍有不同，文字執行計畫閱讀時為由下往上讀。

SET SHOWPLAN_TEXT 也可以利用 SSMS 的選項來設定（如下圖），但設定完畢之後必須新增查詢才會生效。

若您覺得 SET SHOWPLAN_TEXT 所提供的文字執行計畫資訊不夠充足，可以改用 SET SHOWPLAN_ALL 來取得更詳盡的文字執行計畫資訊。

下列指令碼利用 SET SHOWPLAN_ALL 來呈現查詢陳述式的執行資訊的樹狀結構：

use Northwind

go

SET SHOWPLAN_ALL ON

go

select *

from Orders a

join [Order Details] b

on a.OrderID = b.OrderID

left join Employees c

on a.EmployeeID = c.EmployeeID

left join Customers d

on a.CustomerID = d.CustomerID

go

SET SHOWPLAN_ALL OFF

go

使用 SET SHOWPLAN_ALL 選項會連同每個陳述式的詳細執行資訊都一併呈現出來（如下圖）。

下表節錄自 MSDN，說明每個資料行所代表的意義。

另外，您也可以利用 SET SHOWPLAN_XML 來以 XML 的方式呈現陳述式的執行資訊，例如下列的指令碼：

use Northwind

go

SET SHOWPLAN_XML ON

go

select *

from Orders a

join [Order Details] b

on a.OrderID = b.OrderID

left join Employees c

on a.EmployeeID = c.EmployeeID

left join Customers d

on a.CustomerID = d.CustomerID

go

SET SHOWPLAN_XML OFF

go

設定 SET SHOWPLAN_XML 產生的結果會以 XML 方式呈現（如下圖）。

您可以點選 XML 上的超連結來展開圖形執行計畫（如下圖），或是利用另存執行計畫的方式來將執行計畫儲存成 *.sqlplan，再提供給 DBA 或分析人員來進行分析。

【參考資料】

- SET SHOWPLAN_TEXT (Transact-SQL)

- SET SHOWPLAN_ALL (Transact-SQL)

- SET SHOWPLAN_XML (Transact-SQL)

- 顯示圖形執行計畫 (SQL Server Management Studio)