March 6, 2014 at 12:38 pm
If the partitioning MERGE command attempts to drop historic data at the wrong boundary point then data movement between file groups may be necessary before or during the next index rebuild. The script below creates 2 test tables, one using a range right function and the other using range left. The partitioning key is a number between 0 - 59, an empty partition is maintained at the start and end of ranges, 4 partitions contain data in the ranges between 0-14, 15-29, 30-44, 45-59. Data in the lowest range (0 - 14) is switched out and a merge command is run, edit the script to try the different merge boundaries, edit the variables at the start to suit runtime environment 'Data Drive' & 'Log Drive' paths.Variables are redeclared but commented out at the start of code blocks to allow stepping through if desired.
--=================================================================================
-- PartitionLabSetup_20140330.sql - TAKES ABOUT 1 MINUTE TO EXECUTE
-- Creates a test database (workspace)
-- Adds file groups and files
-- Creates partition functions and schema's
-- Creates and populates 2 partitioned tables (PartitionedRight & PartitionedLeft)
-- Creates target tables for switching out
-- Performs SWITCH & MERGE RANGE
--=================================================================================
USE [master]
GO
-----------------------------------------------------------------------------------
-- Create Test Database
-----------------------------------------------------------------------------------
:SETVAR DatabaseName "workspace"
:SETVAR TableNameRight "PartitionedRight"
:SETVAR TableNameLeft "PartitionedLeft"
:SETVAR DataDrive "D:\SQL\Data\"
:SETVAR LogDrive "D:\SQL\Logs\"
-- Drop if exists and create Database
IF DATABASEPROPERTYEX(N'$(databasename)','Status') IS NOT NULL
BEGIN
ALTER DATABASE $(DatabaseName) SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE $(DatabaseName)
END
GO
CREATE DATABASE $(DatabaseName)
ON
( NAME = $(DatabaseName)_data,
FILENAME = N'$(DataDrive)$(DatabaseName)_data.mdf',
SIZE = 10,
MAXSIZE = 500,
FILEGROWTH = 5 )
LOG ON
( NAME = $(DatabaseName)_log,
FILENAME = N'$(LogDrive)$(DatabaseName).ldf',
SIZE = 5MB,
MAXSIZE = 5000MB,
FILEGROWTH = 5MB ) ;
GO
DECLARE @msg varchar(200) = 'Run Status';
SET @msg = 'Database $(DatabaseName) has been created';
RAISERROR (@msg,0,0);
-----------------------------------------------------------------------------------
-- Add file groups and files
-----------------------------------------------------------------------------------
--:SETVAR DatabaseName "workspace"
--:SETVAR TableNameRight "PartitionedRight"
--:SETVAR TableNameLeft "PartitionedLeft"
--:SETVAR DataDrive "E:\SQL12\Data\"
--:SETVAR LogDrive "E:\SQL12\Logs\"
DECLARE @nSQL NVARCHAR(2000) ;
DECLARE @x INT = 1;
-- Range Right
WHILE @x <= 6
BEGIN
SELECT @nSQL =
'ALTER DATABASE $(DatabaseName)
ADD FILEGROUP $(TableNameRight)_fg' + RTRIM(CAST(@x AS CHAR(5))) + ';
ALTER DATABASE $(DatabaseName)
ADD FILE
(
NAME= ''$(TableNameRight)_f' + CAST(@x AS CHAR(5)) + ''',
FILENAME = ''$(DataDrive)\$(TableNameRight)_f' + RTRIM(CAST(@x AS CHAR(5))) + '.ndf''
)
TO FILEGROUP $(TableNameRight)_fg' + RTRIM(CAST(@x AS CHAR(5))) + ';'
EXEC sp_executeSQL @nSQL;
SET @x = @x + 1;
END
-- Range Left
SET @x = 1
WHILE @x <= 6
BEGIN
SELECT @nSQL =
'ALTER DATABASE $(DatabaseName)
ADD FILEGROUP $(TableNameLeft)_fg' + RTRIM(CAST(@x AS CHAR(5))) + ';
ALTER DATABASE $(DatabaseName)
ADD FILE
(
NAME= ''$(TableNameLeft)_f' + CAST(@x AS CHAR(5)) + ''',
FILENAME = ''$(DataDrive)\$(TableNameLeft)_f' + RTRIM(CAST(@x AS CHAR(5))) + '.ndf''
)
TO FILEGROUP $(TableNameLeft)_fg' + RTRIM(CAST(@x AS CHAR(5))) + ';'
EXEC sp_executeSQL @nSQL;
SET @x = @x + 1;
END
SET @msg = 'File Groups have been created';
RAISERROR (@msg,0,0);
-----------------------------------------------------------------------------------
-- Create partition functions and schemes
-----------------------------------------------------------------------------------
--:SETVAR DatabaseName "workspace"
--:SETVAR TableNameRight "PartitionedRight"
--:SETVAR TableNameLeft "PartitionedLeft"
--:SETVAR DataDrive "E:\SQL12\Data\"
--:SETVAR LogDrive "E:\SQL12\Logs\"
USE $(DatabaseName);
CREATE PARTITION FUNCTION $(TableNameRight)_func (int)
AS RANGE RIGHT FOR VALUES
(
0,
15,
30,
45,
60
);
CREATE PARTITION FUNCTION $(TableNameLeft)_func (int)
AS RANGE LEFT FOR VALUES
(
-1,
14,
29,
44,
59
);
CREATE PARTITION SCHEME $(TableNameRight)_scheme
AS
PARTITION $(TableNameRight)_func
TO
(
$(TableNameRight)_fg1,
$(TableNameRight)_fg2,
$(TableNameRight)_fg3,
$(TableNameRight)_fg4,
$(TableNameRight)_fg5,
$(TableNameRight)_fg6
);
CREATE PARTITION SCHEME $(TableNameLeft)_scheme
AS
PARTITION $(TableNameLeft)_func
TO
(
$(TableNameLeft)_fg1,
$(TableNameLeft)_fg2,
$(TableNameLeft)_fg3,
$(TableNameLeft)_fg4,
$(TableNameLeft)_fg5,
$(TableNameLeft)_fg6
);
SET @msg = 'Functions and scheme have been created';
RAISERROR (@msg,0,0);
-----------------------------------------------------------------------------------
-- Creates and populates test tables
-----------------------------------------------------------------------------------
--:SETVAR DatabaseName "workspace"
--:SETVAR TableNameRight "PartitionedRight"
--:SETVAR TableNameLeft "PartitionedLeft"
--:SETVAR DataDrive "E:\SQL12\Data\"
--:SETVAR LogDrive "E:\SQL12\Logs\"
-- Range Right
CREATE TABLE [dbo].$(TableNameRight)(
[Partition_PK] [int] NOT NULL,
[GUID_PK] [uniqueidentifier] NOT NULL,
[CreateDate] [datetime] NULL,
[CreateServer] [nvarchar](50) NULL,
[RandomNbr] [int] NULL,
CONSTRAINT [PK_$(TableNameRight)] PRIMARY KEY CLUSTERED
(
[Partition_PK] ASC,
[GUID_PK] ASC
) ON $(TableNameRight)_scheme(Partition_PK)
) ON $(TableNameRight)_scheme(Partition_PK);
ALTER TABLE [dbo].$(TableNameRight) ADD CONSTRAINT [DF_$(TableNameRight)_GUID_PK] DEFAULT (newid()) FOR [GUID_PK]
ALTER TABLE [dbo].$(TableNameRight) ADD CONSTRAINT [DF_$(TableNameRight)_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
ALTER TABLE [dbo].$(TableNameRight) ADD CONSTRAINT [DF_$(TableNameRight)_CreateServer] DEFAULT (@@servername) FOR [CreateServer]
-- Range Left
CREATE TABLE [dbo].$(TableNameLeft)(
[Partition_PK] [int] NOT NULL,
[GUID_PK] [uniqueidentifier] NOT NULL,
[CreateDate] [datetime] NULL,
[CreateServer] [nvarchar](50) NULL,
[RandomNbr] [int] NULL,
CONSTRAINT [PK_$(TableNameLeft)] PRIMARY KEY CLUSTERED
(
[Partition_PK] ASC,
[GUID_PK] ASC
) ON $(TableNameLeft)_scheme(Partition_PK)
) ON $(TableNameLeft)_scheme(Partition_PK);
ALTER TABLE [dbo].$(TableNameLeft) ADD CONSTRAINT [DF_$(TableNameLeft)_GUID_PK] DEFAULT (newid()) FOR [GUID_PK]
ALTER TABLE [dbo].$(TableNameLeft) ADD CONSTRAINT [DF_$(TableNameLeft)_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
ALTER TABLE [dbo].$(TableNameLeft) ADD CONSTRAINT [DF_$(TableNameLeft)_CreateServer] DEFAULT (@@servername) FOR [CreateServer]
-- Populate table with test data
SET NOCOUNT ON;
DECLARE @Now DATETIME = GETDATE()
WHILE @Now > DATEADD(minute,-1,GETDATE())
BEGIN
INSERT INTO [dbo].$(TableNameRight)
([Partition_PK]
,[RandomNbr])
VALUES
(
DATEPART(second,GETDATE())
,ROUND((RAND() * 100),0)
)
END
INSERT INTO [dbo].$(TableNameLeft)
SELECT * FROM [dbo].$(TableNameRight);
SET @msg = 'Test tables and data has been created';
RAISERROR (@msg,0,0);
-----------------------------------------------------------------------------------
-- Target tables for SWITCH of old archive data.
--:SETVAR DatabaseName "workspace"
--:SETVAR TableNameRight "PartitionedRight"
--:SETVAR TableNameLeft "PartitionedLeft"
--:SETVAR DataDrive "E:\SQL12\Data\"
--:SETVAR LogDrive "E:\SQL12\Logs\"
IF OBJECT_ID('dbo.$(TableNameRight)Out') IS NOT NULL
DROP TABLE [dbo].[$(TableNameRight)Out]
CREATE TABLE [dbo].[$(TableNameRight)Out](
[Partition_PK] [int] NOT NULL,
[GUID_PK] [uniqueidentifier] NOT NULL,
[CreateDate] [datetime] NULL,
[CreateServer] [nvarchar](50) NULL,
[RandomNbr] [int] NULL,
CONSTRAINT [PK_$(TableNameRight)Out] PRIMARY KEY CLUSTERED
(
[Partition_PK] ASC,
[GUID_PK] ASC
)
) ON $(TableNameRight)_fg2;
IF OBJECT_ID('dbo.$(TableNameLeft)Out') IS NOT NULL
DROP TABLE [dbo].[$(TableNameLeft)Out]
CREATE TABLE [dbo].[$(TableNameLeft)Out](
[Partition_PK] [int] NOT NULL,
[GUID_PK] [uniqueidentifier] NOT NULL,
[CreateDate] [datetime] NULL,
[CreateServer] [nvarchar](50) NULL,
[RandomNbr] [int] NULL,
CONSTRAINT [PK_$(TableNameLeft)Out] PRIMARY KEY CLUSTERED
(
[Partition_PK] ASC,
[GUID_PK] ASC
)
) ON $(TableNameLeft)_fg2;
SET @msg = 'Switch out tables have been created';
RAISERROR (@msg,0,0);
-- Adapted from source http://davidpeterhansen.com/view-partitions-in-sql-server/
SELECT
GETDATE() AS CaptureDate
,fg.name AS FileGroupName
,fg.data_space_id
,df.name AS DataFileName
,df.physical_name
,x1.*
FROM sys.filegroups fg
INNER JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT OUTER JOIN
(
SELECT
o.name ObjectName
,s.name AS SchemeName
,p.partition_number PartitionNumber
,p.rows
,i.name IndexName
,au.total_pages
,au.used_pages
,CASE au.first_page
WHEN NULL THEN ''
ELSE CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
SUBSTRING (au.first_page, 5, 1))) + ':' + CONVERT (VARCHAR(20),
CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +
SUBSTRING (au.first_page, 1, 1)))
END AS first_page
,ps.name PartitionSchemeName
,pf.name PartitionFunctionName
,pf.boundary_value_on_right
,rv.value BoundaryValue
,dds.data_space_id AS destination_data_space_id
FROM sys.partitions p
INNER JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.objects o
ON p.object_id = o.object_id
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
INNER JOIN sys.system_internals_allocation_units au
ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values rv
ON pf.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
WHERE i.index_id < 2
AND p.index_id < 2
) x1
ON x1.destination_data_space_id = fg.data_space_id
ORDER BY x1.SchemeName, x1.ObjectName, PartitionNumber
-----------------------------------------------------------------------------------
-- Switch out partition and merge range
--:SETVAR DatabaseName "workspace"
--:SETVAR TableNameRight "PartitionedRight"
--:SETVAR TableNameLeft "PartitionedLeft"
--:SETVAR DataDrive "E:\SQL12\Data\"
--:SETVAR LogDrive "E:\SQL12\Logs\"
----------------------------------------------------------------------------------
-- SS01
ALTER TABLE dbo.$(TableNameRight)
SWITCH PARTITION 2 TO dbo.$(TableNameRight)Out;
ALTER TABLE dbo.$(TableNameLeft)
SWITCH PARTITION 2 TO dbo.$(TableNameLeft)Out;
-- Adapted from source http://davidpeterhansen.com/view-partitions-in-sql-server/
SELECT
GETDATE() AS CaptureDate
,fg.name AS FileGroupName
,fg.data_space_id
,df.name AS DataFileName
,df.physical_name
,x1.*
FROM sys.filegroups fg
INNER JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT OUTER JOIN
(
SELECT
o.name ObjectName
,s.name AS SchemeName
,p.partition_number PartitionNumber
,p.rows
,i.name IndexName
,au.total_pages
,au.used_pages
,CASE au.first_page
WHEN NULL THEN ''
ELSE CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
SUBSTRING (au.first_page, 5, 1))) + ':' + CONVERT (VARCHAR(20),
CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +
SUBSTRING (au.first_page, 1, 1)))
END AS first_page
,ps.name PartitionSchemeName
,pf.name PartitionFunctionName
,pf.boundary_value_on_right
,rv.value BoundaryValue
,dds.data_space_id AS destination_data_space_id
FROM sys.partitions p
INNER JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.objects o
ON p.object_id = o.object_id
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
INNER JOIN sys.system_internals_allocation_units au
ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values rv
ON pf.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
WHERE i.index_id < 2
AND p.index_id < 2
) x1
ON x1.destination_data_space_id = fg.data_space_id
ORDER BY x1.SchemeName, x1.ObjectName, PartitionNumber
----------------------------------------------------------------------------------
-- TEST GOOD AND BAD BOUNDARY POINT
-- SS02
ALTER PARTITION FUNCTION $(TableNameRight)_func()
MERGE RANGE (15); -- 0 is correct point, 15 is incorrect
ALTER PARTITION FUNCTION $(TableNameLeft)_func()
MERGE RANGE (14); -- -1 is correct point, 14 is incorrect
-- Final State
-- Adapted from source http://davidpeterhansen.com/view-partitions-in-sql-server/
SELECT
GETDATE() AS CaptureDate
,fg.name AS FileGroupName
,fg.data_space_id
,df.name AS DataFileName
,df.physical_name
,x1.*
FROM sys.filegroups fg
INNER JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT OUTER JOIN
(
SELECT
o.name ObjectName
,s.name AS SchemeName
,p.partition_number PartitionNumber
,p.rows
,i.name IndexName
,au.total_pages
,au.used_pages
,CASE au.first_page
WHEN NULL THEN ''
ELSE CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) +
SUBSTRING (au.first_page, 5, 1))) + ':' + CONVERT (VARCHAR(20),
CONVERT (INT, SUBSTRING (au.first_page, 4, 1) +
SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) +
SUBSTRING (au.first_page, 1, 1)))
END AS first_page
,ps.name PartitionSchemeName
,pf.name PartitionFunctionName
,pf.boundary_value_on_right
,rv.value BoundaryValue
,dds.data_space_id AS destination_data_space_id
FROM sys.partitions p
INNER JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.objects o
ON p.object_id = o.object_id
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
INNER JOIN sys.system_internals_allocation_units au
ON p.partition_id = au.container_id
INNER JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf
ON pf.function_id = ps.function_id
INNER JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values rv
ON pf.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
WHERE i.index_id < 2
AND p.index_id < 2
) x1
ON x1.destination_data_space_id = fg.data_space_id
ORDER BY x1.SchemeName, x1.ObjectName, PartitionNumber
----------------------------------------------------------------------------------
SET @msg = 'Switch out and merge range has completed';
RAISERROR (@msg,0,0);
-----------------------------------------------------------------------------------
The T-SQL code below illustrates one of the problems caused by MERGE at the wrong boundary point. File Group 3 of the Range Right table is empty according to the data space views, it cannot be dropped though. File Group 2 contains data according to the views but you are allowed to drop it's file.
USE workspace;
DROP TABLE dbo.PartitionedRightOut;
USE master;
ALTER DATABASE workspace
REMOVE FILE PartitionedRight_f3 ;
--Msg 5042, Level 16, State 1, Line 2
--The file 'PartitionedRight_f3 ' cannot be removed because it is not empty.
ALTER DATABASE workspace
REMOVE FILE PartitionedRight_f2 ;
-- Works surprisingly although contains data according to system views.
If the wrong boundary point is used then the system 'Data Space' views show where the data should be (FG2), not where it actually still is (FG3). You can't tell if data movement between file groups is pending and the file group files are not protected from deletion by the OS.
I'm not sure this is worth raising a connect item for but it would be useful knowing where data physically resided after a MERGE RANGE and before an INDEX REBUILD, the data space views reflect the logical rather than the physical location if a data movement is pending.
I read articles and watched videos here - http://www.brentozar.com/archive/2013/01/sql-server-table-partitioning-tutorial-videos-and-scripts/ and read this http://www.sqlskills.com/blogs/kimberly/clarifying-left-and-right-in-the-defintion-of-a-partition-function-in-sql-server-2005/ , both are far superior to this post which is from a learner. Chris and Manuj also explained and helped a lot.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply