Finding Code Smells Using SQL Prompt: The SET NOCOUNT Problem (PE008 and PE009)
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.
Join the DZone community and get the full member experience.Join For Free
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:
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.
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
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
SETSTATISTICS trio of statements (
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.
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
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
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.
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
SQLRowCount returns 0, even though there were results. If
SQLRowCount returns 0, the application should find out if
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
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.
Published at DZone with permission of Phil Factor . See the original article here.
Opinions expressed by DZone contributors are their own.