6

I have a big partitioned table (300+M rows, 70 active partitions). The table has clustered PK of (K0, K1) and it's partitioned on column K1. And K1 has very low selectivity.

However the following query takes a half minute and the scan count is 170 and logical read is 603K.

select max(K1) from table

However the similar query on on non-partitioned table with index on K1 takes no time and scan count is 1 and logical read is 5.

Should a none partitioned index be created on K1?

Edit:
Plan text for partitioned table without index on K1. With clustered PK on K0,K1, before create an index on C1.

  |--Stream Aggregate(DEFINE:([Expr1004]=MAX([partialagg1005])))
       |--Parallelism(Gather Streams)
            |--Stream Aggregate(DEFINE:([partialagg1005]=MAX([DB1].[dbo].[table1].[K1])))
                 |--Clustered Index Scan(OBJECT:([DB1].[dbo].[table1].[PK_dbo_tabl1]))

Plan text for non-partitioned table with index on K1.

  |--Stream Aggregate(DEFINE:([Expr1003]=MAX([DB1].[dbo].[table2].[K1])))
       |--Top(TOP EXPRESSION:((1)))
            |--Index Scan(OBJECT:([Db1].[dbo].[table2].[idx_K1]), ORDERED BACKWARD)

Plan text for partitioned table with clustered PK on K0,K1, and index on C1.

  |--Stream Aggregate(DEFINE:([Expr1004]=MAX([partialagg1005])))
       |--Parallelism(Gather Streams)
            |--Stream Aggregate(DEFINE:([partialagg1005]=MAX([db1].[dbo].[table1].[K1])))
                 |--Index Scan(OBJECT:([db1].[dbo].[table1].[IX_C1]))
6
  • Are your indexes partition aligned? What indexes exist now?
    – JNK
    Feb 14, 2012 at 16:29
  • Also what datatype is K1?
    – JNK
    Feb 14, 2012 at 16:34
  • 1
    Can you check post the execution plans for both cases? Wondering if one has generated a parallel plan. Feb 14, 2012 at 16:46
  • @JNK The table has clustered index/PK (K0, K1). The data type of K1 is Date. There is another index on a non-key column.
    – u23432534
    Feb 14, 2012 at 17:10
  • @MarkStorey-Smith I've updated the question to include the execution plans.
    – u23432534
    Feb 14, 2012 at 17:15

3 Answers 3

4

Think I mis-understood/mis-read the question initially.

The table is partitioned on column K1. However the following query takes a half minute and the scan count is 170 and logical read is 603K.

To satisfy the MAX(k1) query in this case requires a clustered index scan of each of the 70 partitions.

However the similar query on on non-partitioned table with index on K1 takes no time and scan count is 1 and logical read is 5.

With an index defined on K1, an ORDERED BACKWARD scan of the index is chosen, which probably translates to 4 pages read from the non-leaf index pages plus 1 for the leaf level.

0
2

The order of your index keys is the issue. You can create a partition aligned index on K1 to resolve this pretty easily.

Right now your index on K0, K1 can't be used directly to find the max value quickly since K1 is the second key.

2
  • The table is huge and K1 has very low selectivity and it's partitioned. It seems SQL server engine should be able to figure out from the partition meta information?
    – u23432534
    Feb 14, 2012 at 17:31
  • @NickW it still needs to check the actual data.
    – JNK
    Feb 14, 2012 at 18:05
0

Whether it is partitioned or not,

select max(K1) from table

needs INDEX(k1) or an index starting with K1. And it must be an ordered index (such as BTree), not Hash.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.