使用SQL語法提示工具SQL Prompt，測試批處理中單個SQL語句的性能

---

SQL Prompt根據數據庫的對象名稱、語法和代碼片段自動進行檢索，為用戶提供合適的代碼選擇。自動腳本設置使代碼簡單易讀--當開發者不大熟悉腳本時尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據需要進行自定義，使之以預想的方式工作。

點擊下載SQL Prompt正式版

本文全部涉及使用SQL Prompt代碼片段在一批SQL代碼上創建和運行測試工具，以提供性能數據。在幕后，此代碼段創建并啟動了擴展Events事件會話，插入并運行您在SSMS中選擇的SQL批處理的文本，并對XML事件數據使用XQuery，以可消化的表格形式返回它。只需單擊幾下即可調用和執行代碼段，就可以獲取任何批處理的執行計劃，以及批處理中每個SQL語句的SQL文本和查詢執行統計信息。

---

為什么要這樣設置呢？在處理XML和擴展事件時，有時會覺得自己就像一條金魚盯著電視機看。它很催眠，我知道它很可愛，但是我不會假裝理解它。作為開發人員，我更傾向于從應用程序的角度簡單地將性能視為耗時。這是與用戶體驗相同的可靠指標。但是，當經過的時間超出用戶的承受能力或不穩定時，并且我在邏輯上找不到明顯的錯誤時，我希望查看基于服務器的I / O指標并檢查執行情況計劃。

為了獲得這些，我需要使用Extended Events，并且如果我可以通過代碼片段或模板來控制復雜性，那是一個更容易做出的決定。

使用代碼片段的示例

假設您有一個聚合查詢的執行效果不理想。您已經在Grant Fritchey的《執行計劃》一書中讀到過他對散列|訂單查詢提示的描述，這迫使SQL Server為查詢中由GROUP BY或DISTINCT引起的所有聚合選擇特定的聚合機制。該HASH GROUP提示迫使它使用哈希匹配聚合（基于哈希），和ORDER GROUP迫使使用流聚合（基于訂單）。

您想知道這些提示是否值得追求，或者創建索引是否更有意義。唯一確定的方法是測試所有的選項。清單1顯示了我使用的代碼。

清單1

哪種策略效果最好？在SSMS查詢窗格中，我按Ctrl-A選擇整個批處理。選擇代碼后，我發現SQL Prompt的紅色小操作列表圖標出現在所選內容的左下方。它的下拉菜單列出了所有已安裝的代碼段，然后單擊我的chk代碼段。

chk片段（稍后將向您介紹如何創建）將所選代碼嵌入特殊的擴展事件的事件會話中，該會話將跟蹤以特定字符串開頭的批處理中的各個語句。 該標識符由代碼段的注釋添加。 在本文稍后的部分，我將向您展示如何檢查多個批次。

相當多的代碼被添加到查詢窗格中，但是我有穩定的神經。我只需要點擊“Execute”，一秒鐘后我就會看到結果，其中包括所有已執行語句的文本，它們的執行時間以及到執行計劃的鏈接。

滾動查看每個語句的更多信息，包括CPU時間、IO統計信息和返回的行數。

從這些執行統計數據中我們可以直接看到，提供合適的索引比弄亂提示要好得多。 通過提供按我們要分組的列在邏輯上排序的索引，我們可以使查詢的運行速度快四倍。

如果我們想知道為什么，可以看一下展示計劃。就像我們在第一個示例中一樣，僅檢查一個批處理，所有showplan鏈接都指向同一計劃，這是該批處理的計劃，并且包含該批處理中每個語句的各個計劃：

在沒有有用索引的情況下，優化器可以使用哈希匹配聚合（這會在內存中構建和填充哈希表），也可以首先對從聚合索引中出現的數據進行排序，然后使用流聚合。

在這種情況下，它認為前一種策略是最便宜的策略，但是ORDER GROUP提示迫使優化器采用后者。我們的查詢執行統計信息表明，對于像這樣的小表來說，它幾乎沒有什么區別，但是排序操作非常昂貴，因此，隨著源表中行數的增加，此提示查詢的性能可能會下降。相反，通過提供按GROUP BY列排序的索引，我們使優化器可以選擇開銷較低的流聚合，而無需其他排序操作。

