SQL Server, better performance with the NOCOUNT option

Hi Guys,

As you know in our blog, the final goal is always performance.

So today is the time to talk about another best practice.

Are you Ready? yes? go!

p.s. By the way, if you want to know when my posts come out follow me also on linkedin here

 

SET NOCOUNT ON

What is the option NOCOUNT?  

The NOCOUNT is an option, its value can be ON or OFF and determines whether  the DONE_IN_PROC messages are sent to the client of not.

The DONE_IN_PROC message indicates the completion status of an SQL statement within a stored procedure (and therefore also a trigger). Inside a SP a message is sent for ech executed statement.

By default, the NOCOUNT option is set to OFF but you can set it to ON with the command 

SET NOCOUNT ON

In this case, SQL Server will not send the DONE_IN_PROC messages.

DONE_IN_PROC messages are sent to the client via the TDS protocol.

Tabular (o token) data Stream (or TDS) is the protocol implemented by drivers that allows an application to communicate directly with SQL Server. We have already talked about it here Speaking to Sql Server, sniffing the TDS protocol

Remember that NOCOUNT is responsible for "x rows affected" or "command (s) completed successfully" messages. So if you set it to ON these message will not be displayed.
...but inside a stored procedure, you don't need it!

Why we are speaking of performances?

Because set the NOCOUNT to ON inside a stored procedure or a trigger can lead to better performance. You do not believe me?

Let's do our usual test!

 

The Test

Well, this time we will not be using SSMS! Today we will be using the RML utilities instead as they allow us to run a workload in parallel.

Through the RML utilities we are going to execute two stored procedures that differ only in the fact that the nocount value is set to ON or OFF.

The stored procedure in this example insert a certain number of rows into a table (how? See tips from last post) and then perform an UPDATE.

CREATE PROCEDURE SP_TEST_NOCOUNT_ON AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #PROGR (x1 INT ,x2 INT,x3 INT)

  ;WITH progr(x) AS
  (
		SELECT 1
		UNION ALL
		SELECT x+1 FROM progr WHERE x < 50
  )
  INSERT INTO #PROGR(x1,x2,x3)
    SELECT x,x,x FROM PROGR
	OPTION (MAXRECURSION 0);

  UPDATE #PROGR SET x2 = 0 , X3 = 0 where x1 < 25
END
 

CREATE PROCEDURE SP_TEST_NOCOUNT_OFF AS
BEGIN
  SET NOCOUNT OFF;

  CREATE TABLE #PROGR (x1 INT ,x2 INT,x3 INT)

  ;WITH progr(x) AS
  (
		SELECT 1
		UNION ALL
		SELECT x+1 FROM progr WHERE x < 50
  )
  INSERT INTO #PROGR(x1,x2,x3)
    SELECT x,x,x FROM PROGR
	OPTION (MAXRECURSION 0);

  UPDATE #PROGR SET x2 = 0 , X3 = 0 where x1 < 25
END

We invoke the RML utilites with the ostress command just to execute our Stored procedure 20 times in 400 threads.

OStress.exe -ic:\scambio\OStress\Nocount\NoCount_OFF.SQL -Usa -P1Password1 -SCSW011 -dtest_temp_table -oc:\scambio\ostress\Nocount\ -n400 -r20

Where the NoCount_OFF.SQL simply contain exec SP_TEST_NOCOUNT_OFF

OStress.exe -ic:\scambio\OStress\Nocount\NoCount_ON.SQL -Usa -P1Password1 -SCSW011 -dtest_temp_table -oc:\scambio\ostress\Nocount\ -n400 -r20

Where the NoCount_ON.SQL simply contain exec SP_TEST_NOCOUNT_ON
      

And now the results:

With NOCOUNT = OFF the execution lasted 23.302 seconds

With NOCOUNT = ON the execution lasted 12.095 seconds

WOW! Great difference ...from 23 to 12 seconds!!! 

 

In this case the execution time of is reduced to about half.

So what i can say ....if you have or manage stored procedures or triggers know what tests to do on Monday!

That'all for today mate,

Don't forget to follow me, here and on linkedin, if you liked this post. 

Have a nice weekend!

Luca

Luca Biondi @ SQLServerPerformance blog!

Next post:

Previous post: SQL Server, generate a number of rows