Identifying Stored Procedure Recompilation Problems in SQL Server 2000

There are two tools available from Microsoft to monitor recompiles: Windows Performance Monitor and SQL Server Profiler. Windows Performance Monitor, or PerfMon, monitors compiles and recompiles in the aggregate. It is a good tool to use when you are determining the overall frequency of recompiles.

SQL Server Profiler gives you a detailed picture of the stored procedures that are executing and shows you exactly where recompiles are occurring. However, it can produce voluminous output and must be used carefully.

Using PerfMon to Gather Aggregate Recompile Statistics

The Perfmon video

When SQL Server is installed, it adds its own performance counters to PerfMon. If you install multiple instances of SQL Server on the same computer, each instance has its own counters. The most interesting counters to watch when monitoring recompiles are in the Performance object named "SQL Server:SQL Statistics". The counters to monitor are "SQL Compilations/sec" and "SQL Re-Compilations/sec." Figure 1 shows PerfMon's Add Counter dialog box as "SQL Re-Compilations/sec" is added to the list of running counters.

Figure 1 Adding SQL Re-Compilations/sec to PerfMon

Once the counters are added to PerfMon's current list, monitoring begins. In order to generate enough recompile events, I'm going to use this script to generate one hundred invocations of usp_Recompiles_SeveralTimes.

DECLARE @i int, @rc int
SET @i = 1
WHILE @I <= 100 BEGIN
    EXEC @RC = usp_Recompiles_Due2Index  
    SET @I = @I + 1
END

When I run the script the counts of compiles and recompiles in PerfMon spike near the 100/second mark. Figure 2 shows PerfMon after I ran the script 3 consecutive times.

Figure 2 PerfMon showing 100s of Recompiles

The lines for SQL Compilations/sec and SQL Re-compilations/sec are running on top of each other. Compilations include recompilations but also count initial compiles when the procedure is inserted into the cache.

PerfMon shows only aggregate numbers. To get a more detailed look at the performance numbers the SQL Profiler is the best tool around.