CREATE PROCEDURE usp_noCountOFF
declare @i int, @j int, @dbname varchar(128)
select @i = 0, @j = coutn(*) from sys.databases
while @i <= @j
select @dbname = name from sys.databases where database_id = @i
select @i += 1
Now, under the Query menu option, choose INCLUDE CLIENT STATISTICS ON an execute exec usp_noCountOFF
Look at the number of selects, Number of Server Round Trips, Bytes Sent By Client, Bytes Received from server and all of the time statistics.
Next, add the following line to the above procedure, just after the AS statement
SET NOCOUNT ON
Execute the procedure and look at the client statistics. You will see a much lower values compared to the previous execution.
Now extrapolate these figures from this simple example to a 1000+ line stored procedure that's called several hundred times a day? How many round trips, how much network bandwidth and how much processing time can be saved?
If you have large procedures I would suggest compiling alternate versions and comparing the client statistics.