We help IT Professionals succeed at work.
Get Started

Table partitioning in sql server

92 Views
Last Modified: 2016-03-21
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
Comment
Watch Question
Senior Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE