sys.dm_exec_cached_plans

  • So that you understand why I am asking these questions; My ultimate goal is that I want to be able to detect procedures that are not being used in production, so with that in mind...

    1.) I thought that the info in this DMV's got reset when SQL server services are started BUT books online describes the USECOUNTS column as "the number of times this cache object has been used since its INCEPTION"...? Implying to me that its the count since the object's create date, not sql server services start date? Is that correct?

    2.) What causes a query execution plan to be cached and thus contained in this view? Will merely compiling a proc cause it to be in this view, or does it have to be compiled and executed?

  • 1.) I thought that the info in this DMV's got reset when SQL server services are started BUT books online describes the USECOUNTS column as "the number of times this cache object has been used since its INCEPTION"...? Implying to me that its the count since the object's create date, not sql server services start date? Is that correct?

    It is reset on server start plus a number of other scenarios : dbcc freeproccache, detaching any database etc - I had a link for this information but I can't find it at the moment. All the plans for a specific database can be removed based on a few other factors. Plans can also be removed based on memory pressure.

    2.) What causes a query execution plan to be cached and thus contained in this view? Will merely compiling a proc cause it to be in this view, or does it have to be compiled and executed?

    They are added when the proc is first executed (this is when the plan is first generated).

    You could possibly add a line to each proc you suspect is not used - insert an entry into a table created for the purpose. After your comfort period has passed with no entries, remove the proc.

  • A proc has to be executed to appear in the procedure cache. Some of the things that can cause plans to be removed:

    service restart (obviously)

    sp_configure (some options when changed)

    Alter Database (some options)

    Database closing, going offline, detached.

    Restore

    memory pressure (causing older, less used plans to be aged out of cache) The cache has a threshold after which older, less used plans will get removed to save space

    Also, if a proc/query is marked WITH RECOMPILE, it will never be added to the cache.

    The cache is not a safe way to tell if a proc has ever been used. Rather try Matt's suggestion.

    Or, if you like living on the edge, rename the proc and see what breaks. :Whistling:

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply