Friday, November 6, 2009

SET NOCOUNT ON

Many DBAs may have heard that setting NOCOUNT ON in stored procedures is a"best practice". But why? Try out this little example that loops through the sys.databases catalog view, gets the database name and prints the date.

CREATE PROCEDURE usp_noCountOFF
as
declare @i int, @j int, @dbname varchar(128)
select @i = 0, @j = coutn(*) from sys.databases
while @i <= @j
begin
select @dbname = name from sys.databases where database_id = @i
select GETDATE()
select @dbname
select @i += 1
end
go


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
and recompile.

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.