What they don’t tell you about Microsoft SQL Server

 

Part 2: Stored Procedure Recompiles and Temporary Tables in Microsoft SQL Server 2000

 

Howard Long

12 September 2001

Updated 17 February 2003

 

Copyright © Howard Long 2001,2003

 

Introduction

 

With the introduction of Microsoft SQL Server 7 and SQL Server 2000, there has been an increased incidence of stored procedure recompiles over previous versions, in particular those stored procedures which use temporary tables. This article explains why and presents some workarounds.

 

Many reasons for stored procedure recompiles have already covered by Microsoft documentation, and these are referenced in this document, although the material presented here was not available elsewhere at the time of writing.

 

There are four basic issues with SQL Server 2000 and stored procedure recompilation:

 

·                     Because of the significant increase in the complexity of the optimizer, stored procedure recompilation takes more CPU. In tests, generally recompilation in SQL Server 2000 takes ten times more CPU the SQL Server 6.5.

·                     Stored procedures are much more likely to be recompiled than in SQL Server 6.5 and earlier.

·                     Serialization lockout is a big problem because under SQL Server 2000 there is only one compiled plan for all sessions. If a recompile happens, all sessions attempting to use the stored procedure in question will have to wait in the event of a recompile.

·                     If a recompile occurs within a transaction, all the transactions’ locks remain in place while the stored procedure recompiles, leading to potentially further serialization.

 

How the above issues show themselves in real life is rather subtle.

 

Identifying Recompiles

 

The Profiler SP:Recompile event identifies recompiles. The EventSubClass data column indicates why the recompile occurred, but it is only populated by recompiles on SQL Server 2000 SP2 and later. See (1) for documentation on decoding the EventSubClass figures. The ObjectName column displays the stored procedure being recompiled.

 

Once the recompiling stored procedures have been identified, it’s instructive to identify what statement is causing the recompile. For this, switch on SP:StmtStarting and SP:StmtCompleted.

 


 

 


Problem #1

 

If a temporary table is dropped and then recreated between calls to a stored procedure which references the temporary table, this will cause a recompile next time the stored procedure references the table. It is recommended that each session keeps its temporary tables created permanently throughout the life of the session, using TRUNCATE instead of dropping and re-creating. But… see #2!

 

Problem #2

 

There is a very low threshold for the number of changes to temporary tables (and to a lesser extent permanent tables) in SQL Server 2000. Typically only six changes to a temporary table will cause a recompile. This can make life very expensive! On an individual query basis it is possible to increase the recompile threshold to permanent table limits (~500 changes) using OPTION (KEEP PLAN) or remove the threshold altogether with OPTION (KEEPFIXED PLAN).

 

Another trick is to use the undocumented trace flag –T8720 (CAPITAL ‘T’!) in the SQL Server startup parameters. This removes the thresholds in the same way as applying OPTION (KEEPFIXED PLAN) to all queries.

 

Problem #3

 

Since Microsoft SQL Server 7, the developers at Redmond have replaced the original Sybase design where each session had its own stored procedure query plan. Now there is only one compile plan for all sessions. Although at first take this seems like a sensible option to take, the side effects are far reaching when referencing temporary tables.

 

Consider two sessions running the same stored procedure, each using the same compilation plan, but each accessing their own temporary table. Each time the sessions switch, when they refer to their own temporary table SQL Server sees a schema binding change and you have a recompile situation.

 

Now imagine it takes 1s to compile your stored procedure and you have 1000 sessions trying to run the same stored procedure. Because there’s only one compile plan, everyone has to wait for the recompile to happen. You will have enormous amounts of CPU being used to recompile the same stored procedure and you entire server will appear to grind to an almighty halt.

 

There is a way to resolve this although it is a bit of a hack. If each session could have its own plan, we could stop recompiles. This can be done using the concept of temporary stored procedures.

 

First, it’s necessary to show the problem itself.

 

Suppose we have the following stored procedure:

 

Create Procedure StoredProc01 as

  Declare @x int

  Select @x=x From #Table01

 

If we now run the following on a single Query Analyzer session, with Profiler watching SP:Recompile events:

 

Create Table #Table01(x int)

Go

Set nocount on

Declare @dt datetime

Select @dt=dateadd(ss,10,getdate())

While getdate()<@dt

Begin

  Exec StoredProc01

End

go

Drop Table #Table01

 

Apart from the first time, each time you run the above query a single recompile event will occur which is because the #Table01 temporary table was dropped and recreated (see Problem #1 above).

 

Now try this. Open up another Query Analyzer window so that you can run two of the same query above simultaneously. The Profiler will be full of SP:Recompile events.

 

As an alternative, run the following simultaneously in two separate Query Analyzer windows:

 

Create Procedure #StoredProc01 as

  Declare @x int

  Select @x=x From #Table01

go

Create Table #Table01(x int)

Go

Set nocount on

Declare @dt datetime

Select @dt=dateadd(ss,10,getdate())

While getdate()<@dt

Begin

  Exec #StoredProc01

End

go

Drop Table #Table01

go

Drop Procedure #StoredProc01

go

 

This is the same except that temporary stored procedures are being used which have a session specific compilation plan. A binding change is therefore not seen, and the recompilation doesn’t happen.

 

So how easy is it to make a permanent stored procedure into a temporary stored procedure? The difficulty here is that, like temporary tables, temporary stored procedures only last as long as the SQL Server connection remains open.

 

Here’s one hack which can be used to simplify the process.

 

·                     Rename your existing stored procedures to a slightly different name, for example: StoredProc01_Org.

·                     Create a stored procedure wrapper with the stored procedure’s name which takes all of the original stored procedure and compiles it as a temporary stored procedure if it is not already created, passing any parameters.

 

