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.
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)
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.
The execution times for NOCOUNT ON and OFF differed by 1 second at most.There is no much difference.
What happen if I add?
SET NOCOUNT ON;
SELECT * FROM dbo.Alumnos WITH(NOLOCK)
SET NOCOUNT OFF;
In this case SET NOCOUNT ON works fine?