Home Dashboard Directory Help
Search

Partition Table using min/max functions and Top N - Index selection and performance by Mark Harvey1


Status: 

Active


60
0
Sign in
to vote
Type: Bug
ID: 240968
Opened: 11/26/2006 2:07:58 PM
Access Restriction: Public
3
Workaround(s)
view
18
User(s) can reproduce this bug

Description

Partitioned Tables performance issues - For select statements using Min and Max functions and Top N with ordering over an index.

Poor performance is being detected for Queries on partitioned tables utilising the min and max functions and select Top N clause with "order by" matching columns of the index.

The candidate index is either not being used or is being used to scan or seek all rows rather than a subset.
Details
Sign in to post a comment.
Posted by Adi Cohn on 9/2/2013 at 2:32 AM
I've just got hit by this bug on SQL Server 2012 with service pack 1. Unfortunately the workaround that was published here did not work. I had to stop working with partitioning and go back to work with regular tables. I have to admit that it is very disappointing to see that this bug exists since 2007, and that for the past 6 years and 3 new versions, Microsoft did not find the time to fix this bug.

Adi
Posted by TheSQLGuru on 5/24/2012 at 5:53 AM
Tomwifly - you have used the $partition-based workaround, right??
Posted by Kasper B on 4/26/2012 at 6:00 AM
The problem has not been fixed in SQL Server 2012

Version:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Posted by Tomwifly on 3/16/2012 at 12:10 PM
Hi All,

Anybody know if this problem is solved in SQL Server 2012? I have 23bln rows partitioned table and this issue is killing me.

Thanks,
Tomek
Posted by SPE109 on 1/11/2012 at 4:30 AM
Hi,
I have just implemented partitioning on a large table, the main reason being to be able to run maintenance tasks at partition level and spread the load of this table over drives. This bug is however killing the performance of simply queries and causing table scans on 1.4 billion row tables instead of scanning a partition with 1 million rows in.

I know there are workarounds but business users write their own reports and it is difficult to get them to use the workarounds.
Thanks
Paul
Posted by Kasper B on 12/1/2010 at 3:25 AM
Is there any plan for when "on the rader for a future release" is, in terms of a date?


This problem is a show stopper for using partitioning in our current situation.

The problem results in 100 GB's worth of data being ordered (partitioned table), instead of just selecting 1 row from an index (non-partitioned table).
Posted by Robert L Davis on 11/26/2010 at 11:25 AM
I am seeing similar performance problems with queries that do not include TOP or MAX/MIN. Just a simple query that uses an nonclustered index seek and does an order by. Due to the nonclustered index being marked as unordered, it is performing a huge sort operation.
Posted by Microsoft on 5/10/2010 at 10:50 AM
Hi Chris,

The optimization you requested has not been delivered in SQL 2008 R2 and it will not be delivered in SQL 2008 SP2. It is on the rader for a future release.

Campbell

Posted by ConfusedDBA on 4/9/2010 at 3:46 AM
Good Morning,

Can you tell me whether this problem has been resolved in SQL Server 2008 R2 or if not if it will be addressed in SQL Server 2008 SP2?

Thanks,
Chris
Posted by Microsoft on 1/29/2009 at 10:48 AM
Mark,

You've provided a very nice analysis that has shown several limitations in the optimizer related to queries on partitioned tables that use MIN and TOP. We're not in a position to fix these issues as a bug fix for SQL Server 2008 because they require some significant effort. We'll keep a record of these and try to improve the behavior for the next major release of SQL Server.

I did try adding OPTION(RECOMPILE) to one of your queries that had local variables in the where clause, but that didn't give the same plan as using literals instead of variables, which is unexpected. I'll have our development and test teams investigate that further. This may be something we can correct in the short term.

Eric
Posted by justingrant on 10/27/2008 at 3:01 PM
Also note that adding an index hint does not work-- SQL is already using the correct index, it's just scanning the entire index instead of accessing each partition only once.
Posted by justingrant on 10/27/2008 at 2:59 PM
This problem *does* repro on SQL 2008 RTM. I posted into the SQL forums about this: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=4053421&SiteID=1
Posted by Mark Harvey1 on 12/11/2007 at 2:43 PM
Sorry I have not tested under 2008 CTP (have not downloaded a copy yet!).

I agree that this is related to a problem optimizing queries that step row by row through an index up or down - which are of the form ( and also applies to Top N where N is small eg. 0 - 100 ):

select Top 1 key
from table
where key < @value
order by key desc

OR (in the opposite direction)

select Top 1 key
from table
where key > @value
order by key asc

where the wrong index is frequently chosen by the optimizer. This problem exists regardless of partitioning. And can be corrected (optimised successfully) with an index hint:

select Top 1 key
from table with (index(key_idx))
where key < @value
order by key desc

OR

select Top 1 key
from table with (index(key_idx))
where key > @value
order by key asc
Posted by Microsoft on 12/11/2007 at 12:08 PM
Thanks for the nice repro! I'll ask somebody from the development team to look into this.

Note that if you use a local variable in the where clause of a query, we guess the selectivity of the filter. Use parameters instead and we'll compile for the value passed in the first time the query is run. Or, you can use OPTIMIZE FOR but be sure to include compile time values for all variables. Any you leave out will still get guesses. Alternatively, use dynamic SQL, don't use local variables, but use constant literals instead.

Do you know if the problem still occurs on SQL Server 2008 CTP5? Optimization for partitioned tables changed quite a bit, and the behavior may have changed.
Posted by Mighty-O on 9/20/2007 at 2:59 PM
This is a daunting issue. I have experienced it with a simple top X query where the optimizer must be hard instructed to employ the proper index.

For example, assuming 200 million records in Tbl A and 10 thousand in Tbl B.

SELECT TOP 1000 * FROM dbo.A a JOIN dbo.b b ON a.bID = b.ID AND b.Other = 10

Table A and B have a Clusterd index on their ID fields. Table A has an index on it’s bID field that includes its ID field, we will call that idn_a_bID. Table B has a non clustered index on its ‘Other’ field which is an int, it’s name is irrelevant.

The query takes minutes to complete.

Query hint for the bID’s index:

SELECT TOP 1000 * FROM dbo.A a with( index( idn_a_bID ) ) JOIN dbo.b b ON a.bID = b.ID AND b.Other = 10

Runs in a few milliseconds. The query plan shows the problem being the non hinted query employs a’s clustered index hence the SLOW performance.

By the way,
SELECT * FROM dbo.A a JOIN dbo.b b ON a.bID = b.ID AND b.Other = 10

This issue is crippling.
Posted by Mark Harvey1 on 8/10/2007 at 9:17 PM
Hi Microsoft, please confirm this optimizer problem by running the scripts, it is self evident and can be optimised significantly better.

Eg. For a minimum, the optimal plan is to obtain the minimum for each partition using the index and then taking the minimum of all the (partition) results. The same principle can apply to maximum, and for Top N over an index ...

The plan should stand out as optimal, because the number of reads over the index is very small, and there will be one set of page reads (usually only one page) for each partition.... you can use the assumption that the number of partitions is significantly smaller than the number of rows (or even count the number of partitions), to estimate the cost (page reads) - which comes out at about N pages where N is the number of partitions. This result is significantly smaller than the current optimiser choice... which is reading many many pages for the result!!!

IF you are having problems understanding please contact me, I will be very happy to explain...

Regards,
Mark

Sign in to post a workaround.
Posted by Eric N. Hanson MSFT on 3/21/2011 at 4:41 PM
Correction: the final queries in my workaround below should be these (the point is to do true TOP on each partition and only scan as little of the index as needed to get the top N values).

-- does a full scan
select top 3 *
from t
order by a;
go

-- This query gets a plan that uses a top on each partition, and then another top
-- over that to get the final result.
select top 3 o.*
from (values (1),(2), (3)) as p(a) cross apply
     (select top 3 *
        from t
        where $partition.pf(t.b) = p.a
        order by t.a asc) as o
order by o.a;
Posted by Eric N. Hanson MSFT on 3/21/2011 at 4:33 PM
Using the new "VALUES" clause and an outer apply, you can work around the issue with a single query in some cases. See below. I know, it is inconvenient but it can get you much faster performance.

use tempdb;
go

create partition function pf(int) as range left for values (100000,200000,300000);
go

create partition scheme ps as partition pf ALL TO ( [PRIMARY] );
go

create table t(a int, b int) on ps(b);
create clustered index idx on t(a);

go

set nocount on;
declare @i int;
set @i = 1;
while (@i<>10000)
begin
    insert t values(@i*100, (@i*100 + 200000) % 89890)
    set @i += 1;
end
go

select top 3 *
from t
order by b;
go

-- This query gets a plan that uses a top on each partition, and then another top
-- over that to get the final result.
select top 3 o.*
from (values (1),(2), (3)) as p(a) cross apply
     (select top 3 *
        from t
        where $partition.pf(t.b) = p.a
        order by t.b) as o
order by o.b;

Posted by justingrant on 10/27/2008 at 2:58 PM
I was finally able to figure out a workaround-- see below for an example on my database schema, although the approach is generally applicable. It's ugly but fast: create a temp table, loop through every partition and get the MAX WHERE $Partition = PartitionNumber, and then compute the MAX of the resulting temp table. As long as the each query is limited to one partition, you sidestep the bug.

-- DECLARE @StartingNewVisitId int
-- SELECT @StartingNewVisitId = ISNULL ((SELECT MIN (VisitId) FROM Traffic WITH (NOLOCK, INDEX(NIX_Traffic_Visit)) ), 0)
-- Instead, have to iterate through each partition to get the MAX
DECLARE @Partition int, @MaxPartition int

SELECT @Partition = (SELECT TOP 1 $PARTITION.TrafficMonthYear(RequestDateTime)
FROM Traffic ORDER BY RequestDateTime)

SELECT @MaxPartition = (SELECT TOP 1 $PARTITION.TrafficMonthYear(RequestDateTime)
FROM Traffic ORDER BY RequestDateTime DESC)

CREATE TABLE #Maxes (VisitId int)

WHILE (@Partition <= @MaxPartition)
BEGIN

INSERT INTO #Maxes
SELECT MAX (VisitId) as VisitId
FROM Traffic (NOLOCK)
WHERE $PARTITION.TrafficMonthYear(RequestDateTime) = @Partition

SET @Partition = @Partition + 1

END

DECLARE @StartingNewVisitId int

SELECT @StartingNewVisitId = ISNULL (MAX (VisitID), 0) + 1 FROM #Maxes

DROP TABLE #Maxes
File Name Submitted By Submitted On File Size  
PartitionedTable_Queries.sql (restricted) 11/26/2006 -