What does this code do?

Question

I found one of my queries was over written with following code:

SELECT 
    cp.objtype AS ObjectType,
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
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 
WHERE 
    st.text LIKE  '%StatRiskPrem_udf%' 

I believe maybe one of the admins might have done this accidentally because I do not remember having written this code. As a matter of fact I don't even know what the code does but I might have copied it from somewhere and pasted in and saved by mistake myself. Before I go asking the admins if they overwrote my original query, does anyone here know what this code seems to be trying to do? If i knew what the code was trying to do then I might be able to remember if I did this myself or not. Thanks!

Answer 1

That query looks in the procedure cache for a cached plan that contains the text StatRiskPrem_udf. The result set will contain the following:

• The object type that is cached
• The cached object name
• The amount of times the plan was used
• The cached SQL text
• The cached query plan

I would guess that the objective is to find out how many times that UDF was referenced. I wouldn't say that's extremely accurate, though, because the procedure cache is a revolving door, especially under memory pressure. But that's the gist of the query.