Erik Darling answers the question, why is it so hard to figure out why my query sometimes sucks:
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.
Read on for a smorgasbord of factors to consider based on the steps SQL Server takes.