A naukri.com initiative
Dbi-Services
1M
301
Image Credit: Dbi-Services
Customer case study – SQL Server table partitioning
- A client submitted the following issues about their SQL Server: their database hosts multiple tables, including one table of approximately 1 TB, consisting of several billion rows; the client wanted to archive certain data; and, they also wanted to reduce the duration of maintenance jobs for indexes, statistics, and those responsible for verifying data integrity.
- They decided to partition the large table, as partitioning a large dataset can enhance query performance and simplify maintenance. In their case, they decided to create a new table that is an exact copy of the source table but is partitioned.
- In this partitioned table, they created corresponding filegroups and used a partition function based on a datetime column to determine the partition ID according to the date value.
- Creating a partition scheme maps the partition ID to the filegroups, and finally, they copied data from the source table to the new partitioned table and built the indexes.
- Once the bulk of the data was copied, they were able to retrieve the delta data and switch to the new partitioned table without affecting applications that use it. In this way, they were able to process queries more efficiently and maintain the table more easily.
- They used Ola Hallengren's solution for database maintenance, which allowed them to verify data integrity and filter by filegroup. For index maintenance, they used a cursor and controlled partition by partition by dynamically generating SQL queries.
- Finally, they were able to create a job that checks data integrity and filter by filegroup and partition maintenance so that they only need to check or maintain relevant partitions.
Read Full Article
18 Likes
For uninterrupted reading, download the app