Question on Database Mirroring SQL Server 2016 RRS feed

  • Question

  • Dear Experts,

    We just migrated a set of prod and DR servers to SQL Server 2016 Enterprise from SQL 2008 Enterprise. These are workgroup servers.

    DB Mirroring was used for a database on SQL 2008. Client only wanted DB mirroring configured on SQL 2016 and were not interested in Always on. Testing went fine.

    Today they had to insert 2 millions record in a table on the principal SQL 2016 database and encountered considerable slowness. It look them 2 hours to load 500,000 records and estimated to take 8 hours for the total load. However, the same load took

    - only 20 minutes on the old principal (prod) database which was on SQL Server 2008

    - Same load took only 20 minutes on the UAT SQL Server 2016 database

    The mirroring configuration on both the old and current systems is high safety without automatic failover (synchronous). Once I paused the mirroring on new production database on SQL Server 2016, the load completed in around 10 minutes. We did not have to do in SQL Server 2008. Is there any reason why I had to pause mirroring for the insert to complete quicker. Can you please share your thoughts?

    In fact, configuration wise, new servers have double the memory (32 GB). while the insert was running, there were no other processes running or causing any blocking


    • Edited by MnMkeyviwel Wednesday, August 1, 2018 2:33 AM
    Wednesday, August 1, 2018 2:30 AM

All replies