Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2016
    Posts
    3
    Provided Answers: 1

    Answered: SQL is working really slow after adding Mirror (async mode)

    Hello,

    We have a small database (270GB), with SQL Server 2012 SP3.

    We need to create a real-time backup to secondary server over WAN, and our changes are very small - so it should work fine.

    I changed the DB Recovery Model from Simple to Full Recovery, and created a mirror using Async mode.

    it all worked fine for 1-2 days, but then i suddenly had a big slowness, and querys took 30-40 seconds instead of less then 1 second (a simple get top 1000 query), another thing i saw was the Perf Monitor shows very high read (1gb/s) from the disk when my app is working (normally on 1-2mb/s).

    i tried to pause the mirror, and restart the SQL service - that did not help.

    i even removed the mirror completely, and that did not help.

    what DID help - was a full backup - but it only worked for 15-16 hours then it stuck again untill i did another Full backup.

    I now also changed the Recovery Mode to "Simple' again - hopefully that will resolve it -


    My question is - am i missing some procedure ? are logs \ data gathering and it killing the DB ?

    the sync was 100% at all time, and the DB on the other server was online and getting the changes rather quickly, so its not the changes awaiting to be written.

    Thanks for your answers.

  2. Best Answer
    Posted by GodAnubis

    "Just wanted to say that creating log backup every 6 hours completely solved this issue.

    thanks !"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There is not enough information for me to find the answer to your question yet.

    My first question is: How did you configure your transaction log backups while you were using FULL recovery?

    Next questions would be how have you arranged your SQL files on disk? All files on one logical drive, system/data/log/backup on different logical drives, a mix? Which IO read counters went high?

    What infrastructure (power, VM, SAN, TCP/IP, etc.) do your mirrored systems share?

    I'll probably have more questions, but these should get me started helping you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Apr 2016
    Posts
    3
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    There is not enough information for me to find the answer to your question yet.

    My first question is: How did you configure your transaction log backups while you were using FULL recovery?

    Next questions would be how have you arranged your SQL files on disk? All files on one logical drive, system/data/log/backup on different logical drives, a mix? Which IO read counters went high?

    What infrastructure (power, VM, SAN, TCP/IP, etc.) do your mirrored systems share?

    I'll probably have more questions, but these should get me started helping you.

    -PatP
    Thank you for your reply,

    I did not configure transaction log backups - i think this might be the problem (the video i used to create the mirror did not include this info)
    all files are on the same drive (logical drive) but other logical drives are on the same physical HD
    the infrastructure is a Dedeicated server physical

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That's not a "normal" problem fixed by transaction log backups, but I have seen it happen a few times.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #5
    Join Date
    Apr 2016
    Posts
    3
    Provided Answers: 1
    Just wanted to say that creating log backup every 6 hours completely solved this issue.

    thanks !

  7. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It could be that if the log was not getting backed up, the number of logical log files in the transaction log was getting to a point where it could choke the mirroring process. You may want to spend some time with the DBCC LOG() command, and see if you can get the number of logical log files down to under 1,000 at least (most suggest under 200).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •