在本文中，我們探討了 SET NOCOUNT 在 SQL Server 中的作用和重要性，討論它如何減少客戶端的處理負載并提高查詢執(zhí)行時間。此外，我們還研究了需要使用 SET NOCOUNT OFF 的場景，并強調(diào)了不使用 SET NOCOUNT 時可能出現(xiàn)的潛在問題。

dbForge Studio for SQL Server正版試用下載

了解 SQL Server 中的 SET NOCOUNT

SET NOCOUNT 是什么的解釋

每次執(zhí)行 DML 命令時，都會向客戶端發(fā)回一條簡短消息，指示受查詢影響的行數(shù)。

該SET NOCOUNT語句在 SQL Server 中用于控制執(zhí)行 SQL 語句后默認返回的“X rows受影響”消息的生成。當SET NOCOUNT ON啟用時，它會禁止消息在結果集中返回，當SET NOCOUNT OFF設置或未指定時，消息將包含在結果集中。

SQL Server 中 SET NOCOUNT ON 的作用

使用的主要目的SET NOCOUNT ON是提高SQL Server查詢和存儲過程的性能和效率。SET NOCOUNT ON以下是常用的幾個原因：

1. 減少網(wǎng)絡流量：當從客戶端應用程序執(zhí)行 SQL 語句時，行計數(shù)消息可能會產(chǎn)生額外的網(wǎng)絡流量，因為它需要從服務器傳輸?shù)娇蛻舳恕Ｍㄟ^使用SET NOCOUNT ON，您可以減少傳輸?shù)臄?shù)據(jù)量并提高整體網(wǎng)絡性能。
2. 減少客戶端應用程序的負載：通過使用SET NOCOUNT ON，可以抑制這些行計數(shù)消息，從而使 SQL Server 和客戶端應用程序之間的通信更加簡化。這樣，客戶端應用程序可以只專注于檢索實際結果數(shù)據(jù)，而不需要解析或處理行計數(shù)信息。
3. 縮短執(zhí)行時間：返回每個受影響行的行計數(shù)可能會增加處理開銷，特別是對于涉及大量行的查詢或操作。通過抑制行計數(shù)消息，您可以通過消除此額外處理來縮短執(zhí)行時間。
4. 增強結果集的可讀性：在批處理或腳本中執(zhí)行多個 T-SQL 語句或存儲過程時，行計數(shù)消息可能會使結果集變得混亂，并使實際結果的解釋變得更加困難。通過使用SET NOCOUNT ON，您可以保持結果集干凈并專注于相關數(shù)據(jù)。

注意
設置SET NOCOUNT ON不會禁用 T-SQL 語句的實際執(zhí)行或影響結果的準確性。它僅抑制行計數(shù)消息。

不使用 SET NOCOUNT ON 的影響

當 SQL Server 中未使用 SET NOCOUNT 時，行計數(shù)消息將包含在每個執(zhí)行語句的結果集中，這會增加網(wǎng)絡流量、使結果集混亂并使數(shù)據(jù)處理復雜化。此外，不使用 SET NOCOUNT 也可能導致緩沖區(qū)溢出問題。

緩沖區(qū)溢出問題

行計數(shù)消息必須由客戶端應用程序處理。在為每個 T-SQL 語句生成和傳輸行計數(shù)消息，并且執(zhí)行大量語句或處理大量結果集時，這些消息的累積可能會達到緩沖區(qū)的容量限制。

因此，緩沖區(qū)無法處理傳入的行計數(shù)消息，導致查詢執(zhí)行暫停，直到客戶端讀取所有累積的行計數(shù)消息。一旦客戶端消耗完所有累積的行計數(shù)消息，SQL Server 就會恢復執(zhí)行，因為輸出緩沖區(qū)中現(xiàn)在有可用內(nèi)存。

要解決此問題，您可以使用該SET NOCOUNT ON選項來抑制行計數(shù)消息的生成。這有效地減少了緩沖結果所需的內(nèi)存，從而顯著降低了緩沖區(qū)溢出的風險。

必須設置 SET NOCOUNT ON 的情況

在某些情況下，使用SET NOCOUNT ON是絕對必要的。讓我們考慮設計一個依賴異步處理的高性能中間層系統(tǒng)的情況，通過 SqlClient 的 BeginExecuteXXX 方法利用線程池。在這種情況下，會出現(xiàn)一個與行計數(shù)有關的關鍵問題。

