SPLIT RANGE TOOK 10 HRS to Complete

Question

SPLIT RANGE TOOK 10 HRS to Complete

Reo

Hall of Fame

Points: 3774
More actions
December 3, 2012 at 8:15 am

#265606

Hi All,
I just split the Range (introduced new range) of my existing Partition Function but it took 10HRS to complete and my database transaction log grew around 120GB.
My File Groups are : only one table using these file groups by Table Partitioning using below Partition Scheme & Function.
1. PRIMARY - 5GB
2. JulData - 20 GB
3. AugData - 17 GB
4. SepData - 19 GB
5. OctData - 20 GB
6. NovData - 22 GB
My Partition Scheme & Function is:
CREATE PARTITION SCHEME [psch__DateTime] AS PARTITION [pfn__DateTime] TO ([PRIMARY], [JulData], [AugData], [SepData], [OctData], [NovData])
CREATE PARTITION FUNCTION [pfn__DateTime] (datetime) AS RANGE RIGHT FOR VALUES ('2012-7-01', '2012-08-01', '2012-09-01', '2012-10-01', '2012-11-01')
Everything was fine. Today, I like to introduce new boundary for Dec Month so I followed following steps:-
1. I created new File Group “DecData” and assign a file for Dec Data.
2. ALTER PARTITION SCHEME [psch__DateTime] NEXT USED [DecData]
3. ALTER PARTITION FUNCTION [pfn__DateTime]() SPLIT RANGE ('2012-12-01')
But my 3rd step took around 10hrs to complete and my database log size increased from 5GB to 120GB, I surprised why this happen? I just introduced a new boundary that's it. What was wrong in my task? I still investigate, if any one idea plz help to understand why it took so much time to just introduce new boundary.
Harware Details:
OS : Windows Server 2008 DC SP2 64bit
CPU : 2.66GHz - 2 Quad Core
RAM : 24 GB
Only one SQL Server 2008 64bit Instance running on it.
🙂
Ram
MSSQL DBA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The general recommendation when splitting ranges is to ensure that no existing data will have to move to a new partition. If it does, trhen you get what you saw, large time requirement and large log growth.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Reo Hall of Fame Points: 3774 More actions · Answer 2

Thank u Gail for your update. Yes I ware have Dec month data in "NovData" FG before spliting.

So u mean we have to split/introduce new range before new data arraving to avoid loong time to complete?

Ram
MSSQL DBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Yup. If you split the range when there's data that will qualify for the new range, SQL has to move that data to a new partition, which kinda defeats the point of the sliding window setup.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101120 More actions · Answer 4

What was the status of the log file when you ran the command? How large was the log, how much space was free, and what was the Autogrowth amount?

Be sure to pre-allocate, and thus pre-format, the space required for the log. Make sure the growth is not a percentage or too small (or too large, but that's much less common).

If you are not using IFI, do the same for the data file/partition as well (or, better yet, activate IFI immediately!).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".