troubleshooting Question

Table partitioning in sql server

Avatar of mssql_v2005
mssql_v2005 asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
8 Comments1 Solution95 ViewsLast Modified:
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)
Table without PartitionTable with Partition
ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros