INSERT INTO dbo.PermaLarge WITH(TABLOCKX) (n) , tally AS ( SELECT TOP ( 100000) n FROM Nums ORDER BY n) Nums AS( SELECT ROW_NUMBER() OVER( ORDER BY ( SELECT NULL)) AS n FROM L5) L5 AS( SELECT 1 AS c FROM L4 CROSS JOIN L4 AS B), L4 AS( SELECT 1 AS c FROM 元 CROSS JOIN 元 AS B), 元 AS( SELECT 1 AS c FROM L2 CROSS JOIN L2 AS B), L2 AS( SELECT 1 AS c FROM L1 CROSS JOIN L1 AS B),
L1 AS( SELECT 1 AS c FROM L0 CROSS JOIN L0 AS B), WITH L0 AS( SELECT 1 AS c UNION ALL SELECT 1), N int, CONSTRAINT PK_PermaLarge PRIMARY KEY (n) N int, CONSTRAINT PK_PermaMedium PRIMARY KEY (n) N int, CONSTRAINT PK_PermaSmall PRIMARY KEY (n) N int, CONSTRAINT PK_Nums PRIMARY KEY (n)
OPTION RECOMPILE SQL SERVER UPDATE
SET AUTO_UPDATE_STATISTICS ON /* Helper table to fill and update the tables under test */ CREATE TABLE dbo.Nums The AUTO_UPDATE_STATISTICS must be enabled on the test DB and the tempdb. Therefore I ran all my tests in a Docker container which didn’t have this problem ⚠️ When testing this on my local instance running on Windows, the plan cache was randomly cleared, which affects the testing. Preparing the environment and monitoring 🔗︎ Finally, I’ll repeat the tests on a statement using Temporary tables.Then I’ll rerun the tests with the KEEP PLAN hint and see if the thresholds are affected.Rerun the Procedure to see auto_stats and recompilation events.Modify the Statistics just below the threshold.The first part of the test includes running the Procedures without the KEEP PLAN hint to verify the thresholds.Then I’ll prepare monitoring to track the modification to stats, cached plans and recompilations.Each table size will test one of the thresholds ( 500, 20% and SQRT(1,000 * n)).I’ll create an environment with 3 different table sizes and 3 separate Stored Procedures that read from them.I’ll test the Permanent and Temporary thresholds separately because there are subtle differences. I will do the tests only on the CL150 environment as it includes both the old and new thresholds. Post CL130 # modifications to trigger recompilationĪrmed with this knowledge, let’s test. Pre CL130 # modifications to trigger recompilation There has been a change in Compatibility Level (CL) 130, so I put it in the same table side by side for comparison. The recompilation thresholds are described here. Before running a cached query plan, the Database Engine verifies that the query plan references up-to-date statistics. Query Optimizer determines this information before it compiles a query. 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 kommentar(er)
