December 29, 2010 at 7:39 am
I have the following partition definition in 2 of my main tables. I would like to know what the best way to alter or modify a partition is. I might need still this information for statistics next year. But I need to have my table ready to storage more data. I'm not sure if switch will be the best practice. I do understand how to use the Statements (switch, split, etc.). I just need you to guide me for the best practice in order to keep using the information in these tables since I have store procedures that retrieve data from these tables.
Thank you in advance
Partition Scheme: vw_hh_PartitionSchemeDate
Partition Function: vw_hh_PartitionFunctionDate
Tables: Table1, Table2
Partition Column: Date, ActualAirTime
Range Value for
Partition Function: Range Right Values from 2009-01-01 to 2010-12-01
Partition Scheme
For partition(def): [Part_JAN], [Part_FEB], [Part_MAR], [Part_APR], [Part_MAY], [Part_JUN], [Part_JUL], [Part_AGU], [Part_SEP], [Part_OCT], [Part_NOV], [Part_DEC], [Part_JAN10], [Part_FEB10], [Part_MAR10], [Part_APR10], [Part_MAY10], [Part_JUN10], [Part_JUL10], [Part_AUG10], [Part_SEP10], [Part_OCT10], [Part_NOV10], [Part_DEC10], [PRIMARY]
December 29, 2010 at 7:43 am
I wouldn't create partitions for every month so far. I would for example do the following (of course it depends on how your users use the data):
partitions for the last 6 months, 2 partitions for the 2 trimesters before those 6 months and one big partition for data that is older than 1 year.
This means that once a month you must merge partitions together and create a new one for the current month.
It's just an example, you can do so much more.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 29, 2010 at 7:50 am
I do understand it. it sounds good. But that means that I need to re-create the entire shceme structure that I already have. The reason I did a monthly one is because I have more than 100million records in each month/partition. And we do a lot of queries per period of time.
do you think your advice will help me in performance ?
December 29, 2010 at 11:43 am
MTY-1082557 (12/29/2010)
I do understand it. it sounds good. But that means that I need to re-create the entire shceme structure that I already have. The reason I did a monthly one is because I have more than 100million records in each month/partition. And we do a lot of queries per period of time.do you think your advice will help me in performance ?
You don't have to change your entire partition schema every time. You just have to merge and split partition boundaries.
If your users query only recent data, you can put older data in seperate partitions who are stored on less expensive disks, while the recent data is on faster, more expensive disks. (but I think that in order to use the switch statement, partitions need to be in the same filegroup. I should look that up).
To have a real performance gain, it is best that the partition column is used in the WHERE clause of the SQL queries.
For example, if you have a partition on December 2010, then the database engine knows it needs to search only that partition if the following query is launched:
SELECT * FROM MyTable WHERE MyDate BETWEEN 2010-12-01 AND 2010-12-31 (where MyDate is your partitioning column)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 29, 2010 at 11:54 am
MTY-1082557 (12/29/2010)
I have the following partition definition in 2 of my main tables. I would like to know what the best way to alter or modify a partition is. I might need still this information for statistics next year. But I need to have my table ready to storage more data. I'm not sure if switch will be the best practice. I do understand how to use the Statements (switch, split, etc.). I just need you to guide me for the best practice in order to keep using the information in these tables since I have store procedures that retrieve data from these tables.Thank you in advance
Partition Scheme: vw_hh_PartitionSchemeDate
Partition Function: vw_hh_PartitionFunctionDate
Tables: Table1, Table2
Partition Column: Date, ActualAirTime
Range Value for
Partition Function: Range Right Values from 2009-01-01 to 2010-12-01
Partition Scheme
For partition(def): [Part_JAN], [Part_FEB], [Part_MAR], [Part_APR], [Part_MAY], [Part_JUN], [Part_JUL], [Part_AGU], [Part_SEP], [Part_OCT], [Part_NOV], [Part_DEC], [Part_JAN10], [Part_FEB10], [Part_MAR10], [Part_APR10], [Part_MAY10], [Part_JUN10], [Part_JUL10], [Part_AUG10], [Part_SEP10], [Part_OCT10], [Part_NOV10], [Part_DEC10], [PRIMARY]
How many month worth of data you have to keep in the main table?
Twelve? If that's the case partitioning strategy is correct - probably adding the '10' representing year 2010 was not a great idea but you can forget about it and just look at JAN, FEB, etc.
Once a month just switch out the older month and store it elsewhere for reporting purposes. You may want to take a look at "sliding partitions"
_____________________________________
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.December 29, 2010 at 1:18 pm
I was worried about my Range Value in the Partition Function. I thought that the data from 2011 wont be storage 'cause the range value. So I only need to switch out to another Partitioned table the "old data" (January to June 2010) for reporting and the new data will be located in which partition?
for now, I need to keep mostly of the data because the first trimester I will need to run reports for 2010. I need to be sure my actual table will allow me to continue adding new data for 2011
I just need to have a clear idea about how the data is located in my actual tale. for what you explain I can re-use the same FileGroups to storage my new data right?
thank you a lot guys, you really helping me here 🙂
December 30, 2010 at 4:07 am
I would switch out the oldest partition, replace it with an empty partition, merge the empty partition with the one ahead of it, then split the newest partition (newest as in the one with the most recent data). After you merge the partition at the beginning (oldest) and split the newest, you will have the same amount of partitions. I'm not sure, but the names of your partitions ([Part_MAR]), you might have to just block out of your mind, because maybe the name won't align with the date of the data in each partition. You should have named the partitions part1, part2, part3, etc.
I think this is what the other poster means by 'sliding partition.' I inherited my setup so not sure if it is defined as a sliding partition scheme or not, but it seems that way. The above scenario would drop the oldest month and a new month (hope that is your goal).
After you are finished switching out the oldest partition, you can do whatever you like with the resulting table which residing alone.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply