要查看 SQL 指令的執行計劃,SSMS 絕對是首選,透過圖形化呈現,效能瓶頸一秒現形。(參考:應用範例)

不過,不是每一台機器都有裝或能裝 SSMS,在沒有安裝工具軟體的環境,要如何用 PowerShell/C# 取得 SQL 查詢的執行計劃呢?

SQL 提供幾個控制選項 SET SHOWPLAN_TEXTSET SHOWPLAN_XMLSET SHOWPLAN_ALL。運作方式是先呼叫 SET SHOWPLAN_TEXT ON,之後的 SELECT 動作將不會真的執行傳回資料,改傳回執行計劃,而傳回內容也依參數會傳回純文字(SHOWPLAN_TEXT,精簡描述)、XML(SHOWPLAN_XML,結構化詳細資料)、詳細資訊(SHOWPLAN_ALL,除 StmtText 外包含 StmtId, NodeId, Parent, PhysicalOp... 等十多個欄位),直到呼叫 SET SHOWPLAN_TEXT OFF 恢復正常執行。

具體做法是用同一條連線,先 SET SHOWPLAN_TEXT ON,傳入 SELECT 指令跑 ExecuteReader() 取得 DataReader,跑 while(dr.Read()) 讀取執行計劃內容,SHOWPLAN_TEXT 的話,dr[0] 或 dr["StmtText"] 即為一列執行計劃文字。結果可能有多組,故要用 dr.HasRows 配合 dr.NextResult() 切換將資料讀完。

用說的有點模糊,看程式就清楚了:

$cs = 'data source=.;initial catalog=EmployeeCaseStudy;integrated security=SSPI'
$cn = New-Object System.Data.SqlClient.SqlConnection $cs
$cn.Open()
$cmd = $cn.CreateCommand()
$cmd.CommandText = 'SET SHOWPLAN_TEXT ON'
$cmd.ExecuteNonQuery() | Out-Null
$cmd.CommandText = @'
SELECT LastName,FirstName,Address,City,State,Zip,Phone
FROM Employee
WHERE  Status = 1 AND (City = 'Madison' OR MiddleInitial = 'A')
ORDER BY State, City
'@
$dr = $cmd.ExecuteReader()
$maxLen = [System.Console]::ConsoleWidth - 5;
while ($dr.HasRows) {
    while ($dr.Read()) {
        $stmttext = $dr[0]
        if ($stmttext.Length -gt $maxLen) {
            $stmttext = $stmttext.Substring(0, $maxLen) + '...'
        }
        Write-Host $stmttext
    }
    $dr.NextResult() | Out-Null
}
$dr.Close()
$cmd.CommandText = 'SET SHOWPLAN_TEXT OFF'
$cmd.ExecuteNonQuery() | Out-Null
$cmd.Dispose()
$cn.Dispose()

這一切貌似手到擒來,易如反掌,但其實我在研究時 $dr.HasRows 顯示沒有內容、$dr.Read() 也傳回 false,爬文查到到的資料寫法跟我差不多,為什麼獨獨我讀不到東西,鬼打牆快一個小時才繞出來...

還原問題現場:

我實際測試的 SQL 是由其他地方搬來的,裡面用了 SqlParameter (如上圖的 @st),就這個小差別,SET SHOWPLAN_TEXT ON 後,即不會傳回 SELECT 資料也不會傳回執行計劃,什麼都沒有。

關鍵在文件的這兩句話:

The setting of SET SHOWPLAN_TEXT is set at execute or run time and not at parse time.
SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL cannot be specified in a stored procedure. They must be the only statements in a batch.

當使用 SqlParameter,背後會轉成 sp_executesql (參考),屬於預先準備好的指令,故不會產生執行計劃。

就醬,雖然耗了時間踩了坑,但學會免工具查詢執行計劃,多認識一些 SqlParameter 背後行為,也算有收獲。

Example of how to get execution plan by ADO.NET in PowerShell.


Comments

# by Ike

「直到呼叫 SET SHOWPLAN_TEXT ON 恢復正常執行。」 這裡應是「OFF」?

# by Jeffrey

to lke, 沒錯,複製貼上忘了改 Orz...

Post a comment