Generating the SQL query plan takes 5 minutes, the query itself runs in milliseconds. What's up?

Question

I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatten out. Unfortunately, it's 3rd party and I cannot change it).

Anyway, it had been running fine for weeks until I periodically noticed a run that would take 3-5 minutes. It turns out that this is the time it takes to generate the query plan. Once the query plan exists and is cached, the stored procedure itself runs very efficiently. Things run smoothly until there is a reason to regenerate and cache the query plan again.

Has anyone seen this? Why does it take so long to generate the plan? Are there ways to make it come up with a plan faster?

Answer 1

You can try using a Plan Guide. Plan generation will still last some time, but should be significantly shorter.

Answer 2

Have you considered rewriting your 30-join SELECT to smth like this?

SELECT [key], NULL AS [a], NULL AS [b]
  INTO #temp
  FROM [pk-table]

UPDATE t SET t.[a] = fk.[a], t.[b] = fk.[b]
  FROM #temp t
  INNER JOIN (
    SELECT f.[key],
      MAX(CASE WHEN f.[name] = 'a' THEN f.[value] ELSE NULL END) AS [a],
      MAX(CASE WHEN f.[name] = 'b' THEN f.[value] ELSE NULL END) AS [b]
    FROM [fk-table] f
    GROUP BY f.[key]
    ) fk ON (fk.[key] = t.[key]

Although it's maybe not an answer to your original question :)

Answer 3

Something will have caused the plan to require recompiling such as a statistics update or DDL change. The list if here: Execution Plan Caching and Reuse

The query in it's current form will always take 3-5 minutes to recompile: this can't be avoided.

Assuming you can't change it (PIVOT, use a trigger to maintain a "proper" table etc), then you can only control when the recompilation happens.

Remus' plan guide answer is one way. I'd also look at my statistic maintenance and ensure it's done overnight say, so it only happens once at start of day