Avatar of mssql_v2005
mssql_v2005

asked on 

Table partitioning in sql server

Hi All,
We have implemented Table partitioning in our TEST server using below TSQL code based on the column CreatedOn. After implementation we have compared performance with partition table & without partition table (data & table structure same on both tables). I'm herewith attaching screenshots of performance results, please review and let me know If this looks good to build in PROD environment OR is there any changes required. In PROD DB table we have around 1 million records.
Table partitioning Code:
CREATE PARTITION FUNCTION [SHIVENDOOPF](DATETIME)  AS
  RANGE LEFT FOR VALUES 
  (  '20151231 23:59:59.997',
  '20161231 23:59:59.997' )

ALTER DATABASE [TESTDB] ADD FILEGROUP [2015FG1];
ALTER DATABASE [TESTDB] ADD FILEGROUP [2016FG2];

ALTER DATABASE [TESTDB] ADD FILE (NAME = '2015FG1', FILENAME = 'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\2015FG1.NDF') TO FILEGROUP [2015FG1];
ALTER DATABASE [TESTDB] ADD FILE (NAME = '2016FG1', FILENAME = 'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\2016FG1.NDF') TO FILEGROUP [2016FG2];

CREATE PARTITION SCHEME SHIVENDOOPS
AS PARTITION SHIVENDOOPF TO
(
[2015FG1],
[2016FG2],
[PRIMARY]
)

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'BaseTable') AND name = N'[PK_BaseTable]')
ALTER TABLE BaseTable DROP CONSTRAINT [PK_BaseTable]
GO

ALTER TABLE dbo.BaseTable ADD  CONSTRAINT [PK_BaseTable] PRIMARY KEY CLUSTERED
(
      [productId],[CreatedOn] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON SHIVENDOOPS (CreatedOn)

Open in new window

Table without PartitionTable with Partition
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
mssql_v2005

8/22/2022 - Mon