July 26, 2011 at 11:41 pm
Hi all,
I am Using sql server 2005 Standard Edition sp3 with 16 GB Ram, Xeon 32-Bit Processor. Here, i am having 45 MBPS MPLS Line to Connect DR Site. The DR Server Also, Xeon 32-Bit Processor, with 4 GB Ram. IN Normal Hours everything is working fine. The application is also running fine. When the Application Team are uploading some files into Database(The file size is around 6MB). At this time The Application is running Very Slowly. The Data upload alos taking too much time.( Like Before Mirror, it usually takes 10 Minutes & after Mirror it is taking Half-an Hour.) I checked the Network Usage also. There is no Issue with Network as well!! Please help me in Solving this Issue??
July 27, 2011 at 10:01 am
Standard Edition only supports Synchronous Mirroring. In this mode, the transaction must be fully committed on BOTH the principal and the mirror (therefore written to BOTH Tlog files) before the batch is returned.
Sounds like you tested this without mirroring and so have a baseline. That's good.
Next it network, which you say is not the bottleneck.
Lastly, look at the slower, poorer, mirror system overall, especially the ldf write performance.
You may want to baseline loading your file over the network directly to the smaller box. This will isolate mirroring from general slow performance issues.
Also, in your current process, you may be able to do the entire import in one explicit transaction, rather than looping with a lot of transactions. How are you loading the file?
Diagnose the remote boxes performance like any other box: WaitStats, perfmon (especially disk/IO metrics), etc. I am guessing your issue is the slow speed of a substandard IO subsystem on the mirror which is causing everything to crawl.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply