locked
High "Disk Write Bytes/sec" in Mirror server RRS feed

  • Question

  • Hello,

    I'm having the same problem that this fellow here is having:

    http://social.msdn.microsoft.com/Forums/en/sqldatabasemirroring/thread/16f867a6-8c13-44c7-a6b1-cd623f09d7d5

     

    Even though we have a much smaller database, our "Disk read per sec" in the mirror is a lot higher than the main database.

    We have two machines who are just about the same configuration, with a lot more load, with a very low counters for all the Physical Disk counters, they work fine, mirror and principal. Our problematic databases, both are running SQL 2005, the mirror is in high safety with no witness. All machines are for SQL use only. There is no workload from other programs at all at them. All applications are run on third machines.

     

    Me and my friend, the DBA, started investigating and came up with a change at a .Net transaction that may have triggered this problem.

    We have a table under a concurrence of 100 transactions per sec, and we had a scenario where a Queue registry had to be taken and associated with a order number, and we were using Serializable transaction in this process. We discovered that for example one registry was being associated with more than one order, so we changed the SELECTs statements to use UPDLOCK & READPAST hints, and so had to remove the 'Serializable Isolation' level for the transaction. Then that's when we think the problem started.

    Further investigation using SP_WHO and SP_LOCK showed us that there were too many locks on the Queue table, some hit the 7k locks. These locks take a lot to finish to execute. Even though the code is very simple, it evolves three or four tables across two different databases, which are under very concurrence as well.

    We are guessing that fixing the problem with the Queue registry with the orders, broke our database.

    So, what we are going to try now is to make the transaction the shorter the possible, so we can diminish the time to complete the whole operation step, and check if that takes down the number of locks on the Queue table.

     

    Things that are weird for me yet:

    - sp_lock information is not useful when using .Net transaction provider.

    - The key/table lock information is useful for count purposes only.

    - .Net transactions appear as TM_REQUEST under sp_who proc.

    - Extracting the text is not possible from the "dm_" views.

    - DBCC inputbuffer() only shows the last command in the operation sequence, which is a cadence of 5 SqlCommand queries.

     

    Another possibility is to make it all a procedure, but that would be very man/time costly. So we have to deal directly with the .Net source code which will be faster.

    And yet, the principal and mirror machines are connected through a point-to-point cable, the network card is a gigabit and both machines are identical, like twins. That means, there shouldn't be a network problem. And to finish this long story, our disks are on Raid-10, everything is new. We have already changed disks to see if it was a machine problem.

    Oh, and I forgot to tell, that when the mirror is off or paused, all transactions and all the database processes runs very smoothly, all fine, there are few or no blocks. 




    Friday, April 29, 2011 4:58 AM

Answers

  • Hello everyone.

    This is what I found so far.

    I Googled a little more and found this blog:

    http://blog.rolpdog.com/2010/04/sql-server-database-mirroring-woes.html

    And it helped.

    Next I'm trying this:

    http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

    I hope it works.

    Thank you for your attention gentleman.

    Regards.

    • Marked as answer by Du Pereira Wednesday, May 4, 2011 4:02 AM
    Wednesday, May 4, 2011 4:01 AM

All replies

  • Hi,

    What edition of SQL 2005 are you running? If Enterprise / Developer as a test have you tried running the mirroring in high performance and seeing if you still get this issue? (So you eliminate the waiting on the double commit).

    What build of SQL 2005? Are you running with SP4 +?

    If the .NET code is just running SELECTs then you could try enabling READ_COMMITTED_SNAPSHOT on the database(s) being accessed...so that the NOLOCK and READPAST options are not needed, and reads will run from snapshots of the data - may improve concurrency between your .NET code and database mirroring.

    ALTER DATABASE blah SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    ALTER DATABASE blah SET READ_COMMITED_SNAPSHOT ON;

    ALTER DATABASE blah SET MULTI_USER;

    Regards,

    Gary.

    MCP, MCDBA, MCITP Database Administrator

     


    Gary Wells
    Saturday, April 30, 2011 11:08 AM
  • Take a look at Adam's great utiluty

    http://sqlblog.com/blogs/adam_machanic/archive/2011/04/30/twenty-nine-days-of-activity-monitoring-a-month-of-activity-monitoring-part-30-of-30.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, May 1, 2011 8:13 AM
  • Take a look at Adam's great utiluty

    http://sqlblog.com/blogs/adam_machanic/archive/2011/04/30/twenty-nine-days-of-activity-monitoring-a-month-of-activity-monitoring-part-30-of-30.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, May 1, 2011 8:13 AM
  • Why don't you try with Snapshot isolation level as Gary suggested, problem could be with your current isolation with UPDLOCK
    http://uk.linkedin.com/in/ramjaddu
    Monday, May 2, 2011 12:23 PM
  • Hello Gary,

     

    We are running SQL 2005 Standard, with SP4.

    I don't know if that concurrency is the problem at all. It's only what we suspect it is the problem.

     

    When the mirror is on for this database we get a scenario like this:

    % disk write time

    Principal: 100

    Mirror: 600

    And sometimes this goes up like this:

    Principal: 60

    Mirror: 10000

     

    I've been watching locks on the database and they seem very low, including the situation above, which takes around 120 ~ 170 per query.

    When mirror is on, things start to slow down until querying anything is impossible.

    This started a month ago, and I don't know what could have caused it and how to get to the root.

     

    Is there a way to see why writes on the mirror are so high?




    Tuesday, May 3, 2011 1:37 PM
  • Uri,

     

    Thanks for the link.

     

    I used it on the server and got what I already knew, which is, a lot of DBMIRROR_DBM_EVENT with high wait time: 1000ms and higher.

     

    Just to remember, the servers are connected through a point-to-point network card, so there shouldn't be any interference. 

    Tuesday, May 3, 2011 2:09 PM
  • Hello everyone.

    This is what I found so far.

    I Googled a little more and found this blog:

    http://blog.rolpdog.com/2010/04/sql-server-database-mirroring-woes.html

    And it helped.

    Next I'm trying this:

    http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

    I hope it works.

    Thank you for your attention gentleman.

    Regards.

    • Marked as answer by Du Pereira Wednesday, May 4, 2011 4:02 AM
    Wednesday, May 4, 2011 4:01 AM
  • Gary, I might consider using this isolation you pointed me. Thank you.
    Wednesday, May 4, 2011 4:03 AM
  • Uri, this procedure has some really good options. I liked it and will start to use it from now on. Thank you!
    Wednesday, May 4, 2011 4:05 AM