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