May 19, 2009 at 1:19 am
I have a table with 32 columns and 1947534 records.
1296327 number of records are not used, but we need to keep it.
We use only 651207 number of records, as of now.
The number of records are added daily but not much.
I wish to :
1. Create a file group,
2. create a secondary file in new file group on separate DRIVE on same disk
3. Create a partition on above table and keep unused record on new partition on new file on separate DRIVE.
So that the usable records will exists on current file.
Is this ok. I am doing this to increase the performance.
Is there any other way to this?
Regards
May 19, 2009 at 7:47 am
A 2M rows table can be considered a small one.
Question would be: what's the reason why you may want to partition such a table? is it performance?
Working with such a small table chances are performance can be fixed by creating appropriate indexes and tunning any offending queries.
Having said that, there are just two reasons why you may want to partition a table: a) to help performance during querying and, b) to help performance during archiving/purging.
I'm not convinced a small table with little activity is a real candidate for partitioning.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 20, 2009 at 9:31 pm
Thanks Paul,
I got it. I wanted to partition it, only for performance.
This is the most heavily used table in our system. I can dare and say its one of the core table.
Also we have more number of useless records in it, which we need to keep.
Can you please explain which table can be a suitable candidate for partitioning?
Thanks once again.
May 20, 2009 at 9:38 pm
[font="Verdana"]Partitioning is certainly one strategy. Have you considered archiving off the "useless records" into another table?
Also, if the changes to the table are infrequent, have you considered building covering indexes to cover your most frequent queries? It may be that a well chosen index will bump the performance noticeably without any additional modifications.
[/font]
May 21, 2009 at 11:25 am
Diamond Mouse (5/20/2009)Can you please explain which table can be a suitable candidate for partitioning?
As already posted you may want to consider table partitioning in one of both of the next two scenarios:
1) When Table Paritioning will help query performance.
In this case your most heavily used queries as supposed to access the table doing range scan on the partition key -if there is no such a suitable partition key you may find that partitioning the table will end up hurting performance instead of improving it.
2) When Table Partitioning will help during archiving/purging
In this case your partition key should be the one that would allow to purge just by getting rid of a whole partition.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 21, 2009 at 10:27 pm
Thanks Pablo, Bruce.
From your suggestions, I think I must check for archiving/purging of data.
As I already mentioned, I wanted to partition my table in 2 parts :
1. With active (useful) records.
2. With inactive (useless) records.
We need to keep all records (active, inactive) with us.
We have a flag (column with BIT datatype) which decides the active status of records. I was thinking to use this column as partition key.
I.e. Active records would go in one partition and incative records would go to another.
We always use this flag in our queries, so that we get only active records. So, I thought it would access patition only with active records and may increase the performance.
But I think that would not be the case. Suggest me, if I am wrong.
I will check for the data archival / purging process. Will let you know about the progress.
Thanks again.
May 22, 2009 at 12:52 pm
The practical benefit of partitioning by active/inactive flag would be to automatize the archiving and purging process -each time a row goes inactive it has to be phisically deleted -purge from the active partition and inserted -archived into the inactive one.
In the Oracle world you have to enable row_movement feature to accomplish this, not sure if there is something alike on SQL Server -I'll put this one in my research queue.
Fact is that even in different partitions all active/inactive rows would be on the same table -depending on your architecture and querying strategy this could be good or bad news; hard to tell without a deeper knowledge of the environment.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply