Post reply

Diagnosing source of Error 601 – Data movement during NOLOCK

  • November 11, 2015 at 10:46 am

    #305005

    We have (on same server) two 3rd party (OLTP) databases/APPs that make heavy use of NOLOCK. I hate it because of risk that data is missing/duplicated in queries and users rely on the bottom line that they see.

    Some time ago I put in place an alert notification on Error 601 and yesterday I got an email. That's good (I think??) as it confirms my concern that data CAN change under-foot.

    But from the email I cannot see which database (and better yet: which query) triggered it. The SQL Error Log gives me the SPID but I don't think (after the fact) that helps me.

    Is there a way I can trap this better, next time it happens, to get a better diagnostic?

    (Ideally I would like to know which database, and preferably the actual query)

  • November 11, 2015 at 10:53 am

    #1839173

    Try setting up an Extended Events session.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 11, 2015 at 11:05 am

    #1839175

    Thanks Gail. Bit beyond my capability - I'm aware of EEs, had a brief look, but never tried to actually code one. If you can easily point me in the direction of a starter-for-10 example that would be appreciated.

    Could I get the same out of SQL Profiler? I've got plenty of experience of that 🙂

  • November 11, 2015 at 11:09 am

    #1839178

    Profiler, no. Hence the explicit suggestion of XE only.

    Just dive in. It's got a GUI in 2012, create new session and look around (or read Jonathan's XE series on his SQLSkills blog. I think Jason (SQLrnnr) may have one on his too)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 11, 2015 at 12:08 pm

    #1839191

    GilaMonster (11/11/2015)

    It's got a GUI in 2012

    Ah, that sounds encouraging, thanks. I'll check out the blog links too. It looks a bit daunting, as a newbie, last time I looked!

  • November 12, 2015 at 4:44 am

    #1839308

    If you're on 2012, the GUI is really easy. Don't use the Wizard. It's a waste of time and more than a little misleading.

    "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

Viewing 6 posts - 1 through 5 (of 5 total)

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