March 16, 2007 at 4:44 am
Hi Chaps,
I want to know if anyone has managed to implement partitioned tables on SQL 2005. I would like to ask some few questions about this:
They are as follows:
With partitioned tables, under what circumstances will you benefit from a performance gain ?
If you have multiple filegroups, can you delete the data in that filegroup and eventually drop the filegroup as well?
How easy is it to automate the maintenance of a partitioned table?
When creating partitioned tables, can they be based on a monthly basis, so a large table exists with millions of rows say 500m, will I be able to have a vertical partition based on the date, i.e have January marketing records, February and March as well.
Also, as I understand the indexes can also be partitioned, on my application data for the current month is the most used data, and once the data has been written onto the system, it doesn’t get updated again, can I partition my indexes so that for example I can drop the indexes of data that is over 2 months old, but for example have indexes on data for march and February?
I look forward to your replies on this, and should anyone have any questions, please ask them and I will reply ASAP.
March 17, 2007 at 6:22 pm
Partitioned Tables and Indexes in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms345146.aspx
Exploring Table and Index Partitioning in SQL Server 2005
http://www.sqljunkies.com/article/f4920050-6c63-4109-93ff-c2b7eb0a5835.scuk
MohammedU
Microsoft SQL Server MVP
March 19, 2007 at 7:32 am
We've implemented partioning on one large table and it wasn't easy. We apparently have it working now, but its taken a few iterations to get it right. We rotate through 37 filegroups and use RANGE RIGHT partition functions monthly. There's not much out there in terms of automating partitioning, but there's enough clues in the literature to work it out.
The most helpful resource to us was:
Vince
March 19, 2007 at 9:29 am
I am currently testing the implementation of partitioned tables also.
In addition to the resources mentioned, I have found the information from the 'Project Real' test that MS did in conjunction with Barnes and Noble to be helpful as there are lot's of script examples and implementation issues discussed. http://www.microsoft.com/sql/solutions/bi/projectreal.mspx
Also, I have read the book 'The Microsoft Data Warehouse Toolkit' and taken the 1 week training course it is based on (Joy Mundy and Warren Thornwaite of the Kimball Group). They have sucessfully implemented partioning solutions with both the relational engine and SSAS as part of their consulting practice.
Bob
March 28, 2007 at 7:53 am
March 28, 2007 at 8:03 am
Assuming the table is big enough to be worth partitioning (big enough that you don't just get scans by default) you should always see a performance boost when doing a table/index scan simply because there are fewer rows/pages to scan.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply