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.