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

dbForge Studio for SQL Server正版試用下載

了解 SQL Server 中的 SET NOCOUNT

SET NOCOUNT 是什么的解釋

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

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

SQL Server 中 SET NOCOUNT ON 的作用

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

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

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

不使用 SET NOCOUNT ON 的影響

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

緩沖區溢出問題

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

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

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

必須設置 SET NOCOUNT ON 的情況

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

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

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

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

可能需要 SET NOCOUNT OFF 的情況

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

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

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

使用 SET NOCOUNT 的最佳實踐

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

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

使用 SET NOCOUNT ON 可以獲得哪些性能優勢？

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

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

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

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

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

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

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

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

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

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

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

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

我們的建議

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

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

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

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

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

結論

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

數據庫管理工具交流群：765665608   歡迎進群交流討論

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