SQL Server has an option for statistics to autoupdate asynchronously (
But, even if this option is ON, from time to time I see locks like this on pic below: say, spid 549 locks spid 42 and spid 42 locks 8 other sessions. As I can see, system spid tries to change old statistics to a newly computed one, so it waits for
SCH-M lock to acquire.
Suddenly, it turns out, async stats update is not so async: any long-running SELECT (which, of course, holds
SCH-S lock) can freeze system spid, which, in turn, can gather queue of many other sessions. Even worse, whole queue wait time depends on of locker’s SELECT duration. It means, if I have a long OLAP query, all the stalled sessions could be timed out.
So, my question is: am I missing something or it works just by design? Can I somehow avoid these sudden locks, caused by autoupdated statistics?