locked
Partiotion table performance RRS feed

  • Question

  • Hi,

    We have total 500 Million records in one Partitoned table with 24 (month by create_date) file groups.

    I have a query (based on create_date and F1, f2,f3 and f5 fileds inner join with other tables)need to run on daily basis go through 24 partitions.

    I have a primary key on create_date and f1, f2.

    the query is very slow. ANy suggestions on Index or anything to improve the performance?

     

    Thansk

     

    The result returns about 200,000 records.


    mecn
    Thursday, February 17, 2011 10:31 PM

Answers

  • Hi,

     1)    Since already there is primary key on created_date , f1 and f2 .. we can have non-clustered index created one on f3 and other on f5. Since all the columns are used in the join ,  these indexes will improve the performance

     2) Since there is huge data in the tables , please check the fragmentation also ... if the fragmentation is high , then refragmentation will increase the performance

    3) also check the statistics

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Friday, February 18, 2011 4:51 AM
  • Is CreatedDate the first column in the clustered index? You mentioned that it touches all 24 partitions so I suspect that it does the full scan. Please post the table structure and query so we will have better understanding of what's going on.

    I'd be careful with the separate NCI on F3/F5 - most likely those would not be unitized in this scenario. You need to be careful with NCI in general  - there are some interesting issues that can lead to extensive IO: http://aboutsqlserver.com/2010/12/22/sql-server-and-table-partitioning-part-2-when-partitioning-is-you-enemy

    Last, but not least, if this query runs one time per day, is the performance really important? Is it worth the maintenance cost for the extra index?

     


    Thank you!

    My blog: http://aboutsqlserver.com

    Friday, February 18, 2011 2:21 PM

All replies

  • Hi,

     1)    Since already there is primary key on created_date , f1 and f2 .. we can have non-clustered index created one on f3 and other on f5. Since all the columns are used in the join ,  these indexes will improve the performance

     2) Since there is huge data in the tables , please check the fragmentation also ... if the fragmentation is high , then refragmentation will increase the performance

    3) also check the statistics

     


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    Friday, February 18, 2011 4:51 AM
  • Is CreatedDate the first column in the clustered index? You mentioned that it touches all 24 partitions so I suspect that it does the full scan. Please post the table structure and query so we will have better understanding of what's going on.

    I'd be careful with the separate NCI on F3/F5 - most likely those would not be unitized in this scenario. You need to be careful with NCI in general  - there are some interesting issues that can lead to extensive IO: http://aboutsqlserver.com/2010/12/22/sql-server-and-table-partitioning-part-2-when-partitioning-is-you-enemy

    Last, but not least, if this query runs one time per day, is the performance really important? Is it worth the maintenance cost for the extra index?

     


    Thank you!

    My blog: http://aboutsqlserver.com

    Friday, February 18, 2011 2:21 PM