NOCOUNT Performance Differences

Aaron Bertrand looks into whether SET NOCOUNT ON provides a performance improvement:

For years, I had been operating under the impression that SET NOCOUNT ON; was a critical part of any performance strategy. This was based on observations I had made in, arguably, a different era, and that are less likely to manifest today.

Check out the comments as well.  This is an interesting conundrum as there’s a lot of ingrained knowledge that SET NOCOUNT ON is faster (and I admit that I thought I remembered it being the case when going through loops), but people have had limited success in coming up with a scenario in which it makes an appreciable difference.

Related Posts

SQL Server Memory Usage Myths

Eric Blinn has a few myths regarding memory usage in SQL Server: My VM administrator says that I’m not using all the memory I asked for.  In fact, 70% of it is idle at any given time.  We’re going to return that memory to the resource pool to better utilize it on other VMs. The […]

Read More

Separating Data And Log Files

Brent Ozar looks at an old chestnut: So it’s time for a quiz: If you put all of a SQL Server’s data files & logs on a single volume, how many failures will that server experience per year? Bonus question: what kinds of data loss and downtime will each of those failure(s) have? If you […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29