August 29, 2013 at 8:04 pm
Hi Folks,
I am working with sql server 2008 r2 standard (build 10.50.4000)
Occasionally (not all the time) we get slow data modifications (e.g 16+ seconds) for the following updates and inserts - see attached file for slow updates and inserts.
I have also attached the schema of the tables involved.
Running a blocked process report in profiler (threshold 4 seconds) come up with nothing when the data mods come up with slow response times
Wait stats show mirroring waits - but looking at the mirror history the mirror has been synchronized during the time when data mods have been an issue.
Is anyone able to advise what else I should be looking for here?
August 30, 2013 at 4:07 pm
Are there any triggers on the table? The INSERT statements themselves are plain vanilla, but there could be issues with trigger code.
But since you say mirror waits, I guess you have set up mirroring to be synchronous, which means that if there are delays in connection to the mirror or the I/O subsystem on the mirror that you will see delays.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 2, 2013 at 12:13 am
thanks for your help!
No triggers on the tables.
Yes it is using synchronous mirroring.
September 2, 2013 at 1:12 am
OK, if there are no triggers no the tables, we can exclude problems with query plans as such, but problem is related to the mirroring setup.
One way to resolve it is to give up on synchronous mirroring and use asynchronous instead. But that requires that you fully understand the consequences for disaster recovery - asynchronous mirroring means that there is a risk for data loss in case of a failover.
Another angle is to look at the context of these INSERT statements. I guess since they are problematic, that you are not submitting these in singletons a few times a day, but maybe you are inserting many rows at a time? In such, there is plenty of oppurtunities for improvement!
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 2, 2013 at 1:17 am
As I said - I'm using sql server standard .. asynchronous mirroring is not avialable in that edition I believe (correct me if I'm wrong).
Can you clarify
Another angle is to look at the context of these INSERT statements. I guess since they are problematic, that you are not submitting these in singletons a few times a day, but maybe you are inserting many rows at a time? In such, there is plenty of oppurtunities for improvement!
I think you may have missed a few words so I am unsure what you mean:-)
thanks!
September 2, 2013 at 2:38 am
Sorry, I overlooked the edition issue.
What I tried to say is that while you could investigate what is going on with your hardware, you could also consider these INSERT statements. They insert a single row, and if there is only a single row to insert, there is not much to do about it. But since they were problematic, my suspicion is that there are more than one row to insert, and the INSERT statements are performed as part of a loop. And in this case there is certainly room for improvements.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 2, 2013 at 2:46 am
thanks,
if it was more than 1 statement I wouldn't expect to see it multiple times in profiler? (i.e because the statement was in a stored proc I actually used the Audit Schema Object event to monitor those statements.
I am only seeing the statement once before the procedure ends.
Currently I am monitoring in profiler looking for statements greater than 5 seconds to see when the procedure fires again and also monitoring for status changes in mirroring
September 2, 2013 at 3:23 am
I don't know your application, so I can only make speculations. Nor do I know why think you actually think this is a problem. That is, my assumption is that someone has reported a performance problem.
Keep in mind that even if the stored procedure is a single INSERT statement, the procedure may be called repeatedly.
And please observe that I don't anything - it is just that I have seen bad application patterns before.
And, of course, if this a third-party application there is not much you can do but bug the vendor.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 2, 2013 at 7:19 pm
yes that right we are getting complaints of timeouts. The stored procedure doing these inserts contains most of the longest running queries.
I was just asking that question
if it was more than 1 statement I wouldn't expect to see it multiple times in profiler?
to make sure I was not missing anything - I'm sorry if it was interpreted the wrong way.
This stored procedure does get called repeatedly. But I am guessing you are wondering if there a content issue. The thing is I ran a blocked process report in profiler (4 second threshold) for a extended period of time (at least a day) and when that procedure ran slow no blocked process report occurred. I did block a process in a dummy database on the db instance to make sure it was working ok.
Thanks!
September 3, 2013 at 1:56 am
Please forgive me. I like to help you with your performance problem, but I only have fragmentary information and it is difficult to piece things together.
It seems that you have a longer stored procedure, and occasionally it runs for such a long time that the clients time out. By using Profiler you have found that the slow statements are two INSERT statements to insert a single row. What else is going on in this stored procedure? Could you post the code?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 3, 2013 at 2:13 am
If you're seeing mirroring-related waits, check the latency of the line to the mirror, make sure that nothing's wrong, check write throughput on the mirror's log drive. The state will be synchronised (SQL's spending a long time on the inserts to ensure that), it'll only switch to synchronising if it falls way behind.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2013 at 2:20 am
Its all good - I appreciate any advise you can give.
Anyway I sent you two procedures because I think our archiving process might be involved with causing the issues. Apparently it runs 24/7 so I spoke to the vendor about limiting its run time. I was concerned that the mirrored archive database was interfering with the day to day transactional stuff -but I'm not exactly sure if that is the issue because I would of expected something to show up with the blocked process report when I run it earlier.
So after discussions today with the vendor I have decided to remove mirroring from the archive database. It is a bit early to tell but it so far 1 have only got 1 query/stored proc in the last 8.5 hours that is running slow (over 5 seconds).
Anyway the procedures are attached
insertvehiclenotification.txt is the stored proc that contains those sql statement I originally sent.
movearchiverecordtotable.txt is the proc that move data out of the live table into the archive tables.
Let me know if I can help you/help me any other way:-)
September 3, 2013 at 2:36 am
I forgot to mention a couple of other things.
My infrastructure guys tell me they see no problem with the network link between principal and mirror.
A few times I continously pinged the mirror box (for about an hour at a time) from the principal most of the time the return trip time was less than 1ms but around the time my procedure ran slow I got a couple (no more than 5) return trips around the 25-35ms. Because there was only a couple of slow rtts infrastructure did not think it was an issue.
A few times last week our System Center Operations Manager system complained that databases on the suspect database instance had changed there mirroring state from synchronized to synchronizing. Gilamonster mentioned that the management studio will not show a synchrozing state unless the databases are a lot out of sync - would it show synchronizing if the database where out of sync for more than 10 seconds because sometimes my proc can run for more than 15 seconds.
September 3, 2013 at 2:41 am
djordan 4543 (9/3/2013)
Would it show synchronizing if the database where out of sync for more than 10 seconds because sometimes my proc can run for more than 15 seconds.
Unlikely. Synchronous mirroring means SQL tries to stay synchronous, that means waiting for log commits on the mirror. Falling back to synchronising means it's failing to do what it's required to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 3, 2013 at 2:01 pm
The plot thickens...
Anyway, the stored procedure had quite a lot of INSERT statements, so why would only these two be slow? If it's a problem with mirroring, it could happen anywhere in the procedure one would think. Unless these tables are on a particular filegroup, but I doubt since they are on PRIMARY.
Anyway, your ping times indicates that there is a connection to the mirror. Maybe because the archival procedure helps to consume the bandwidith. The funny thing is that this one seemed to work one row at a time (but the code was about unreadable), which inefficient, but lean on resources.
As for the procedure InsertIntoVehicleNotification, permit to try to what explain what I was getting at earelier. Say that the client needs to insert 30 notifications. Apparently then it will call the procedure 30 times. It if it was rewritten to accept a table-valued parameter, all 30 rows could be inserted at once. Of course, if this is a vendor app, there is little you can do, but to bug the vendor.
I also note that there is no transaction in the procedure, but maybe the client takes care of that.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply