使用 PowerShell/C# 讀取 SQL 執行計劃(以及我踩到的坑)

使用 PowerShell/C# 讀取 SQL 執行計劃(以及我踩到的坑)-黑暗執行緒

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

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

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

具體做法是用同一條連線，先 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()
while ($dr.HasRows) {
    while ($dr.Read()) {
        $stmttext = $dr[0]
        if ($stmttext.Length -gt 100) {
            $stmttext = $stmttext.Substring(0, 100) + '...'
        }
        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 背後行為，也算有收獲。