Is SET NOCOUNT ON the Answer?

You may have a stored procedure that completes in an acceptable amount of time for the dozen or so times a day it gets called. Maybe it returns results in a few seconds and that makes the users calling it happy enough that you can move onto more pressing matters. But what about a stored procedure being called millions of times a day? The definition of acceptable can be drastically different when you consider the speed and traffic that type of stored procedure produces. When every millisecond matters, it’s worth checking to see what your setting is for SET NOCOUNT.

What is NOCOUNT?

If you’re running a stored procedure, especially one that loops through data, it doesn’t take much to see a wall of text showing “# rows affected” in the Messages tab. Setting NOCOUNT ON prevents these DONE_IN_PROC messages from displaying. Not returning these DONE_IN_PROC messages for each statement will help lessen the load of network traffic when the stored procedure is being executed.

NOCOUNT in Action

Here’s an example stored procedure to grab the top 5 names out of a Person_Archive table a thousand times:

CREATE PROCEDURE [dbo].[spGetPerson]
AS
BEGIN

	DECLARE @Counter INT

	SET @Counter = 0

	WHILE (@Counter < 1000)
	BEGIN
		SELECT TOP 5 first_name,last_name
		FROM Person_Archive

		SET @Counter = @Counter + 1
	END
END

Without specifying SET NOCOUNT OFF, we will see the following in the Messages tab when this stored procedure runs:

We see our wall of text and there are plenty more lines of “5 rows affected” beyond what’s in that screenshot. In this case, we’re going to assume we don’t care about the rows affected counts and SET NOCOUNT ON:

ALTER PROCEDURE [dbo].[spGetPerson]
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Counter INT

	SET @Counter = 0

	WHILE (@Counter < 1000)
	BEGIN
		SELECT TOP 5 first_name,last_name
		FROM Person_Archive

		SET @Counter = @Counter + 1
	END
END

Now, we can run our stored procedure and we’ll only see the “Commands completed successfully” message:

SET ON, SET OFF?

Adding SET NOCOUNT ON to the beginning of a stored procedure is unlikely to take execution time from around 10 seconds to under 1 second. It’s beneficial to use when you’re not concerned with counts but I would not take the time blindly adding it to every one of your stored procedures expecting it to solve all of your problems.

Using our example above, we can compare running the stored procedure a 100 times with and without SET NOCOUNT ON. Without specifying SET NOCOUNT ON we get a time of 13 seconds:

If we include SET NOCOUNT ON our time is reduced to 9 seconds:

SET NOCOUNT ON in this case saved us about 4 seconds which is a nice chunk of time, especially if that time is on a stored procedure that is frequently used.

Thanks for reading!