我提這一切原因并不是要阻止您使用提示，而是要說服您在決定采取行動之前盡可能準確地測量此類事情。有了這個代碼段，我們可以將其用于任何一組查詢，我們可以很快查看是否有任何一種策略可以提供“巨大的成功”。

批處理中檢查SQL語句的代碼段

清單2顯示了用于創建Prompt代碼段的代碼。

清單2

通過$ SELECTEDTEXT $占位符，您可以將代碼從所選代碼段的任何一側放入。 如您所見，我們創建會話，執行代碼并立即停止會話。 我們獲取表示環形緩沖區內容的XML文檔，并將其切碎成出現在結果中的關系表。 事件會話會過濾掉除以字符串“ --Test these query”開頭的批處理之外的所有內容。

只需將清單2中的代碼粘貼到SSMS查詢窗格中，使用Ctrl-A突出顯示代碼，然后右鍵單擊并選擇Create Snippet。確定要輸入以調用代碼段的字符（我選擇了chk），為代碼段提供描述，然后單擊保存。

檢查一批以上

此代碼段僅適用于一個批處理。它在批處理的開始處插入以下行；

--Test these queries

但是，事件會話設置為報告以該字符串開頭的任意數量的批處理。可以為一系列單獨批處理中的每個語句提供單獨的執行計劃。為了證明這一點，我們將重復測試，但將每個語句放入自己的批處理中。

清單3

請注意，在每個批處理的開始處都帶有--Test these queries字符串。 還要注意，我們已經用臨時表替換了表變量，因為它在批處理之間可見。 現在，使用“提示”將其放入測試工具，您將得到只包含您感興趣的查詢的更簡潔的結果，并且每個查詢都有其自己的執行計劃。

我只將一個查詢放在批處理中，但是如果有多個查詢，它們將出現并與同一批處理中的其他查詢共享執行計劃。

用于檢查存儲過程或函數中的語句的代碼段

擴展基本代碼片段以顯示例如在存儲過程和函數中執行的語句非常容易。 只需執行清單2的CREATE EVENT部分，然后在SSMS對象資源管理器中導航事件會話，并從其右鍵單擊上下文菜單中選擇Properties。

我們可以添加所需的任何事件、字段和過濾器。在這里，我將sp_statement_completed事件添加到事件會話中。

進行更改后，您可以編寫DDL腳本以創建事件會話，并在“提示”代碼段中使用它。切記添加初步代碼以刪除該名稱的任何現有會話。

通過更改事件會話來熟悉擴展事件是值得的。通過屬性窗口，查看可以從中獲取哪些詳細信息。但是，請注意，您必須能夠凝視原始XML而不會退縮，直到能夠將XML分解為關系形式為止。幸運的是，TSQL事件之間有相似之處，因此記錄所有此類事件的摘要相當容易。

清單4顯示了一段代碼，該代碼將返回存儲過程或批處理中所有語句的執行統計信息和計劃。它適用于功能和程序。作為輔助調查非常好，但是詳細程度很快就會變得勢不可擋。

清單4

例如，我在這里使用它來檢查我的舊存儲過程，該過程列出了當前月份或您指定的任何月份的日歷。

結論

如果要使用擴展事件來開發代碼，那么值得暫時退一步來創建代碼片段或模板，以便在開發時可以重用這些測試工具。

他們節省了大量時間來嘗試弄清楚究竟是什么導致代碼緩慢運行。解決性能問題時，您不一定總是需要非常詳細的信息，并且在關注SQL語句級別之前，我通常會花整段時間。但是，當您想了解細節時，沒有什么可以與基于擴展事件的測試工具進行比較了。

看完了文章，不知道內容是否對您有所幫助？如果您對該產品感興趣，可以繼續關注我們，了解更多產品資訊，或者下載產品，自己動手體驗一番~

---

想要購買SQL Prompt正版授權，或了解更多產品信息請點擊