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.
The one place where NOCOUNT ON comes in handy is when you’re dealing with older data access methods which tend to become very unhappy when they get rowcount messages back from the server. But, if you were dealing with those methods, you’d know all about that pain. If you’re using a lot of dynamic SQL, I can definitely see how NOCOUNT ON could definitely improve performance by not sending back 30+ ‘1 row(s) affected.’ messages.
this bit is the kicker for me:
“According to the vendor, this is recommended to help performance and/or avoid issues with stored procedures that forgot to enable the NOCOUNT option”
so you are catering for people who FORGOT top enable NOCOUNT where is was required, how about, dont forget???
whats next, automatic ‘selects’ for those that forgot to select?
Jeremiah,
would you enable all user connections to the instance have SET NOCOUNT ON by default? or would you prefer for people to manage that option themselves?
Wow, the vendor really needs to tighten up their QC. If the product requires a database with some custom settings either ensure your devs are creating the objects from a template that turns these settings on or dump your schema to text and have someone verify that everything conforms to desired settings (the latter would be more time consuming and I feel for that guy).