• Grant Fritchey (7/31/2012)


    SQLSACT (7/31/2012)


    Grant Fritchey (7/31/2012)


    The DMV stores data for queries that are currently in cache. As soon as a plan ages out of cache all the information in that DMV is lost. So it really depends on how volatile the plan cache on your machine is for how much information is available to you through that DMV. For a complete measure of query performance, I'd go with extended events.

    I could be wrong but I remember reading somewhere that when plans are flushed out of cache or when they are flushed too quickly, it affects performance of SQL Server

    Am I on the right track here?

    Thanks

    Plans flush out of cache all the time. It doesn't necessarily affect performance at all. It's when they are constantly flushing, or never being reused that you have an issue. You can get an idea of how active your cache is by simply looking at the oldest date within that DMV.

    Thanks

    Besides checking the oldest date,

    How can I check if plans are never being reused?

    Is there some sort of standard as to how long a plan stays in cache? 1 day, 2 days etc..

    Thank you