Post reply

Query slower after partitioning

  • January 9, 2007 at 6:44 am

    #115649

    Hi

    I have partitioned a very large table by creating the partition function and scheme. I then rebuilt the clustered index for that table into the partition scheme by adding a primary key constraint (having dropped the original PK).

    However, the query I am using to test the performance is now taking 4 times longer (6 minutes to 24 minutes).

    Anybody have any ideas why this should be so? Have I done something wrong when creating the partitioned table?

    Any help appreciated

    Regards

    Andy

  • January 9, 2007 at 7:00 am

    #682006

    We can't answer this type of question without having the tables ddl (with indexes and keys) along with the query and execution plan.  ONce you provide that we'll most likely have enough info to get you on your way.

  • January 9, 2007 at 7:15 am

    #682009

    what's the best way to provide this info?

  • January 9, 2007 at 7:39 am

    #682019

    You got a little bit of reading ahead but it's really worth it, for everyone involved.

    http://www.aspfaq.com/etiquette.asp?id=5006

    http://qa.sqlservercentral.com/columnists/siyer/2645.asp

  • January 9, 2007 at 8:15 am

    #682028

    I'm happy to provide this info, but my first query was really about partitioning, and if there are any known issues i.e. "some queries can run slower after partitioning unless you do xyz"

  • January 9, 2007 at 8:18 am

    #682030

    Sorry but I don't know the answer to that one... I guess we'll know at the same time.

     

    But if I were you I'd keep reading those articles... they'll come in handy some day for sure.

  • January 9, 2007 at 9:09 am

    #682047

    This is a stored procedure with 2 parameters. If I run the SQL with the parameters hard coded (i.e. not as a stored procedure call), I get a different (better) execution plan and I'm back to 6 minutes.

    Next step is to work out why the different execution plan.

  • January 9, 2007 at 9:12 am

    #682048

    Dev server??

    DBCC FREEPROCCACHE

     

    Now you can search these forums for parameter sniffing to get more info on that topic.

  • January 9, 2007 at 3:42 pm

    #682224

    Database Partitioning is *not* a primary performance feature. It is mostly a maintainablility one.

    I have "several" instances in which that was the case. On SP2, M$ is saying that they have improved the optimizer to make better use of the partitioning feature. It is key that you check that only the partitions needed by your most heavy queries are actually accessed; the table on-disk configuration is as parallel as possible, etc.

    This feature is very usefull for datawarehouse loading scenarios but It hasn't prove me yet what it could represent a performance booster.

    -Regards


    * Noel

  • January 11, 2007 at 1:36 am

    #682626

    Thanks Noel

    I'm looking at partitioning to help maintain some very big tables, (i.e. rebuild indexes at partition level rather than table level). As long as queries perform the same with partitioned tables then that would be acceptable. Not sure that they are at the moment!

    Ninja, DBCC FREEPROCCACHE helped. Thanks for that. The execution plan changed and the query came down to 9 minutes. Still not as good as without partitioning though. I will continue to investigate.

    Regards

    Andy

  • January 30, 2007 at 9:53 am

    #686509

    Hoping that:

    http://support.microsoft.com/?id=923849

    will solve the problem!

    Andy

  • February 12, 2007 at 7:35 am

    #689156

    It didn't!

  • February 28, 2007 at 10:26 am

    #692359

    Microsoft investigating

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply