Home Page
Articoli
Tips & Tricks
News
Forum
Archivio Forum
Blogs
Sondaggi
Rss
Video
Utenti
Chi Siamo
Contattaci
Username:
Password:
Login
Registrati ora!
Recupera Password
Home Page
Stanze Forum
SQL Server 2000/2005/2008, Express, Access, MySQL, Oracle
Advice about partitioning in a "small" Db
lunedì 15 ottobre 2007 - 15.01
Elenco Threads
Stanze Forum
Aggiungi ai Preferiti
Cerca nel forum
Wentu
Profilo
| Newbie
1
messaggi | Data Invio:
lun 15 ott 2007 - 15:01
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
alx_81
Profilo
| Guru
8.814
messaggi | Data Invio:
lun 15 ott 2007 - 16:17
>Hi
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
ok, i'm agree with you.
>- 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 ?
You have to analize what's your table growth..
If the number of your rows grows too much, you'll have maybe to use partitioned tables.
I've used this method when I've more than 30,000,000 of rows.
But if you divide all in three tables, you can use the live one as a "normal" table, the statistical one like the first (if number of records is not high) and the historical one as a partitioned table.
Remember that you'll need to design indexes with accuracy in order to make partitions and some structure are so strict to manage.
>
> 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
from MSDN
"Partitioning a table improves performance and simplifies maintenance. By splitting a large table into smaller, individual tables, queries that access only a fraction of the data can run faster because there is less data to scan."
So, larger is the table, better is to use partitions. With this method you can navigate data with better performances.
>
> 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
you have to chioice what objects need really partitions.
i suggest you to read BOL, they maybe can help you..
>- 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?)
IMHO the best way to follow is to analize with accuracy your objects and then to decide if partitions way is really necessary.
>
>Thank you very much
not at all!
Alx81 =)
http://blogs.dotnethell.it/suxstellino
lbenaglia
Profilo
| Guru
5.625
messaggi | Data Invio:
lun 15 ott 2007 - 19:53
>Which solution would You suggest me (and why) ? (are other information
>useful for a correct evaluation of the situation?)
Hi Wentu,
I agree with Alessandro, partitioning is the way to go
Kimberly wrote an amazing article about partitioning and I'm sure you will find all the information you are sarching for
http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm
>Thank you very much
You're welcome.
Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org
Torna su
Stanze Forum
Elenco Threads
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?
Dopo esserti registrato potrai chiedere
aiuto sul nostro
Forum
oppure aiutare gli altri
Consulta le
Stanze
disponibili.
Registrati ora !