David,
Quite an interesting twist on some things you got used to.
In fact I put this statement in all my Stored Procedures.
However I do it (at least did) because of different reason. Back in time of ADO (NOT .Net) the returned message sometimes was treated as a Recordset along with the result you expected. Technically it was a collection of Recordsets, so you needed to fetch through in order to get what you really wanted. The message always was the first in that collection and could easily break the front end logic if you tried to refer some field by name, because message did not have any named fields. I remember several times I had a hard time troubleshooting the problem when someone "optimized" SP by removing/commenting "set nocount on".
I guess it is not a problem with ADO.Net though never put it in test if message adds any table in output Dataset. Indeed it is good to know that performance is better, though 3% boost can easily be eaten in a network environment. Also it is marginaly close to the "experiment error" - the difference in 8 and 9 (with ON) is about 1.5% itself.
Anyway it is not probably bad sometimes to re-evaluate some "good practice" recommendations just to get sense of them and understand a bit better the meaning and origines.
Thanks
Excellent article!
I knew it increased performance setting nocount off; the fact that the performance increase is so slight is interesting.... I would have expected more of a performance increase.
Mark
Great article with interesting points. I'm going to have to run tests to see what impact this has on SQL Moblie.
David, as far as the owner thing goes I see one possible hole in how you set up your experiments. You did the creates and tests with the same user. More interesting would be to log on as dbo and do the create, then log on as a user and do the tests. Often owner based object searches do the local logon first and then work up the tree. Some start at the top of the tree and work down so that globally available objects come up first.
we had a situation where a job on a server was performing in a couple of minutes while on a remote pc (wan connected) it took 30+ minutes. changing this setting made the performance comparable with running on a server. the exact timings i dont remember but it was over 50% time reduction.
Hi David,
That's a good article. Thank you very much for it - its good to have theories be tried and tested.
A couple of comments.
1) Is there there a reason you didn't run
DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE
on each iteration? I don't know if it would have made a difference or not - perhaps its worth trying out.
2) You've created four sprocs there. In the interests of a proper scientific experiment where one should control variables properly - you should make sure there is only one difference between the things you are testing, should you not? Well AddTestWithNoCount & AddTestWithoutNoCount have TWO differences. The former specifies NOCOUNT and does not specify a schema qualifier for the table. The second does not specify NOCOUNT but also DOES specify a schema qualifier. Hence - two differences. If you are testing the effect of NOCOUNT then shouldn't all other things be equal?
Minor points though. its a good article.
Jamie
Agreed - the graph is misleading in the extreme. That completely passed me by so thank you to Michael for point it out.
-Jamie
It increases slightly mostly because of the messages produced by
themselves. Looks like it adds a little more than a mintue to each run.
Also even thou it is fractional the savings wasn't just about time it has also been stated as reducing network overhead. But either way when you are trying to streamline your process and keep interaction speed high doing this is still a bennefit overall. Especially on servers with high loads.
