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

    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
    sys.dm_exec_cached_plans AS cp 
    sys.dm_exec_query_plan(cp.plan_handle) AS qp 
    sys.dm_exec_sql_text(cp.plan_handle) AS st 
    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!

  • To me it looks like someone was trying to see how many times I had ran a particular UDF (statriskprem_udf)....
    – Juan Velez
    Aug 1, 2012 at 13:58

1 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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.