SET Options and Recompilation

In "Controlling Query Behavior," January 2000, I wrote about the idiosyncrasies in the way you use various SET options to control the behavior of your queries when you connect to SQL Server 7.0. (Most of that information is equally relevant to SQL Server 2000, which Microsoft hadn't yet released.) One thing about SET options that I didn't mention was the relationship between SET options and stored procedure recompilation. As you probably know, SQL Server caches the execution plans for most of your stored procedures so that it doesn't need to reoptimize and recompile the plans on subsequent executions. In general, reusing a query plan is good because optimization can be an expensive process when you use complex stored procedures. However, in some cases, a recompilation is necessary. One of these cases is when a session setting has changed so that a stored procedure might produce different results. For example, let's say you have a procedure that returns the locations of all the publishers in the Pubs database. In the result, you want a single string composed of the city, state, and country of each publisher, so you write the following stored procedure to return the desired data:

USE pubsGOCREATE PROCEDURE GetPublisherLocationsASSELECT LOCATION = city + ', ' +  state +  ', ' + countryFROM publishers

The entries for all publishers located outside the United States have a NULL in the state column. By default, when SQL Server concatenates a NULL to a character string, it treats the NULL as an empty string. However, starting with SQL Server 7.0, a query option called CONCAT_NULL_YIELDS_NULL is set to ON for each connection that uses ODBC or OLE DB, so any expression involving a concatenation with NULL results in NULL. You can set this option to OFF in your session if you prefer the old behavior in which SQL Server treats NULL as an empty string for concatenation operations. Keep in mind that the GetPublisherLocations stored procedure (and other stored procedures that concatenate strings that may be NULL) will behave differently and return different results depending on this SET option's value. The following two scripts illustrate this difference in behavior. The first EXEC statement treats the NULLs as empty strings and returns the results that Figure 1 shows:

SET concat_null_yields_null OFFEXEC GetPublisherLocations

If you want, you can tell the procedure not to print the second comma if no value exists for state, but I'll leave that enhancement as an exercise for the reader.

When SQL Server treats NULLs as unknown values, the result of an expression involving a NULL is always unknown. The next EXEC statement returns the results that Figure 2 shows:

SET concat_null_yields_null ONEXEC GetPublisherLocations

A session executing this procedure with CONCAT_NULL_YIELDS_NULL set to ON can't use the same plan that SQL Server produced for a session running with CONCAT_NULL_YIELDS_NULL set to OFF. In a field called setopts, the syscacheobjects system table keeps track of the session settings. This field is a bitmap in which each bit corresponds to one SET option that can affect a query's results. By comparing the value of setopts for a saved plan with the current session settings, the Query Optimizer can decide whether recompilation based on a change of SET options is necessary.

Detecting Recompilations

So how can you tell when a procedure has been recompiled? One way is to use SQL Server Profiler to capture the event SP:Recompile, which occurs whenever a stored procedure recompiles during execution. With the Profiler, you can set up a simple trace that captures four events in the Stored Procedures category. In addition to capturing SP:Recompile events, remove all events from the trace except SP:Starting, SP:StmtStarting, and SP:Completed. As an alternative to the SP:StmtStarting event, you could capture the SP:StmtCompleted event, but including both is unnecessary and could produce too much information for you to process. Including either the SP:StmtStarting or SP:StmtCompleted event lets you keep track of which procedure is executing when a SP:Recompile event occurs.

For data columns, you really need only the Event column and the event's Text column event. For an SP:Starting, SP:Completed, or SP:Recompile event, the Text column specifies the name of the stored procedure that started, completed, or was recompiled.

I use a trace with the four events I described here to illustrate a cause for concern besides the fact that changing certain SET options before executing a procedure can cause a procedure to be recompiled. If you include SET statements inside your stored procedure definition, you could cause SQL Server to recompile your procedure multiple times while the procedure is executing. The code in Listing 1 modifies the GetPublisherLocations procedure to perform two SELECT statements and changes the CONCAT_NULL_YIELDS_NULL option value for each statement. When you execute this procedure and use SQL Server 7.0 Profiler to examine the contents of the trace, you'll see the captured events that Table 1 shows. The results look slightly different in SQL Server 2000, but the sequence of statements and recompiles are the same.

Note that Table 1 lists the statements twice—once before and once after the SP:Recompile event. This shows that SQL Server started to compile the statement, then determined that it had to recompile. After the recompilation, SQL Server executes the statement. Although it might seem that SQL Server is recompiling only the single statement, it is actually recompiling the entire procedure. For a large and complex procedure, the extra time needed for recompilations can become quite noticeable. Also note that the first recompile event occurred because the first SET statement changed an option to something different from what was in effect for the session. If you changed the session setting to SET CONCAT_NULL_YIELDS_NULL OFF before running the procedure, the first recompile wouldn't occur. Another interesting behavior is that if you have multiple SET statements in your procedure, you can have multiple recompiles, even if you list all the SET statements at the beginning of the procedure. Every time SQL Server encounters a SET statement that changes the value of CONCAT_NULL_YIELDS_NULL, it will recompile the procedure.

Many SET options besides CONCAT_NULL_YIELDS_NULL cause similar recompilation behavior, but two classes of exceptions exist. First, there are two options for which SQL Server saves the value that is in effect at the time the procedure is created along with the stored procedure definition. SQL Server stores the settings of ANSI_NULLS and QUOTED_IDENTIFIER in the procedure's row in the status column of the sysobjects table. If the 0x8 bit in status is set, ANSI_NULLS is in effect for the procedure. If the 0x10 bit is set, QUOTED_IDENTIFIER is in effect for the procedure. Issuing a SET command to change either of these values within a procedure has no effect and causes no recompilation. The only way to change the value of these SET options for a procedure is to drop and recreate the procedure with a different SET option in effect.

The second class of SET options that don't cause recompilation consist of options that don't really change the query's behavior. That is, SQL Server returns the same rows no matter what you set the option to. One example is SET NOCOUNT. When you set this option to ON, it inhibits the display of the message that tells how many rows the statement affected. This setting affects only the client tool's display; the setting has no impact on how SQL Server actually processes your query. Setting this option ON or OFF doesn't cause recompilations.

Recommendations

Because SET options can change the way your queries behave and the data they return, you should carefully consider which option values to use. If each developer can choose the option settings for each execution of a procedure, your applications' behavior might be totally unpredictable. And certain tools and drivers set some of the options ON or OFF, making matters even more complex. I recommend that you choose one set of option values to use as a company standard, or at least as a standard for each SQL Server system. You can use the configuration option 'user options' to set certain options ON for every connection, no matter what interface the connection uses to communicate with SQL Server.

Also, to use SQL Server 2000's new indexed views or indexes on computed columns features, you must adhere to a certain set of option values across all connections. To take full advantage of SQL Server 2000's functionality, even if you haven't upgraded yet, you might consider modifying your code to work with these options set the way the new features expect them to be set. I discussed the required options for indexed views in "Introducing Indexed Views," May 2000, but here they are again. You must set the following options ON:

ARITHABORTCONCAT_NULL_YIELDS_NULLQUOTED_IDENTIFIERANSI_NULLSANSI_PADDINGANSI_WARNINGS

In addition, this SET option must be OFF:

NUMERIC_ROUNDABORT

Although changing your code to expect certain SET options might seem like a lot of work, I believe the ease of maintenance, the lack of worry about stored procedure recompilations, and the ability to use SQL Server 2000's new high-performance features will make this change worthwhile.