Table Partiotion on Datetime column - nonclusterd index

Question

Post reply

Table Partiotion on Datetime column - nonclusterd index

Minnu

SSCertifiable

Points: 6451
More actions
July 21, 2014 at 5:26 am

#291343

Hi Team,
i have a table with below columns.
S_id (int)
S_Name (varchar)
S_DOB (Datetime)
S_City (varchar)
S_id is a Primry Key with clustered index on it, and S_DOB is a non clustered index.
having 10 millision records in my table, so i want to apply table partiotion on S_DOB column.
having file groups : FG1, FG2, FG3, FG4.
S_DOB
>=1950 <=1960 under FG1
>=1961 <=1980 under FG2
>=1981 <=2000 under FG3
>=2001 under FG4
by appying table partition under above scenaio, is there is any improvement in table performance..
Please suggest

Viewing 9 posts - 1 through 8 (of 8 total)

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

You've given far too little information to answer your question.

For example:

1. Do you have specific, measured performance problems with specific queries today against your table?

2. What have you tried so far to address any performance problems?

3. What is the insert/delete volume against this table? With your proposed scheme, all four partitions seem likely to have lots of new inserts and deletes.

4. What is the primary purpose of your table? (e.g. OLTP, OLAP)

Minnu SSCertifiable Points: 6451 More actions · Answer 2

Select query is taking long time on this table, there are more than 10000 inserts per day.

and this table is mainly used for OLAP process only.

Finally...performance of table needs to be improved.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

Minnu (7/21/2014)
Select query is taking long time on this table, there are more than 10000 inserts per day.
and this table is mainly used for OLAP process only.
Finally...performance of table needs to be improved.

You're likely using the wrong tool to get better performance. A 10 million row table is actually a pretty small table even on a desktop machine, never mind a server and 10,000 inserts per day should be a cake walk. There's something else wrong with the code if you're having performance issues related to this table and partitioning isn't likely to resolve any of those issues, especially for such a small table that grows so slowly (only 3 million rows per year).

I strongly recommend that you spend your valuable time on correcting the code instead of partitioning because partitioning usually won't fix the performance problems caused by bad code.

--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)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Minnu (7/21/2014)
Select query is taking long time on this table, there are more than 10000 inserts per day.
and this table is mainly used for OLAP process only.
Finally...performance of table needs to be improved.

Can you post an actual execution plan of a typical select query which is causing problems? It would be very helpful. As others have suggested, partitioning isn't likely to do much good - it's a table management feature rather than a performance feature.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Minnu SSCertifiable Points: 6451 More actions · Answer 5

Actually Performance is not a major issue in my case, trying to implement the table management.

i want to store the older records away by creating partitions.

my overrall request is, by implementing partitions is there any improvement in Database

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Minnu (7/21/2014)
Actually Performance is not a major issue in my case, trying to implement the table management.
i want to store the older records away by creating partitions.
my overrall request is, by implementing partitions is there any improvement in Database

Minnu (7/21/2014)
Select query is taking long time on this table, there are more than 10000 inserts per day.
and this table is mainly used for OLAP process only.
Finally...performance of table needs to be improved.

Post the Actual Execution Plan of a SELECT query which takes a long time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Minnu (7/21/2014)
Actually Performance is not a major issue in my case, trying to implement the table management.
i want to store the older records away by creating partitions.
my overrall request is, by implementing partitions is there any improvement in Database

How would partitioning by DOB put older records in different partitions?

It would just put older people in different partitions, even if the row was inserted today.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 8

Michael Valentine Jones (7/21/2014)
Minnu (7/21/2014)
Actually Performance is not a major issue in my case, trying to implement the table management.
i want to store the older records away by creating partitions.
my overrall request is, by implementing partitions is there any improvement in Database
How would partitioning by DOB put older records in different partitions?
It would just put older people in different partitions, even if the row was inserted today.

To add to what Michael stated above, you ask the question...

i want to store the older records away by creating partitions.

WHY? What would you do after that? There are certainly reasons to do this with partitioning but, since you're using the wrong column to do this, I have to question what your motivation actually is. What is the real purpose of the partitioning you want to do? What is the real reason why you "want to store the older records away"?

--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)