The Waiting Life Of A SQL Server Query, Revisited

Posted on by Erik Darling

Often Asked

Sometimes people will ask me penetrating questions like “why does SQL Server choose a bad execution plan?” or “why is this query sometimes slow?”

Like many things in databases, it’s an endless spiral of multiverses (and turtles) in which many choose your own adventure games are played and, well, sometimes you get eaten by a Grue.

In this post, I’m going to talk at a high level about potential reasons for both.

I can’t possibly go in depth on both in a single blog post, but I thought it would be fun to talk about all the things that could possibly cause your Unfortunate Circumstances™️.

At any rate, I hope you enjoy it. And maybe learn something. And maybe feel bad about blaming SQL Server for things that are all your fault.

Getting There

A lot of assumptions are gonna get made here, because a lot of things can stand between you and your query getting to SQL Server.

We’re talking networks, security, Active Directory, the cloud. Who knows? Not me. I stick to what happens inside the server.

Just know that there are a lot of moving parts between your application server and your database server.

I’ve seen logins take 30+ seconds to process. Not on purpose, mind you. I stick to what happens inside the server.

But hey, maybe we can just say this all worked quickly, and now we can talk about the stuff I stick to.

Inside the server.

Upon Arrival

There are even more things that have to happen now, even after you log in.

Your connection has to pass some checks and do a bit of routing, and then at a high level, your query will either:

  • Re-use a cached plan if one exists
  • Start building a query plan

This is when SQL Server’s query processor comes along and goes through the various phases of figuring things out:

  • Parse
  • Bind
  • Expand views
  • OPTIMIZE!
  • Come up with an executable plan
  • Apply memory grant and degree of parallelism

There’s one step in that process that seem important. The actual optimization portion. In there, you have:

  • Simplification
  • Cardinality estimation
  • Join ordering (depends a bit on which search phase you make it to: 0, 1, 2
  • Plan exploration phases

Rules, Smoky

During plan exploration is when the optimizer starts applying various rules to your query.

If you want to see the names of all the crazy rules that get may get applied, look in here:

SELECT
    deqts.*
FROM sys.dm_exec_query_transformation_stats AS deqts
ORDER BY
    deqts.name;

In SQL Server 2022, there are about 440 of them. It’s a wonder anything gets done around here.

This process has to happen quickly. So when you throw your crazy-town queries at SQL Server, it may not figure out the single best possible plan ever.

If your queries are parameterized, this brand spankin’ new plan will be created for those parameters.

At this point, it would be good to remind you that your query hasn’t even started executing yet.

It’s just been waiting to get going.

Once it has an executable plan, that happens.

Inside the server.

Ready Set Go

Once your query gets on its horse, you’re subject to many potential holdups, both physical and logical. It may even have to wait for memory to compile an execution plan at times.

  • Memory to compile a query plan (RESOURCE_SEMAPHORE_QUERY_COMPILE)
  • Read pages from disk into memory (PAGEIOLATCH_SH, PAGEIOLATCH_EX)
  • Writing to the transaction log (WRITELOG)
  • Memory for a memory grant (RESOURCE_SEMAPHORE)
  • CPU threads to run on (THREADPOOL)
  • CPU attention while other queries are busy (SOS_SCHEDULER_YIELD)
  • Spilling to disk (IO_COMPLETION, SLEEP_TASK)
  • Building spools (EXECSYNC — parallel plans only)
  • Blocking (LCK_X)
  • Latching (LATCH_XX and PAGELATCH_XX)
  • Parallel threads to process (All those CX waits)

Not enough memory? Wait. Not enough CPU? Wait. Need to read data? Wait. Need to write data? Wait. Blocked? Wait.

And that doesn’t even count all the work your query might have to do while it’s running and not waiting on anything.

The next time you have a gripe about a slow query, try to keep all this stuff in mind.

There’s a lot going on.

Inside the server.

Since You Want To Know

A great way to see all the stuff your queries are waiting on all at once is to run sp_PressureDetector.

Inside the server.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.

Related Posts