An interesting question came in on PollGab. DBAmusing asked:
If a query takes 5-7s to calculate the execution plan (then executes <500ms) if multiple SPIDS all submit that query (different param values) when there’s no plan at start, does each SPID calc the execution plan, one after the other after waiting for the prior SPID to finish?
Well, it’s easy enough to demonstrate! Let’s take a query from my Fundamentals of Query Tuning class that takes tens of seconds (or longer) to generate a plan on most versions and compat levels:
DECLARE @TheRootOfAllEvil TABLE (Id INT PRIMARY KEY CLUSTERED); WITH CTE1