Does SET NOCOUNT ON really make that much of a performance difference

Question

In this article, the author suggests that there is material overhead associated with SET NOCOUNT ON and that "By removing this extra overhead from the network it can greatly improve overall performance for your database and application"

The author references a change in the default stored procedure template from 2000 to 2005 and suggests that "Microsoft even realized the issue " which prompted the change in this template.

Does somebody have hard evidence that either supports or refutes the claimed performance gain with setting NOCOUNT ON.

Answer 1

There are scenarios where SET NOCOUNT ON is mandatory. When designing a high performance mid tier based on asynchrnous processing leveraging the thread pool via the SqlClient's BeginExecuteXXX methods, there is a very serious problem with the row counts. The BeginExecute methods complete as soon as the first response packet is returned by the server. But when a EndExecuteXXX is invoked, this completes on non-query requests when the call is complete. Each rowcount response is a response. When procesing even moderatly complex procedures the first row count could come back in 5-10 ms, while the call completes in 300-500ms. Instead of having the submited async request call back after 500ms, it calls back after 5 ms and then the callback blocks in the EndExecuteXXX for 495 ms. The result is that asynchronous calls complete prematurely and block a thread from the thread pool in the EndExecuteNonQuery calls. This leads to ThreadPool starvation. I've seen high performance systems improve the throughput from hundreds of calls per second to thousands of calls per second simply by adding the SET NOCOUNT ON, on specific scenarios.

Given that for high scale/high throughput midle tier processing asynchronous calls are the only way to go, the NOCOUNT is pretty much a mandatory requirement.

Answer 2

The last link in my question here: "SET NOCOUNT ON usage" refers to an article on it.

Given how trivial it is, why not leave it in and stop the client processing another resultset?

And without SET NOCOUNT ON, nHibernate can break too (mentioned in question)

Answer 3

I agree that using NOCOUNT is a great idea. It is a terrible idea to Add it to all code executed in every SPROC or dynamic SQL statement.

Especially if you are talking about high performance. TSQL NOCOUNT should be set as needed by the code in the data access layer. Just like transactions and locking levels.

Setting these things every time in the executing SQL to be executed every time does not give you the great performance boost you could get by setting them in the code of the connecting application.

Better performance is found by writing better code not by adding SET NOCOUNT statements to all your SQL.