Slow Storage Can Cause Slow Compilation Time in SQL Server
Up till now, I’ve thought of compilation time in SQL Server as being dependent only on CPU resources– not something that requires fast storage to be speedy. But that’s not quite right.
Slow storage can result in periodic long compile time in SQL Server. And long compile time not only extends the runtime for the query, it can also result in blocking with waits for compile locks.
Thanks to Erik Darling for helping me figure this out, and explaining this all in his video, What Else Happens When Queries Try To Compile In SQL Server: COMPILE LOCKS!. For great details and demos, go watch that! I’ll be working through the topic with some simple flow charts here.