Menu
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.
587
When the documentation refers to recompiling a stored procedure or the SQL Server Profiler records a compilation event, compilation means the process of compiling the special T-SQL statements and optimizing the SELECT, INSERT, UPDATE, and DELETE statements.
Stored procedures that are marked with NATIVE_COMPILATION are natively compiled. This means the Transact-SQL statements in the procedure are all compiled to native code for efficient execution of performance-critical business logic.
Query compilation is the complete process from the submission of a query to the actual execution. There are many steps to query compilation? one of which is optimization. All T-SQL statements are compiled, but not all are optimized.
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:
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
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:
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.
130
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