它的工作原理如下： BeginExecute 方法設計為在服務器返回第一個響應數(shù)據(jù)包后立即完成。但是，當我們調(diào)用 EndExecuteXXX 方法時，它會等待非查詢請求完成，然后才考慮調(diào)用完成。請務必注意，每個行計數(shù)響應都被視為單獨的響應。

現(xiàn)在，讓我們考慮一種具有中等復雜程序的情況。第一個行計數(shù)可能會在 10 毫秒內(nèi)收到，而整個調(diào)用最多需要 500 毫秒才能完成。問題就出在這里：異步提交的請求回調(diào)不是在 500 毫秒后發(fā)生，而是在僅僅 5 毫秒后發(fā)生。但是，回調(diào)會在剩余的 495 毫秒內(nèi)卡在 EndExecuteXXX 方法中。因此，異步調(diào)用會過早完成，并最終阻塞 EndExecuteNonQuery 調(diào)用中線程池中的線程。這一系列不幸的事件會導致線程池饑餓。

好消息是，SET NOCOUNT ON在這些特定場景中實施可以產(chǎn)生重大影響。據(jù)報道，只需適當利用，高性能系統(tǒng)即可將吞吐量從每秒數(shù)百個調(diào)用提高到每秒數(shù)千個調(diào)用SET NOCOUNT ON。這個微小但重要的變化確保異步調(diào)用得到最佳處理，避免過早完成和線程阻塞，最終導致更高效和可擴展的系統(tǒng)。

可能需要 SET NOCOUNT OFF 的情況

雖然通常建議用于SET NOCOUNT ON性能優(yōu)化和減少網(wǎng)絡流量，但在某些情況下設置SET NOCOUNT OFF變得至關重要。這里有一些例子：

1. 與遺留代碼的兼容性：如果您正在使用依賴于接收結果集中的行計數(shù)消息的遺留應用程序或代碼，您可能需要進行設置SET NOCOUNT OFF以保持兼容性并確保應用程序按預期運行。
2. 所需的行計數(shù)信息：在某些情況下，客戶端應用程序或下游進程可能依賴于行計數(shù)消息提供的行計數(shù)信息。如果此信息對于應用程序的邏輯或報告目的至關重要，您可能需要進行設置SET NOCOUNT OFF以在結果集中包含行計數(shù)消息。
3. 調(diào)試和故障排除：在調(diào)查性能問題或對特定查詢進行故障排除時，訪問行計數(shù)消息以了解每個執(zhí)行語句的影響會很有幫助。在這種情況下，設置SET NOCOUNT OFF允許您分析行計數(shù)信息以用于診斷目的。
4. 特定報告要求：如果您的報告框架或工具依賴于行計數(shù)消息來生成準確的報告或指標，您可能需要使用它SET NOCOUNT OFF來確保所需的行計數(shù)信息可用于報告目的。

過去，在某些情況下必須強制設置NOCOUNT OFF，特別是在 BDE（Borland 數(shù)據(jù)庫引擎）等較舊的技術中。

使用 SET NOCOUNT 的最佳實踐

SET NOCOUNT ON為了優(yōu)化性能，Microsoft 建議有選擇地在會話級別使用以防止傳輸這些行計數(shù)消息。這對于包含多個不返回太多實際數(shù)據(jù)的語句的存儲過程特別有用。通過消除這些消息，可以顯著提高性能，因為網(wǎng)絡流量和客戶端負載會大大減少。

一般來說，除非需要，否則建議避免發(fā)送行計數(shù)消息。然而，適應依賴并有時濫用這些消息的遺留應用程序可能會帶來挑戰(zhàn)。

使用 SET NOCOUNT ON 可以獲得哪些性能優(yōu)勢？

使用的性能優(yōu)勢SET NOCOUNT ON可以根據(jù)具體情況而有所不同。性能優(yōu)勢的程度取決于過程中執(zhí)行的查詢的數(shù)量和頻率等因素。例如，如果一個過程使用游標執(zhí)行大量查詢并將其結果合并到最終查詢輸出中，或者如果該過程包含多個不會產(chǎn)生大量數(shù)據(jù)的語句，則與使用游標相比，性能最多可以提高十倍NOCOUNT OFF。這一改進主要是由于網(wǎng)絡流量的減少。

但是，如果過程僅包含一兩個查詢，則通過使用實現(xiàn)的性能增益SET NOCOUNT ON將不太明顯，通常小于百分之五。

為什么在實例級別啟用 NOCOUNT 不是個好方案

如今，您可以在實例級別啟用NOCOUNT，并且現(xiàn)代 ORM（對象關系映射）框架完全能夠有效地處理它。以下查詢設置SET NOCOUNT ON實例級別的行為。

EXEC sys.sp_configure 'user options', '512';
RECONFIGURE

注意
該user options設置是位掩碼，請相應處理。

但是，由于以下幾個原因，在實例級別啟用 NOCOUNT 可能被認為是一個壞主意：

覆蓋設置的可能性：如果用戶在單個會話中指定 NOCOUNT ON/OFF，他們可以覆蓋在實例級別配置的行為。

兼容性問題：在實例級別啟用 NOCOUNT 可能會對依賴行計數(shù)消息的舊應用程序或組件產(chǎn)生影響。如果這些應用程序期望并依賴于返回的行計數(shù)消息，則更改實例級設置可能會導致兼容性問題或意外行為。

意外后果：更改實例級別設置以啟用 NOCOUNT 可能會影響修改后啟動的所有用戶會話。如果某些組件或過程未設計用于處理行計數(shù)消息的缺失，則可能會產(chǎn)生意想不到的后果。徹底測試和評估這一變化對現(xiàn)有系統(tǒng)的影響至關重要。

ORM 或框架兼容性： ORM 框架或其他數(shù)據(jù)庫相關工具可能對行計數(shù)消息的可用性有特定的要求或假設。在實例級別啟用 NOCOUNT 可能會破壞這些框架的功能并導致兼容性問題。

對特定場景的有限控制：在實例級別啟用 NOCOUNT 會影響該實例上的所有會話和數(shù)據(jù)庫。在處理需要或需要行計數(shù)消息以用于報告、監(jiān)視或其他目的的特定場景或數(shù)據(jù)庫時，這種粒度的缺乏可能會產(chǎn)生問題。

我們的建議

我們建議不要在實例級別啟用 NOCOUNT，而是有選擇地SET NOCOUNT ON在相關存儲過程、觸發(fā)器或查詢中使用。這允許對何時抑制行計數(shù)消息進行更細粒度的控制，確保兼容性、維護預期行為并避免由全局實例級更改引起的潛在問題。顯式設置NOCOUNT ON為高效查詢執(zhí)行和客戶端處理添加了額外的保證。

通過SET NOCOUNT ON在每個存儲過程、觸發(fā)器和動態(tài)執(zhí)行批處理的開頭添加，您可以遵循一致的方法，并有助于避免因未顯式設置而可能出現(xiàn)的任何潛在問題。這是優(yōu)化 SQL Server 查詢、提高性能并確保應用程序和用戶獲得流暢體驗的主動措施。

dbForge Studio for SQL Server 提供了一個內(nèi)置的，可以檢查 SQL 代碼是否存在潛在問題、錯誤或違反最佳實踐的情況。它包括兩個專門設計用于識別 SET NOCOUNT 命令的潛在誤用的靜態(tài)代碼分析規(guī)則：

1. PF008：存儲過程和觸發(fā)器中的 DML 之前沒有 SET NOCOUNT ON
   此規(guī)則檢查存儲過程和觸發(fā)器中的 DML 語句之前未使用 SET NOCOUNT ON 命令的實例。它有助于確保在執(zhí)行 DML 操作之前應用適當?shù)?SET NOCOUNT ON 命令。
2. PF009：使用了 SET NOCOUNT OFF 選項
   此規(guī)則驗證是否正確使用了 SET NOCOUNT OFF 選項。它確保僅在必要時才使用 SET NOCOUNT OFF 命令，并考慮到它對網(wǎng)絡流量和性能的潛在影響。

這些 T-SQL 分析規(guī)則為識別和解決與 SQL Server 代碼中使用 SET NOCOUNT 相關的任何潛在問題提供了寶貴的幫助。

結論

SET NOCOUNT ON命令通過減少網(wǎng)絡流量、減少客戶端負載并增強查詢執(zhí)行來優(yōu)化 SQL Server 性能，發(fā)揮著至關重要的作用。借助其內(nèi)置的 T-SQL 分析器，dbForge Studio for SQL Server可以針對 SET NOCOUNT 命令的潛在誤用提供有價值的見解，從而確保代碼合規(guī)性和性能優(yōu)化。

數(shù)據(jù)庫管理工具交流群：765665608   歡迎進群交流討論

正版數(shù)據(jù)庫管理軟件下載、購買、授權咨詢，請點這里！