November 6, 2008 at 9:05 am
Ok. I'm not too familiar with partitioning and here's what I have.
Table1 with millions of rows
I created the quarterly function and schema
I created 4 tables,
Table1_20081stQtr on schema
Table1_20082ndQtr on schema
Table1_20083rdQtr on schema
Table1_20084thQtr on schema
I then moved the appropriate data from Table1 to the 4 tables.
Now I'm not sure what I'm supposed to do next.
How will Table1 know to use these tables when users query against Table1? Not sure I understand these instructions. What am I doing wrong???
¤ §unshine ¤
November 6, 2008 at 9:57 am
You don´t have to create 4 tables to partition the big table.
Instead, you have to create 4 filegroups and 4 files (1 file per filegroup).
Then you have to create a partition function and a partition scheme.
In the function you set the ranges to make the partition.
In then scheme you make the relations between the filegroups and the ranges.
The final result is 1 big table partitioned over 4 files using the partition function criteria.
You must follow this url for a more detailed explanation http://www.databasejournal.com/article.php/3300441.
Scroll to the section Data Partitioning in SQL Server 2005.
There are 5 articles that can help you.
HTH
November 6, 2008 at 10:09 am
So if I choose to do one table quarterly for 2 years and another table monthly for 2 years. That would be a total of 32 files and filegroups. Is that feasible?
¤ §unshine ¤
November 6, 2008 at 10:42 am
Yes 🙁
But not all the tables must be partitioned, only the big ones.
You can make some google search, there are different opinions in this area, to find good candidates to partition.
(Some folks think that the table size must be over 1 Terabyte).
I am already in a test phase to partition a table of 120 GB into 5 partition (50 Millions rows per partition), and I have plans to partition another table of 50 GB, by year.
Of course, you can create less files than filegroups, but usually you don´t, to make a partition.
The performance gain is related to the deploy of files/filegroups in differents hard disk drives.
There are others gains if the partitions are static (read only), because you can make backup only of the filegroups that really have had changes.
Take a look at this advanced paper on this subject http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm#_Toc79339965
HTH
November 6, 2008 at 10:46 am
Right, it's only a handful...
¤ §unshine ¤
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply