Need to Create Partition to an existing Table

Question

Need to Create Partition to an existing Table

Viewing 9 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101200 More actions · Answer 1

Though it has a clustered in the name of Primary Key and a non Clustered Index in our case is a date column, as this is most frequent column used while writing queries. And we never use this primary key while writing queries as this is more of table specific column to tell us row uniqueness.

That's your performance issue.

The clustered index should be on the date column first, not the identity.

[(Related) Rant: The single biggest myth/falsehood on table indexing is that (virtually) every table should be clustered on identity. Wrong! In most cases, that just destroys performance! There are the exceptions where an identity cluster is actually best, but that's rare.]

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

rangu Default port Points: 1407 More actions · Answer 2

I guess this is not possible in my case. We need a primary key, if that's treated as unique row identifier. that acts as a clustered index.

Ideally the other index must be a non clustered index, since I already have one clustered in the name of primary key.

If am not wrong, there cannot be two clustered indexes.

ScottPletcher SSC Guru Points: 101200 More actions · Answer 3

You can only have one clustered index, but the PK does not have to be the clustered index.

When time is available:

1) Drop the existing indexes (Edit: non-clustered first, clustered last)

2) Create a unique clustered index on ( <date_column>, <ident_column> )

3) Create a PK on ( <ident_column> )

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

rangu Default port Points: 1407 More actions · Answer 4

Thanks Scott, for your valuable inputs and thoughts.

However I haven't got the answer yet, when exactly do we need to go for Table Partition and Page compression. If this is not the case?

ScottPletcher SSC Guru Points: 101200 More actions · Answer 5

rangu (6/6/2014)
Thanks Scott, for your valuable inputs and thoughts.
However I haven't got the answer yet, when exactly do we need to go for Table Partition and Page compression. If this is not the case?

You don't need to partition this table, certainly not yet.

Page compression could help, given the very large number of columns, but the first thing to do is get the proper clustered index on the table. Until then, other "tuning" is a waste of time and other resources.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

rangu (6/6/2014)
Thanks Scott, for your valuable inputs and thoughts.
However I haven't got the answer yet, when exactly do we need to go for Table Partition and Page compression. If this is not the case?

Scott beat me to it. The first thing to do is get the clustered index on that table right.

Based on some recommendations and some slick calculations by Kimberly Tripp (I'll have to dig up the URL when I get home), I recommend that the clustered index actually be a UNIQUE composite index based on the date and ID columns and in that order. It sounds like the PK on the ID column can simply be dropped on what is apparently some form of an audit table.

The speed of the queries that you have included the date column as part of the criteria should see a significant increase in performance... without partitioning.

Once you have that done, then you can think about partitioning but not for performance reasons. Ask your DBA (or yourself if it's you) how long full backups of the database are taking and what % of the size of the database is occupied by this table and it's indexes and whether or not (s)he think they have a problem in those areas. Then ask how soon the table will double in size. Then, ask how long a DR restore would take and whether or not just restoring the current month to get back in business (with additional restores over time) would be worth it or will ever be worth it depending on when you expect the table to double in size.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101200 More actions · Answer 7

Jeff Moden (6/6/2014)
rangu (6/6/2014)
Thanks Scott, for your valuable inputs and thoughts.
However I haven't got the answer yet, when exactly do we need to go for Table Partition and Page compression. If this is not the case?
Scott beat me to it. The first thing to do is get the clustered index on that table right.
Based on some recommendations and some slick calculations by Kimberly Tripp (I'll have to dig up the URL when I get home), I recommend that the clustered index actually be a UNIQUE composite index based on the date and ID columns and in that order. It sounds like the PK on the ID column can simply be dropped on what is apparently some form of an audit table.
The speed of the queries that you have included the date column as part of the criteria should see a significant increase in performance... without partitioning.
Once you have that done, then you can think about partitioning but not for performance reasons. Ask your DBA (or yourself if it's you) how long full backups of the database are taking and what % of the size of the database is occupied by this table and it's indexes and whether or not (s)he think they have a problem in those areas. Then ask how soon the table will double in size. Then, ask how long a DR restore would take and whether or not just restoring the current month to get back in business (with additional restores over time) would be worth it or will ever be worth it depending on when you expect the table to double in size.

Indeed. I posted the same index definition earlier in this thread:

"

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex_on_ps_MONTHLY_LOADS_635375610053010257] ON [HISTORICAL_Test]

(

[AS_OF_TS],

[hist_row_id]

) WITH ( SORT_IN_TEMPDB = ON, ONLINE = OFF ) ON [<<filegroup_name>>]

"

Rangu and his coworkers seem intent on partitioning. Partitioning would indeed help somewhat in this case, but only because the partitions would have the proper clustering key!

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

rangu Default port Points: 1407 More actions · Answer 8

Thanks Jeff for your thoughts.

Scott, thank you. I am following you :). Unfortunately I am not the decision maker here. I have been giving all the information you are sharing here.

I would like to thank you all again for making me understand the whole life of Partitioning and the importance of Indexes etc.

This has been so useful so far, I will try to see where it goes next week, in case if I need to have more help in either cases Partition/Index, I will post back here.

Meanwhile anyone of you has more info to help me out, will appreciate :). Have a nice weekend guys.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 9

rangu (6/6/2014)
Thanks Jeff for your thoughts.
Scott, thank you. I am following you :). Unfortunately I am not the decision maker here. I have been giving all the information you are sharing here.
I would like to thank you all again for making me understand the whole life of Partitioning and the importance of Indexes etc.
This has been so useful so far, I will try to see where it goes next week, in case if I need to have more help in either cases Partition/Index, I will post back here.
Meanwhile anyone of you has more info to help me out, will appreciate :). Have a nice weekend guys.

I do have some more information... or, rather, some recommendations.

I notice they had you predefined partitions from the start of the table to a month of Sundays from now. If they insist on partitioning, then I also recommend that the monthly partitions each live in their own filegroup to take advantage of things like reduced backups and to allow for online piecemeal backups in case of corruption or to get "back in business" quickly if you need to do a full "DR Restore".

When it comes to partitioning, my suggestion is that if it turns out to be simple, you've probably done something wrong. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)