trigger on a dynamic management view

can you put a trigger on a DMV?
Who is Participating?
Mark WillsTopic AdvisorCommented:
Cannot do it, but can run various queries on   sys.dm_exec_query_stats

There is a usecount in there and that is what you are probably looking for.

There can be a lot of reasons why a query plan remains or is flushed from memory, so "how long" is not really going to do much for you. More so, high query plan reuse is important.

Now, from your other thread about memory pressure, then it alone can cause low plan re-use, so looking at that measure without considering some of the others is going to be guesses at best...

So, back to Perfmon. This time, want to start moinitoring (or add in the extra objects) SQL Server:SQL Statistics.

The idea is to compare batch requests to initial compilations. An initial compilation occurs when a plan is not found in cache. Ideally you have high plan reuse, > 80% (the higher the better, but I tend to stick with a "real life 80/20 rule" I think MS says > 90 %).

Initial Compilations = SQL Compilations/sec  SQL Re-Compilations/sec
Plan reuse = (Batch requests/sec  Initial Compilations/sec) / Batch requests/sec

Memory pressure can result in reduced plan reuse - remember that other thread as to what type of measure we need to check for memory pressure ?

Even if you could put a trigger on a DMV I don't think it would ever fire because they aren't updateable. DMVs are purely derived data.

What are you tring to achieve? Maybe a DDL trigger will do the job:
david_32Author Commented:
I want to see how long an entry stays in the dm_exec_cached_plans DMV. Effectively seeing how long a plan is cached for
david_32Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.