cached query plans

Posted on 2009-02-18
Last Modified: 2012-08-13
Is there any way you can work out how long cached query plans will be cached for  (dm_exec_cached_plans)?
Question by:david_32
    LVL 57

    Accepted Solution

    SQL server will create execution plans for all queries and stored procedures it executes. Query plans are detailed steps as to how to get the results for the query specified. So this would include which tables to query, which indexes to use, how to join or merge the results, and so on. By default this will be done when you execute or compile a query and hence It would be added in your cached plans.

    You have the option to remove specific query plans from the server using any of the following commands.

    Following operations will also flush the entire plan cache and new batches will require new plans.

        * Detaching any database
        * Upgrading any database to compatibility level 90 or above on SQL Server 2005 or above
        * Running ALTER DATABASE ... MODIFY FILEGROUP command for any database
        * Running ALTER DATABASE ... COLLATE command for modifying collation of any database

    Altering a database with any of the following commands will remove all plans cached for that specific database.


    Following operations will also remove cached plans for a specific database.

        * Dropping a database
        * Database is auto closed

    In syscacheobjects table, you have a column named usecounts. Based on that value, less used plans will be thrown out if some new plans needs to be included in that table.

    Hope I have answered you..
    LVL 51

    Assisted Solution

    by:Mark Wills
    Short answer, no.

    Long answer, well (you saw my last one)....... you probably want to read up on :

    There are some good technical guides and manuals on MS Technet for performance analysis.

    Have a look at lowest reuse means it gets swapped out quickest (or least used) - need to keep a watch on that usecount - adapted from one of those techo manuals.

                 (case when qs.statement_end_offset = -1
                then len(convert(nvarchar(max), qt.text)) * 2
                else qs.statement_end_offset end -qs.statement_start_offset)/2)
            as statement
    FROM sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
    inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
    where cp.plan_handle=qs.plan_handle
    and qt.dbid = db_id('my_database')    ----- put your database name
    ORDER BY [Usecounts] asc

    LVL 51

    Expert Comment

    by:Mark Wills
    Might want to combine this question with your other question about a trigger for a more complete answer to this...

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    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 combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now