SET NOCOUNT ON usage
Ok now I've done my research, here is the deal:
In TDS protocol, SET NOCOUNT ON only saves 9-bytes per query while the text "SET NOCOUNT ON" itself is a whopping 14 bytes. I used to think that 123 row(s) affected was returned from server in plain text in a separate network packet but that's not the case. It's in fact a small structure called DONE_IN_PROC embedded in the response. It's not a separate network packet so no roundtrips are wasted.
I think you can stick to default counting behavior almost always without worrying about the performance. There are some cases though, where calculating the number of rows beforehand would impact the performance, such as a forward-only cursor. In that case NOCOUNT might be a necessity. Other than that, there is absolutely no need to follow "use NOCOUNT wherever possible" motto.
Here is a very detailed analysis about insignificance of SET NOCOUNT setting: http://daleburnett.com/2014/01/everything-ever-wanted-know-set-nocount/
It took me a lot of digging to find real benchmark figures around NOCOUNT, so I figured I'd share a quick summary.
- If your stored procedure uses a cursor to perform a lot of very quick operations with no returned results, having NOCOUNT OFF can take roughly 10 times as long as having it ON. 1 This is the worst-case scenario.
- If your stored procedure only performs a single quick operation with no returned results, setting NOCOUNT ON might yield around a 3% performance boost. 2 This would be consistent with a typical insert or update procedure. (See the comments on this answer for some discussion about why this may not always be faster.)
- If your stored procedure returns results (i.e. you SELECT something), the performance difference will diminish proportionately with the size of the result set.
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement.
The setting of SET NOCOUNT is set at execute or run time and not at parse time.
SET NOCOUNT ON improves stored procedure (SP) performance.
Syntax: SET NOCOUNT { ON | OFF }
Example of SET NOCOUNT ON:
Example of SET NOCOUNT OFF:
