This is the final in a series of posts about query plans. The third entry explained how the plans are stored in a cache to be reused. This post will cover the opposite. When are plans NOT able to be reused? What would cause a plan to need to be compiled again?
There are several reasons that a query plan would need to be compiled again, but they can be boiled down to a few popular reasons.
The first one is simple. The plan cache is stored exclusively in memory. If there is memory pressure on the instance SQL Server will eject plans from cache that aren’t being used to make room for newer, more popular plans or even to expand the buffer pool. If a command associated to a plan that has been ejected from the plan cache is issued, it will need to be compiled again before it can execute.
Since SQL Server 2008 a system stored procedure, sp_recompile, has been available to clear a single stored procedure plan from the cache. When executed with a valid stored procedure name as the only parameter any plans for that procedure will be marked for recompilation so that a future execution of that procedure will need to be compiled. Running sp_recompile does not actually compile the procedure. It simply invalidates any existing plans so that some future execution, which in theory may never come, will need to compile before executing.
A very dangerous command, DBCC FREEPROCCACHE, will clear out the entire plan cache from memory for the entire instance. This command should be used sparingly–if at all. If this command is executed then every single statement executed by every single user will require an initial compile. It will surely create a short term CPU spike while the plan cache is rebuilt from scratch.
The final reasons can be found via a trace or, preferably, and Extended Event session. These are plans that are in cache, do match an issued query, and aren’t marked for recompilation, but cannot be reused for one of 14 reasons. There are 2 separate recompile events to be traced indicating that T-SQL code was compiled and the reason for the recompile is recorded in the event subclass. A list of all 14 reasons can be read from the trace subclass system table.
SELECT evts.name, tc.name, sbc.subclass_value, sbc.subclass_name FROM sys.trace_events evts INNER JOIN sys.trace_columns tc ON tc.name = 'EventSubClass' INNER JOIN sys.trace_subclass_values sbc ON evts.trace_event_id = sbc.trace_event_id AND tc.trace_column_id = sbc.trace_column_id WHERE evts.name IN ('SP:Recompile','SQL:StmtRecompile') ORDER BY evts.name
Of these 14 there are only a few that are commonly seen.
Schema changed – An object associated to the query has changed. Perhaps a procedure, table, or view definition has changed. This isn’t the same as rows being added or removed from a table. A schema change would be something akin to adding a column to a table or changing a procedure definition.
Statistics changed – The column or index statistics have been updated on at least one table associated to the query. Since the statistics are new the optimizer wants to take another run at building an efficient query plan based on this new information. Statistics can be updated by a statistics update maintenance plan, an index rebuild, or an auto update of statistics if that setting is turned on.
Set option change – Query plans can vary based on SET options. If 2 identical queries are executed, but with different SET options in place, the first query will store a plan that when found by the second will not be valid. The second query will be forced to recompile. This can also happen if any SET options are changed in the middle of a stored procedure. If SET options are needed within a stored procedure they should be the first line(s) of the procedure so that all subsequent commands are run with the same SET options.
Option (recompile) requested – Any query can be called with an explicit request to recompile any saved plan by adding OPTION (RECOMPILE) to the end.
SELECT * FROM Sales.Invoices OPTION (RECOMPILE)
That’s it. If you’ve read the entire series I hope you have an understanding of query plans, the query plan cache, and how plans are able to be reused or not able to be reused so that you can use the information to push to get the most performance out of a SQL Server instance.