Extremely slow commits

  • 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?

  • 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;-)

  • 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.

  • Thanks ngreene, have got some I/O perfmons set up on the drives now. Just waiting for it to happen again!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply