The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P121: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | 582 Chapter 13 Performance Tuning and therefore faster. Partitioning maintains data integrity and it is possible to partition based on any column however date ranges are most common for partitioning. This allows administrators to separate recent usually more active data from older archive data improving the performance of data access to the frequently accessed data usually recent rows . Vertical Partitioning This method splits a large table into a number of smaller tables each table contains every row but a subset of all columns. The database normalization process provides vertical partitioning by removing any attributes not dependent on the primary key joining these with a primary key foreign key constraint. Consider this method with caution since retrieving all columns for a given row will require a join between the tables which could be expensive in terms of performance. Filegroups Successful implementations of table partitioning improve availability and performance of the entire table since many operations can be performed in parallel. Availability can be improved because backup and restore operations can be performed on an individual filegroup. Additionally performance can be improved because CHECKDB and regular scan and seek operations can be performed in parallel when partitions are implemented on their own filegroups. It s common for SQL Server databases to grow to hundreds of gigabytes and multiterabyte databases are no longer unusual. It s likely that the fastest disk storage and backup target full backups will take many hours to complete. Filegroups help by allowing partial backups or by allowing backups to run in parallel. Every database has a primary filegroup and if you re going to create multiple data files it s recommended to create these on a secondary filegroup. Partitions can operate within a single filegroup however it is recommended that to gain the full benefit of table partitioning each partition reside on its own filegroup. This approach .
