Performance Tuning Part -10 -NoCount

Use ‘SET NOCOUNT ON’ in procedures. SQL server writes the number of rows affected to the client by default, which is not useful for application procedures, but forces SQL to do extra work. This may affect the query performance for no good reason. SQL server keeps the row count in @@rowcount, which can be used if the row count is required

Demo:

Let’s create two procedures first.

--Procedure 1(Without NoCount)

CREATE
OR

ALTER PROCEDURE WithOutNoCount
AS
BEGIN
CREATE TABLE #Table (ID INT)

DECLARE @ID INT = 1
DECLARE @MAXID INT = 100000

WHILE (@ID <= @MAXID)
BEGIN
INSERT INTO #Table
VALUES (@ID)

SET @ID = @ID + 1
END
END
GO

--Procedure 2(With NoCount)

CREATE
OR

ALTER PROCEDURE WithNoCount
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #Table (ID INT)

DECLARE @ID INT = 1
DECLARE @MAXID INT = 100000

WHILE (@ID <= @MAXID)
BEGIN
INSERT INTO #Table
VALUES (@ID)

SET @ID = @ID + 1
END
END
GO

There are two procedures in above script and both are same except second one also has ‘SET NOCOUNT ON’ 

Now let’s execute the procedures and capture the profiler data.

EXEC WithOutNoCount

EXEC WithNoCount

Above is the profiler data for both procedure executions. The total duration of execution for procedure without ‘SET NOCOUNT ON’ on is continuously more 2000ms while duration of procedure with ‘SET NOCOUNT ON’ on is just more 1200ms .

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 9+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.