The Meaning of Stored Procedures. Part 2 — Avoiding recompilations due to plan stability-related reasons.

In the previous article I tried to convince you that stored procedures are a proper way to avoid unnecessary compilations and recompilations. They may not be the fastest (in most cases prepared queries will be faster), but they are widely used, mainly because they are great from security and manageability perspectives. Unfortunately, stored procedures are also commonly misunderstood. If you did not read the first part of this series, please take some time — it can be found here: The Meaning of Stored Procedures.Part 1 — Plan Caching and Reuse is a Good Thing.

In this post we are going to investigate recompilations, namely what can be done to avoid excessive recompilations due to plan stability-related reasons.

Quick summary of what we already discussed

When a stored procedure is created, SQL Server verifies that it is syntactically correct and resolves all references to the base objects. Please note, that due to “deferred name resolution” misfeature the CREATE PROC statement will succeed, even if the referenced objects do not exist at this time. The important part is that SQL Server will not compile an execution plan for stored procedure until it is first called. The bottom line is, the plan will be optimized and compiled based on parameters supplied during the first execution, and future executions will reuse it — we will go through this in one of the upcoming articles.

As you can imagine, plans stored in the procedure cache not always can be used, for correction and optimality reasons. For example, an execution plan becomes invalidated when schema changed or when some SET option changed.

Recompilation reasons

To see all recompilation reasons, please execute the following query:

Selec All Code:
1
2
3
4
5
6
7
SELECT v.subclass_name, v.subclass_value
FROM sys.trace_events AS e 
JOIN sys.trace_subclass_values AS v
	ON e.trace_event_id = v.trace_event_id
WHERE e.name = 'SP:Recompile'
AND v.subclass_value < 1000
ORDER BY v.subclass_value;

image

First of all, a stored procedure (or a batch) must be recompiled if not doing so would result in incorrect results or actions. Stability-related reasons fall into two sub-categories.

1. Scheme changes. A stored procedure may reference objects (like tables, views, indexes, statistics, user-defined functions, and so on), and if scheme of some of the objects has changed since the stored procedure was last compiled, it needs to be recompiled.

2. SET options changes. Some of the SET options affect query results. If one of those plan-reuse-affecting SET options has changed inside a stored procedure, a recompilation takes place.

In this post we will examine the changed schema and changed SET option recompilation reasons, and deferred compile concept.

Recompilation due to schema changes

Let me start with busting a very common misconception, that putting all DML statements at the beginning of a stored procedure will minimize recompilations. To verify this myth, create (in AdventureWorks2008R2 database) this stored procedure:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE uspSchemaChangeRecompilationsv1 AS 
CREATE TABLE tab1 (kol int);             
SELECT * FROM tab1;                   
CREATE TABLE tab2 (kol int);            
SELECT * FROM tab2;
CREATE INDEX tab1idx1 ON tab1(kol);   
SELECT * FROM tab1;
DROP TABLE tab1;
DROP TABLE tab2;                          
GO

Then start Profiler and capture a SP:Recompile event. You can do this by:

1. Starting a Profiler from SSMS Tools menu.

2. Connecting to a SQL Server instance.

3. Choosing a Blank template.

4. Selecting a SP:Recompile event (one of 12 events from Stored Procedures category).

5. Starting a trace.

In addition, I selected only some from column available for this event and rearranged them slightly. During the first execution of this stored procedure, the following event will be captured:

Selec All Code:
1
EXEC uspSchemaChangeRecompilationsv1;

image

Since during creation the stored procedure, tab1, tab2 and tab1idx1 didn’t exist, subsequent SELECT statements could not be compiled at that time. That is why SQL Server has to compile those statements during the first execution of the stored procedure. And because those compilations took place when the stored procedure was already being executed, they count as deferred compilations. Please note, that the reason for those recompilations can be found in EventSubClass column.

Next, clear the trace window and run this stored procedure two more times:

Selec All Code:
1
2
EXEC uspSchemaChangeRecompilationsv1; 
GO 2

image

As you can see, each execution caused three recompilations, all because of scheme changes.

Lesson learned — SQL Server 2005 onwards does statement level recompilations, which means that an individual statement, not the whole stored procedure, is being recompiled if needed.

