How to determine what is compiling in SQL Server

Question

In tracking performance monitors on my SQL Server 2008 Std Edition installation, I've noticed that SQL Compilations/sec spikes every five seconds or so from 3 to around 50.

We also have a relatively high ratio of compilations to batch requests/sec. I understand this should ideally be a 1/10 ratio, but we are working at more like 8/10.

The db supports a busy website with a number of applications, so it's hard to pin down what is causing the excess compilation, especially the 5 second spikes. Nearly all the queries are stored procedure calls rather than embedded SQL, and we have substantial (48gb) RAM.

Is there a way to see at a given moment in time what queries are currently in compilation? If so, we could work out if any are problematic.

Answer 1

When I’ve had to look into issues with plan caching/excessive query recompilation in the past I’ve followed the guidance provided in the Microsoft whitepaper ‘Plan Caching in SQL Server 2008’ and I would strongly suggest reading that as it covers plan caching, query plan reuse, causes of recompilations, identifying recompilations and other related topics.

With that said, SQL Server Profiler (Should be under located under Microsoft SQL Server 2008 -> Performance Tools if you installed it as part of your client tools installation) exposes three events directly related to query compilation that may be of help to you:

• Cursor
  • CursorRecompile
• Performance
  • Showplan XML For Query Compile
• Stored Procedure
  • SP:Recompile

You are using Stored Procedures so likely you only need to worry about the SP:Recompile event. This event will fire any time a stored procedure, trigger or user defined function has been recompiled. The TextData column will show the text of the tsql statement which caused the statement recompilation and the EventSubClass column will show a code which indicates the reason for the recompilation.

EventSubClass Codes for SP:Recompile in SQL 2008

• 1 = Schema Changed
• 2 = Statistics Changed
• 3 = Recompile DNR
• 4 = Set Option Changed
• 5 = Temp Table Changed
• 6 = Remote Rowset Changed
• 7 = For Browse Perms Changed
• 8 = Query Notification Environment Changed
• 9 = MPI View Changed
• 10 = Cursor Options Changed
• 11 = With Recompile Option

If you monitor the following 5 events you’ll be able to see which stored procedures and statements are being invoked on the SQL Server and which ones are triggering recompilations:

• Store Procedures
  • SP:Starting
  • SP:StmtStarting
  • SP:Recompile
  • SP:Completed
• Performance
  • Auto Stats

I also usually setup the Profiler trace to capture all columns for those events. I would say setup a trace with those 5 events, run a trace for 30 to 60 seconds and then pause it and then you should have a good snapshot of what is causing the recompiles.

If there is too much noise you can start adding column filters to the trace properties to filter in/out events. For instance if you find most of your recompiles happening on just once database, setup a column filter on the databaseID or databaseName column so just queries run against that database are included in your trace.

Then start looking for patterns in which queries are being recompiled and use the whitepaper from Microsoft as a guide to why they might be triggering the recompile.