SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Extremely slow commits


Extremely slow commits

Author
Message
jim 29109
jim 29109
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 19
I am a .NET dev working with have a high-availability SQL2008-R2 database, which is a bit of an OLTP/OLAP mix.

Periodically we suffer from extremely long commits (>8 seconds (yes, seconds...)).

These particular transactions involve about 20 Selects and a few updates, none of which typically takes more than a few milliseconds. When things are ok the whole transaction takes <250ms, even at times of high load.

My application logs are pretty clear that it is the Commit operation and this has been confirmed by the DBA. We have query timeout set to 6 seconds on these particular appservers. The issue does not seem to last more than the 8 seconds or so.
The DBA is blaming MaxDop as he can see CXPACKAGE wait types around the time that we experience the issue, and is suggesting that MaxDop is set to 1. However, that would adversely impact many extracts and heavy queries that we need.

I cannot find anything suggesting that Commits are affected by parallelism, and think it is much more likely to be a problem with the TLog. I have asked the DBA to look for WRITELOG wait types but he is hell-bent on changing MaxDop! (Currently set to 12, with 24 CPUs available). While this issue occurs at a period of high usage, the SQL Server machine never appears stressed (at least in terms of CPU and memory – I have not been able to check I/O). Personally I think the CXPACKAGE wait types are a symptom rather than a cause, but I am no expert in this area...

Has anyone got any suggestions on how to go about investigating it further? And whether MaxDop could make a difference to Commits?
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5332 Visits: 4076
jim 29109 (12/27/2012)
whether MaxDop could make a difference to Commits?
i dont think maxdop can affect the commit although it can speedup the queries at the cost of CPU. have you tried to test the database query through management studio directly and ask the DBA to set the trace and see whats baking the query

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
ngreene
ngreene
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 347
Check for blocking in sys.dm_exec_requests. I have ran into situations where fast DML operations slowed down dramatically and not always, but frequently it was blocking. Since the database is not only for OLTP there could be other report operations hitting the data in the destination tables.

If your 100% sure its the commit, then monitor using perfmon, the IO activity on the specific drive the log files are on.
jim 29109
jim 29109
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 19
Thanks ngreene, have got some I/O perfmons set up on the drives now. Just waiting for it to happen again!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search