The Compiles - Percentage Recompilations alarm becomes active when the average ratio between the number of recompiles and the total number of compiles exceeds a threshold. This value is taken over a specific number of background collections.
This highlights when more than a certain percentage of compilations are due to run-time recompiles.
A Compile is the process SQL Server goes through to determine how a query or stored procedure will be executed. During a compile, SQL Server looks at the query, including the tables, clause conditions, joins, sub queries, sort and grouping requirements used. It then takes into account all the existing indexes, locking methods and join algorithms that could help the query run faster. It comes up with what it considers to be the fastest way of executing the query. This is known as an execution plan.
A compile can take a relatively large amount of time and CPU resources, especially when the query or stored procedure is large or complex. Because of this, SQL Server stores execution plans in the Procedure Cache in the hope that they can be reused later, thereby saving the CPU that would otherwise be required to re-compile the query or stored procedure.
A Recompile occurs when SQL Server believes that the execution plan for a stored procedure that is currently executing may no longer be the best possible plan. SQL Server pauses the query execution and compiles the stored procedure again. This not only slows down the process that is executing the procedure, but adds extra CPU load on the server.
Many recompiles can add an excessive CPU load on SQL Server and slow down everything running on that machine. In general, the fewer recompiles the better.
There are many conditions that can cause SQL Server to recompile a stored procedure. The most common are:
When the alarm is raised
It is a good idea to review the code of each of the stored procedures. You should look at changing the stored procedures to remove coding practices that can cause recompiles. Things you might consider include: