Phil Factor

4 January 2018

• SQL Prompt
• SQL Code Analysis
• Query Performance

Finding code smells using SQL Prompt: the SET NOCOUNT problem (PE008 and PE009)

SQL Prompt implements two static code analysis rules to check code for potential misuse of the SET NOCOUNT command:

• PE008 – SET NOCOUNT OFF is used
• PE009 – No SET NOCOUNT ON before DML

Whenever you execute a query, a short message is returned to the client with the number of rows that are affected by that T-SQL statement. When you use SET NOCOUNT ON, this message is not sent. This can improve performance by reducing network traffic slightly. It is best to use SET NOCOUNT ON in SQL Server triggers and stored procedures, unless one or more of the applications using the stored procedures require it to be OFF, because they are reading the value in the message. SET NOCOUNT ON doesn’t affect the result that is returned. It only suppresses the extra packet of message information, which is otherwise sent back to the client as a small (nine-byte) message packet called DONE_IN_PROC, for each statement executed. The server-based logic, and values such as @@ROWCOUNT, are all unaffected.

By default, SET NOCOUNT is set to OFF, at the SQL Server instance level, meaning that DONE_IN_PROC messages will be sent to the client for each statement in a stored procedure. When using the utilities provided with Microsoft SQL Server to execute queries, the message ‘nn rows affected‘ will, by default, be displayed at the end of Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.

Microsoft recommends selective use of SET NOCOUNT ON, at the session level, to prevent sending these messages: “For stored procedures that contain several statements that do not return much actual data, eliminating these messages can provide a significant performance boost, because network traffic is greatly reduced”.

The best approach, generally, is to prevent rowcount messages being sent, unless they are required, but the tricky part is accommodating legacy applications that use, and often misuse, these messages. Additionally, sending these messages can sometimes be a problem for asynchronous processing of procedures by intermediate layers of database applications such as ORMs. The rowcount messages are much slower to be transmitted to the client than the result of the stored procedure, and this can block threads.

What is a message?

A connection to a database passes data and messages separately. In Management Studio (SSMS) these are represented by separate panes in the query window, when queries results are rendered as a grid. When you make a sqlClient connection, the message stream can be read by the InfoMessage handler. For the client to be able to read and process warnings or messages sent by the server, it can listen for these messages via a SqlInfoMessageEventHandler delegate that can respond to these events.

In this article, we are only concerned with one type of message; the rowcount. However, SQL Server can also send messages in response to specific commands; including Raiserror (severity of 10 or less), as well as PRINT statements and the SET STATISTICS trio of statements (SET STATISTICS IO ON, SET STATISTICS TIME ON, SET STATISTICS XML ON). My article Routine SQL DML Testing for the Unenthusiastic Tester illustrates how valuable it can be to use this stream of messages when monitoring performance. The SET NOCOUNT command determines only whether the rowcount messages are sent.

Several applications, components, widgets (such as grids), and middleware (such as ORMs) use the rowcount message to get a count of the current data result, even though it is often difficult to match the query with the count message when the same session is doing a lot of other queries, and it can also cause blocked threads. It is far better to switch off these rowcount messages using SET NOCOUNT ON and return the counts using the value of @@ROWCOUNT, either in the return code of the procedure or by an output variable. However, there is a lot of legacy code that needs to be accommodated.

What is the scope of a SET NOCOUNT setting?

Once you make a connection to SQL Server, and start a session, you need only set NOCOUNT once, and it will affect everything you do in that session. The SET statements that you make will change just the current session’s handling of specific information; every batch you execute in that session will inherit these settings.

If a SET statement is run in a stored procedure or trigger, the previous value of the SET option is restored after control is returned from the stored procedure or trigger. Likewise, if you have a SET NOCOUNT statement in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, then the initial value of the SET option is restored after the dynamic SQL string has been executed. For this reason, there is no need to explicitly set the NOCOUNT at the end of a stored procedure or trigger.

Other than procedures, triggers and dynamically executed batches, every setting of NOCOUNT remains in place within the session until it is changed.

What is the performance advantage of SET NOCOUNT ON?

With well-designed stored procedures, you will see only marginal performance gains from overriding the default server-wide setting for NOCOUNT. That said, in exceptional circumstances, the gains from using SET NOCOUNT ON will be significant. It all depends on the number and frequency of queries that are executed within the procedure. For example, if a procedure is using a cursor to perform a lot of queries whose results then go to make up part of the returned query, or if the procedure contains many statements that do not return much actual data, then it can perform at up to ten times the speed, compared to having NOCOUNT OFF, because network traffic is greatly reduced. With just one or two queries in the procedure, the gain will be less than five percent.

Why not just enable NOCOUNT at the database instance level?

The user options sever configuration setting specifies ‘global defaults’ for each of the SET options, including NOCOUNT. By default, a SQL Server instance will have NOCOUNT disabled, and so each statement issued against a database on that instance will result in a message returned at the end, stating how many rows were affected.

You can modify the instance-level behavior, enabling NOCOUNT and so preventing these messages from being sent, using sp_configure, as shown in Listing 1. This will affect the default settings for all user sessions that start after the setting was made.

Listing 1

Users can override the server-level default, by issuing a SET NOCOUNT statement that affects only their individual sessions.

Triggers should not send rowcount messages; there are no exceptions to this rule. In fact, if the intermediate application layer is expecting certain row count messages, and you use SET NOCOUNT OFF for triggers, this can cause strange random bugs. Even SSMS’s data grid can fall foul of the trigger problem.

However, elsewhere, there are plenty of exceptions. Preventing these messages at the instance level could cause problems if you have any legacy components accessing your databases that make use of the returned rowcount message. Often, these can easily be accommodated by setting the NOCOUNT as appropriate for the stored procedures being used by these components. However, if they are accessing tables directly and you have no means of adding a SET NOCOUNT OFF for those sessions, then changing the database instance-level setting would be unwise.

Also, if a component within the application, such as an ORM or LINQ, is misusing this message to determine the row count of the result, then several bad things can happen if you turn the messages off.

What can break if you turn NOCOUNT on?

If you are using a DataAdaptor to call a SQL Server stored procedure, to edit or delete data, do not use SET NOCOUNT ON in the stored procedure definition. This causes the rows affected count returned to be zero, and the DataAdapter throws a DBConcurrencyException. In fact, sensible defensive programming will mean issuing an explicit SET NOCOUNT OFF in these cases.

The sqlclient.sqlcommand class can also suffer from problems with SET NOCOUNT ON, probably caused by the way that the client is using ODBC. The rowcount message is made available in ODBC when an application calls SQLRowCount. It isn’t reliable information because some data sources cannot return the number of rows in a result set before fetching them.

Even in SQL Server, this value is only reliable if you subsequently test the ‘NOCOUNT status’ after reading the SQLRowCount. When the NOCOUNT option is set to on, then SQLRowCount returns 0, even though there were results. If SQLRowCount returns 0, the application should find out if NOCOUNT is ON by testing the value of the SQL Server-specific SQL_SOPT_SS_NOCOUNT_STATUS attribute. If the value SQL_NC_ON is returned then the value of 0 from SQLRowCount merely means that SQL Server has not returned a rowcount. If SQL_NC_OFF is returned, it means that NOCOUNT is off and the value of 0 from SQLRowCount indicates that the statement did not affect any rows, and so there is no need to process the result.

So, what’s the ‘best practice’ advice?

The simple advice that works well is to leave the database instance-level default as-is, and to add a SET NOCOUNT ON at the start of every stored procedure, trigger and dynamically executed batch. This rule will apply to all triggers, without exception. No stored procedures will need these messages either, unless they are called from outside the database by an application that is trying to use them to get a gauge of the number of rows of a result. In general, it is better to send the count in an output variable, using the value in @@RowCount, but this doesn’t help pre-existing application components. If you need to ensure that a query returns a rowcount message, you should specify it rather than assume the current setting.