Relational data Warehouse partitioning strategy in SQL Server (2)

Which strategy is better?

Partitions can be implemented either by using one of these two policies or by combining two policies effectively. For strategies I and Strategy II, refer to the table below, which explains the effects of these two strategies on partitioning factors in relational data warehouses.

	Strategy I	Strategy II
Data import	You cannot specify a filegroup explicitly in a Select/into SQL statement. Because of this limitation, the initial conversion cannot be done in parallel with a select/into statement Stepping data import is not affected by the partitioning policy	Transformations can be done in parallel when all partitions are mapped to the same filegroup Stepping data import is not affected by the partitioning policy
Backup/Restore	Create a one by one-corresponding relationship between filegroups and partitions to allow fragmented backup and restore operations at the partition level Make sure that the partition is marked read-only before backing up the database. If not, the transaction log must be rolled forward when the database is restored	If all partitions are mapped to the same filegroup, the partitioned table as a whole can be backed up and restored using a single command This policy does not provide the flexibility of fragmented backups under the granularity of a single partition

Query performance

If the filegroup contains only one file, and the table is populated one after another, the allocation of the extents of such an object is contiguous. This means that SQL Server can provide up to 256 KB of I/O (4 extents) rate for a continuous scan Because the data is continuous, it can provide a better workload for a large number of sequential scans

If a filegroup is made up of multiple files, SQL Server uses a proportional fill mechanism that causes fragmentation of the extents Similarly, extents allocated to objects/partitions during parallel operations such as parallel data import operations are not guaranteed to be contiguous When the extents allocated to an object are not contiguous, the rate of I/O (one extents) of approximately kilobytes of KB can be provided for continuous scan SQL Server Because the data is distributed across many physical disks, it helps provide better workload performance as a replacement for a large number of concurrent random I/O operations, and you can add the-e switch when you start SQL Server. When the-e switch is specified at startup, SQL Server can allocate 4 instead of one extents. Thus the-e switch allows SQL Server to provide up to 256 KB of I/O rates even if there are extents fragmentation resulting from the use of a proportional fill mechanism

Conclusion

This white paper discusses the factors that affect partitioning, as well as the pros and cons of the two main strategies that the design partition can use. The information provided here may help to manage your relational data warehouse more effectively through partitioning.

For more information, please visit: http://www.microsoft.com/sql/

This document shows some of the special features associated with relational data warehouse partitions in SQL Server 2005. For more information, please refer to:

SQL Server 2005 Books Online provides valuable information on this topic, and it can be a good starting point for using SQL Server 2005 to implement data partitioning.

CLARiiON CX600 Web site: http://www.emc.com/products/systems/clariion_cx246.jsp