Sql-server – Compiled version of stored procedure

sql serversql-server-2012

If I have administrator rights, would it possible to view details on the compiled version of a stored procedure or a view?

Is there a visual representation available?

Best Answer

Maybe this will be a good starting point, just click on the blue XML to show the execution paths that have been cached.

    SELECT TOP 100
    cp.objtype
    , st.objectid
    , cp.usecounts
    , ISNULL(qp.query_plan,'') [ActualQueryPlan CLick to Open]
    , DB_NAME(st.dbid) AS [DB_NAME]
    , OBJECT_NAME(st.objectid,st.dbid) AS [OBJECT_NAME]
    , st.text [SQLText]
    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(plan_handle) AS qp
    LEFT OUTER JOIN sys.procedures sp ON Sp.object_id = st.objectid
    ORDER BY cp.usecounts DESC
Related Question