Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question
To me it looks like someone was trying to see how many times I had ran a particular UDF (statriskprem_udf).... – PokerPlayer23 Aug 1 '12 at 13:58
up vote 3 down vote accepted

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.

share|improve this answer
Awesome thanks. – PokerPlayer23 Aug 1 '12 at 14:12

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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