Monitoring Snapshot Isolation with Perfmon in SQL Server (video)

Snapshot Isolation can be a great way to reduce lock waits and speed up your SQL Server, but long running transactions could still slow you down. Join Kendra Little to learn how to monitor for these sneaky killers using performance monitor.

Brent says: if you’re a developer and you’re getting started building a new SQL Server application, you should totally check out RCSI as a default setting. Learn more about isolation levels here.

Previous Post
sp_BlitzFirst v14 adds CPU % Utilization
Next Post
The Top 3 Mistakes T-SQL Developers Make

9 Comments. Leave new

  • Great video, thanks Kendra.

    Reply
  • Hello all. I applied yesterday for one of the dba positions but after taking the job search interview questions tests I think I may have to reconsider. I don’t feel I qualify as a hardcore SQL DBA.

    While I can always get done what I need to on servers and both developed using SQL Server and maintained my own dev farm everywhere I went, I also handled the networking and user support too, so I didn’t really ever have call to memorize all of these buzzwords and terminology and such. I am completely self taught on computers, so I learn by doing and am a completely different guy while on a computer than I am trying to remember and talk about what I do. This is why current interview process do me no favors – I have a horrible memory even for things I do every day, despite being able to do them every day.

    I don’t truly know to what degree I have to be able to ace these questions, but when you are tasked with writing code in 5 different programming languages, dealing with every version of AD post 2000, dealing with two different versions of SQL, and then dealing with users and being the subject matter expert on every piece of software running on the system, there comes a time when shop manuals, just like the ones on every desk when I was growing up, or Google, will have to do the memorizing and recall for me.

    I leave it to you to decide if one can be called a DBA if one doesn’t have the entirety of the discipline on the brain in cache ready for instant retrieval!

    Reply
    • Hi Keith. Yeah, at our level, when clients call us in to diagnose their toughest SQL Server pains in a matter of hours, we do need to be on our A-game at all times. Basically, every client engagement is an interview.

      Reply
  • James Lupolt
    March 10, 2015 3:34 am

    Hi Kendra, thanks for this. One thing I’ve been confused about is how long row versions need to be retained under RCSI. Say I run a multi-statement transaction under RSCI, not SNAPSHOT. I would have thought row versions only need to be maintained for the life of each statement within the transaction, rather than for the life of the whole transaction, because RCSI is described as providing only statement-level consistency. However, my test results appear to suggest that the row versions generated while the first statement in the transaction ran don’t get cleaned up until the entire transaction finishes. It is a bit difficult to tell for sure because the cleanup is deferred (as you say). Do you know what my misconception is here?

    Reply
    • Kendra Little
      March 10, 2015 10:36 am

      RCSI provides statement level consistency, so cleanup should be able to kick in.

      How are you doing your test? You should be able to do BEGIN TRAN, run a SELECT statement, and then leave that transaction open for a long time while you have another connection executing writes and observe cleanup kick in. I haven’t done a demo proving this in a while, but I have a vague memory of having done it in the past and observing it in a test environment (because in a production environment, I agree, it’s difficult to “prove”).

      Reply
      • Hi Kendra, I’m testing like this on an isolated test system: http://pastebin.com/waEEK8kx

        It’s on 2014 CU2.

        So far the row versions have hung around for 10 minutes. I’m going to go out for a while and see if they get cleaned up.

        Reply
        • Kendra Little
          March 10, 2015 3:40 pm

          In my testing I was running a lot of modification activity from multiple threads via HammerDB as in the video– and I do think that level of activity impacts when cleanup runs, so that’s probably the difference.

          I could possibly re-run the demo for a future video or blog post. My memory is that it worked like I expected, but I always love to show things at work since they don’t always act like the documentation says!

          Reply
          • James Lupolt
            March 10, 2015 5:22 pm

            I came back a couple hours later and the row versions were still hanging around (1 for each row that was read by the open transaction in session 1 and later updated by the statement in session 2). I committed the transaction in session 1 and the row versions were cleaned up within a few seconds.

            I’m not sure I understand why it would behave this way under RCSI, so I’ll do some digging when time allows and see what I come up with.

          • Kendra Little
            March 10, 2015 9:22 pm

            I went back in and reran my demo code on this. You’re totally right– an explicit open transaction (not a snapshot transaction, just a transaction) can prevent cleanup from kicking in with RCSI enabled.

            In my test, when this is the case with RCSI, the “Non Snapshot Version Transactions” counter is incremented for the open transaction, and the “Longest Transaction Running Time” is also accurate for the counter (it only updates every 60 seconds as in the video). So number of the transactions and the age of the command which is blocking cleanup is monitor-able. (That’s always the thing I’ve always been most concerned about, for alerting purposes.)

            For the why, Sunil Agarwal wrote about it here: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/01/managing-tempdb-in-sql-server-tempdb-basics-version-store-growth-and-removing-stale-row-versions.aspx

            I did a very brief amount of experimenting with longrunning transactions with RCSI off and Snapshot enabled (just in a non-snapshot transaction), and the behavior actually seems to vary depending on what kind of statements I run (SELECT vs DELETE). The perf counters are behaving consistently for me and supporting alerting that something’s preventing cleanup in each case.

            Thanks for pointing this nuance out!

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}