How to measure or find cost of creating a query plan? I have a typical case where parameter sniffing causes a "bad" execution plan to land in the plan cache, causing subsequent executions of my stored procedure to be very slow. I can "solve" this problem with local variables, OPTIMIZE FOR ... UNKNOWN, and OPTION(RECOMPILE). However, I can also dive into the query and try to optimize it.

I'm trying to determine whether I should: given limited time to fix problems I would like to know the cost of not doing it. As I see it, if I just stick with OPTION(RECOMPILE), the net effect is that a query plan is recreated every time the query is run. So, I think I need to know:

How to find out what the cost of creating a query plan is?

To answer my own question, I've Googled (e.g. with this query), and I've gone through the documentation of columns for the  dm_exec_query_stats DMV. I've also inspected the output window in SSMS for "Actual Query Plan" to find this info. Finally, I've searched DBA.SE. None of those led to an answer.

Can anyone tell me? Is it possible ot find or measure time needed for plan creation?
How to find out what the cost of creating a query plan is?


You can look at the properties of the root node in the query plan, for example:


(screenshot from the free Sentry One Plan Explorer)

This information is also available by querying the plan cache, for example using a query based on the following relationships:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    CompileTime = c.value('(QueryPlan/@CompileTime)[1]', 'int'),
    CompileCPU = c.value('(QueryPlan/@CompileCPU)[1]', 'int'),
    CompileMemory = c.value('(QueryPlan/@CompileMemory)[1]', 'int'),
    ST.[text],
    QP.query_plan
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) AS QP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
CROSS APPLY QP.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS N(c);




For a full treatment of the options you have for handling these sorts of queries, see Erland Sommarskog's recently updated article.