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.

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.

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!