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
mssql_v2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
mssql_v2005Author 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.
mssql_v2005Author Commented:
The attached file contains table structure. Please suggest if any changes required on Table partition code.
TableStru.txt
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

ste5anSenior DeveloperCommented:
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.
mssql_v2005Author Commented:
Is it possible to create partition on GUID column in SQL Server  OR does it make sense creating partition on uniqueidentifier column?
mssql_v2005Author Commented:
Most of our queries use columns X, Y,Z and these columns datatype is uniqueidentifier
ste5anSenior DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mssql_v2005Author Commented:
--
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.