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

For archiving (or other large transaction operations) in AlwaysOn and mirror environments, I think we should pay attention to:

1. Try to avoid doing large transaction operations in synchronous mode, and once you decide to do so, please change to asynchronous mode.

two。 Do it frequently and loop delete top n and waitfor every time you do it.

3. Index maintenance, change index reconstruction to index reorganization as far as possible.

4. You should delete in large numbers, cut into small pieces to delete or not file, rename the original table, and create a new table with the same schema.

5. Modify the data files of the mirror database to PCI-E SSD, startup can speed up synchronization, and should also be able to be used on alwayson.

6. Synchronous commit mode brings latency for large transactions, especially in environments where large ETL batches must be completed on weekends or evenings to prepare data for the next business day. Using explicit transactions can optimize load performance and still take advantage of the security provided by synchronous commit (to avoid data loss) and reduce HADR_Commit waits.

7. Upgrade to AlwaysOn 2016 to take advantage of the latest log performance improvements to change log capture and application from single-threaded to multithreaded.

Better log transfer performance

With the widespread use of high-speed hardware SSD, it provides greater throughput, which is important for writing transactions to secondary copies. As a result, Microsoft updated AlwaysOn's data synchronization process, simplifying the pipeline to have better throughput and less pressure on CPU. Most performance bottlenecks can occur in log capture (Log Capture) and redo (Redo) steps. Previously, log capture and redo steps used a single thread to process logs, but now these steps run in parallel with multiple threads, greatly improving performance.

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.