October 20, 2010 at 6:01 pm
This may be a double post, but if this is the wrong forum, please send me to the correct one. THANKS!
***
I'd like to get some input, thought on a partitioning project that I am working on. I've been reading a lot of articles, white papers so I hope it will be a smooth execution once we try to implement it.
We have a HUGE transaction table that is a good candidate for partitioning.
Table Name: tblDetails
Used Pages KB: 275,347,808 (Yeah, only for this single table)
RowCount : 62,187,467
This table has an XML column which is most likely why the table is so huge. Also, it has all current and old records but there is really no date column that can be used for partitioning in this table. The date is dependent on another table. (tblHeader). We are considering adding an int column (lets call it VaultID) that can have either a value of 0 (Default), 1, 2 and will be calculated based on the date value on the header table. This column is not a calculated column, but will just be updated by a job periodically.
VaultID = 0 if the date in the header table is within 90 days.
VaultID = 1 if the date is 18 months to 90days (minus one sec)
VaultID = 2 if the date is beyond 18 months.
This is already an existing table and doing this may take a significant amount of time and I am hoping that we can execute this at the shortest time possible. (This steps outlined may not be the shortest, if there's another way, please let me know, as a warning, I'm still a noob but always eager to learn).
1. Prepare the database by adding the filegroups.
2. Assigning files to each filegroup.
3. Create the partitioning function.
CREATE PARTITION FUNCTION pf_VaultFunction(int)
AS RANGE RIGHT FOR VALUES (0,1,2);
4. Create Partitioning Scheme.
CREATE PARTITION SCHEME ps_VaultScheme
AS PARTITION pf_VaultFunction TO (PartitionVault0, PartitionVault1, PartitionVault2, PartitionVault0)
5. Drop Foreign keys that references to the Primary key of tblDetail. (to be re-added later)
6. Drop Constraint with Move to the first fileGroupVault0.
7. Add the DetailID as the Primary Key (Non-clustered).
8. Add the VaultID column and populate the value based on the date of the Header table.
9. Create the Clustered key with the DetailID and VaultID using the Partitionin
CREATE CLUSTERED INDEX IX_myIndexName ON tblDetail
(DetailID,VaultID)
ON ps_VaultScheme(VaultID)
10. Re-add the Foreign key constraints that were dropped from Step 5.
Any thoughts, ideas, are welcome. Oh yeah, why am I doing this.
1. Whenever we perform maintenance, it bloats our log files big time and it also takes a long while to do it. And so we are hoping to make the Vault1 and Vault2 to be read-only, and also, perform index maintenance only on Vault0 (and Primary as well).
2. Hoping to do only partial restore to other environments (QA and DEV) and not have to worry if there's not enough disk space to accomodate the whole database.
3. For performance purposes. Included is a plan to change queries to include the VaultID = 0 in the WHERE clause.
Any thought of why we shouldn't do it this way is also welcome, and I do appreciate and welcome suggestions.
THANKS SO MUCH!
October 20, 2010 at 11:44 pm
Please don't cross post. It just wastes peoples time and fragments replies.
No replies to this thread please. Direct replies to: http://www.sqlservercentral.com/Forums/Topic1008107-145-1.aspx
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply