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

---

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

點(diǎn)擊下載SQL Prompt正式版

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

---

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

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

使用代碼片段的示例

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

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

清單1

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

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

相當(dāng)多的代碼被添加到查詢窗格中，但是我有穩(wěn)定的神經(jīng)。我只需要點(diǎn)擊“Execute”，一秒鐘后我就會看到結(jié)果，其中包括所有已執(zhí)行語句的文本，它們的執(zhí)行時間以及到執(zhí)行計劃的鏈接。

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

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

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

在沒有有用索引的情況下，優(yōu)化器可以使用哈希匹配聚合（這會在內(nèi)存中構(gòu)建和填充哈希表），也可以首先對從聚合索引中出現(xiàn)的數(shù)據(jù)進(jìn)行排序，然后使用流聚合。

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

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

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

清單2顯示了用于創(chuàng)建Prompt代碼段的代碼。

清單2

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

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

檢查一批以上

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

--Test these queries

但是，事件會話設(shè)置為報告以該字符串開頭的任意數(shù)量的批處理?？梢詾橐幌盗袉为?dú)批處理中的每個語句提供單獨(dú)的執(zhí)行計劃。為了證明這一點(diǎn)，我們將重復(fù)測試，但將每個語句放入自己的批處理中。

清單3

請注意，在每個批處理的開始處都帶有--Test these queries字符串。 還要注意，我們已經(jīng)用臨時表替換了表變量，因?yàn)樗谂幚碇g可見。 現(xiàn)在，使用“提示”將其放入測試工具，您將得到只包含您感興趣的查詢的更簡潔的結(jié)果，并且每個查詢都有其自己的執(zhí)行計劃。

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

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

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

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

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

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

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

清單4

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

結(jié)論

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

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

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

---

想要購買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請點(diǎn)擊【咨詢在線客服】