Shulou(Shulou.com)06/01 Report--

Effective index maintenance under SQL Server database mirroring

When we do index reconstruction, because the index reconstruction produces too many logs, we are used to switching to the mass log recovery model. In the production environment, we configure database mirroring as highly available, and mirroring can only run in the full recovery model, so don't we do index maintenance?

This is a common problem when mirroring is used in production environments. Although many people do load performance tests before using mirrors, in my experience, maintenance operations are not included in their tests. Regular maintenance operations are part of the business process, so we should include them in all test scenarios.

Let's first understand the principle of using the bulk-logged recovery model to allow index rebuilding (using ALTER INDEX. The REBUILD) operation produces fewer transaction logs, that is, the transaction log is not as large as the operation performed under the full recovery model. Database mirroring allows only the full recovery model, so index reconstruction records full logs. The large amount of extra logs generated can easily lead to network problems between the master and the mirror, which can lead to very large SEND queues on the master. If the SEND queue becomes very large, there is a potential risk of data loss when the main outage occurs, even beyond the tolerable data loss service-level agreement.

So what should we do?

We can stagger the time to use ALTER INDEX. REORGANIZE does index maintenance. Reorganizing the index only deals with existing fragments, which can be interrupted without losing the work that has been done. Rebuilding an index, no matter how many fragments there are, will always create a new index, and if you interrupt, the entire rebuild process will be rolled back.

For large indexes, it is impractical to rebuild, and the following steps should be performed:

Day 1: in the maintenance window, start ALTER INDEX. REORGANIZE . Let it run for an hour, and then KILL off. It will not roll back the operation of index maintenance, and the index reorganization just now will remove some fragmentation.

Day 2: start reorganizing the index again. It will not remember where the first day of work went, it will quickly go through the first day of work and begin to remove fragments from the next part of the index. The KILL dropped in an hour later.

Repeat the above until the fragment level falls to the appropriate threshold, or just continue daily processing indefinitely.

This method allows you to limit the number of transaction logs generated through regular index maintenance. If you want to optimize more deeply, instead of killing the reorganized process after a certain amount of time, you can monitor the number of transaction logs generated and then kill them after reaching a certain threshold. For more information, please see: Script: open transactions with text and plans (http://www.sqlskills.com/blogs/paul/script-open-transactions-with-text-and-plans/)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.