March 8, 2021 at 8:29 pm
I've yet to find an answer for this. I've found a few queries that have a few executionplans. Why is that? For example, I've seen queries (in QueryStore) that has executionplans A and B (and more)... For a few days the query uses A and then goes back to B, and then back to A again. I've understand that queryplans can get recompiled every now and then for a few reasons, but can the optimizer actually keep old plans and reuse them again later?
See attached screenshot for example, is there a good explanation? 🙂
March 9, 2021 at 1:33 pm
There are a number of things that can cause different execution plans. First up, and usually the most common, is parameter sniffing. A value is passed to a stored procedure. Said value returns, for our example, 100 rows. An execution plan optimized around returning 100 rows is created. Data changes occur on the table. Statistics get updated. Your query gets marked for recompile. Next time it gets executed, a new value, this time returning 100,000 rows is passed. A new plan, optimized for 100,000 rows is created.
You can get the same effect with hard coded values too. It doesn't have to be a parameter. Also, in the case of recompiles, local variables can be sniffed and their precise value can lead to different row estimates, again, resulting in different plans.
You can also get different plans if you have different connections using different ANSI settings. These can compile into different plans for the same query.
There's probably a couple of other reasons I'm not remembering at the moment. However, the data distribution and row estimates are usually the driver for different plans. I have a query I demonstrate from the AdventureWorks database that can produce up to 8 different plans, based on the values in a single column in one table. It's just a question of getting the recompile to get the optimizer to pick different row counts based on the parameter value.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 9, 2021 at 7:17 pm
SQL makes the best estimate of the situation at compile time. If the distribution of the data has changed, and the previous execution plan might not be the best. For example, consider an actual example from the real world. In Georgia license tags are issued by the state, but are sold by the county. When the big counties submit their paperwork in batches, the data distribution suddenly switches. Small rural counties will have a lot more farm equipment than, say Fulton County with Atlanta. While it doesn't apply to SQL Server, you will find our SQL products designed for larger amounts of data that actually execute multiple plans on the same data by breaking the data into partitions.
If you like playing around with other models of computing, I would suggest you look at Dykstra's guarded command syntax. In this model, the order of execution of statements is unpredictable!.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply