There are a variety of reasons that you may have large tables. When these tables (or databases) reach a certain size, it becomes difficult to perform activities such as database maintenance, backup, or restore operations that consume a lot of time. Environmental issues such as poor concurrency due to a large number of users on a sizable table result in lock escalations, which translates into further challenges. If archiving the data is not possible because of regulatory compliance needs, independent software vendor (ISV) requirements, or cultural requirements, partitioning is most likely the tool for you. If you are still unsure whether to implement partitioning, run your workload through the Database Tuning Advisor (DTA), which makes recommendations for partitioning and generates the code for you.
Following is a high-level process for partitioning:
1. Create a partition function to define a data-placement strategy.
2. Create filegroups to support the partition function.
3. Create a partition scheme to define the physical data distribution strategy (map the function data to filegroups).
4. Create a table or index on the partition function.
5. Enjoy redirected queries to appropriate resources.
After implementation, partitioning can positively affect your environment and most of your processes. Make sure you understand this technology to ensure that every process benefits from it. The following list presents a few processes that may be affected by partitioning your environment:
- Database backup and restore strategy (support for partial database availability)
- Index maintenance strategy (rebuild), including index views
- Data management strategy (large insert or table truncates)
- End-user database workload
- Concurrency:
- Parallel partition query processing: In SQL Server 2005, if the query accessed only one table partition, then all available processor threads could access and operate on it in parallel. If more than one table partition were accessed by the query, then only one processor thread was allowed per partition, even when more processors were available. For example, if you have an eight-processor server and the query accessed two table partitions, six processors would not be used by that query. In SQL Server 2012, parallel partition processing has been implemented whereby all available processors are used in parallel to satisfy the query for a partition table. The parallel feature can be enabled or disabled based on the usage requirements of your database workload.
- Table partition lock: escalation strategy.
- Enhanced distribution or isolated database workloads using filegroups
Leave a Reply