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?