trigger on a dynamic management view

Posted on 2009-02-18
Last Modified: 2012-05-06
can you put a trigger on a DMV?
Question by:david_32
    LVL 22

    Expert Comment

    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:

    Author Comment

    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
    LVL 51

    Accepted Solution

    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 ?


    Author Closing Comment


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now