I was a little skeptical of the NOCOUNT ON boosting query performance so I went to test it. My new job requires me to wear both DBA and DB Developer hats so I am trying to get up to speed on the basics.
I created a customer table with 3,000 rows. I used http://www.generatedata.com/#generator to generate some data. Love that site. You can quickly generate data free (I know there are probably better pay-to-play tools out there, but this is free).
I then created two very simple Stored Procedures.
create procedure SelectCustomer as set nocount on select * from customer
create procedure SelectCustomer2 as select * from customer
I turn on query statistics using set statistics time on, and called the procs. Execution times vary, but one constant was the fact that SelectCustomer performed a lot better than SelectCustomer2. In 3 samples SelectCustomer performed 24%, 34%, and 35% better. These numbers seem skewed, and I would not go by the drastic performance gains of this little test case. But one thing for sure the SET NO COUNT ON does provide a performance boost. I imagine the performance boost on proc that returns several million rows could be drastic.