Home Forums SQL Server 2012 SQL 2012 - General Slow running query - only off peak RE: Slow running query - only off peak

  • September 14, 2018 at 5:22 am

    #2005731

    sharky - Friday, September 14, 2018 3:33 AM

    Hi Grant,

    Thanks for this. Think blocking is the most probable, and have set up an XE to catch something next time..

    We are using Redgate and a quick question about the counters:

    a) The avg. lock time briefly shoots up when the query starts, but then flatlines for the duration of  execution. Can this be expected behaviour when there is massive blocking?
    b) Will logical reads also shoots up dramatically when blocking occurs?

    Thanks,

    a) I would expect to see lock time go up, yeah. No locking at all (or little locking) suggests you might be looking at simple resource contention. What's happening with the waits?
    b) That's an "it depends" situation. Maybe, maybe not, completely situational. In fact, the more likely scenario is that reads drop, but it's just all around what's actually happening.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning