Database Mirroring Slow
in [Server Configuration]
|
From: Kate Carowski on 25 May 2010 12:24 Hello, I'm running SQL 2008 database mirroring (high-safety) for a single database. The mirrored server is on the same LAN. I'm running the following query but it seems to be taking a huge amount longer when mirroring is enabled. IT takes around 15 seconds to run without mirroring and when I enable mirroring it takes almost 5 minutes! DECLARE @counter INT SET @counter = 0 WHILE @counter < 100000 BEGIN INSERT INTO t_names (int_id, [name]) VALUES (165, 'jane dale') SET @counter = @counter + 1 END I'd obviously expect a slight performance hit but not the slowdown that I am seeing in this instance? Thanks Kate
From: Dan Guzman on 26 May 2010 08:14 > I'd obviously expect a slight performance hit but not the slowdown that I > am seeing in this instance? In high-safety mode, the principal waits (synchronously) until the transaction is committed on the mirror. This introduces a significant amount of latency when you have a lot of transactions, as in your test. Exactly how much latency depends on a number of factors. Does the script take 15 seconds if your run the script locally on the mirror server database (without being a partner)? What sort of throughput do you see of you wrap the WHILE loop in a transaction in high-safety mode? -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "Kate Carowski" <kcarow63(a)hotmail.com> wrote in message news:uQW$PbC$KHA.5044(a)TK2MSFTNGP04.phx.gbl... > Hello, > > I'm running SQL 2008 database mirroring (high-safety) for a single > database. The mirrored server is on the same LAN. I'm running the > following query but it seems to be taking a huge amount longer when > mirroring is enabled. IT takes around 15 seconds to run without mirroring > and when I enable mirroring it takes almost 5 minutes! > > DECLARE @counter INT > SET @counter = 0 > WHILE @counter < 100000 > BEGIN > INSERT INTO t_names (int_id, [name]) VALUES (165, 'jane dale') > SET @counter = @counter + 1 > END > > I'd obviously expect a slight performance hit but not the slowdown that I > am seeing in this instance? > > Thanks Kate
From: Kate Carowski on 27 May 2010 05:18 thanks for the reply Dan. Yes, wrapping it in a BEGIN TRAN.... COMMIT dramatically reduces the time of the execution, right down to 2 seconds in fact for the 100,000 rows. I guess I just need to gather some information on exactly how the mirroring works. Do you know of any technical articles on the inner workings of db mirroring? "Dan Guzman" <guzmanda(a)nospam-online.sbcglobal.net> wrote in message news:BFE1ACC6-7083-45AF-B6D3-7A85D0940104(a)microsoft.com... >> I'd obviously expect a slight performance hit but not the slowdown that I >> am seeing in this instance? > > In high-safety mode, the principal waits (synchronously) until the > transaction is committed on the mirror. This introduces a significant > amount of latency when you have a lot of transactions, as in your test. > Exactly how much latency depends on a number of factors. > > Does the script take 15 seconds if your run the script locally on the > mirror server database (without being a partner)? What sort of throughput > do you see of you wrap the WHILE loop in a transaction in high-safety > mode? > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > "Kate Carowski" <kcarow63(a)hotmail.com> wrote in message > news:uQW$PbC$KHA.5044(a)TK2MSFTNGP04.phx.gbl... >> Hello, >> >> I'm running SQL 2008 database mirroring (high-safety) for a single >> database. The mirrored server is on the same LAN. I'm running the >> following query but it seems to be taking a huge amount longer when >> mirroring is enabled. IT takes around 15 seconds to run without mirroring >> and when I enable mirroring it takes almost 5 minutes! >> >> DECLARE @counter INT >> SET @counter = 0 >> WHILE @counter < 100000 >> BEGIN >> INSERT INTO t_names (int_id, [name]) VALUES (165, 'jane dale') >> SET @counter = @counter + 1 >> END >> >> I'd obviously expect a slight performance hit but not the slowdown that I >> am seeing in this instance? >> >> Thanks Kate >
From: Dan Guzman on 27 May 2010 08:38 > Do you know of any technical articles on the inner workings of db > mirroring? I suggest you start with the Database Mirroring topics in SQL Server Books Online (http://msdn.microsoft.com/en-us/library/bb934127(v=SQL.105).aspx). Synchronous (high-safety) is described in topic http://msdn.microsoft.com/en-us/library/ms179344(v=SQL.105).aspx. Be aware that each commit is a synchronous write to the transaction log so transaction log write performance I critical to overall throughput. Make sure it is optimal on both the principal and mirror. You likely have a write-caching controller in the principal since you achieved over 6K transactions/sec in your 15 second test (a single spinning disk can typically only handle a couple of hundred I/Os per second). When you add high-safety mirroring, transactional throughput will be reduced by at least a factor of 2, plus network latency and other overhead. So, an addition of only a few milliseconds per mirrored transaction will significantly limit the maximum possible transaction rate as you observed. The question is whether you actually need to sustain a rate of 6K/sec. If you currently perform less than a few hundred per second, then high-safety is a viable option without noticeable performance degradation. OTHO, if you need to sustain several thousand/sec., then synchronous mirroring probably isn't an option. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/
|
Pages: 1 Prev: Receiving from Service Broker Queue Next: How to export policies to excel |