In this post we continue looking at the Cardinality Estimator (CE). The article explores some join estimation algorithms in the details, however this is not a comprehensive join estimation analysis, the goal of this article is to give a reader a flavor of join estimation in SQL Server.
Yesterday I came across a question on one of SQL forums, that I may rephrase like:
“Does a query plan compilation depend on how busy is SQL Server”.
Before we go further, I should explicitly mention that we talk about a Compiled plan, not an Executable plan. Plan execution will of course depend on how busy is server, for example, the query may wait for the memory grant to start execution, or execution may be slow because there are no cached pages in the Buffer Pool etc.
However, the question was about a Compiled plan: does the shape of a plan depend on the server load.
From the first glance it should not. But…