Wait Stats When VSS Snaps Are Slow

Deus Redux

A while back I wrote about the Perils of VSS Snaps.

After working with several more clients having similar issues, I decided it was time to look at things again. This time, I wanted blood. I wanted to simulate a slow VSS Snap and see what kind of waits stats I’d have to look out for.

Getting software and rigging stuff up to be slow would have been difficult.

Instead, we’re going to cheat and use some old DBCC commands.

Hold It Now Hit It

Whenever we want to do something important we’re going to freeze IO and then observe what it’s doing:

Whenever we want find out what we’re waiting on, we’re going to run:

There were a couple things that weren’t blocked, like creating temp tables, and running select queries.

Anything we did that attempted to create or modify something in Crap was blocked when IO was frozen, though.

That means Insert, Update, and Delete queries absolutely blocked read queries.

Some Pictures

Creating a table waits on DISKIO_SUSPEND.

What was really interesting here is that it would wait on it for a couple seconds, then the wait would cycle back to 0.

But at the session level, the wait accumulated quite a bit of total time.

Trying to insert into a table generated long WRITELOG waits.

They’d just keep piling up.

And they’d block other queries.

Trying to create indexes and constraints would generate PAGEIOLATCH_EX waits, which would also block queries.

Trying to create most things, like functions and procedures also generated WRITELOG waits.

You probably don’t need more screencaps of that.

Sort of curiously, if I froze IO during DBCC CHECKDB, I got a bunch of PAGEIOLATCH_UP waits.

CH-CH-CHECK

Trying to take a log backup while IO was frozen was cute. BACKUPIO waits and DISKIO_SUSPEND seemed to cycle a bit, and only added up to total wall clock time in total.

Right To Choose

 

Fudgey Bottoms

This is about where I ran out of stuff I wanted to see blocked. If there’s anything you’re interested in, well, you now have UNLICENSED DBCC COMMANDS to play with.

So, if you’re seeing long pauses between IO being frozen and thawed in your error log, these are waits you may be able to look for to corroborate a problem with VSS Snaps.

Thanks for reading!

Previous Post
How Trace Flag 2335 Affects Memory Grants
Next Post
A Presenter’s Guide to the Stack Overflow Database

4 Comments. Leave new

  • Love the article – VSS is sneaky in many ways! We had an issue where some of our backup drives were running out of space way ahead of where we predicted … “hmmm, why has space used jumped up by tens of gigs overnights? … wait I’ve totalled the file size and it says we’re using 120GB but we only have 90GB worth of files on that drive!” – VSS caches the snapshot as hidden files, sometimes it fails to delete the snapshot and the drive suddenly jumps in usage but when you go to look the file size totals less than the actual size of files on disk.

    “Hold It Now Hit It” Nice Beastie Boys reference – I listened to this on the way to work this morning. 🙂
    And then had to deal with an application support guy trying to persuade me that 230k transactions a day is “a lot”.

    Reply
  • I am DYING to be asked what my favourite DBCC command is in my next job interview now. (It was previously previously, but this would make for a much more interesting interview discussion.)

    Thanks

    Reply
  • […] Erik Darling shows us the wait stats associated with the Volume Shadow Copy Service (VSS): […]

    Reply

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":""}