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.