Table Partitioning Bug – SWITCH OUT / MERGE RANGE

  • Paul Brewer

    SSCrazy

    Points: 2827

    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 post 1 (of 1 total)

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