Advantage of Table Partitioning
• No need to create separate physical tables for each ranges manually.
• There are few limitations when you insert data into Partition View like you cannot use BULK INSERT/BCP etc.
• Less complex to manage and administrate
• SQL Server automatically manages the placement of data in the proper partitions.
Disadvantage of Table Partitioning
• Major disadvantage is, we cannot have different index model for different range. Ie. In general, we may need to have more number of indexes when the data is READ ONLY and less or different index model for the data which is READWRITE. That is not possible with partition table.
• You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.
Showing posts with label Guidelines for table partitioning. Show all posts
Showing posts with label Guidelines for table partitioning. Show all posts
Friday, November 27, 2009
FAQ: How to determine partition suits for a table ?
There are few general guidelines for determining whether the partition feature is suited for a table or not. One point to be noted here is, if a table is not partitioned properly (ie. Not selected the most suitable partition column), though it will not adversely affect the performance but it will not give any performance benefit either. Remember that the query optimizer can limit the partitions that are scanned to resolve a query only if the query filters on the partitioning column. Precisely, just because the table is partitioned, we will not have performance benefit.
1. First of all, just because of a table is big, it should not be partitioned. Ie. Only size of the table cannot be "THE" criteria for partition.
2. If the index maintenance on a table is time consuming or make the table offline for the users, you may opt partition because re-indexing on only required partition is better than re-indexing the whole table. Partitioning a large table divides the table and its indexes into smaller partitions, so that maintenance operations can be applied on a partition-by-partition basis, rather than on the entire table.
3. You can go for partition if the data must be aged out of the table (archived) periodically and the current delete process is taking time or blocking other users.
4. If the table is loaded with new data periodically and the loading process takes time and the table data lends itself to a partition column based on ascending date or time we may go for partition.
5. Table backup of huge table can be managed smartly by using new backup features of sql server 2005 and partitioning.
6. To summarize, the best way to put this in single statement is, if you have any table which fits into SLIDING WINDOW scenario you must partition it.
1. First of all, just because of a table is big, it should not be partitioned. Ie. Only size of the table cannot be "THE" criteria for partition.
2. If the index maintenance on a table is time consuming or make the table offline for the users, you may opt partition because re-indexing on only required partition is better than re-indexing the whole table. Partitioning a large table divides the table and its indexes into smaller partitions, so that maintenance operations can be applied on a partition-by-partition basis, rather than on the entire table.
3. You can go for partition if the data must be aged out of the table (archived) periodically and the current delete process is taking time or blocking other users.
4. If the table is loaded with new data periodically and the loading process takes time and the table data lends itself to a partition column based on ascending date or time we may go for partition.
5. Table backup of huge table can be managed smartly by using new backup features of sql server 2005 and partitioning.
6. To summarize, the best way to put this in single statement is, if you have any table which fits into SLIDING WINDOW scenario you must partition it.
Subscribe to:
Posts (Atom)
Posts
