locked
Partition Elimination - How to Make it Happen RRS feed

  • Question

  • I have a table that is partitioned on a BigInt column (called SampleDateTime). There is a partiton per month for the next 20 years.

    The primary key is based on this column plus another Int column (TagID), with the SampleDateTime column second in the order of things.

    When I run a query where the only predicate (WHERE clause) is based on the SampleDateTime field only, for a range within a single month, query execution takes about 50 seconds. If I delete the primary key and recreate it with the SampleDateTime first and the TagID second, the same query executes in about 1 second.

    I assume that this a dramatic example of what partition elimination can do to improve performance. Am I correct?

    With the onginal primary key order (TagID then SampleDateTime), and an index created on SampleDateTime only, query time is reduces to about 35 seconds. It seems that only changing the order of the primary key causes partition elimination to kick in. Is this true?

    By the way, is there a way to see when partition elimination takes place? I don't THINK that it shows up in the execution plan.

    Richard Campbell

    Friday, May 14, 2010 3:37 AM

Answers

  • Dick

    Now  you are seeing that the order of columns affect perfromnace... When you create a clustered index on partitioned coulumn SQL Server builds B-TREE sturcture  for all partitions, that means if you have a table with three partitions SQL Server builds three B-TREE for that table.

    Also , SQL Server contains histogramm (statistics info) on very left column  and  it if is selective that means that optimzer is able to create more efficient execution plan.

     

    Friday, May 14, 2010 7:49 AM

All replies

  • Definietly it shows up in the plan, In your query it would show you the boundary value actuall and even the partition numbers it scans.

     

    "Displaying Partition Information by Using Other Showplan Methods" in "Query Processing Enhancements on Partitioned Tables and Indexes" in SQL Server 2008 Books Online at http://msdn.microsoft.com/en-us/library/ms345599.aspx.



    SQLEnthusiast
    http://sqlsimplified.com/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, May 14, 2010 6:19 AM
  • I tried "set statistics profile on" and found that in both cases partition elimination appeared to be used as per the following

    SELECT Tags.TagName, NumericSamples.SampleDateTime, NumericSamples.SampleValue, NumericSamples.QualityID  FROM  NumericSamples INNER JOIN                 Tags ON NumericSamples.TagID = Tags.ID  WHERE (NumericSamples.SampleDateTime > @START)  AND (NumericSamples.SampleDateTime < @END)  --23 Mar 10 1:45  --WHERE ($partition.fn_HistPartitionMonthly(SampleDateTime) IN (9))   --WHERE ($PARTITION.fn_HistPartitionMonthly(SampleDateTime)=9 )  --WHERE (NumericSamples.SampleDateTime > dbo.ToBigInt('23 Mar 10 1:45'))  -- 1 Apr 10 00:00  --WHERE (NumericSamples.SampleDateTime > 634056372000000000)  --DELETE April Records  --DELETE FROM NumericSamples WHERE SampleDateTime > 634056372000000000  -- $PARTITION.fn_HistPartitionMonthly(634048659000000000)  --select (dbo.ToBigInt('1 Mar 10 2:00'))

    The correct partition is number 9.

    So the question I am asking is not the right one.

    It seems that it's the efectiveness of patition eleimination that is affected by the order of columns in the primary key.

    Friday, May 14, 2010 7:31 AM
  • Dick

    Now  you are seeing that the order of columns affect perfromnace... When you create a clustered index on partitioned coulumn SQL Server builds B-TREE sturcture  for all partitions, that means if you have a table with three partitions SQL Server builds three B-TREE for that table.

    Also , SQL Server contains histogramm (statistics info) on very left column  and  it if is selective that means that optimzer is able to create more efficient execution plan.

     

    Friday, May 14, 2010 7:49 AM
  • Thanks,

    I was looking for confirmation for what I thought I had observed, that order od the columns n the primary key have a big effect on they way partition elimination works.

    Friday, May 14, 2010 9:13 AM