![]() Query Optimizer uses the columns, tables, and indexed views in the query predicate to determine which statistics might be out-of-date. Query Optimizer checks for out-of-date statistics before it compiles a query and runs a cached query plan. The threshold is based on the number of rows in the table or indexed view. Query Optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. Specifies that Query Optimizer updates statistics when they’re used by a query and when they might be out-of-date. What happens is described nicely in the AUTO_UPDATE_STATISTICS documentation. I’m primarily interested in #2 - Statistics changed. Multi-plan statement required compilation of alternative query plan ![]() Interleaved execution required recompilation WHERE dxmv.name = 'statement_recompile_cause' map_key Recompilations and AUTO_UPDATE_STATISTICS □︎įirst, we need to cover these concepts for our test to make sense.Īccording to Extended Events, there are 20 reasons for recompilations. Therefore, if changes to temporary tables are causing many recompilations, this query hint can be used.īut since it doesn’t show the proof, I decided to test it out anyway. The KEEP PLAN query hint changes the recompilation thresholds for temporary tables, and makes them identical to those for permanent tables. I found the answer in a Plan Caching and Recompilation in SQL Server 2012 whitepaper before trying it out on my own. Like Dwarves of Moria, I delved too greedily and too deep. In this post, I’d like to prove what it really does. The documentation isn’t very specific (emphasis mine):įorces the Query Optimizer to relax the estimated recompile threshold for a query. I was always wondering what the KEEP PLAN hint does. ![]() Update 5: The documentation fix has been merged!
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |