April 10, 2013 at 1:17 pm
Hi
Here are some details of structure before I state the problem:
Table Employee (dummy name) is of size 750GB on SQL Server 2008. There is clustered index on ID column and nonclusterd index on WeekOfJoining(int). WeekofJOining has increasing values like 1,2,3....52,53,54.... Table is RIGHT partitioned on WeekOfJoining. Each partition has around 10 million rows. At then beginning of the year, 52 new partitions are added for new weeks of new year. Old data is not archived and hence we keep on increasing the partitions. Currently there are 650 partitions.
Now when we split the partition function at the beginning of year, it is taking ages because of huge data movement that split causes. So it is becoming impossible to add new partitions.
So some questions are here:
1.What's wrong with existing structure?
2.Table is partitioned on WeekOfJoining, which has nonclustered index.( the index itself is not partitioned). Is it better to partition on clustered index column? Will it help if I partition the Nonclustered index as well?
3. What can I do to make the data movement lesser while doing split?
4.Any other design changes that can make life easier?
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
April 10, 2013 at 6:12 pm
S_Kumar_S (4/10/2013)
So some questions are here:1.What's wrong with existing structure?
2.Table is partitioned on WeekOfJoining, which has nonclustered index.( the index itself is not partitioned). Is it better to partition on clustered index column? Will it help if I partition the Nonclustered index as well?
3. What can I do to make the data movement lesser while doing split?
4.Any other design changes that can make life easier?
1. As presented, I'd say there's nothing wrong with the structure, at least how it relates to this problem.
2. Partitioning on the clustered index: The choice of partition key follows a similar, but separate decision process than selecting a clustered index. They usually line up, but not always. Aligning the WeekOfJoining index with the clustered index's partition scheme will produce benefits, at least when you do a split. When splitting a partition that has data in it, all rows must be scanned to determine on which side of the split they must go. Even if no rows will go to the new partition, they must all be scanned and evaluated. Having an aligned nonclustered index on the partitioning column really helps that out. (It's not very useful for most other uses, though. See the next point.)
3. The best way to avoid the pain of long split times is to ensure you're splitting empty partitions - split early instead of waiting for the new year, when the partition you're splitting is active. An empty partition has no rows to scan or move, so the only concern is getting a schema-modify (SCM-M) lock on the table. You should also check if there are other tables or indexes that are also using that partition scheme, as they will participate in any Split actions.
Query to check for all indexes on a given partition scheme:
SELECT schema_name(schema_id) + '.' + o.name AS [TableName],
i.name AS [IndexName], i.index_id
FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id
WHERE EXISTS(SELECT * FROM sys.partition_schemes ps
WHERE ps.data_space_id = i.data_space_id
AND ps.Name = 'the name of your partition scheme')
4. If you intend to take advantage of other partition features (such as using SWITCH to archive old data), you will need to align all indexes on the same partition scheme.
Eddie Wuerch
MCM: SQL
April 10, 2013 at 6:26 pm
I'm curious as to you reasoning behind the choice for your partitioning.
April 11, 2013 at 6:13 am
Well, I can't give the reasoning as it has been there for years. So I have to start from what I have been given.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply