June 5, 2014 at 7:48 am
Hi,
I have an existing table with 34M records and is expected to grow further.
we have decided to partition the table.
we are planning to create a partition base on a date value.
sample values of the date is like below and the type is integer.
20081231, 20090131, 20090228, 20090331, 20090430, 20090531, 20090630, 20090731, 20090831, 20090930, 20091031, 20091130, 20091231, 20100131, 20100228, 20100331, 20100430, 20100531, 20100630, 20100731, 20100831, 20100930, 20101031, 20101130, 20101231, 20110131, 20110228, 20110331, 20110430, 20110531, 20110630, 20110731, 20110831, 20110930, 20111031, 20111130, 20111231, 20120131, 20120229, 20120331, 20120430, 20120531, 20120630, 20120731, 20120831, 20120930, 20121031, 20121130, 20121231, 20130131, 20130228, 20130331, 20130430, 20130531, 20130630, 20130731, 20130831, 20130930, 20131031, 20131130, 20131231, 20140131, 20140228, 20140331, 20140430, 20140531
May I know the steps involved in creating the same, am new to Partition.
Thanks in Advance.
June 5, 2014 at 8:56 am
1. Create partition function. List all these dates as boundary values in this function
2. Optional. If you plan to place partitions on dedicated files/filegroups, create them. Number of FG = (number of boundary values)-1
If all to primary (or another FG), skip this step.
3. Create partition scheme which links PF and FG's.
4. Create clustered index on your table. At the and not on primary, but on your PS(column).
June 5, 2014 at 9:07 am
Thank you, I have used an existing Partition function and Scheme.
I could create the partitions.
Wondering what is this Index creation, I see Null when I pull the report for this partition. Do we really need a clustered index on this?
So when you say I need to create an Index on PS, is it the same date column name on which the partition has been done. Sorry just want to understand in detail.
When I created the Partition script using wizard, the below script has been generated. This has that Index created and then dropped, am confused here. please suggest.
BEGIN TRANSACTION
ALTER TABLE [HISTORICAL_Test] DROP CONSTRAINT [PK_HISTORICAL_Test]
ALTER TABLE [HISTORICAL_Test] ADD CONSTRAINT [PK_HISTORICAL_Test] PRIMARY KEY NONCLUSTERED
(
[hist_row_id] 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, FILLFACTOR = 80) ON [PRIMARY]
CREATE CLUSTERED INDEX [ClusteredIndex_on_ps_MONTHLY_LOADS_635375610053010257] ON [HISTORICAL_Test]
(
[AS_OF_TS]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ps_MONTHLY_LOADS]([AS_OF_TS])
DROP INDEX [ClusteredIndex_on_ps_MONTHLY_LOADS_635375610053010257] ON [HISTORICAL_Test] WITH ( ONLINE = OFF )
COMMIT TRANSACTION
June 5, 2014 at 9:20 am
rangu (6/5/2014)
Do we really need a clustered index on this?
If we are talking about partitioned table, then clustered index is needed. If you create non-clustered index on PS, then only index will be partitioned, not the table.
This has that Index created and then dropped
When you drop a clusterd index which has been created on PS, you will have a partitioned HEAP. If your goal is really to have a partitioned heap, then drop it. But the problem will arise when you eventually will need to split the last partition to a new date.
June 5, 2014 at 9:38 am
Thanks Much, So you recommend to create a Clustered index on the PS?
The below script is generated through wizard, if I provide this to DBA, will he be able to create partition in production, with PS and PF already there?
If there has to be a clustered index on my PS, what should be my script look like?
BEGIN TRANSACTION
ALTER TABLE [HISTORICAL_Test] DROP CONSTRAINT [PK_HISTORICAL_Test]
ALTER TABLE [HISTORICAL_Test] ADD CONSTRAINT [PK_HISTORICAL_Test] PRIMARY KEY NONCLUSTERED
(
[hist_row_id] 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, FILLFACTOR = 80) ON [PRIMARY]
CREATE CLUSTERED INDEX [ClusteredIndex_on_ps_MONTHLY_LOADS_635375610053010257] ON [HISTORICAL_Test]
(
[AS_OF_TS]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ps_MONTHLY_LOADS]([AS_OF_TS])
DROP INDEX [ClusteredIndex_on_ps_MONTHLY_LOADS_635375610053010257] ON [HISTORICAL_Test] WITH ( ONLINE = OFF )
COMMIT TRANSACTION
June 5, 2014 at 9:57 am
This is what this script is doing:
1. Drops PK (which is non-clustered).
2. Recretaes PK, non-clustered again.
3. Creates clustered index on PS.
4. Drops it.
My questions:
1. Why do you need to touch your PK at all? Did you receive an error if you skip it?
2. Again, dropping partitioned clustered index is very unlikely, it is not recommended.
I would leave only the 3rd statement here. And change ONLINE=ON.
June 5, 2014 at 10:18 am
Thanks, now that I understood what exactly is going on.
I had ran thae 3rd part alone and it has created a clustered index. The index name looks clumsy like below. Since we are creating an Index on field AS_OF_TS, a name IX_AS_OF_TS would look much better when someone pulls the report, how exactly can i achieve this using the above script? Also you have asked me to change ONLINE = ON, what does it mean?
ClusteredIndex_on_ps_MONTHLY_LOADS_635375610053010257
June 5, 2014 at 10:27 am
I guess this should work if am correct? if this is correct, still have the confusion on ONLINE =ON, what is it doing?
CREATE CLUSTERED INDEX [IX_AS_OF_TS] ON [HISTORICAL_Test]
(
[AS_OF_TS]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ps_MONTHLY_LOADS]([AS_OF_TS])
June 5, 2014 at 11:10 am
ONLINE=ON means that users will be able to access your table while is being rebuilt.
With OFF this table will be locked for entire duration.
June 5, 2014 at 11:14 am
ok, Thanks for your clarification.
I have seen Page compression being used in couple of other tables in the DB am working on.
Having created the Partition on this table, do you think creating the page compression too on this table would benefit me?
if so how?
This should conclude my series of doubts, I would then summarize steps to confirm with you. so that it helps anyone else on this forum.
Thanks again.
June 5, 2014 at 12:09 pm
Page compression usually improves performance in most of the cases, however not in all of them. It compresses the pages so that more data can fit into a page. It helps I/O because less pages needs to be read when they are compressed. However it puts more burden on CPU because this is CPU's job to compress data before packing them into pages.
So if your system suffers mostly from I/O but has plenty of available CPU (you can get these metrics from perfmon and DMV's) then do it. But I would suggest to duplicate this table, make one of them compressed, and test and compare performance against each of them.
June 5, 2014 at 12:17 pm
Thankyou.
What if I want to drop the parition I have just created on this table?
Could you please give me the steps. Such a way I have my orginal table without partitions nd compressions.
June 5, 2014 at 12:46 pm
Recreate clustered index on PRIMARY or any other FG.
You can do it WITH DROP_EXISTING = ON
June 5, 2014 at 1:06 pm
Does this work out? Please correct me if there is any modification. my aim is to remove any partition and compressions on this table HISTORICAL_Test.
CREATE CLUSTERED INDEX [IX_AS_OF_TS] ON [CREDIT_RW].[HISTORICAL_Test]
(
[AS_OF_TS]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE = OFF) ON [ps_MONTHLY_LOADS]([AS_OF_TS])
June 5, 2014 at 1:17 pm
CREATE CLUSTERED INDEX [IX_AS_OF_TS] ON [CREDIT_RW].[HISTORICAL_Test]
(
[AS_OF_TS]
)
WITH (DROP_EXISTING = ON, ONLINE = ON, DATA_COMPRESSION = NONE)
ON [PRIMARY]
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply