Performance Boost Up With SET NOCOUNT ON

Test with Set NOCOUNT OFF

Creating temporary table:

create table #numbers(num int not null primary key);

Sample code:

Set NOCOUNT OFF
Declare
@i int
set @i=1
while(@i<100000)
begin
insert into #numbers values (@i)
set @i=@i+1;
end

Result set:

Sample message in the result set is:
(1 row(s) affected)

For our sample code we will get 99999 similar messages from server to client because that many number of times our DML statement executed in the loop.

Total execution time to insert these many records (99999 rows) is nearly 00:00:53 seconds.

These are the messages that SQL Server sends back to the client after each T-SQL (Select and DML (INSERT, UPDATE, and DELETE statements)) statement is executed.

This kind of information is required when we execute individual statements in the query analyser, but if we execute these set of T-Sql statements in the stored procedure there is no need to pass back this info to the client except in case of while debugging the stored procedure.

Test with SET NOCOUNT ON:

set nocount ON
declare
@i int
set @i=1
while(@i<100000)
begin
insert into #numbers values (@i)
set @i=@i+1;
end

Now it has taken only 00:00:06 seconds to insert 99999 records into table.

we can see an considerable performance boost up with SET NOCOUNT ON.
Set nocount on statement turn off these kind of messages coming from server to client after for each T-Sql statement executed.

By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

There’s no need to SET NOCOUNT OFF at the end of the stored proc.  When you exit the scope of the stored procedure it’ll turn back on if the connection is configured to have it turned on by default (which SSMS is).

Microsoft even realized the issue that this creates and has changed the stored procedure templates from SQL Server 2000 to SQL Server 2005.

Here is the old template style available in SQL Server 2000 without the SET NOCOUNT ON.

=============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<procedure_name, sysname, proc_test>'
AND type = 'P')
DROP PROCEDURE <procedure_name, sysname, proc_test>
GO

CREATE PROCEDURE <procedure_name, sysname, proc_test>
<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>
AS
SELECT @p1, @p2
GO
-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE <procedure_name, sysname, proc_test> <value_for_param1, , 1>, <value_for_param2, , 2>
GO
Here is the new template style available in SQL Server 2005 with the SET NOCOUNT ON.

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

Data from Greg Robidoux blog(SET NOCOUNT ON Improves SQL Server Stored Procedure Performance):