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.

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)