Trying to understand why partition elimination is happening in some plans, but not others

Hi folks- We're trying to understand why we're getting some grossly different execution plans. Here's some info about the anonymized tables:

Table4 - fact table with 12 partitions, integer date_id as the key

Table1 - date dimension with 12 partitions, date_id as the key, same part scheme as Table 4

Table1, Index10 - unique NC index on a datetime column

Table2, Table6 - misc dimension table

In the first query/plan, date_id is passed in as the seek predicate. We can see partition elimination happening properly and a quick execution is returned. In the second, we are passing in a value for the unique NC index, but it isn't being used before processing the large fact table. In the third, our seek predicate was changed to WHERE date_id = (SELECT date_id FROM Table1 WHERE uniqueNCCol = 'val'), and was processed properly. To see if collocation was happening/affecting anything for the partition processing, queries 4 & 5 are mirrors of 1 & 2 against a non-partitioned Table1.

We're trying to ascertain why such a bad plan is being generated for query 2, as it seems like using the unique NC column to get a date_id value, then performing partition elimination would make more sense. Statistics were updated with a 100% sample before generating these plans.

STATISTICS IO/TIME data:

--Query 1, date_id, good

Table 'Table6'. Scan count 0, logical reads 22994, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Table4'. Scan count 45, logical reads 265, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Table1'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 109 ms, elapsed time = 104 ms.

--Query 2, unique NC col, bad

Table 'Table6'. Scan count 0, logical reads 22994, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Table1'. Scan count 0, logical reads 11966794, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Table4'. Scan count 540, logical reads 1341810, physical reads 0, read-ahead reads 1890, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 89716 ms, elapsed time = 106752 ms.

--Query 3, unique NC col hack, good

Table 'Table1'. Scan count 12, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Table4'. Scan count 1, logical reads 333, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Table6'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 46 ms, elapsed time = 54 ms.

avatar image By SleipOdin 48 asked Feb 21, 2014 at 08:45 PM
more ▼
(comments are locked)
avatar image Shishir Khandekar Feb 21, 2014 at 11:14 PM

SleipOdin,

In the bad query, are intermediate results being stored in Table Variables? Also, can you post the good plan too?

-Shishir

avatar image SleipOdin Feb 23, 2014 at 07:21 AM

Hi Shishir- These are single, simple SELECT queries with only joins and where clauses. The 5 different versions of the statement are described in the question text. The first plan is the good partition elimination plan. The second is not.

10|10000 characters needed characters left

1 answer: sort voted first

In the second, we are passing in a value for the unique NC index Table1, Index10 - unique NC index on a datetime column

If the unique non-clustered index you refer to is partitioned in the same way as the base table (the default, unless you explicitly tell SQL Server otherwise), the index does not in fact guarantee uniqueness of the datetime column. Rather, the combination of (datetime, date_id) is unique, because SQL Server automatically adds the partitioning column to the unique index keys. This complicates things in many ways, but the simplest answer is that SQL Server cannot rely on the datetime being unique without an index or constraint. The lack of a uniqueness guarantee is probably the reason why the seek touches all partitions rather than performing a dynamic partition elimination.

In the third, our seek predicate was changed to WHERE date_id = (SELECT ...

This construction guarantees that there will be a single date_id, or else a runtime error will be raised if the subquery returns more than one row. This possible runtime error (again, due to the lack of a uniqueness guarantee on the datetime column alone) is catered for by a Stream Aggregate (counting the rows) and an Assert (to raise the error).

Ultimately, even moderately complex queries involving several joins of partitioned tables represent a considerable challenge to the optimizer. The number of possible strategies quickly explodes the search space, particularly where many partitioned candidate indexes also exist.

The heuristics used by the optimizer to avoid unacceptable compilation times often result in query plans that are 'obviously' sub-optimal to a human being. In many cases, it is possible to use hints (including the extended FORCESEEK syntax) either directly or in plan guides, to ensure a reasonable execution strategy is used. In other cases, using different syntax to express the same logical requirement can improve plan quality.

The most difficult problems are complex systems with large partitioned data sets, many joins, candidate indexes, and complex views all of which support ad-hoc user queries. On some occasions I have had to remove partitioning entirely because it complicates query optimization too much, resulting in unstable execution plans.

The problems of data management (partition switching, the primary benefit of partitioning) are frequently much more manageable in comparison. Many of the benefits of partition elimination can be regained via reindexing - either with the previous partitioning column as an explicit leading key, using filtered indexes, or both.

avatar image By SQLkiwi ♦ 6.6k answered Feb 27, 2014 at 12:33 AM
more ▼
(comments are locked)
avatar image SleipOdin Feb 27, 2014 at 06:28 AM

Thanks a bunch for the in-depth explanation Paul! I'm glad that my understanding wasn't too far off. :) I hadn't consideration trying FORCESEEK yet, which could be quite interesting to see how things change. Ideally I'd like to avoid using plan guides, as I doubt the DBA team would be able to keep up with the devs in this area.

I know that the plan will benefit from collocation if the dimension and fact tables use the same partition scheme, but if it's causing the optimizer to time out while generating a plan, it's probably not worth it. I imagine our best option is likely to remove the partitioning on the dimension table and create the unique NC strictly on the datetime column. Does this sound about right, and would we then likely see the seeks we expect start popping up?

avatar image SQLkiwi ♦ Feb 27, 2014 at 06:48 AM

There are all sorts of things you can try using FORCESEEK, from a basic bare hint to an OPTION (TABLE HINT (exposed_alias, FORCESEEK(index_name_or_id(key_list...)))). The latter is supposed to propogate to views, but I have had limited success with it. Anyway, I digress. It's not necessarily always a time-out, sometimes the optimizer just prunes off an alternative early, or the join reordering heuristics start with an unhelpful shape, or the index-matching code throws a curve-ball, or ... well, you get the idea. I would expect removing the partitioning on the dimension and adding a unique nci on the datetime column would work (but who knows for sure before you try). You could have an unpartitioned unique index on datetime on the partitioned table too, of course.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x117
x109
x19

asked: Feb 21, 2014 at 08:45 PM

Seen: 554 times

Last Updated: Feb 27, 2014 at 06:48 AM