Non-Yielding Scheduler During Backups?

Memories

Sparked by my friends at Straight Path SQL, we’re going to look at a rare cause of non-yielding scheduler dumps. This is another one of those items that I meant to write about a long time ago when I worked in Support and then, well, I suck and clearly forgot about it. This should give you an idea of how rare this specific issue hits.

Backups, They Be A-Changin’

When you take a backup, the allocated extents are read from disk and written to wherever you asked, this could be local disk, a network location, some place in the cloud across the world. Seems pretty straight forward, taking away all the compression and encryption stuff for now. However, the database probably has things changing it while the backup is going, after all the database may be huge (100 TB, 1 PB, etc.) and take a non-trivial amount of time. What happens when you want to flush all those data page changes (the log is flushed first, WAL, and then checkpoint/lazywriter are responsible for flushing the data pages) to disk and that’s also the data being read/waited on by the backup? If you would try to read a page in an extent in the middle of checkpoint writing a new value, you may end up with gobbledygook in the backup.

The solution in this case is something called BackupSync, whose entire job is to make sure everything stays coherent when backups are occurring and workload is running.

Checkpoint And Mate

The whole job of BackupSync is to make sure things stay consistent and make sure the buffer manager isn’t getting all locked up (since that’d kill concurrency when a backup occurs). When the backup wants to read some items from disk, it checks to see if anyone is currently changing the data. If it is, it needs to wait for those changes to be completed before it can read anything from the disk. If it isn’t, it needs to keep track of the backup IO so that it knows how to eventually proceed, for example, the IO may have failed and it needs to be read again.

Something, Something, This Should Never Happen

Let’s say you’re doing a full backup of a 100 GB database (arbitrary number) which consists of a single data file (MyBigDatabase.mdf) and log file (MyBigDatabase_log.ldf) to some network device, whether that’s local, across the internet, doesn’t matter. During this full backup there are things in your database making changes, maybe you just really love rebuilding indexes in the middle of backups, I don’t know what you’re into. Now you have all of these changes on pages in your buffer pool that need written out to disk and also all this data sitting on disk that needs read and copied to super_secure_backup_location and BackupSync gets some exercise. Each data file will keep track of its own items, cool, we only have 1 data file so not much of a physical layout. The buffers start being written to disk and there’s a lot of them, you want your money’s worth so CPU/Disk is churning hard, no ounce of possible performance bandwidth will be left on the table. That backup you have running wants to go read a new extent and send it to the backup location, it needs to go check the list it has to make sure that is stabilized and no one else is doing anything with it (otherwise, it’ll need to wait so it can get access to a consistent copy of it). So it starts to check the list, which is now possibly hundreds of thousands of items long. This is going to take some CPU time to traverse the list so long that you cause a non-yielding scheduler event. Womp womp.

Stacks On Stacks

If you’re looking at a non-yielding scheduler dump, you may see threads with the following stacks. If you do, then you’re running into a workload/physical layout/disk performance issue. I’m sure some of you will point out that a non-yielding scheduler event can easily be mitigated by putting in a yield call, and sure that’s a possible technically correct answer. Might not be the right call but it would most likely work. Performance regression? Probably, but who cares about that.

00 sqlmin!Spinlock<130,13,257>::SpinToAcquireWithExponentialBackoff
01 sqlmin!BackupSyncFile::SeekPage
02 sqlmin!BackupSync::NoteWriteCompleted
03 sqlmin!FCB::PostWrite
04 sqlmin!BPool::WritePageCompletion
05 sqlmin!JoinedIoCompletion
06 sqldk!SOS_IOCompRequest::ExecuteCompRoutine
07 sqldk!IOQueue::CheckForIOCompletion
08 sqldk!SOS_Scheduler::SwitchContext
09 sqldk!SOS_Scheduler::SuspendNonPreemptive
0a sqldk!WaitableBase::Wait
0b sqlmin!BPool::LazyWriter
0c sqlmin!lazywriter

05 sqlmin!Spinlock<130,13,257>::SpinToAcquireWithExponentialBackoff
06 sqlmin!BackupSyncFile::SeekPage
07 sqlmin!BackupSync::NoteWriteCompleted
08 sqlmin!FCB::PostWrite
09 sqlmin!BPool::WritePageCompletion
0a sqlmin!JoinedIoCompletion
0b sqldk!SOS_IOCompRequest::ExecuteCompRoutine
0c sqldk!IOQueue::CheckForIOCompletion
0d sqlmin!SOS_Scheduler::AddIOCompletionRequest
0e sqlmin!DiskWriteAsync
0f sqlmin!FCB::AsyncWriteInternal
10 sqlmin!FCB::AsyncWriteSingle
11 sqlmin!FCB::AsyncWrite
12 sqlmin!RecoveryUnit::GatherWrite
13 sqlmin!BPool::LazyWriter
14 sqlmin!lazywriter

You’ve Told me nothing, Sean

A common quip by most folks, not necessarily incorrect either. What can be done about this, outside of a product change? First, look at your disk performance, are you having IO latency issues? If so, that’s a good start… but maybe you can’t change those because your CIO gets kickbacks for using some cloud provider located in a small island nation on the other side of the world. Next would be to look at your physical database architecture, is there one giant file? 10 giant files? Are you telling people to hold your beverage while you go try to hit the maximum single file size limit in SQL Server? If so, a great thing would be making more files so that the BackupSync integration has less items to track per individual file, thus removing much of the work finding data. The other, quite obvious, action item would be to take something such as a full backup at off-peak times… though that may not be possible if the system is highly utilized regardless of date or time (the struggle is real, here).

Again, this happens less than once in a blue moon and generally never for the vast majority of people.