When Tuple Mover discovers flushable delete buffers it needs to iterate over sysrowsets to discover existing tables or indexes. To read each row from sysrowsets he needs to hold a row level S lock on the entry it currently reads. The discovery process stops when Tuple Mover finds the first rowset it can do work on. At this point Tuple Mover will do the actual work of flushing the delete buffer, but in order to make sure that the discovered rowset is still valid it will hold a SCH-S lock on all rowsets it evaluated during the discovery process until it finishes his work.
Sysrowsets is ordered by the rowset id, thus it can happen that Tuple Mover scanned multiple existing rowsets on which it has no action to do, but will still hold SCH-S lock and it arrives to an entry in sysrowsets for which it cannot acquire row level S lock, because it is currently being altered by a long running transaction that holds an X lock on the row. At this point DDLs involving rowset id change are blocked behind Tuple Mover's SCH-S locks.
Article ID: 3168793 - Last Review: Jul 26, 2016 - Revision: 1