Value of a Partitioned Table

Recently I came across some larger tables in our production CRM database and decided to do some testing on the value of adding a partition to an existing larger MS SQL table (177 million rows). At the end of the day as with everything in IS, it depends on how the application interacts with the database/table. In my example, I decided to use a yearly partition schema. This seems to be most common approach with larger tables. The below script will walk you through and directly compare the same query on a non-partitioned clustered index to a partitioned cluster index. If you review the pre/post execution plans you will notice that Execution I/O Cost has been greatly reduced with the use of a partition. I approximate around 98% reduction in IO cost. With additional testing on the production table, I feel the reduction in Execution I/O cost from a partition schema is something everyone should look into on larger tables.



-- check for table
IF OBJECT_ID('dbo.TestPartitionTable') IS NOT NULL 
    BEGIN
        DROP TABLE dbo.TestPartitionTable
    END
go

-- create table
CREATE TABLE [dbo].[TestPartitionTable]
    (
      [pkcol] [int] NOT NULL ,
      [datacol1] [int] NULL ,
      [datacol2] [int] NULL ,
      [datacol3] [varchar](50) NULL ,
      [partitioncol] DATETIME
    )
GO

-- Populate table 
DECLARE @val INT
SELECT  @val = 1
WHILE @val < 1000001  -- million rows
    BEGIN  
        INSERT  INTO dbo.TestPartitionTable
                ( pkcol ,
                  datacol1 ,
                  datacol2 ,
                  datacol3 ,
                  partitioncol
                )
        VALUES  ( @val ,
                  @val ,
                  @val ,
                  'TEST' ,
                  GETDATE() - ( @val * .01 )
                )
        SELECT  @val = @val + 1
        PRINT @val
    END
GO

/*
-- recovery if needed
INSERT INTO dbo.TestPartitionTable
        ( pkcol ,
          datacol1 ,
          datacol2 ,
          datacol3 ,
          partitioncol
        )
SELECT * FROM dbo.TestPartitionTableBackup
*/

-- added standard PK
ALTER TABLE dbo.TestPartitionTable ADD CONSTRAINT PK_TestPartitionTable PRIMARY KEY CLUSTERED (pkcol) 
GO
CREATE NONCLUSTERED INDEX IX_TestPartitionTable_col2col3 ON dbo.TestPartitionTable (datacol1,datacol2)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON [PRIMARY]
GO

-- check plan performance prior to partition

 -- Estimated I/O Cost
 -- The estimated cost of all I/O activity for the operation. This value should be as low as possible
 -- 4.50542
SELECT  *
FROM    TestPartitionTable
WHERE   partitioncol >= '20130101'


-- check for 
DROP PARTITION SCHEME myPartitionScheme 
go
 DROP PARTITION FUNCTION pfDateRange
 go

-- create date range partition
CREATE PARTITION FUNCTION pfDateRange (DATETIME)
AS RANGE RIGHT FOR VALUES ('20110101', '20120101','20130101')
GO
CREATE PARTITION SCHEME myPartitionScheme 
AS PARTITION pfDateRange ALL TO ([PRIMARY]) 
GO

-- check the partition schema and range
SELECT  ps.name ,
        pf.name ,
        boundary_id ,
        value
FROM    sys.partition_schemes ps
        INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
        INNER JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id

-- drop original cluster index
ALTER TABLE dbo.TestPartitionTable DROP CONSTRAINT PK_TestPartitionTable
GO
-- add noncluster index
ALTER TABLE dbo.TestPartitionTable ADD CONSTRAINT PK_TestPartitionTable PRIMARY KEY NONCLUSTERED  (pkcol)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- add partition index
CREATE CLUSTERED INDEX IX_TestPartitionTable_partitioncol ON dbo.TestPartitionTable (partitioncol)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON myPartitionScheme(partitioncol)
GO

-- check parition size
SELECT  t.name AS TableName ,
        i.name AS IndexName ,
        p.partition_number ,
        p.partition_id ,
        i.data_space_id ,
        f.function_id ,
        f.type_desc ,
        r.boundary_id ,
        r.value AS BoundaryValue
FROM    sys.tables AS t
        JOIN sys.indexes AS i ON t.object_id = i.object_id
        JOIN sys.partitions AS p ON i.object_id = p.object_id
                                    AND i.index_id = p.index_id
        JOIN sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
        JOIN sys.partition_functions AS f ON s.function_id = f.function_id
        LEFT JOIN sys.partition_range_values AS r ON f.function_id = r.function_id
                                                     AND r.boundary_id = p.partition_number
WHERE   t.name = 'TestPartitionTable'
        AND i.type <= 1
ORDER BY p.partition_number;

-- parition column info
SELECT  t.object_id AS Object_ID ,
        t.name AS TableName ,
        ic.column_id AS PartitioningColumnID ,
        c.name AS PartitioningColumnName
FROM    sys.tables AS t
        JOIN sys.indexes AS i ON t.object_id = i.object_id
        JOIN sys.columns AS c ON t.object_id = c.object_id
        JOIN sys.partition_schemes AS ps ON ps.data_space_id = i.data_space_id
        JOIN sys.index_columns AS ic ON ic.object_id = i.object_id
                                        AND ic.index_id = i.index_id
                                        AND ic.partition_ordinal > 0
WHERE   t.name = 'TestPartitionTable'
        AND i.type <= 1
        AND c.column_id = 1;


SELECT  OBJECT_SCHEMA_NAME(i.object_id) AS [schema] ,
        OBJECT_NAME(i.object_id) AS [object] ,
        i.name AS [index] ,
        s.name AS [partition_scheme]
FROM    sys.indexes i
        JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id

 -- Estimated I/O Cost
 -- The estimated cost of all I/O activity for the operation. This value should be as low as possible
 -- from 4.50542 to 0.0717498
SELECT  *
FROM    TestPartitionTable
WHERE   partitioncol >= '20130101'

-- reset if needed
--SELECT * INTO TestPartitionTableBackup FROM TestPartitionTable

Reference link