Now, alter this stored procedure in the following way:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
ALTER PROCEDURE uspSchemaChangeRecompilationsv1 AS 
CREATE TABLE tab1 (kol int);  
CREATE INDEX tab1idx1 ON tab1(kol);              
CREATE TABLE tab2 (kol int);  
SELECT * FROM tab1;                   
SELECT * FROM tab2; 
SELECT * FROM tab1;
DROP TABLE tab1; 
DROP TABLE tab2;                           
GO

Then clear the trace window and run this procedure three times:

Selec All Code:
1
2
EXEC uspSchemaChangeRecompilationsv1;
GO 3

image

Rearranging DDL and DML statements changed exactly nothing.

Lesson learned — due to statement level recompilations, in SQL Server 2005 onwards keeping DDL statement at the beginning of stored procedures does absolutely nothing.

The only solution is to use temporary tables instead of permanent ones. But there still is a trick:

Selec All Code:
1
2
3
4
5
6
7
8
9
CREATE PROCEDURE uspSchemaChangeRecompilationsv2 AS 
CREATE TABLE #tab1 (kol int);             
SELECT * FROM #tab1;                   
CREATE TABLE #tab2 (kol int);            
SELECT * FROM #tab2;
ALTER TABLE #tab1
ADD CONSTRAINT UQ_#tab1 UNIQUE (kol);   
SELECT * FROM #tab1;                                 
GO

After the second version of our stored procedure was created, execute it three times and check, how many recompilations occurred:

Selec All Code:
1
2
EXEC uspSchemaChangeRecompilationsv2; 
GO 3

image

During the first execution four deferred recompilations occurred — the extra one was triggered by this new ALTER TABLE statement. But subsequent executions triggered only one recompilation, again only due to this ALTER TABLE statement. Seems like SQL Server caches temporary objects (so they don’t have to be created for each execution), but altering definitions of those temporary objects disables this caching mechanism. To confirm our suspicions, alter the second version of our stored procedure by replacing ALTER TABLE with CREATE UNIQUE INDEX statement:

Selec All Code:
1
2
3
4
5
6
7
8
ALTER PROCEDURE uspSchemaChangeRecompilationsv2 AS 
CREATE TABLE #tab1 (kol int);
CREATE UNIQUE INDEX tab1idx1 ON #tab1(kol);   
CREATE TABLE #tab2 (kol int); 
SELECT * FROM #tab1;                   
SELECT * FROM #tab2; 
SELECT * FROM #tab1;                                 
GO

Then clear the trace window and execute the new version of this stored procedure three times:

Selec All Code:
1
2
EXEC uspSchemaChangeRecompilationsv2;
GO 3

image

And that is how we achieved our goal — no more recompilations due to creating and altering base tables!

Lesson learned —As a result of TempDB optimization, SQL Server 2005 onwards caches temporary objects, but only under some restrictions. One of them is that cached objects cannot have named constraints. A direct effect of this optimization technique (which is aimed at infamous allocation contention problem) is that using temporary tables inside stored procedures helps avoiding unnecessary recompilations.

In the last example we will see if the same solution works when a temporary table is created inside one stored procedure, and used inside another one. To do this, create the following stored procedures:

Selec All Code:
1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE dbo.uspSchemaChangeRecompilationsv3 AS 
CREATE TABLE dbo.#tab1 (kol int);
CREATE UNIQUE INDEX tab1idx1 ON dbo.#tab1(kol);
EXEC dbo.uspInsideProc;
EXEC dbo.uspInsideProc;
GO
 
CREATE PROCEDURE dbo.uspInsideProc AS
SELECT * FROM dbo.#tab1;
GO

And after cleaning the trace window, execute the outer one three times:

Selec All Code:
1
2
EXEC dbo.uspSchemaChangeRecompilationsv3; 
GO 3

image

As we can see, when a temporary table is being accessed from the inner stored procedure for the first time, a recompilation due to the changed scheme occurs. The reason for this is that accessing objects created outside the current scope requires object id to name resolution.

Lesson learned — even using two part names (which is a best practice nevertheless) does not prevent recompilation when a temporary object is accessed from inner stored procedures.

Recompilation due to SET option changes

When an execution plan is created, the SQL Server stores the environmental setting with it. This means, that some SET options used when the stored procedure was created or altered will be saved as part of its metadata. In addition, some execution plan attributes are considered cache key ones — if those attributes do not match, SQL Server will consider the execution plan invalid.

The following query illustrates this concept:

Selec All Code:
1
2
3
4
5
6
7
8
SELECT objtype,attribute,value
FROM sys.dm_exec_cached_plans qp
CROSS APPLY sys.dm_exec_plan_attributes(plan_handle) ga
CROSS APPLY sys.dm_exec_sql_text(plan_handle) as qt
WHERE qt.text LIKE '%uspSchemaChangeRecompilationsv1%'
AND qt.text NOT LIKE '%dm_exec_sql_text%'
AND objtype='Proc'
AND is_cache_key=1;

image

Right now, we are interested in the first plan attribute — the set_options one. It is a bitmap of various plan-reuse-affecting connection options. The complete list of those option looks goes as follows:

  • ANSI_NULL_DFLT_ON
  • ANSI_NULL_DFLT_OFF
  • ANSI_NULLS
  • ANSI_PADDING
  • ANSI_WARNINGS
  • ARITHABORT
  • CONCAT_NULL_YIELDS_NULL
  • DATEFIRST
  • DATEFORMAT
  • FORCEPLAN
  • LANGUAGE
  • NO_BROWSETABLE
  • NUMERIC_ROUNDABORT
  • QUOTED_IDENTIFIER

Unfortunately, different tools may have different SET options in their default configuration. The most common cause of lack of plan reuse are different ARITHABORT and QUOTED_IDENTIFIER settings. The former is set to OFF by applications using OLEDB, ODBC or ADO.NET and by SQLCMD tool, but is set to ON by SSMS. The latter one is set to ON by most tools and interfaces, except SQLCMD. As a result, executing exactly the same queries (i.e. the same stored procedures, even with identical parameters) inside SSMS and from an application will produce distinct execution plans.

Checking active SET option for a user connection is easy — all you have to do, is execute DBCC USEROPTIONS statement:

Selec All Code:
1
DBCC USEROPTIONS;

image

To get information about option set for a particular stored procedure, you can use the following, written by Erland Sommarskog, query:

Selec All Code:
1
2
3
4
5
6
7
8
9
SELECT distinct qs.plan_handle, a.attrlist
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS  APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + '   '
              FROM   sys.dm_exec_plan_attributes(qs.plan_handle) epa
              WHERE  epa.is_cache_key = 1
              ORDER  BY epa.attribute
              FOR    XML PATH('')) AS a(attrlist)
WHERE  est.objectid = object_id ('uspSchemaChangeRecompilationsv1');

You can also display actual execution plan, pick the leftmost operator (the SELECT one), click F4 and expand Set Options in the Properties window:

image

Finally, we are ready to see why changing some SET option will cause a recompilation. To do this, please create the following stored procedure:

Selec All Code:
1
2
3
4
5
CREATE PROCEDURE uspSetChangeRecompliation AS 
    SET DATEFORMAT ydm;
	DECLARE @dt datetime = '2012-02-12';
    SELECT MONTH(@dt);
GO

Then, select month number directly and by calling this stored procedure (remember to clean the trace window before executing those statements):

Selec All Code:
1
2
3
4
5
DECLARE @dt datetime = '2012-02-12';
SELECT MONTH(@DT);
GO 
 
EXEC uspSetChangeRecompliation;

image

During the first execution this stored procedure was recompiled twice. There is a hint — note different results (the latter one is a result of stored procedure):

image

Interestingly, subsequent execution of our stored procedure will not trigger recompilations. That is because during the first execution it was recompiled with the DATEFORMAT set to ydm, and from now on its execution plan is now valid.

Lesson learned —setting plan reuse affecting SET option inside a stored procedure is a lame programming practice, but on SQL Server 2005 onwards it has a much less impact than before, when a stored procedure would have been recompiling each time it had been executed.

Cheers

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



One thought on “The Meaning of Stored Procedures. Part 2 — Avoiding recompilations due to plan stability-related reasons.

  1. Pingback: v6tfmijbtfvxerxse