Rajendra Gupta
Include Client Statistics with SET NOCOUNT ON

SET NOCOUNT ON statement usage and performance benefits in SQL Server

August 26, 2019 by

Have you ever noticed SET NOCOUNT ON statement in T-SQL statements or stored procedures in SQL Server? I have seen developers not using this set statement due to not knowing it.

In this article, we will explore why it is a good practice to use SET NOCOUNT ON with T-SQL statements. We will also learn the performance benefit you can get from it.

Introduction

Before we explore this statement, let’s create a sample table in the SQL Server database with the following script.

Let’s insert a few records data in this table using the following script.

Once you execute the command, you get the following message in SSMS. You get ‘1-row affected’ message for each row.

The output '1 row affected' in SSMS

Suppose you insert a million rows in the table and for each record, you get this message. As you can see, it is not a useful message and does not provide relevant information.

Now, let’s select the records from this table.

In the output, we get two tabs Results and Messages.

  • Result tab shows the record from the table
  • The messages tab gives the number of rows affected message

Result and message tab in SSMS

Let’s create a stored procedure to get the records from the specified table.

Execute this stored procedure, and you get a similar output in the following screenshot as well.

Output of a stored procedure in the results and message tab

SET NOCOUNT ON/OFF statement controls the behavior in SQL Server to show the number of affected rows in the T-SQL query.

  • SET NOCOUNT OFF – By default, SQL Server shows the number of affected rows in the messages pane
  • SET NOCOUNT ON – We can specify this set statement at the beginning of the statement. Once we enable it, we do not get the number of affected rows in the output

Let’s try running the previous queries with NOCOUNT statement.

  • Execute the insert statement after enabling the NOCOUNT

    Once we execute the above queries we do not get messages of 1 row(s) affected. It gives the following message.

    Effect of SET NOCOUNT ON

  • Execute the Select statement with NOCOUNT ON, and you get the following output

    Similar to the insert statement, we did not get the number of rows affected message in the select statement output.

    Effect of NOCOUNT messages on select statement

  • Execute Stored procedure

    We cannot directly execute the stored procedure with the SET NOCOUNT ON statement. The above statement does not work on the stored procedure.

    We either need to create a new stored procedure or alter the procedure and add the SET NOCOUNT statement as per the following script.

    Execute the stored procedure, and we get the required output without the number of affected rows message.

    Impact of SET NOCOUNT ON with the stored procedure

Configure the behavior of NOCOUNT at instance level

The SET NOCOUNT ON works at the session-level. We need to specify it with each session. In the stored procedures, we specify the code itself. Therefore, it does not require specifying explicitly in the session.

We can use the sp_configure configuration option to use it at the instance level. The following query sets the behavior of SET NOCOUNT ON at the instance level.

If we specify the NOCOUNT ON/OFF in the individual session, we can override the behavior configured at the instance level.

SET NOCOUNT and @@ROWCOUNT function

We can use the @@ROWCOUNT function to get the number of affected rows in SQL Server. The NOCOUNT ON function does not have any impact on the @@ROWCOUNT function.

Execute the following query, and we get the number of rows affected with the Insert statement.

SET NOCOUNT and @@ROWCOUNT function

SET NOCOUNT ON and the SQL Trigger

Let’s check the impact of the NOCOUNT statement on the SQL Triggers.

The following command inserts two records in the tblEmployeeDemo table.

Create another table to store the record transaction records inserted using the trigger.

Let’s create a SQL INSERT, Update trigger to capture the records for the insert, update values.

Execute the following query to update an existing value in the tblEmployeeDemo table, and it invokes the SQL trigger for inserting record in the Audit_tblEmployee table.

The update statement updates only one record; however, in the following SSMS message, it shows two rows affected.

Trigger issue

It might create issues for us if the further code depends upon the number of rows affected message. We get this message due to update record tblEmployeeDemo and insert record in the Audit_tblEmployee table.

We do not want the result ‘ 1 row affected’ for the data insertion in the audit table. We should use the trigger with SET NOCOUNT ON for suppressing this message.

Let’s alter the trigger with the following script:

Rerun the update statement, and we get the expected result and get only the number of affected rows using the update statement.

SET NOCOUNT impact on the trigger output

The Performance impact of NOCOUNT statement

According to Microsoft documentation, using NOCOUNT option can provide a significant performance improvement.

Microsoft Documentation

Let’s explore this performance benefit with the following example.

Create two different stored procedures with different NOCOUNT properties.

  • Create a stored procedure with default behavior ( NOCOUNT OFF)

  • Create stored procedure with explicit set statement

Execute both the stored procedures with the different number of rows 1000, 10000, 100000 and 1000000. We want to capture client statistics for these executions. In the query window of SSMS, go to Edit and enable the Include Client Statistics.

Include Client Statistics

Let’s compare the client statistics

  • with SET NOCOUNT OFF

    Include Client Statistics with default SQL behaviour

  • With SET NOCOUNT ON

    Include Client Statistics with SET NOCOUNT ON

Let’s put both screenshots of client statistics together to see a difference

Comparison of client statistics

We can see a huge difference in the TDS packagers received from the server, Bytes received from the server and the client processing time. The number of the select statement also shows a significant improvement. We did not specify the Select statement in the stored procedure, but still, SQL Server treats SET statement as a select statement with the default NOCOUNT value. We can reduce the network bandwidth with the SET NOCOUNT ON option in the stored procedures or T-SQL statements. It might not improve the query performance drastically, but definitely, it puts an impact on the processing time, reducing the network bandwidth and client processing times.

Conclusion

In this article, we explored the behavior of T-SQL statements and stored procedures using the SET NOCOUNT ON. We should consider this SET option and eliminate unnecessary messages to reduce network traffic and improve performance.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views