OK. I like logging a lot of information for my tests.
So I created two-three logging tables for perf stat captures :-)
And I've got a stored procedure for my test workload. This is after a few revisions - there's a couple of important comments in there that maybe someday I'll be able to explain more adequately :-) Why count to 8000000 in the loop by default? It was an early estimate for 10 seconds of solid CPU work on this system, and I wasn't motivated enough to bump it higher once I saw that it fell a little short once NOCOUNT was ON. But I don't want to spill the beans early.
So... here's the stored procedure.
OK. Now Aaron Bertrand has a post from February 2016...
Performance Surprises and Assumptions : SET NOCOUNT ON
In that blog post the potential performance benefit of NOCOUNT ON was elusive. And, to be honest, this round of my tests was NOT to learn about NOCOUNT but other stuff. Just happened to stumble on NOCOUNT when I was sifting through stuff after some early testing. But with these results in hand, maybe NOCOUNT has a few more performance surprise for all of us :-)
OK, still need a driver for the tests. I'm old as the dinosaurs so I usually throw together a .bat script before Powershell.
I put together a pair of .bat scripts, one for NOCOUNT OFF (default) and one for NOCOUNT ON. These scripts resided on my laptop, and will be executed from my laptop, with a remote connection to SQL Server via sqlcmd. They looked like this.
The timed_count__reg_2 stored procedure does a fair bit of self-logging, but whenever possible I like to keep eyes-on from outside the individual test sessions as well. Plus, I really like perfmon.
So, on the SQL Server RDBMS VM I have a Sasquatch directory. Its got a .bat script for starting logman and a text file for the counters to log. And a .bat script to stop the counter collection. For this type of test, I typically use a one second collection interval.
Fire up the Sasquatch collector. First executed the script with NOCOUNT OFF. Then executed the script with NOCOUNT ON. Stopped logman. Let's see what I've got.
I'm glad that worked out. The first test was with NOCOUNT OFF. It ran about 50 seconds. The second test was with NOCOUNT ON. It ran really hot on the CPU, in less than 10 seconds. There's something goofy about the CPU vs elapsed numbers for that particular run... that'll have to be for another day.
Notice the difference in the first test between the end ASYNC_NETWORK_IO waiting_tasks_count and the beginning count: 194332 - 186696 = 7636
That's exactly the ASYNC_NETWORK_IO waiting_tasks_count for the first test's session_id 55.
Same with wait_time_ms and signal_wait_time_ms. Nifty :-)
And no ASYNC_NETWORK_IO waits recorded during the 2nd test with NOCOUNT ON. Not at session level, not at system level.
So NOCOUNT made a big difference here. Huh.
Well - can perfmon tell us anything in addition to what we've already seen? Yep. First of all, today was the day I learned that the perfmon wait counters only update every 5 seconds :-). But the other thing perfmon shows us is that the ASYNC_NETWORK_IO waits that occur while NOCOUNT is OFF occur throughout the WHILE LOOP. And... the total wait time isn't determined by the work being done, but by network latency from SQL Server to the client. If the same test is run within the VM, a small amount of ASYNC_NETWORK_IO wait occurrences and total wait time might be observed. But it'll be far less than what I show in these tests. And... this particle test run was really tame. Earlier in the week I ran a similar test from same laptop, same VM and saw the variance from under 10 seconds to over 10 minutes. Gotta go for now though...