locked
Trace Flag 3499 to Reduce Transaction delays in Sync Mirroring RRS feed

  • Question

  • Does anyone have any experience with DBCC TRACEON(3499, -1)

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

    This is interesting but there is no info on the Web about this approach to reducing I-O related transaction delays on the partner in a Synchronous mirroring Session.

    Have you tested this? Used it in Production? Are there side effects other than possible slower Automatic Failovers?

    For manual failover I figure one could wait for the size of the Redo Queue to be small enough to ensure a quick Failover.  Is this correct?


    ...Ray
    Friday, January 7, 2011 9:41 PM

Answers

  • Hi Ray!

    Yeah, we have tested it and are not using it. In our high transaction environment it didn't really boost performance significantly.

    That said, there is no reason not to use it if failover time is not your concern. I have not encountered any problems due to that trace flag whatsoever.

    Lucifer

    Wednesday, January 12, 2011 6:03 AM

All replies

  • Hi Ray,

    Thanks for your post! As you can see this trace falg is undocumented, which means unsupported and there is no guarantee that an undocumented feature will continue to behave in the same way or exist in the next version of SQL Server. Please use it under the direction of Microsoft Service and Support therefore I would recommend you open a case with Microsoft CSS (http://support.microsoft.com) if you encounter such issue.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Alex Feng (SQL) Friday, January 14, 2011 10:12 AM
    • Unmarked as answer by RayG Friday, January 14, 2011 2:15 PM
    Monday, January 10, 2011 6:43 AM
  • Hi Ray!

    Yeah, we have tested it and are not using it. In our high transaction environment it didn't really boost performance significantly.

    That said, there is no reason not to use it if failover time is not your concern. I have not encountered any problems due to that trace flag whatsoever.

    Lucifer

    Wednesday, January 12, 2011 6:03 AM
  • Hello Ray,

    I have runned into the same problem of this guy. The flag IS useful.

    To shorten this story, turning this flag 3499 on has eliminated the need to turn off our mirror machine, because things were extremely slow. My counters for "Mirror commit overhead" are very high, sometimes we are hitting the thousands ms (starts very low and with time increases) and it goes higher, until the database almost stops. If I turn off the mirror, things run really fast, with the wind.

    We (me and my fellow DBA) monitored some counters in the PerfMon, and compared with the other machine that is doing good, and the only difference is that the less used (the one with problems) is under heavy load of transactions most of the time, where the other that is good has few or no-impact in the main part of our systems.

    These machine have been reconstructed from zero, we have no problem with hardwares, it is very new and had worked pretty well for some time. Now we are even thinking about switching the partners to check infrastructure.

    We have already tried things like restore the database, use dbcc checkdb, rebuild indexes and statistics, reduce the number of VLF, and we have stopped some of our jobs to check CPU load and memory. The machine is a HP Xeon, X5560 @ 2.80Ghz, 16GB RAM, 1TB RAID 10 SATA Hard Drives. SQL 2005 SP4 + Win2k3.

    This flag is not a booster for reducing SQL transactions, I rather think of it as a problem identifier. Turning it on this, solved my problems with database mirroring, but it is a sign of a problem that I haven't found out yet how to solve. I will call Microsoft for this, because we have two databases with almost the same hardware configuration, and the one with less use is with no problems.

    Here is the topic I created when we first detected this: 

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/7d9cc9c3-a6c0-479f-97b6-2dc11781b5ca

     



    Sunday, May 8, 2011 5:37 AM
  • Do you know if this trace works in 2012? I have added the trace flag, but I don't see any difference in IO.

    Thanks.

    Friday, July 27, 2012 4:38 PM