Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Finding Code Smells Using SQL Prompt: The SET NOCOUNT Problem (PE008 and PE009)

DZone's Guide to

Finding Code Smells Using SQL Prompt: The SET NOCOUNT Problem (PE008 and PE009)

The best approach is to prevent rowcount messages from being sent unless they're required, but the tricky part is accommodating legacy apps that use these messages.

· Database Zone ·
Free Resource

New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

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

  • PE008SET 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 the 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 from 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 SETSTATISTICS trio of statements (SETSTATISTICSIOON, SETSTATISTICSTIMEON, and SET STATISTICSXMLON). 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 SETNOCOUNT 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 SETNOCOUNTON 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 SETNOCOUNT 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 SETNOCOUNTON 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 NOCOUNTOFF 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, 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 to prevent 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.

EXEC sys.sp_configure 'user options', '512'; -- 512 = NOCOUNT

Listing 1

Users can override the server-level default by issuing a SETNOCOUNT 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 SETNOCOUNTOFF 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 SETNOCOUNTOFF 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, 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 data, or to delete data, do not use SETNOCOUNTON in the stored procedure definition. This causes the rows affected count returned to be zero, and the DataAdaptor throws a DBConcurrencyException. In fact, sensible defensive programming will mean issuing an explicit SETNOCOUNTOFF in these cases.

The sqlclient.sqlcommand class can also suffer from problems with SETNOCOUNTON, 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, 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 SETNOCOUNTON 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.

New whitepaper: Database DevOps – 6 Tips for Achieving Continuous Delivery. Discover 6 tips for continuous delivery with Database DevOps in this new whitepaper from Redgate. In 9 pages, it covers version control for databases and configurations, branching and testing, automation, using NuGet packages, and advice for how to start a pioneering Database DevOps project. Also includes further research on the industry-wide state of Database DevOps, how application and database development compare, plus practical steps for bringing DevOps to your database. Read it now free.

Topics:
code analysis ,code smells ,sql prompt ,database ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}