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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date