Take the 2-minute tour ×
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. It's 100% free, no registration required.

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!

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).... –  JuanVelez Aug 1 '12 at 13:58

1 Answer 1

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. –  JuanVelez Aug 1 '12 at 14:12

Your Answer

 
discard

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.