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

dbForge Studio for SQL Server正版試用下載

了解 SQL Server 中的 SET NOCOUNT

SET NOCOUNT 是什么的解釋

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

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

SQL Server 中 SET NOCOUNT ON 的作用

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

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

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

不使用 SET NOCOUNT ON 的影響

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

緩沖區(qū)溢出問(wèn)題

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

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

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

必須設(shè)置 SET NOCOUNT ON 的情況

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

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

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

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

可能需要 SET NOCOUNT OFF 的情況

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

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

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

使用 SET NOCOUNT 的最佳實(shí)踐

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

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

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

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

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

為什么在實(shí)例級(jí)別啟用 NOCOUNT 不是個(gè)好方案

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

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

注意
該user options設(shè)置是位掩碼，請(qǐng)相應(yīng)處理。

但是，由于以下幾個(gè)原因，在實(shí)例級(jí)別啟用 NOCOUNT 可能被認(rèn)為是一個(gè)壞主意：

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

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

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

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

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

我們的建議

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

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

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

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

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

結(jié)論

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

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

正版數(shù)據(jù)庫(kù)管理軟件下載、購(gòu)買、授權(quán)咨詢，請(qǐng)點(diǎn)這里！