Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 12, 2003

Identifying Stored Procedure Recompilation Problems in SQL Server 2000 - Page 3

By Andrew Novick

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 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
    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 around.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM