locked
Table Partioning RRS feed

  • Question

  • Hi,

    We have a requirement where 4 large tables need to be partitioned for archival purpose. But we have only one file group created and we do no have additional drives to create a new file group. Is it advisable to partition the 4 tables in already existing primary file group. Will there be any performance improvement in doing so?

    Thanks,

    Preetha

    Tuesday, June 24, 2014 6:02 AM

Answers

  • Table partitioning is very broad subject and the question if table needs to be partitioned is a bit separate from data/filegroups layout. 

    As Maria already mentioned, the best practices recommend you to keep primary filegroup empty. This will allow you to reduce recovery time by implementing piecemeal restore. It is also correct, that you can create additional filegroup(s) and put them to the same drive. In the future, when environment changes, it would allow you to redesign your data files layout and reduce solution cost by implementing tiered storage with old, rarely accessed data stored on the slower disk arrays.

    With all being said, it is almost impossible to move individual partition data from one filegroup to another keeping table online. SQL Server acquires schema modification (SCH-M) lock for the duration of data movement, which prevents other sessions from accessing the table. There are the possibilities of some complex solutions, such as switching partition to separate table, creating partitioned view on both tables, move data with online index rebuild, etc. However, it is complicated from both, maintenance and implementation standpoints and, in the large number of cases, partitioned views (either alone or combined with separate tables) could be the better option when tiered storage is required. 

    It is also the mistake to expect that partitioning always reduces I/O activity and improves performance. While it can help in some cases, it can also degrade performance in some scenarios especially when aligned nonclustered indexes are used. This link discusses one of such cases. You should remember that partitioning is management feature that has nothing to do with performance. 


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Prashanth Jayaram Friday, June 27, 2014 2:09 PM
    • Marked as answer by tracycai Monday, June 30, 2014 10:48 AM
    Friday, June 27, 2014 1:59 PM
  • Best practice is to configure the primary filegroup to contain the system tables only and place data on the secondary filegroup. Those two filegroups still can share the same drive. If for some reason, you still prefer to have all database in one filegroup, you can implement partitioning and data archiving process inside the primary filegroup. In addition to easy data maintenance, partitioning will help queries to scan less data which is many cases greatly improves performance.


    • Proposed as answer by pituachMVP Tuesday, June 24, 2014 8:30 AM
    • Marked as answer by tracycai Monday, June 30, 2014 10:48 AM
    Tuesday, June 24, 2014 8:01 AM

All replies

  • Best practice is to configure the primary filegroup to contain the system tables only and place data on the secondary filegroup. Those two filegroups still can share the same drive. If for some reason, you still prefer to have all database in one filegroup, you can implement partitioning and data archiving process inside the primary filegroup. In addition to easy data maintenance, partitioning will help queries to scan less data which is many cases greatly improves performance.


    • Proposed as answer by pituachMVP Tuesday, June 24, 2014 8:30 AM
    • Marked as answer by tracycai Monday, June 30, 2014 10:48 AM
    Tuesday, June 24, 2014 8:01 AM
  • Table partitioning is very broad subject and the question if table needs to be partitioned is a bit separate from data/filegroups layout. 

    As Maria already mentioned, the best practices recommend you to keep primary filegroup empty. This will allow you to reduce recovery time by implementing piecemeal restore. It is also correct, that you can create additional filegroup(s) and put them to the same drive. In the future, when environment changes, it would allow you to redesign your data files layout and reduce solution cost by implementing tiered storage with old, rarely accessed data stored on the slower disk arrays.

    With all being said, it is almost impossible to move individual partition data from one filegroup to another keeping table online. SQL Server acquires schema modification (SCH-M) lock for the duration of data movement, which prevents other sessions from accessing the table. There are the possibilities of some complex solutions, such as switching partition to separate table, creating partitioned view on both tables, move data with online index rebuild, etc. However, it is complicated from both, maintenance and implementation standpoints and, in the large number of cases, partitioned views (either alone or combined with separate tables) could be the better option when tiered storage is required. 

    It is also the mistake to expect that partitioning always reduces I/O activity and improves performance. While it can help in some cases, it can also degrade performance in some scenarios especially when aligned nonclustered indexes are used. This link discusses one of such cases. You should remember that partitioning is management feature that has nothing to do with performance. 


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    • Proposed as answer by Prashanth Jayaram Friday, June 27, 2014 2:09 PM
    • Marked as answer by tracycai Monday, June 30, 2014 10:48 AM
    Friday, June 27, 2014 1:59 PM