Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extremely slow commits Expand / Collapse
Author
Message
Posted Thursday, December 27, 2012 5:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:58 AM
Points: 2, Visits: 17
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?
Post #1400580
Posted Friday, December 28, 2012 4:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
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
Post #1400866
Posted Friday, December 28, 2012 2:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 10:49 AM
Points: 21, Visits: 312
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.
Post #1401055
Posted Friday, January 4, 2013 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:58 AM
Points: 2, Visits: 17
Thanks ngreene, have got some I/O perfmons set up on the drives now. Just waiting for it to happen again!
Post #1402897
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse