October 15, 2007 at 4:32 am
Hi
I am looking for some advices about the opportunity of using partitiong in my scenario, describable as follows:
- approximately ten tables are growing faster than the others. Their rate of growth is between 1,000 and 5,000 rows/day but in future could arrive to 100,000 rows a day (at the very worst). Each table has at most a size of 27 byte . In 3 weeks of real activity the biggest table grew almost 3 Mb, to a totale of 12 Mb. Index space for this table is 12 Mb .Total size of Db at the moment is approx. 90 Mb on a single [primary] file. The server machine has 4 processors.
- i would like to divide each of these tables in 3 tables depending on the way data are accessed: 1) live data, where data are inserted and elaborated to rappresent the present situation of my system; 2) data for statistical usage, accessible by analysis services; 3) archive data, usually not accessible
- each day i would like to move data older than the most recent midnight from live to statistical data.
- each month i would like to move a month of data older than 2 years from statitical to archive data.
- my system is idle each day from 1 a.m. to 5 a.m. so i have plenty of time for administrative tasks, jobs and so on.
The fundamental question is: do i really need to use partitioned tables and the "switch" partition from one table to the other or would it be better to move data using good old INSERT and DELETE, putting these commands in a sql script ?
Advantages I see in partitions:
- i would learn how to use them
- it sounds cool
- if the data will grow faster than i expect (based on the first weeks of real activity) the performances should be better
Advantages I see in the old method:
- it is simpler to implement and I know how to do it
- characteristics of my table do not seem to call for a "large database solution" since the Db is SMALL
- at the moment i am pretty sure i will continue to have a good amount of time to move data among tables
Which solution would You suggest me (and why) ? (are other information useful for a correct evaluation of the situation?)
Thank you very much
Wentu
October 15, 2007 at 8:35 am
partitioning is a subject which can't be covered in a simple set of posts - I'm sure there's just been a couple of good articles on the forum ( last week ? ) Kimberley Tripp wrote an excellent partitioning article if yousearch her blog or msdn. I've implemeted partitioning in 2000 and 2005 and it works well and as long as you're aware of the limitations is excellent. Check out the sliding window scenario which sounds like what you need. I found that the altering of the partitioning functions to add/remove tables/filegroups seemed to need exclusive use of the database ( e.g. single user ) so if you're really 100% full on you might have issues, normally most of us have a quiet time so it's not a problem.
Data partitioning is always good in a database, however like everything you do add complexity so management is slightly more difficult.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 15, 2007 at 10:49 am
The 3 tables could be joined in a view, if each of the tables was placed in a different filegroup you would have your partitioned view.
You may wish to extend this further with partitioning the larger tables, this is where you will need to research you options looking at table partitioning - sliding partitions is ideal when you want to drop off data, when it's no longer required, but you will be trying to move data, so conventional copy/delete bcp methods may be easier.
October 15, 2007 at 10:52 am
Colin has great advice. It's not a simple thing to implement. The actual partitioning is easy, but there's administrative overhead and you could easily get yourself into trouble with recovery and other administrative tasks.
Do some research and then ask some questions about particular issues that you might think will affect your environment.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 16, 2007 at 7:31 am
You are asking the right questions and I think the other posts provide needed insight to the possible options given your particular circumstances. If you are not sure about the growth potential of these tables and whether new ones will come along, then you have a tough decision to make...go with what is easier now (no partitioning) or incur the knowledge and time overhead that implementing partitioning requires.
If you want to get a great picture of what implementing end-to-end automated partitioning looks like in a large data environment, I recommend taking a look at the MS Project Real site at http://www.microsoft.com/sql/solutions/bi/projectreal.mspx
This includes a lot more than just partitioning but you can easily drill down to that subject...it includes the article by Kimberly Tripp and others that are well written and you can also 'borrow' some of their scripting and design they use to completely automate the process.
Bob:)
October 16, 2007 at 6:26 pm
I used partitioning in a relatively small database for performance reasons, and it worked quite well.
I had three partitions: Current, Old, Ancient.
Current data was still being worked on, usually only a few weeks old. Lots of inserts, updates and deletes, and a small number of rows, so few indexes were needed and few were used, which made for fast OLTP.
Old was not being worked on, but was needed for lots of reports. Once placed in the Old table, data was rarely, if ever, updated, and never deleted. Serious indexing, for very fast selects.
Ancient was still needed for a few reports, but only very rarely (once or twice a year in many cases, never more than quarterly). A few simple indexes, just in case, but otherwise nothing, in order to save space on my index drives (I had the indexes for this database on a separate drive array in the SAN). Huge amount of data, but the lack of indexes made up for a lot of that when it came to drive space.
It worked quite well.
As for moving data from table to table, I just had an Insert ... Select and a Delete wrapped in a transaction, with some checks to make sure all the data being deleted from the Current table had been successfully inserted into the Old table, or all the data from Old had made it successfully in Ancient. Doing it that way added to log files, which is both a plus and a minus, but it worked for what I needed.
I've done more active partitioning, to overcome hardware limits (tables of contacts split by status, with over 1-million rows of data, on old hardware). Having less rows per table made for MUCH faster OLTP, but this kind of thing also meant I had to have very good code for moving data from partition to partition on the fly. Was tricky, and a bit of a maintenance nightmare till I got it tamed with the right validations and such, but it was worth it in the end (zero budget for new hardware).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply