Table partitioning in sql server

mssql_v2005
mssql_v2005 used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
hmm, a million rows is not much. Also the partition function should be applied to a column from your model. CreatedOn sounds like an audit column.

So, I don't see, why you want to use partitions at all. What is your context/scenario here?

And last but not least: Your test queries does not use any data besides that from the primary key. So I would not expect an speed up.

Author

Commented:
As of now the table contains 1 million records. From next week onward we are doing migration..after migration the row rowcount may reach upto 40-90 million.

To avoid performance issues in future we are planning for table partition. Can you please review once again partition code and performance results and advise us on this.

Author

Commented:
The attached file contains table structure. Please suggest if any changes required on Table partition code.
TableStru.txt
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer

Commented:
Without knowing your data model and your queries, it's hard to tell. But from the column naming: CreatedOn sounds like an audit column. Thus I don't assume that you'll have many queries using this column in a predicate at all.

Author

Commented:
Is it possible to create partition on GUID column in SQL Server  OR does it make sense creating partition on uniqueidentifier column?

Author

Commented:
Most of our queries use columns X, Y,Z and these columns datatype is uniqueidentifier
Senior Developer
Commented:
You can use UNIQUEIDENTIFIER as column for a partition.

But there is one thing of interest: How is this UID generated?

When it is a standard identifier, then it is random. In this case you'll have an random order of your data on behalf of the user data. This means partition brings no more performance.

This leads to next advice: a clustered index on a UNIQUEIDENTIFIER column is in this case already nonsense.

Cause in both cases the randomness leads to many unnecessary reads, because two rows belonging logical together are split up into different blocks.

Also random UID leads to bad INSERT performance, cause it increases the number of page splits.

Author

Commented:
--

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial