Hi Mates,
Today I am going to share some important information related to Split() operation in Partitioning. From the High level everything looks very simple and straight forward however when we dig in this really is something we need to keep an eye on.
Okay Let's start with a Demo . First we will go with Left Range partitioning
CREATE PARTITION FUNCTION [Orders_Id_Function](bigint) AS RANGE LEFT FOR VALUES
(1000,2000,3000)
GO
CREATE PARTITION SCHEME [Orders__Scheme] AS PARTITION [Orders_Id_Function] TO
([DailyFG1],[DailyFG2],[DailyFG3],[DailyFG4])
CREATE TABLE [dbo].[Orders](
[OrdDate] [datetime] NOT NULL,
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Addr] varchar(100) NOT NULL)
-- Partition the table
CREATE UNIQUE CLUSTERED INDEX IX_Orders
ON [Orders](OrdDate asc,ID asc)
ON [Orders__Scheme] (ID);
GO
set nocount on
go
declare @i int
set @i = 1
declare @date Datetime
while (@i < 1000)
begin
set @date = dateadd(mi,@i,'2012-11-01T10:17:01.000')
--insert into testtable values (@date)
insert into [Orders] values (@date, 'Testing')
insert into [Orders] values (dateadd(month,3,@date), 'Testing')
insert into [Orders] values (dateadd(month,6,@date), 'Testing')
insert into [Orders] values (dateadd(month,9,@date), 'Testing')
set @i = @i+1;
END
Here is how the data has been partitioned
So As you can see the FG1, FG2 and FG3 has 1000 rows each and the last File group FG4 has 996 rows.
Now imagine we need to partition the data with new partition range as 4000. What do you think will happen? As split is Metadata operation initially I thought this won't create any impact with respect to Log Generation. However to my surprise it is not the case. As said above if we don't understand the concepts we will mess up the things so fast.
Well here is the proof for the same just run the below code
Alter partition scheme [Orders__Scheme] NEXT USED [DailyFG5]
GO
DECLARE @xact_id BIGINT
BEGIN TRAN
ALTER PARTITION FUNCTION Orders_ID_Function() SPLIT RANGE (4000)
SELECT @xact_id = transaction_id
FROM sys.dm_tran_current_transaction
COMMIT TRAN
SELECT [Current LSN], [Operation], [AllocUnitName], [Context]
, [Transaction ID] , [Transaction Name], [Xact ID]
FROM ::fn_dblog(NULL,NULL)
WHERE [Transaction ID] = (
SELECT TOP 1 [Transaction ID]
FROM ::fn_dblog(NULL,NULL)
WHERE [Xact ID] = @xact_id)
GO
if we see this is not metadata operation as the there are 1000 inserts and followed by deletes what does this mean? Well let's see the output of the partitioning.
if you observe carefully even though we have specified the next file group to be used as FG5 this has now become partition number 4.
When we split a partition with data on the left side of the new boundary point which is in our case the new partition would get created to left side of the existing partition hence it is advisable not to carry out this operation with Left Range partitioning.
Note: if you perform the same operation with range right there will not be any issues and the split operation will be Metadata operation.
Let us now see the impact of split operation in Right Range partitioning. Just change the above example to be Right
CREATE PARTITION FUNCTION [Orders_Id_Function](bigint) AS RANGE Right FOR VALUES
(1000,2000,3000)
Here is how the data looks initially
Now Imagine you need to add a new partition with range as 2500. When we split a partition with data on the right side of the new boundary point which is the case of ours then the new partition would get created on the right side of the existing partition.
Alter partition scheme [Orders__Scheme] NEXT USED [DailyFG5]
GO
DECLARE @xact_id BIGINT
BEGIN TRAN
ALTER PARTITION FUNCTION Orders_ID_Function() SPLIT RANGE (2500)
SELECT @xact_id = transaction_id
FROM sys.dm_tran_current_transaction
COMMIT TRAN
SELECT [Current LSN], [Operation], [AllocUnitName], [Context]
, [Transaction ID] , [Transaction Name], [Xact ID]
FROM ::fn_dblog(NULL,NULL)
WHERE [Transaction ID] = (
SELECT TOP 1 [Transaction ID]
FROM ::fn_dblog(NULL,NULL)
WHERE [Xact ID] = @xact_id)
GO
Again here we could see this not being Metadata operation.
if you see the above image now FG5 is now marked as partition 4.
So the key here is if you are doing split operation do consider if you are working with Range left or Right and then remember the concepts . If this is a big table then the log generation would be 4 times than that of normal.
if there is existing table with data sitting and you want to speed up this operation then you need to dump the data in to different table and then make use of switch in. Below is the link I would recommend you to make use of
I hope you learned something new and if you have any questions please feel free to comment.
W
Comments