articles >> performance tuning >> Optimizing SQL Server Stored Procedures to Avoid ...

Optimizing SQL Server Stored Procedures to Avoid Recompiles

By : Randy Dyess Oct 14, 2003

One of the most overlooked areas in optimizing SQL Server and Transact-SQL is the recompilations of stored procedures. I recently started looking at this issue where I worked, and I noticed that there is an issue with SQL developers not taking recompiles into account when they create new code. Because of this, Transact-SQL performance is not as optimized as it could be.

When a stored procedure recompiles, it places 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 negatively affect database users. While 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, including: dropping and recreating the stored procedure, using the WITH RECOMPILE clause in the CREATE PROCEDURE or the EXECUTE statement, changing 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, specify 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 are 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 while 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