April 5, 2010 at 7:37 am
Hello,
I'm in the process of partitioning a 6 billion row plus table, by week, and had some questions on the path I want to try. The table has 250 weeks and growing. I want to create a filegroup/week so it would be easy to maintain and move the filegroups around the disk system.
The server I'm running is the following:
Dual Quad Core
32 GB memory
2 TB EMC SAN
The recent 104 weeks are the most active and average 25-30 million rows per week. Weeks 1 - 146 would be stored compressed on a RAID 5, 3 spindle disk, since these weeks are hardly accessed, but need to be available b/c of contractual obligations. Weeks 147 - 192 would be stored on a RAID 1+0, non-compressed disk with multiple spindles to support I/O and Weeks 193 - 250 on a different disk of the same specs as weeks 147 - 192.
Weekly maintenance would be to detach DB, move nonactive filegroup to RAID 5 disks, create New filegroup for new week on active disks, and re-attach DB.
Does anyone see anything wrong with this approach? The number of filegroups to the number of processors for the proportion fill algorithm is what's bothering me about this set up.
Thanks in advance.
April 5, 2010 at 7:54 am
Frank-461314 (4/5/2010)
The table has 250 weeks and growing. I want to create a filegroup/week so it would be easy to maintain and move the filegroups around the disk system.
Bear in mind that there is a fixed limit of 1000 partitions in total. Performance also tends to tail off as the number of partitions increases.
Weekly maintenance would be to detach DB, move nonactive filegroup to RAID 5 disks, create New filegroup for new week on active disks, and re-attach DB.
You have to take the database offline, but detaching it is not necessary. See Moving User Databases
The number of filegroups to the number of processors for the proportion fill algorithm is what's bothering me about this set up.
This is a myth. See http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx
April 5, 2010 at 8:52 am
Thanks for the quick response, Paul.
I'm going to cap the number of partitions to 500 and archive anything over to a historical archive table. The main thing bothering me was the proportion fill algorithm, but since it doesn't seem like it's a big deal, I'm goin gto move forward.
Thanks again!
April 5, 2010 at 9:06 am
No worries. Good luck 🙂
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply