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:
- Start Profiler.
- Start a new trace.
- Connect to your server.
- On the General Tab, specify the trace a name.
- 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.
- 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