PartitionLoading.sql

SELECT @@VERSION
--Microsoft SQL Server 2014 (SP1-CU3) (KB3094221) - 12.0.4427.24 (X64) 
--	Oct 10 2015 17:18:26 
--	Copyright (c) Microsoft Corporation
--	Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
GO

-----------------------------------------------------------------------------
-- Create test data
-----------------------------------------------------------------------------

SET STATISTICS TIME, IO ON
GO

WITH thousandRows AS (
	SELECT TOP 1000 1 x
	FROM master..spt_values
),
lotsOfRows AS (
	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rn,
		CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS VARBINARY) AS binary_rn
	FROM thousandRows t1
	CROSS JOIN thousandRows t2
	CROSS JOIN thousandRows t3
)
SELECT TOP 100000000
	CONVERT(BIGINT, CRYPT_GEN_RANDOM(8, binary_rn) % 1000000) AS [Col1],
	CONVERT(INT, CRYPT_GEN_RANDOM(8, binary_rn) % 123456) AS [Col2],
	CONVERT(INT, CRYPT_GEN_RANDOM(8, binary_rn) % 100) AS [Col3],
	CONVERT(INT, CRYPT_GEN_RANDOM(8, binary_rn) % 31415) AS [Col4],
	CONVERT(INT, CRYPT_GEN_RANDOM(8, binary_rn) % 27178) AS [Col5],
	CONVERT(MONEY, CRYPT_GEN_RANDOM(8, binary_rn) % 2468) AS [Col6],
	CONVERT(DATETIME, DATEADD(DAY, ABS(CRYPT_GEN_RANDOM(8, binary_rn) % 400), '6/1/2015')) AS [Modified]
INTO [dbo].[AuditTable]
FROM lotsOfRows
GO

CREATE CLUSTERED INDEX [AuditTable_Modified] ON [dbo].[AuditTable]
(   [Modified] ASC   )
GO

CREATE PARTITION FUNCTION ThirteenMonthPartFunction (datetime)
AS RANGE RIGHT FOR VALUES ('20150701', '20150801', '20150901', '20151001', '20151101', '20151201', 
                            '20160101', '20160201', '20160301', '20160401', '20160501', '20160601', 
                            '20160701') 
GO

CREATE PARTITION SCHEME ThirteenMonthPartScheme AS PARTITION ThirteenMonthPartFunction
ALL TO ( [PRIMARY] );
GO

-----------------------------------------------------------------------------
-- Insert into partitioned table
-- Total time: 729s
-----------------------------------------------------------------------------

CREATE TABLE [dbo].[AuditTable_New_PartitionedFirst](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
)
GO

