cached query plans

Posted on 2009-02-18
Medium Priority
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
  • 2
LVL 57

Accepted Solution

Raja Jegan R earned 1000 total points
ID: 23677340
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
Mark Wills earned 1000 total points
ID: 23677948
Short answer, no.

Long answer, well (you saw my last one)....... you probably want to read up on : http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx.

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
ID: 23678139
Might want to combine this question with your other question about a trigger for a more complete answer to this...

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.
Suggested Courses

850 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