Post reply

Table partitions

  • January 9, 2008 at 8:33 am

    #180540

    Hi,

    we are planing to create new database in that we want to partition a table which need to hold last 12 months of data. every month we load new data and archives the data older than 12 months. i am planing to create 12 partitions each for one month and sliding window technique. our database server connected to SAN with raid5. this server has 3 drives 2 for datafiles and 1 for transaction files.

    i am thinking to create 2 filegroups one on each datafile drive.

    my question : are 2 filegroups ok or do i need to create one filegroup for every partion.

    since data go to same drives even thogh we create more partions. so i dont see any advantage?

    one more question: how do we find which partition going to which file group?

    please suggest..

    thanks

    suma

  • January 9, 2008 at 8:43 am

    #766254

    Are you using SQL 2005?

  • January 9, 2008 at 8:50 am

    #766258

    yes it is sql 2005 sp2 enterprise edition

  • January 9, 2008 at 8:54 am

    #766261

    A very comprehensive article including best practices on table partitioning can be found in the following link:

    http://www.microsoft.com/technet/prodtechnol/sql/2005/realpart.mspx

    The article also discuss the sliding-window approach and has useful scripts to view the table partition meta-data in an informative manner.

    HTH

    Paul

  • January 9, 2008 at 8:57 am

    #766267

    OK good.

    You are quite right about the 2 seperate filegroups. Although you could have 12 filegroups I dont see any benefit from doing this unless you wanted to make some filegroups read-only for archiving purposes.

    There are 2 steps to creating filegroups. Firstly you will need to create your partition function. See http://msdn2.microsoft.com/en-us/library/ms187802.aspx

    You will then need to create a partition scheme. See http://msdn2.microsoft.com/en-us/library/ms179854.aspx You can specify in the code which filegroup you would like your partition saved to.

  • January 9, 2008 at 9:01 am

    #766270

    From my experience (just completed almost same project):

    1. For sliding window- single filegroup (FG) is easier to control partition placement;

    2. If you put some FG on the same drive you will not have any benefits.

    One more side note- if your monthly load is pretty big (in my case- around 270 M rows) it's better always to keep 1 extra partition (for next month) as to split this empty partition at the beginning of next month much easier than current big partition. Good luck!

  • January 9, 2008 at 8:23 pm

    #766451

    thank you david and yuri,

    how does table references work. do we need to declare references on stage table( which are going to get in partition table)

  • January 9, 2008 at 8:49 pm

    #766456

    Probably this MS article has answer for your question:

    http://msdn2.microsoft.com/en-us/library/ms191160.aspx

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply