How to measure or find cost of creating a query plan?
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.