CREATE CLUSTERED INDEX [AuditTable_Modified] ON [dbo].[AuditTable_New_PartitionedFirst]
(
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO

CREATE NONCLUSTERED INDEX [AuditTable_Col1_Col2_Col3_Col4_Modified] ON [dbo].[AuditTable_New_PartitionedFirst]
(
    [Col1] ASC,
    [Col2] ASC,
    [Col3] ASC,
    [Col4] ASC,
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO

DBCC TRACEON(610);
GO
DBCC TRACESTATUS
--TraceFlag	Status	Global	Session
--610	1	0	1
GO

-- CPU time = 552134 ms,  elapsed time = 729810 ms.
-- 194,183,543 log writes, 26,592,279 kB log data, according to sp_whoisactive
INSERT INTO [AuditTable_New_PartitionedFirst] WITH (TABLOCK) ([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Modified])
SELECT [Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Modified]
FROM dbo.AuditTable
WHERE Modified >= '6/1/2015'
GO

DBCC TRACEOFF(610);
GO

-----------------------------------------------------------------------------
-- Insert into heap, then add partitioned indexes
-- Total time: 284s with TABLOCK hint, 587s without
-----------------------------------------------------------------------------

CREATE TABLE [dbo].[AuditTable_New](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
)
GO

-- without TABLOCK	CPU time = 111042 ms,  elapsed time = 347803 ms.
	-- 99,396,278 log writes, 13,099,387 kB log data, according to sp_whoisactive
-- WITH TABLOCK		CPU time = 43026 ms,  elapsed time = 43935 ms.
	-- 828,976 log writes, 4,887,139 kB log data, according to sp_whoisactive
INSERT INTO [AuditTable_New] WITH (TABLOCK) ([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Modified])
SELECT [Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Modified]
FROM dbo.AuditTable
WHERE Modified >= '6/1/2015'
GO

-- CPU time = 143146 ms,  elapsed time = 118892 ms.
CREATE CLUSTERED INDEX [AuditTable_Modified] ON [dbo].[AuditTable_New]
(
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO

-- CPU time = 306181 ms,  elapsed time = 121499 ms.
CREATE NONCLUSTERED INDEX [AuditTable_Col1_Col2_Col3_Col4_Modified] ON [dbo].[AuditTable_New]
(
    [Col1] ASC,
    [Col2] ASC,
    [Col3] ASC,
    [Col4] ASC,
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO



-----------------------------------------------------------------------------
-- Insert into heap, one new partition at a time, then switch into final table
-- Total time: ~12s per partition, for a total of 156s if run serially
-- However, this will be substantially faster on most servers if you run multiple of these partitions in parallel
-----------------------------------------------------------------------------

CREATE TABLE [dbo].[AuditTable_New_PartitionSwitch](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
)
GO

CREATE CLUSTERED INDEX [AuditTable_Modified] ON [dbo].[AuditTable_New_PartitionSwitch]
(
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO

CREATE NONCLUSTERED INDEX [AuditTable_Col1_Col2_Col3_Col4_Modified] ON [dbo].[AuditTable_New_PartitionSwitch]
(
    [Col1] ASC,
    [Col2] ASC,
    [Col3] ASC,
    [Col4] ASC,
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO


-- Test the first partition; ultimately, you would need to script this out so that all 13 (or however many) partitions are processed
CREATE TABLE [dbo].[AuditTable_New_PartitionSwitch_Partition1](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
)
GO

-- CPU time = 3417 ms,  elapsed time = 3691 ms.
-- 58,258 log writes, 343,103 kB log data, according to sp_whoisactive
INSERT INTO [AuditTable_New_PartitionSwitch_Partition1] WITH (TABLOCK) ([Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Modified])
SELECT [Col1],[Col2],[Col3],[Col4],[Col5],[Col6],[Modified]
FROM dbo.AuditTable
WHERE Modified >= '6/1/2015'
	AND Modified < '7/1/2015'
GO

-- CPU time = 10249 ms,  elapsed time = 3080 ms.
CREATE CLUSTERED INDEX [AuditTable_Modified] ON [dbo].[AuditTable_New_PartitionSwitch_Partition1]
(
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

-- CPU time = 25180 ms,  elapsed time = 4122 ms.
CREATE NONCLUSTERED INDEX [AuditTable_Col1_Col2_Col3_Col4_Modified] ON [dbo].[AuditTable_New_PartitionSwitch_Partition1]
(
    [Col1] ASC,
    [Col2] ASC,
    [Col3] ASC,
    [Col4] ASC,
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

-- CPU time = 1294 ms,  elapsed time = 1297 ms.
ALTER TABLE [dbo].[AuditTable_New_PartitionSwitch_Partition1]
ADD CONSTRAINT [CK_AuditTable_New_PartitionSwitch_Partition1_partition]
CHECK ([Modified] >= '6/1/2015' AND [Modified] < '7/1/2015')
GO

-- CPU time = 0 ms,  elapsed time = 0 ms.
ALTER TABLE [AuditTable_New_PartitionSwitch_Partition1]
SWITCH TO [AuditTable_New_PartitionSwitch] PARTITION 1
GO



-----------------------------------------------------------------------------
-- Cleanup / start over
-----------------------------------------------------------------------------

DROP TABLE [AuditTable]
GO
DROP TABLE [AuditTable_New_PartitionedFirst]
GO
DROP TABLE [AuditTable_New]
GO
DROP TABLE [AuditTable_New_PartitionSwitch]
GO
DROP TABLE [AuditTable_New_PartitionSwitch_Partition1]
GO
DROP PARTITION SCHEME ThirteenMonthPartScheme
GO
DROP PARTITION FUNCTION ThirteenMonthPartFunction
GO