March 21, 2012 at 9:18 am
Hi All,
Is there a specific rule as to what the size of a table should be to become a candidate for partitioning? 20mm, 30mm rows?
Thanks
March 21, 2012 at 9:27 am
I would think it's more when you see a benefit, or when you see you have a significant amount of queries that only access a subset of your data. So if you find most queries are the current month/year/salesman/etc., then you can partition on that value to limit queries to hitting less of the table (less buffer pool, less I/O, etc)
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 21, 2012 at 9:38 am
I always say you need to ask yourself the following question: "What do I gain by partitioning?"
In our case, it helps with our backup strategy and querying. We partition by month on 1 table in 1 database. The table gets very large (don't care about rows, care about space) and a weekly full backup of all of this would be too much. So, we have each month in a read-only filegroup and that is backed up once, and moved to a backup storage drive. The current month is read-write and we do a weekly full backup with a daily diff.
We also report on monthly data from this table for billing, so partitioning by month makes sense for us.
Jared
CE - Microsoft
March 21, 2012 at 9:41 am
No set rules. There are hidden performance problems in poorly written queries around partitioned tables so I tend to look at them as more of a data management tool.
If you've got a process in place to archive old data they are a perfect solution and you can use a sliding window setup.
That said, I've got 20 million row tables partitioned (keep a set duration of) and 3 billion row tables not partitioned. It depends on the data and what you intend to do with it.
March 21, 2012 at 12:13 pm
MysteryJimbo (3/21/2012)
No set rules. There are hidden performance problems in poorly written queries around partitioned tables so I tend to look at them as more of a data management tool.If you've got a process in place to archive old data they are a perfect solution and you can use a sliding window setup.
That said, I've got 20 million row tables partitioned (keep a set duration of) and 3 billion row tables not partitioned. It depends on the data and what you intend to do with it.
Are there any reads associated with the 3 billion row tables? If so, do those perform ok?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply