We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

trigger on a dynamic management view

Medium Priority
217 Views
Last Modified: 2012-05-06
can you put a trigger on a DMV?
Comment
Watch Question

Commented:
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:
http://technet.microsoft.com/en-us/library/ms175941.aspx

Author

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
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
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 ?


Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
thanks
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.