Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Optimizing Stored Procedures To Avoid Recompiles

By Randy Dyess,

One of the most overlooked areas in optimizing SQL Server and Transact-SQL is the recompilations of stored procedures. Recently started looking at this issue where I worked and stored procedures gathered from other clients and I noticed that there is an issue with SQL developers not taking recompiles into account when they create new code. When a stored procedure recompiles it will place a compile lock on the objects referenced by the stored procedure and if there are enough stored procedure recompiles the database may experience blocking. While all databases will experience stored procedure recompiles as a normal matter of database operations, it is when a stored procedure recompiles with every run that a database administrator or Transact-SQL developer needs to look out for and determine a remedy. A database getting thousands of recompiles an hour will suffer in performance and show short term blocking that will affect the database users. Will the query optimizer in SQL Server 2000 seems to have been greatly enhanced in its ability to reuse cached procedure plans, a few of the following problems will still show up in SQL Server 2000. Reasons stored procedures recompile Stored procedures will normally recompile before execution for a number of reasons. Dropping and recreating the stored procedure, using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement, change the schema of any referenced objects, running the sp_recompile system stored procedure against a table referenced by the stored procedure, restoring the database containing the stored procedure or any object referenced by the stored procedure, or the stored procedures plan dropping from the cache. While these recompilations are normal and cannot be helped, DBAs and developers should not assume that all stored procedure recompiles are for normal reasons and should take a proactive approach to determine if they have a recompile problem. Using Profiler to capture recompiles With the use of SQL Profiler it is very easy for a DBA or developer to determine if a stored procedure abnormally recompiles before it is ever put into a test or production environment. It is also very easy for production DBAs to determine if they have a problem with stored procedures already in production. To determine if you have a problem with existing stored procedures or a specific stored procedure: 1. Start Profiler 2. Start a new trace 3. Connect to your server 4. On the General Tab, give the trace a name 5. On the Events Tab remove all default events and add SP:Recompile, SP:Starting, and SP:Completed under Stored Procedure events. If you want to determine the statement that causes the recompile also add SP:StmtStarting and SP:StmtCompleted to the selection. 6. You can leave the data columns as is or change them as you see fit. You can also leave the trace without filters, but stored procedures run by replication may tend to clutter your trace. If you tracing only one stored procedure, you can filter by the stored procedure name under the Text-Like filter. Example of a stored procedure which will recompile on every run (7.0 and 2000) USE pubs GO IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL DROP PROCEDURE dbo.spShowRecompile GO CREATE PROCEDURE dbo.spShowRecompile AS SET NOCOUNT ON DECLARE @lngCounter INTEGER SET @lngCounter = 1 --create temp table CREATE TABLE #tTemp (a INTEGER, b INTEGER) SELECT count(*) FROM #tTemp --add large amount of rows to table WHILE @lngCounter < 2000 BEGIN INSERT INTO #tTemp(a) VALUES(@lngCounter) SET @lngCounter = @lngCounter + 1 END --Create index on temp table CREATE CLUSTERED INDEX ind_temp ON #tTemp(a) SELECT count(*) FROM #tTemp GO EXEC dbo.spShowRecompile Let the trace run and look for stored procedures that recompile multiple times in a row or that recompile after they have started. To determine the statement causing the recompile look at the statement immediately before and after the recompile if you included SP:StmtStarting and SP:StmtCompleted in your trace. Now that you have determined which stored procedures are recompiling abnormally and which statements are causing the recompiles, we can look at ways to change the stored procedure code to stop the recompiles. Reducing COMPILE locks It is considered a good practice to reference all objects in stored procedure code with the owner's name. While this will not stop recompiles, it will stop SQL Server from placing a COMPILE lock on the procedure will it determines if all objects referenced in the code have the same owners as the objects in the current cached procedure plan. Example to show qualifying objects with their owners (7.0 and 2000) USE pubs GO IF OBJECT_ID('dbo.spShowOwnersName') IS NOT NULL DROP PROCEDURE dbo.spShowOwnersName GO CREATE PROCEDURE dbo.spShowOwnersName AS SELECT * FROM dbo.authors GO EXEC dbo.spShowOwnersName Recompiles due to row modifications Stored procedure will recompile is that a sufficient number of rows in a table referenced by the stored procedure has changed. SQL Server will recompile the stored procedure to be sure that the execution plan has the up-to-date statistics for the table. You will notice this problem quite often when you are working with temporary tables in SQL Server 7.0 as SQL Server will determine that after 6 modifications to a temporary table any stored procedure referencing that table will need to be recompiled. Example of row modifications causing recompile (7.0) IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL DROP PROCEDURE dbo.spShowRecompile GO CREATE PROCEDURE dbo.spShowRecompile AS SET NOCOUNT ON DECLARE @lngCounter INTEGER SET @lngCounter = 1 CREATE TABLE #Temp ( lngID INTEGER ) WHILE @lngCounter < 2000 BEGIN INSERT INTO #Temp VALUES(@lngCounter) SET @lngCounter = @lngCounter + 1 END SELECT COUNT(*) FROM #Temp GO. There are several ways to avoid this specific recompile. Luckily you have two paths to take: using sp_executesql and using the KEEPFIXED PLAN query hint that is found in SQL Server 7.0 SP3 and SQL Server 2000. You can find information in BOL on sp_executesql and in case you don't have SQL Server 7.0 SP3 or SQL Server 2000 here is what BOL says about KEEPFIXED PLAN: "KEEPFIXED PLAN Forces the query optimizer not to recompile a query due to changes in statistics or to the indexed column (update, delete, or insert). Specifying KEEPFIXED PLAN ensures that a query will be recompiled only if the schema of the underlying tables is changed or sp_recompile is executed against those tables." Books Online article on the OPTION clause. The following examples will show you the difference both sp_executesql and KEEPFIXED PLAN will have on avoiding stored procedure recompiles. Example of using sp_executesql to avoid recompiles from row modifications (7.0) IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL DROP PROCEDURE dbo.spShowRecompile GO CREATE PROCEDURE dbo.spShowRecompile AS SET NOCOUNT ON DECLARE @lngCounter INTEGER SET @lngCounter = 1 CREATE TABLE #Temp ( lngID INTEGER ) WHILE @lngCounter < 2000 BEGIN INSERT INTO #Temp VALUES(@lngCounter) SET @lngCounter = @lngCounter + 1 END EXEC dbo.sp_executesql N'SELECT COUNT(*) FROM #Temp' GO Example of using KEEPFIXED PLAN option to avoid recompiles (7.0 SP3) IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL DROP PROCEDURE dbo.spShowRecompile GO CREATE PROCEDURE dbo.spShowRecompile AS SET NOCOUNT ON DECLARE @lngCounter INTEGER SET @lngCounter = 1 CREATE TABLE #Temp ( lngID INTEGER ) WHILE @lngCounter < 2000 BEGIN INSERT INTO #Temp VALUES(@lngCounter) SET @lngCounter = @lngCounter + 1 END SELECT COUNT(*) FROM #Temp OPTION (KEEPFIXED PLAN) GO Recompiles due to interleaving DDL and DML operations Stored procedures will recompile is that the developer has place interleaving Data Definition Language operations with Data Manipulation Language operations. This is usually caused when temporary objects are created and referenced throughout the code. The reason this happens is that the temporary objects due not exist when the initial compilation of the code takes place so SQL Server will have to recompile the stored procedure during execution. This recompilation will take place after the temporary object is referenced for the first time. By placing all of your temporary table creation statements together, SQL Server can create plans for those temporary tables when one of them is referenced for the first time. This recompile will still take place during the execution of the stored procedure, but you have cut down of the recompiles from n to two (one for the stored procedure and one when the first reference to a temporary table is made). SQL Server will also be able to reuse the execution plan for the stored procedure the next time the procedure is called and your recompiles will go to zero. Remember that like permanent objects, if you change the schema of a temporary table, that change will cause the stored procedure to recompile as well. Make all schema changes (such as index creation) right after your create table statements and before you reference any of the temporary tables. If you take the stored procedure created during the section on using Profiler and modify it as written below you will stop the unnecessary recompiles. Modifications to stop the recompile (7.0 and 2000) USE pubs GO IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL DROP PROCEDURE dbo.spShowRecompile GO CREATE PROCEDURE dbo.spShowRecompile AS SET NOCOUNT ON DECLARE @lngCounter INTEGER SET @lngCounter = 1 --create temp table CREATE TABLE #tTemp (a INTEGER, b INTEGER) --Create index on temp table CREATE CLUSTERED INDEX ind_temp ON #tTemp(a) SELECT count(*) FROM #tTemp --add large amount of rows to table WHILE @lngCounter < 2000 BEGIN INSERT INTO #tTemp(a) VALUES(@lngCounter) SET @lngCounter = @lngCounter + 1 END SELECT count(*) FROM #tTemp GO EXEC dbo.spShowRecompile Recompiles due to operations against temporary objects SQL Server will recompile a stored procedure every time it is ran if any of the following conditions apply in that stored procedure: If statements that contain the name of a temporary table refer to a table created by a calling or called stored procedure or in a string execute by using sp_executesql or the EXECUTE statement. If any statement that contains the name of the temporary table appear syntactically before the temporary table is created in the stored procedure or trigger. If there are any DECLARE CURSOR statements whose SELECT statement references a temporary table. If any statements that contain the name of a temporary table appear syntactically after a DROP TABLE against the temporary table (you might read that DROP TABLES for temporary tables are not needed since they are dropped at the conclusion of the stored procedure execute, but it is a good idea to drop temporary tables as you are done with them to free up system resources). Or if any statement that creates a temporary table appear in a control-of-flow statement. By avoiding these conditions when you create your code you can avoid needless stored procedure recompiles. The following SET options are ON by default in SQL Server and changing the state of these options in your stored procedure will cause the stored procedure to recompile: SET ANSI_DEFAULTS, SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS, and SET CONCAT_NULL_YIELDS_NULL. While there are not good workarounds for the first four SET options, you can work around the last one: SET CONCAT_NULL_YIELDS_NULL by using the ISNULL function found in Transact-SQL. By simply using the ISNULL function and setting any data that might contain a NULL to an empty string you can avoid the setting of CONCAT_NULL_YIELDS_NULL in your stored procedure and avoid another unnecessary stored procedure recompilation. Example of SET CONCAT_NULL_YIELDS_NULL causing recompile (7.0 and 2000) USE pubs GO IF OBJECT_ID('dbo.spShowRecompile') IS NOT NULL DROP PROCEDURE dbo.spShowRecompile GO CREATE PROCEDURE dbo.spShowRecompile AS SET NOCOUNT ON SET CONCAT_NULL_YIELDS_NULL OFF SELECT 'Will not showup' + NULL GO EXEC dbo.spShowRecompile Summary While most of these problems occur on a larger scale in SQL Server 7.0, they can still be a problem in SQL Server 2000. By checking for stored procedure recompiles during the development of new Transact-SQL code and running a health-check on your existing stored procedures for recompilations, you can help to optimize your database performance by lowering the risk of blocking while a stored procedure is being recompiled and by improving the overall performance of your stored procedures by not having them constantly being recompiled. Keeping the above recommendations in mind as you create your code or optimize existing code will go a long way on creating that trouble-free, lightening-fast database that all of us would love to manage. Knowledge Based Articles Q243586 INF: Troubleshooting Stored Procedure Recompilation Q276220 INF: How to Use KEEPFIXED PLAN to Disable Stored Procedure Recompilations Q294942 PRB: SET CONCAT_NULL_YIELDS_NULL May Cause Stored Procedures to Recompile Q250506 FIX: Concurrent Execution of Stored Procedure That Recompiles May Fail To Execute All Statements Q263889 INF: SQL Blocking Due to [[COMPILE]] Locks Copyright 2002 by Randy Dyess, All rights Reserved

Total article views: 20899 | Views in the last 30 days: 24
 
Related Articles
ARTICLE

Temporary Stored Procedures

Temporary Stored Procedures - little known sql server feature

FORUM

Stored procedure recompile is very slow

A stored procedure is taking 3 - 4 minutes to recompile

FORUM

Stored Procedure Hangs but Works Fine after Recompile

Stored Procedure Hangs but Works Fine after Recompile

FORUM

SSMS 2008 Stored Proc WITH RECOMPILE

SSMS 2008 Stored Proc WITH RECOMPILE

BLOG

Stale statistics on a newly created temporary table in a stored procedure can lead to poor performance

When you create a temporary table you expect a new table with no past history (statistics based on p...

Tags
performance tuning    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones