SQL SERVER – SET NOCOUNT – Impact on Stored Procedure Performance

Sometimes it is critical to get back to basics when working on solving a complex problem at Comprehensive Database Performance Health Check. Recently, I solved a huge performance problem for one of my clients by just adding the SET NOCOUNT inside the stored procedure. Let us learn more about this topic in today’s blog post.

SQL SERVER - SET NOCOUNT - Impact on Stored Procedure Performance SETNOCOUNT-800x285

Brief Note

Recently, I helped one of the largest banks to tune their SQL Server which had over 1 TB of RAM and with over 256 logical processors. After going over various important parameters for the server, database, and indexes, we had finally arrived on the point to tune the queries. We realized that there is one particular query that is running very slow and it is because it contained a huge WHILE loop. I am one of the people who are totally against WHILE LOOP and CURSORS. I believSET NOCOUNT e that there is no query which can’t be written with the help of the SET theory and there is no need for WHILE LOOPs and CURSORS. (Only exceptions are looping over Database Activity to Maintain Database, we will talk about it in the future).

While I really wanted to re-write the entire query in a couple of hours, it is was not possible to do as the client was not ready to spend time on that stored procedure. Additionally, we were under tremendous pressure to improve the server’s performance by 4x in just 4 hours of time.

SET NOCOUNT ON

After careful observation, I realized that this stored procedure was displaying lots of rows with the help of the cursor. I also noticed that it was missing a keyword SET NOCOUNT ON on the beginning of the stored procedure which suppresses the number of rows selected.

I immediately included the keyword at the beginning of the stored procedure and the performance of the procedure improved over 2x. Let us simulate the scenario.

Let us create two stored procedures.

SP1: With SET NOCOUNT ON

CREATE OR ALTER PROCEDURE TestNoCountON
AS 
BEGIN
SET NOCOUNT ON;
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=100)
BEGIN
SELECT *
FROM [WideWorldImporters].[Purchasing].[PurchaseOrderLines]
WHERE [PurchaseOrderLineID] = @intFlag
SET @intFlag = @intFlag+1
END
END
GO

SP2: With SET NOCOUNT OFF

CREATE OR ALTER PROCEDURE TestNoCountOFF
AS
BEGIN
SET NOCOUNT OFF;
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=100)
BEGIN
SELECT *
FROM [WideWorldImporters].[Purchasing].[PurchaseOrderLines]
WHERE [PurchaseOrderLineID] = @intFlag
SET @intFlag = @intFlag+1
END
END
GO

Now let us execute both the stored procedure one at a time.

EXEC TestNoCountON
GO

The above stored procedure ran in just 6 seconds. As there was no output in the messages window.

EXEC TestNoCountOFF
GO

The above stored procedure ran in just 16 seconds. As this has to display the number of rows impacted in the messages windows. Please note that the time may vary in every single experiment.

Well, just a simple change of adding the keywords, in the beginning: SET NOCOUNT ON you can improve the performance of your system by many folds.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

SQL Cursor, SQL Scripts, SQL Server, SQL Stored Procedure
Previous Post
SQL SERVER – Clone Database Using DBCC CLONEDATABASE
Next Post
SQL SERVER – DATABASE SCOPED CONFIGURATION – PARAMETER SNIFFING

Related Posts

3 Comments. Leave new

  • Albert Van Biljon
    December 18, 2019 1:32 pm

    I could not quite replicate your finding on our dataset using one particular scenario.
    My stored procedure run the same query 150 times for different subsets of data selected from a table, with the result sets being between 10 and 100 rows: SELECT * FROM table WHERE PK_From_ParentTable = @x, where @x had a different value for each iteration in the WHILE-loop.
    The execution times for NOCOUNT ON and OFF differed by 1 second at most.
    Perhaps there is more to this then. But I’ll lean more towards NOCOUNT ON in the future based on your findings anyway.

    Reply
  • The execution times for NOCOUNT ON and OFF differed by 1 second at most.There is no much difference.

    Reply
  • What happen if I add?

    BEGIN
    SET NOCOUNT ON;

    SELECT * FROM dbo.Alumnos WITH(NOLOCK)

    SET NOCOUNT OFF;

    END

    In this case SET NOCOUNT ON works fine?

    regards.

    Reply

Leave a Reply