Post reply

Table partitionong in SQL 2005

  • January 27, 2011 at 3:15 am

    #148213

    Hi All ,

    Some queries are running slow and we are suspecting a table that is causing it .

    We want to go for table partitioning but before- hand we would like to confirm how to make sure whether that table needs to be partitioned or not .

    Please let us know the necessary checks we need to make to ensure this slowness is due to the suspected table and partitioning can come handy .

    Please cc the answers to my id as well .

    abhishek_dwivedi03@infosys.com

    Thanks in advance .

  • January 27, 2011 at 9:24 am

    #1279153

    Is the query slow all the time? I'd start with running the query that running slowly in SSMS and showing the actual execution plan when it completes. That right there will give you an indication of what is taking sop long for the query to run. If it isn't slow all the time you may need to start with a trace to try to find out what the issue could be.

    In most cases partitioning a table will get you a performance boost only if the table is relatively large.

  • January 27, 2011 at 10:15 pm

    #1279499

    Thanks for the reply buddy .

  • January 27, 2011 at 10:56 pm

    #1279506

    One question from top of my head:

    Is a table with around 25 million records considered large enough to partitioning ?

  • January 28, 2011 at 6:27 am

    #1279664

    At first glance, yes, 25 million records sounds like a lot, but, and I hate to put it this way but that depends on record size and the actual size of the files on disk. I'd venture to say though that you do have a file that is large enough to benefit from it.

    I am by no means an expert though so someone with more experience may want to add something but you really need to start with an execution plan to see where the hold up is in the query. It may be something as simple as needing a different index. Also, depending on what type of physical storage you have for the DB files, partitioning may not make much of difference. Meaning of you have them on a multi-disk raid setup then you are probably already getting a performance boost from having the IO spread over multiple disks.

    Hope that helps

Viewing 5 posts - 1 through 4 (of 4 total)

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