Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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]))
share|improve this question
    
Are your indexes partition aligned? What indexes exist now? –  JNK Feb 14 '12 at 16:29
    
Also what datatype is K1? –  JNK Feb 14 '12 at 16:34
1  
Can you check post the execution plans for both cases? Wondering if one has generated a parallel plan. –  Mark Storey-Smith Feb 14 '12 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 '12 at 17:10
    
@MarkStorey-Smith I've updated the question to include the execution plans. –  u23432534 Feb 14 '12 at 17:15

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
Just curious why SQL cannot use the partitioning information so the query can return the result in no time. –  u23432534 Feb 14 '12 at 17:45
    
@NickW - Because the partitioning information isn't sufficient to satisfy the query –  JNK Feb 14 '12 at 18:04
1  
@JNK I thought one benefit of table partitioning is that there is no necessary to create an index on K1,K0. (The table is partitioned by the low selectivity K1). So there is still a necessary to create the index? –  u23432534 Feb 14 '12 at 18:23
    
@NickW if you want to get the value from the table, yes. You can partition on ranges, as well. I don't believe there is any mechanism to get the actual field value from the partition, but I could be wrong. –  JNK Feb 14 '12 at 18:25

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.

share|improve this answer
    
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 '12 at 17:31
    
@NickW it still needs to check the actual data. –  JNK Feb 14 '12 at 18:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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