Sql-server – Why select max partitioned column is much slower than indexed one

sql serversql-server-2008

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]))

Best Answer

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.

Related Question