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
GO
-- =========================================================================================================
-- SQL Server Partitioning Demo Script
-- This script demonstrates how to create a partitioned table and compare its performance
-- with tables stored in single and multiple filegroups.
-- NOTE: This script is for demonstration purposes. Review and adjust file paths and settings as needed.
-- =========================================================================================================
-- IMPORTANT:
-- - This script will take time to run. Plan accordingly.
-- - Ensure the disk has sufficient space (at least 20GB recommended).
-- - Change file paths to match your environment.
-- - Run as a sysadmin or with sufficient privileges.
-- 1. Create Demo Database
IF DB_ID('partitions_demo') IS NOT NULL
BEGIN
ALTER DATABASE partitions_demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE partitions_demo;
END
GO
CREATE DATABASE partitions_demo
GO
-- Set recovery model to SIMPLE to prevent log growth during demo
ALTER DATABASE partitions_demo SET RECOVERY SIMPLE
GO
-- 2. Add Filegroups and Files
-- Add a secondary filegroup for multiple files
ALTER DATABASE partitions_demo ADD FILEGROUP [secondary]
GO
-- Add multiple files to the secondary filegroup
-- NOTE: Update file paths as needed for your environment
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
-- Add filegroups for 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]
GO
-- Add one file to each partition filegroup
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]
GO
-- 3. Switch to Demo Database
USE partitions_demo
GO
-- 4. Create Tables
-- Table in PRIMARY filegroup (single file)
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
-- Table in SECONDARY filegroup (multiple files)
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
-- 5. Insert Sample Data
-- Insert dummy data into SingleFile table
-- Adjust TOP and batch count as needed for your environment
SET NOCOUNT ON
INSERT INTO [SingleFile]
SELECT TOP 100000 p.[object_name], p.[counter_name], p.[instance_name], p.[cntr_value], p.[cntr_type]
FROM sys.dm_os_performance_counters p
CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) a
-- The above CROSS JOIN increases row count for demo purposes
GO
-- Optional: Update cntr_type for partitioning demonstration
UPDATE d
SET cntr_type = ranks
FROM (
SELECT *, NTILE(16) OVER (ORDER BY id) AS ranks
FROM SingleFile
) d
GO
-- Copy data to Partitioned table for partitioning
SELECT *
INTO Partitioned
FROM [dbo].[SingleFile]
GO
-- 6. Partitioning Setup
-- Calculate partition boundaries
DECLARE @rowcount INT = (SELECT COUNT(*) FROM SingleFile)
DECLARE @partitionsize INT = CEILING(1.0 * @rowcount / 7)
DECLARE @sql NVARCHAR(MAX)
-- Drop partition function and scheme if they exist (for reruns)
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'pscheme')
DROP PARTITION SCHEME pscheme
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'pfunction')
DROP PARTITION FUNCTION pfunction
GO
-- Create partition function
SET @sql = N'
CREATE PARTITION FUNCTION [pfunction](int) AS RANGE RIGHT FOR VALUES (' +
CAST(@partitionsize AS VARCHAR(10)) + ',' +
CAST(@partitionsize * 2 AS VARCHAR(10)) + ',' +
CAST(@partitionsize * 3 AS VARCHAR(10)) + ',' +
CAST(@partitionsize * 4 AS VARCHAR(10)) + ',' +
CAST(@partitionsize * 5 AS VARCHAR(10)) + ',' +
CAST(@partitionsize * 6 AS VARCHAR(10)) + ',' +
CAST(@partitionsize * 7 AS VARCHAR(10)) + ')'
EXEC sp_executesql @sql
GO
-- Create partition scheme
CREATE PARTITION SCHEME [pscheme] AS PARTITION [pfunction] TO (
[secondaryP1], [secondaryP2], [secondaryP3], [secondaryP4], [secondaryP5], [secondaryP6], [secondaryP7], [secondary]
)
GO
-- Move Partitioned table to partition scheme 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 MultipleFiles table
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
-- 7. Performance Test: Before Indexes
PRINT '==================== Performance Test: Before Indexes ===================='
DECLARE @table TABLE (cntr_types VARCHAR(100), counts INT, source VARCHAR(100))
DECLARE @int INT = ABS(CHECKSUM(NEWID())) % 1000000
DECLARE @int2 INT = @int + (ABS(CHECKSUM(NEWID())) % 10000)
DECLARE @starttime DATETIME2 = SYSDATETIME()
INSERT INTO @table
SELECT cntr_type, COUNT(*), 'Part'
FROM Partitioned
WHERE id BETWEEN @int AND @int2
GROUP BY cntr_type
SELECT DATEDIFF(ms, @starttime, SYSDATETIME()) AS [Partitions]
PRINT '******************************* Without Partitioning ****************************'
SET @starttime = SYSDATETIME()
INSERT INTO @table
SELECT cntr_type, COUNT(*), 'Single'
FROM MultipleFiles
WHERE id BETWEEN @int AND @int2
GROUP BY cntr_type
SELECT DATEDIFF(ms, @starttime, SYSDATETIME()) AS [Single File]
PRINT '******************************* After Single File ****************************'
SET @starttime = SYSDATETIME()
INSERT INTO @table
SELECT cntr_type, COUNT(*), 'Multiple'
FROM SingleFile
WHERE id BETWEEN @int AND @int2
GROUP BY cntr_type
SELECT DATEDIFF(ms, @starttime, SYSDATETIME()) AS [Multiple Files]
SELECT * FROM @table
GO
-- 8. Create Indexes to Support 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
-- 9. Performance Test: After Indexes
PRINT '==================== Performance Test: After Indexes ===================='
DECLARE @table2 TABLE (cntr_types VARCHAR(100), counts INT, source VARCHAR(100))
DECLARE @int3 INT = ABS(CHECKSUM(NEWID())) % 1000000
DECLARE @int4 INT = @int3 + (ABS(CHECKSUM(NEWID())) % 10000)
DECLARE @starttime2 DATETIME2 = SYSDATETIME()
INSERT INTO @table2
SELECT cntr_type, COUNT(*), 'Part'
FROM Partitioned
WHERE id BETWEEN @int3 AND @int4
GROUP BY cntr_type
SELECT DATEDIFF(ms, @starttime2, SYSDATETIME()) AS [Partitions]
PRINT '******************************* Without Partitioning ****************************'
SET @starttime2 = SYSDATETIME()
INSERT INTO @table2
SELECT cntr_type, COUNT(*), 'Single'
FROM MultipleFiles
WHERE id BETWEEN @int3 AND @int4
GROUP BY cntr_type
SELECT DATEDIFF(ms, @starttime2, SYSDATETIME()) AS [Single File]
PRINT '******************************* After Single File ****************************'
SET @starttime2 = SYSDATETIME()
INSERT INTO @table2
SELECT cntr_type, COUNT(*), 'Multiple'
FROM SingleFile
WHERE id BETWEEN @int3 AND @int4
GROUP BY cntr_type
SELECT DATEDIFF(ms, @starttime2, SYSDATETIME()) AS [Multiple Files]
SELECT * FROM @table2
GO
-- 10. Cleanup and Best Practices
-- - Always validate file paths and disk space before running.
-- - Use appropriate filegroup and file layouts for your hardware.
-- - Monitor IO and execution plans for real-world workloads.
-- - Drop demo database when done to reclaim space.
-- - Partitioning is most effective for very large tables and specific query patterns.
-- End of Script
Please Consider Subscribing
