July 31, 2012 at 6:56 pm
Hi,
What is the best practice when deciding to partition a table? Should I look at the row count of the table or the size of the table?
Currently, I have a 10 gig table with 7.1 million rows...is this table a candidate for partitioning?
Thank you in advanve for your help.
July 31, 2012 at 6:59 pm
Depends. Why are you considering partitioning?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 1, 2012 at 12:47 pm
To echo Gail... What problem are you trying to solve where partitioning came up as a possible solution? If it isn't broken, don't try to fix it. However, if you are simply interested in why people would choose to partition a table(s), it is to make it easier to manage the data. A common misconception is that partitioning your data will always make queries perform better. The real reason for partitioning data is to make it more manageable.
As an example, we partition our data on a monthly basis and keep it in the table for a year. Once each month passes, I make the previous month read only, backup that filegroup, and then take a full backup. This means that I only have to backup the previous month's data once, and it keeps our full backups much smaller and manageable. It also makes it easier to purge data from a year ago because I can switch the partition to a staging table and simply drop the table. If I were to delete a whole month's worth of data it would cause enormous performance problems on the instance.
Jared
CE - Microsoft
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply