Tools to Monitor Recompiles
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
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
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