In this post I have provided the link and script for the video demonstrating how Partitioning improves performance.
Video on how to use the script
The Script
USE MASTER
GO
-- THIS SCRIPT WILL TAKE TIME TO RUN , PLAN ACCORDINGLY.
-- CUP OF COFFEE IS OPTIONAL BUT HIGHLY RECOMMENDED.
---CREATE A DATABASE TO PLAY AROUND WITH
-- MAKE SURE THE HARD DISK HAS SUFFICIENT SPACE -- TYPICALLY 20GB
CREATE DATABASE partitions_demo
-- TO PREVENT TO LOG FROM BLOWING UP. MAKE SURE YOU TAKE A BACKUP TO INITIALIZE
ALTER DATABASE partitions_demo
SET RECOVERY SIMPLE
GO
-- CREATE SINGLE FILEROUP TO HOLD MULTIPLE FILES
ALTER DATABASE partitions_demo ADD FILEGROUP [secondary]
GO
-- ADD MULTIPLE FILES TO THE NEWLY CREATED FILEGROUP
-- PLEASE PAY ATTENTION TO THE FILE PATHS
-- NOT ALL DRIVES ARE CREATED EQUAL.
-- CHANGE AS NEEDED.
ALTER DATABASE partitions_demo ADD FILE (
name = one
,filename = 'G:\SQL\Data\one.ndf'
) TO filegroup [secondary]
ALTER DATABASE partitions_demo ADD FILE (
name = two
,filename = 'G:\SQL\Data\two.ndf'
) TO filegroup [secondary]
ALTER DATABASE partitions_demo ADD FILE (
name = three
,filename = 'G:\SQL\Data\three.ndf'
) TO filegroup [secondary]
ALTER DATABASE partitions_demo ADD FILE (
name = four
,filename = 'G:\SQL\Data\four.ndf'
) TO filegroup [secondary]
ALTER DATABASE partitions_demo ADD FILE (
name = five
,filename = 'G:\SQL\Data\five.ndf'
) TO filegroup [secondary]
ALTER DATABASE partitions_demo ADD FILE (
name = six
,filename = 'G:\SQL\Data\six.ndf'
) TO filegroup [secondary]
ALTER DATABASE partitions_demo ADD FILE (
name = seven
,filename = 'G:\SQL\Data\seven.ndf'
) TO filegroup [secondary]
GO
-- CREATE MULTIPLE FILEGROUPS TO IMPLEMENT PARTITIONING
ALTER DATABASE partitions_demo ADD filegroup [secondaryP1]
ALTER DATABASE partitions_demo ADD filegroup [secondaryP2]
ALTER DATABASE partitions_demo ADD filegroup [secondaryP3]
ALTER DATABASE partitions_demo ADD filegroup [secondaryP4]
ALTER DATABASE partitions_demo ADD filegroup [secondaryP5]
ALTER DATABASE partitions_demo ADD filegroup [secondaryP6]
ALTER DATABASE partitions_demo ADD filegroup [secondaryP7]
--- ADD ONE FILE TO EACH OF THE ABOVE FILEGROUPS
ALTER DATABASE partitions_demo ADD FILE (
name = Pone
,filename = 'G:\SQL\Data\Pone.ndf'
) TO filegroup [secondaryP1]
ALTER DATABASE partitions_demo ADD FILE (
name = Ptwo
,filename = 'G:\SQL\Data\Ptwo.ndf'
) TO filegroup [secondaryP2]
ALTER DATABASE partitions_demo ADD FILE (
name = Pthree
,filename = 'G:\SQL\Data\Pthree.ndf'
) TO filegroup [secondaryP3]
ALTER DATABASE partitions_demo ADD FILE (
name = Pfour
,filename = 'G:\SQL\Data\Pfour.ndf'
) TO filegroup [secondaryP4]
ALTER DATABASE partitions_demo ADD FILE (
name = Pfive
,filename = 'G:\SQL\Data\Pfive.ndf'
) TO filegroup [secondaryP5]
ALTER DATABASE partitions_demo ADD FILE (
name = Psix
,filename = 'G:\SQL\Data\Psix.ndf'
) TO filegroup [secondaryP6]
ALTER DATABASE partitions_demo ADD FILE (
name = Pseven
,filename = 'G:\SQL\Data\Pseven.ndf'
) TO filegroup [secondaryP7]
-- SWITCH DATABASE CONTEXT
USE partitions_demo
GO
-- CREATE A TABLE TO HOLD DATA IN PRIMARY FILEGROUP AND SINGLE FILE -- CASE 1
CREATE TABLE [dbo].[SingleFile] (
id INT identity(1, 1) PRIMARY KEY CLUSTERED
,[object_name] [varchar](128) NOT NULL
,[counter_name] [varchar](128) NOT NULL
,[instance_name] [varchar](128) NULL
,[cntr_value] [bigint] NOT NULL
,[cntr_type] [int] NOT NULL
) ON [PRIMARY]
GO
-- CREATE A TABLE TO HOLD DATA IN SECONDARY FILEGROUP AND MULTIPLE FILES -- CASE 2
CREATE TABLE [dbo].[MultipleFiles] (
id INT identity(1, 1) PRIMARY KEY CLUSTERED
,[object_name] [varchar](128) NOT NULL
,[counter_name] [varchar](128) NOT NULL
,[instance_name] [varchar](128) NULL
,[cntr_value] [bigint] NOT NULL
,[cntr_type] [int] NOT NULL
) ON [Secondary]
GO
-- INSERT DUMMY DATA INTO SINGLE FILE TABLE SO THAT WE CAN TEST
-- CHANGE BATCH COUNT TO INCREASE OR DECREASE THE NUMBER OF ROWS.
SET NOCOUNT ON
INSERT INTO [SingleFile]
SELECT TOP 100000 p.*
FROM sys.dm_os_performance_counters p
CROSS APPLY sys.sysobjects a GO 50
-- MODIFY DATA IN CNTR_TYPE COLUMN SO THAT WE CAN ALSO SCAN
--ACROSS RANGES OF DATA FOR OTHER EXAMPLES AND TESTING
-- THIS STEP IS OPTIONAL
UPDATE d
SET cntr_type = ranks
FROM (
SELECT *
,ntile(16) OVER (
ORDER BY id
) AS ranks
FROM SingleFile
) d
GO
--- INSERT DATA INTO TABLE THAT WILL BE PARTITIONED
SELECT *
INTO Partitioned
FROM [dbo].[SingleFile]
GO
-- START PARTITIONING THE ABOVE TABLE IN 7 GROUPS BECAUSE WE HAVE 7 FILES
DECLARE @partitions INT = (
SELECT count(*)
FROM SingleFile
)
-- CREATE EQUAL NUMBER OF ROWS IN EACH PARTITION
SET @partitions = round(@partitions / 7, 0)
DECLARE @SQL NVARCHAR(4000) = '
CREATE PARTITION FUNCTION [pfunction](int) AS RANGE RIGHT FOR VALUES (' + cast(@partitions AS VARCHAR(10)) + ',' + cast(@partitions * 2 AS VARCHAR(10)) + ',' + cast(@partitions * 3 AS VARCHAR(10)) + ',' + cast(@partitions * 4 AS VARCHAR(10)) + ',' + cast(@partitions * 5 AS VARCHAR(10)) + ',' + cast(@partitions * 6 AS VARCHAR(10)) + ',' + cast(@partitions * 7 AS VARCHAR(10)) + ')'
EXEC sp_executesql @sql
GO
-- MAP EACH PARTITION WITH A FILEGROUP CREATED EARLIER.
CREATE PARTITION SCHEME [pscheme] AS PARTITION [pfunction] TO (
[secondaryP1]
,[secondaryP2]
,[secondaryP3]
,[secondaryP4]
,[secondaryP5]
,[secondaryP6]
,[secondaryP7]
,[secondary]
)
GO
-- REARRANGE THE DATA IN THE TABLE INTO THE NEW PARTITIONS BY CREATING A CLUSTERED INDEX
CREATE CLUSTERED INDEX [CIX_id] ON [dbo].[Partitioned] ([id] 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 [pscheme]([id])
GO
-- INSERT DATA INTO MULTIPLE FILE TABLE AS WELL
SET IDENTITY_INSERT MultipleFiles ON
INSERT INTO MultipleFiles (
[id]
,[object_name]
,[counter_name]
,[instance_name]
,[cntr_value]
,[cntr_type]
)
SELECT [id]
,[object_name]
,[counter_name]
,[instance_name]
,[cntr_value]
,[cntr_type]
FROM SingleFile
SET IDENTITY_INSERT MultipleFiles OFF
GO
--- TEST TABLE PERFORMANCE AND NOTICE HOW PARTITIONING
-- DOESNT OR ONLY MARGINALLY IMPROVES PERFORMANCE
DECLARE @table TABLE (
cntr_types VARCHAR(100)
,counts INT
,source VARCHAR(100)
)
DECLARE @int INT = round(rand() * 1000000, 0)
DECLARE @int2 INT = @int + round(rand() * 10000, 0)
DECLARE @starttime DATETIME2 = getdate()
INSERT INTO @table
SELECT cntr_type
,count(*)
,'Part'
FROM Partitioned
WHERE id BETWEEN @int
AND @int2
GROUP BY cntr_type
SELECT datediff(ms, @starttime, getdate()) AS 'Partitions'
PRINT '*******************************Without Partitioning****************************'
DECLARE @starttime2 DATETIME2 = getdate()
INSERT INTO @table
SELECT cntr_type
,count(*)
,'Single'
FROM MultipleFiles
WHERE id BETWEEN @int
AND @int2
GROUP BY cntr_type
SELECT datediff(ms, @starttime2, getdate()) AS 'Single File'
PRINT '******************************* After Single File****************************'
DECLARE @starttime3 DATETIME2 = getdate()
INSERT INTO @table
SELECT cntr_type
,count(*)
,'Multiple'
FROM SingleFile
WHERE id BETWEEN @int
AND @int2
GROUP BY cntr_type
SELECT datediff(ms, @starttime3, getdate()) AS 'Multiple Files'
SELECT *
FROM @table
USE [partitions_demo]
GO
---- CREATE THE RIGHT INDEXES TO SUPPORT THE ABOVE QUERIES
CREATE NONCLUSTERED INDEX [NCIX_cntr_type] ON [dbo].[Partitioned] ([cntr_type] ASC)
GO
CREATE NONCLUSTERED INDEX [NCIX_cntr_type] ON [dbo].[SingleFile] ([cntr_type] ASC)
GO
CREATE NONCLUSTERED INDEX [NCIX_cntr_type] ON [dbo].[MultipleFiles] ([cntr_type] ASC)
GO
--- RERUN THE QUERIES AGAIN
--- TEST PERFORMANCE AND NOTICE HOW PERFORMANCE HAS INCREASED NOW THAT PARTITION
-- ELIMINATION HAS COME INTO PLAY
DECLARE @table TABLE (
cntr_types VARCHAR(100)
,counts INT
,source VARCHAR(100)
)
DECLARE @int INT = round(rand() * 1000000, 0)
DECLARE @int2 INT = @int + round(rand() * 10000, 0)
DECLARE @starttime DATETIME2 = getdate()
INSERT INTO @table
SELECT cntr_type
,count(*)
,'Part'
FROM Partitioned
WHERE id BETWEEN @int
AND @int2
GROUP BY cntr_type
SELECT datediff(ms, @starttime, getdate()) AS 'Partitions'
PRINT '*******************************Without Partitioning****************************'
DECLARE @starttime2 DATETIME2 = getdate()
INSERT INTO @table
SELECT cntr_type
,count(*)
,'Single'
FROM MultipleFiles
WHERE id BETWEEN @int
AND @int2
GROUP BY cntr_type
SELECT datediff(ms, @starttime2, getdate()) AS 'Single File'
PRINT '******************************* After Single File****************************'
DECLARE @starttime3 DATETIME2 = getdate()
INSERT INTO @table
SELECT cntr_type
,count(*)
,'Multiple'
FROM SingleFile
WHERE id BETWEEN @int
AND @int2
GROUP BY cntr_type
SELECT datediff(ms, @starttime3, getdate()) AS 'Multiple Files'
SELECT *
FROM @table
GO
--- REPEAT ON SERVER WITH MULTIPLE DRIVES.
-- CHECK LOGICAL AND PHYSICAL READS
-- CHECK THE EXECUTION PLAN
-- BE NICE TO YOUR DBAPlease Consider Subscribing