For example:

 

--The original stored procedure is renamed

Create Procedure StoredProc01_Org as

  Declare @x int

  Select @x=x From #Table01

Go

 

-- This is a wrapper

Create Procedure StoredProc01 as

  Exec MakeTempSP 'StoredProc01' –- create temporary sp if necessary

  Exec #StoredProc01 -- execute temporary sp

go

 

For the source of MakeTempSP see Appendix A.

 

Now re-run two concurrent sessions of:

 

Create Table #Table01(x int)

Go

Set nocount on

Declare @dt datetime

Select @dt=dateadd(ss,10,getdate())

While getdate()<@dt

Begin

  Exec StoredProc01

End

go

Drop Table #Table01

 

And there should not be the plethora of recompiles that there were before.

 

Too little too late!

 

The introduction of Table Variables might have been just the solution we were all looking for – except that you cannot pass Table Variables as parameters to Stored Procedures, so they only have local scope. The Microsoft documentation quietly ignores this major limitation!

 

Appendix A

 

The MakeTempSP stored procedure:

 

create procedure MakeTempSP

  @Name varchar(200)

as

declare

  @ID int,

  @NameOrg varchar(255),     

  @NameTemp varchar(255),

  @Line1 varchar(8000),    

  @Line2 varchar(8000),

  @Line3 varchar(8000),    

  @Line4 varchar(8000),

  @Line5 varchar(8000),    

  @Line6 varchar(8000),

  @Line7 varchar(8000),

  @Line8 varchar(8000),

  @Line9 varchar(8000),

  @Line10 varchar(8000),

  @Line11 varchar(8000),

  @Line12 varchar(8000),

  @Line13 varchar(8000),

  @Line14 varchar(8000),

  @Line15 varchar(8000),

  @Line16 varchar(8000),

  @Line17 varchar(8000),    

  @Line18 varchar(8000),

  @Line19 varchar(8000),    

  @Line20 varchar(8000)

 

/* Note that Temp name must the same length or shorter */

/* than base name! */

select

  @NameOrg=@Name+'_Org',

  @NameTemp='#'+@Name

 

if object_id('tempdb..'+@NameTemp) is not null

begin

  /* Temp Stored Proc is already there */

  return 1

end

 

select @ID=object_id(@NameOrg)

 

if @ID is null

begin

  /* Error! base stored procedure doesn't exist! */

  return 0

end

 

select

  @Line1='',

  @Line2='',

  @Line3='',

  @Line4='',

  @Line5='',

  @Line6='',

  @Line7='',

  @Line8='',

  @Line9='', 

  @Line10='',

  @Line11='',

  @Line12='',

  @Line13='',

  @Line14='',

  @Line15='',

  @Line16='',

  @Line17='',

  @Line18='',

  @Line19='',

  @Line20=''

 

select @Line1=text

from syscomments

where id=@ID

  and number=1

  and colid=1

 

select @Line2=text

from syscomments

where id=@ID

  and number=1

  and colid=2

 

select @Line3=text

from syscomments

where id=@ID

  and number=1

  and colid=3

 

select @Line4=text

from syscomments

where id=@ID

  and number=1

  and colid=4

 

select @Line5=text

from syscomments

where id=@ID

  and number=1

  and colid=5

 

select @Line6=text

from syscomments

where id=@ID

  and number=1

  and colid=6

 

select @Line7=text

from syscomments

where id=@ID

  and number=1

  and colid=7

 

select @Line8=text

from syscomments

where id=@ID

  and number=1

  and colid=8

 

select @Line9=text

from syscomments

where id=@ID

  and number=1

  and colid=9

 

select @Line10=text

from syscomments

where id=@ID

  and number=1

  and colid=10

 

select @Line11=text

from syscomments

where id=@ID

  and number=1

  and colid=11

 

select @Line12=text

from syscomments

where id=@ID

  and number=1

  and colid=12

 

select @Line13=text

from syscomments

where id=@ID

  and number=1

  and colid=13

 

select @Line14=text

from syscomments

where id=@ID

  and number=1

  and colid=14

 

select @Line15=text

from syscomments

where id=@ID

  and number=1

  and colid=15

 

select @Line16=text

from syscomments

where id=@ID

  and number=1

  and colid=16

 

select @Line17=text

from syscomments

where id=@ID

  and number=1

  and colid=17

 

select @Line18=text

from syscomments

where id=@ID

  and number=1

  and colid=18

 

select @Line19=text

from syscomments

where id=@ID

  and number=1

  and colid=19

 

select @Line20=text

from syscomments

where id=@ID

  and number=1

  and colid=20

 

/* Replace first occurrence of Base Stored Proc name with Temp Stored Proc name */

Select

  @Line1=

    substring(@Line1,1,charindex(@NameOrg,@Line1)-1)+

    @NameTemp+

    substring(@Line1,charindex(@NameOrg,@Line1)+datalength(@NameOrg),8000)

 

exec

(

  @Line1+

  @Line2+

  @Line3+

  @Line4+

  @Line5+

  @Line6+

  @Line7+

  @Line8+

  @Line9+

  @Line10+

  @Line11+

  @Line12+

  @Line13+

  @Line14+

  @Line15+

  @Line16+

  @Line17+

  @Line18+

  @Line19+

  @Line20

)

 

 

References

 

1.      Query Recompilation in SQL Server 2000: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp

 

2.      INF: Troubleshooting Stored Procedure Recompilation: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243586

 

3.      INF: How to Identify the Cause of Recompilation in a SP:Recompile Event: http://support.microsoft.com/default.aspx?scid=kb;EN-US;308737