Issues with Plan Cache Reuse & Row Goal Optimization
I am presenting here on behalf of my colleague Fabiano Amorim (he is busy resolving other exciting performance issues... :-D ) . Fabiano had an interesting case with one of our customers that is very common in SQL Server. The case is about a performance issue caused by two optimizer decisions not working well together:
Problem DescriptionLet's review the following query:
select top 1 col_date from tab1 where col1 = 10 and col2 = 1 and col3 = 1 order by col_date ascTable tab1 have two indexes:
- ix1 (col1, col_date, col2) include(col3)
- ix2 (col1, col2, col3) include(col_date)
- select -> top -> filter -> index seek (ix1) Read the ordered index ix1 by b-tree seeking by “col1 = 10”, apply the residual predicates (filter) “col2 = 1 and col3=1”, after reading just 1 row (TOP 1) the execution is finished since the index is ordered by col1, col_date, the first col_date returned is already the TOP1 ASC according to the index order.
- select -> top N sort -> index seek (ix2) Read the covered index ix2 b-tree (notice it has all needed columns), seeking by “col1 = 10 and col2 = 1 and col3=1”, get the col_date in the index leaf level (included column), use “top N sort” algorithm to sort and keep only TOP 1 row, finish execution.
select top 1 col_date from tab1 where col1 = 99 and col2 = 1 and col3 = 1 order by col_date ascWhat if after the seek (“col1 = 99”) 2 million of rows are returned? Now this plan is not so good, since it will need to apply the predicate on many rows before it finds a match: col1 | col2| col3 | col_date 99 | 2 | 2 | 2015-12-01 99 | 2 | 2 | 2015-12-02 ...after a couple of million rows… 99 | 1 | 1 | 2015-12-03 99 | 2 | 2 | 2015-12-04 99 | 2 | 2 | 2015-12-05 In this case, using the second option is better. Just go and seek the b-tree for all values (col1 = 99 and col2 = 1 and col3 = 1), this will return 1 row… TOP n SORT will do almost nothing and execution will finish quickly. Here is the problem: most of the times, SQL knows whether to use option 1 or option 2 based on the parameters values. But if it is reusing the plan from cache, the optimization path may already be set improperly resulting in the known issue called "parameter sniffing" (plan reuse that is wrong for the specific set of rows)… That means that the row goal optimization should not be used if there is a covered indx. Unfortunately by default, QO "thinks" this is cheaper than “seek+top n sort”… Of course it all depends on the distribution of data…So in a nutshell, QO chooses rowgoal optimization where this should not be used therefore we should pay extra attention to those kind of plans…
Possible SolutionsThere are many alternatives to fix it. Some examples:
- Force the index (index=ix2)
- drop the index ix1, define ix2 as a unique (tells QO that only 1 row will be returned)
- Inside the Optimizer: Row Goals In Depth
- Optimization Phases and Missed Opportunities
- Query Tuning Mastery: Clash of the Row Goals