Recently we had a vendor suggest to us that we turn off the option for NOCOUNT on their database. Upon hearing this my first question was, naturally, why? What was the issue such that they would say they wanted this turned off for their database(s)? Those questions were immediately followed by, naturally, how? How do I disable this for only one database on an instance?

The answer is you don’t, at least not for MS SQL, despite the vendor documentation that gives the impression that it is an option at the database level. It is, however, an option at the instance level. Of course, it is not as easy as firing up sp_configure and flipping a bit. Oh no, what you need to do is to configure the user options using the following statement:

EXEC sys.sp_configure ‘user options’, ’512′

So, now I knew the ‘how’, but I was left to wonder ‘why’? According to the vendor, this is recommended to help performance and/or avoid issues with stored procedures that forgot to enable the NOCOUNT option. I found this quick blurb about the issue, which shed some additional light on the matter. Now, if it truly helps performance, then why not make it the default on all my instances, right?

Well, because this is one of those that will lead to more confusion than anything else. As the link above suggests, the proper use of this setting is to configure it for each connection, or inside your stored procedures, and not (necessarily) at the instance level. Can you imagine if I made this the default setting after an install? I can see it now, all the phone calls “hey, SSMS stopped telling me how many rows are being returned”, or “hey, all my DataAdapters stopped working”.

And the real kicker to all of this? Well, read that last part of the link. Where it says “using SET NOCOUNT ON can improve performance slightly”. I take that to mean “if you code is so poorly designed such that you need to consider setting this option at the server level to boost performance, then by all means do so”. I did a few tests and found that the performance gain is minimal at best, but that was really just a few quick tests involving some standard queries. I am certain if I had some lengthy stored procedures with lots of result sets there could be a performance boost as well as a reduction in network traffic.

Look, I am all for making things efficient, but this does not seem to be the right way to get the job done. A better way would be to manage the connections yourself and to be aware of this issue when writing your stored procedures.

I found lots of links about how this setting would help boost performance with triggers, so perhaps that is at play here as well. But since we already know how I feel about triggers, I will not accept that as a valid reason for this instance wide configuration option. I would much rather rip out those triggers with a shank than force every connection to the instance to adhere to the SET NOCOUNT ON option.