how to get estimated subtree cost?

If I have a query that returns a query_plan, for instance like this:

SELECT TOP 1000 st.TEXT
    ,cp.size_in_bytes
    ,cp.plan_handle
    ,QP.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS QP
WHERE cp.objtype = N'Adhoc'
    AND cp.usecounts = 1

Then I can click on a query_plan and hover over the left most icon, where tip-text will list the Estimated Subtree Cost.

Is there a way of getting that Estimated Subtree Cost out as a separate column for my query?

I understand that the number is unit-less, and refers to a particular developers PC some 20 years ago. Even so, I think it might tell me how long the query should take if statistics are not too far out.

I've tried really hard to Google for this info, but even dba.stackexchange.com comes up empty.

I believe you will have to do some XML query work to get that estimated cost.

See if this is what you are looking for:

;WITH XMLNAMESPACES  
    (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
    SELECT TOP 1000 st.text
        ,cp.size_in_bytes
        ,cp.plan_handle
        ,QP.query_plan
        ,n.value('(@StatementSubTreeCost)[1]', 'VARCHAR(128)') AS StatementSubTreeCost
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS QP
    CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n)
    WHERE cp.objtype = N'Adhoc'
        AND cp.usecounts = 1
    OPTION(RECOMPILE);

This will return the cost for individual statements inside of a batch. You might need to work in some grouping if you need the total estimated subtree cost for the entire batch.

StackExchange Database Administrators Q#135063, answer score: 8