SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script

I had a very interesting situation at my recent performance tuning project. I realize that the developers there were running very large dataset queries on their production server randomly. I got alarmed so I suggested their developer not to do that on the production server; instead, they could create some alternate scenarios where they could synchronize database and query on the same server. The production server should not be used for development work. It should be queried with proper methods (queries, Stored Procedures, etc.), supporting production application.

The lead DBA, who claimed he has 19 years of experience, pointed out, “What is wrong in running ad-hoc queries if their server is not having any pressure at the moment?” When someone starts talking about his “experience” to me, I always feel confused. How can you compare experience over expertise? You might have learned swimming when you were 3 years old and might have 27 years of experience of it, considering you are already 30 years old now. However, this is apparently mediocre when compared to someone who might have learned swimming just 5 years ago, yet participates in state competitions today. After the long conversation, I was able to convince him that running queries not related to the production would just pollute his cache with unnecessary cache plans.

Here is the query that demonstrates cache plans which are ‘ad hoc’ or called only once in a life time.

SELECT [text], cp.size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
AND cp.objtype = N'Adhoc'
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC;

You can see how much memory is already bloated by not-so-useful queries. If you want to remove any large plan cache which you do not think is useful to you, you can run the following command to remove it:

DBCC FREEPROCCACHE(plan_handle)

You can find the plan_handle from the first query where the third column is indicating the plan handle.

Here is a quick display of my machine’s plan handle and how I can remove any plan from the cache:

Note: Do not play with this settings on production server unless you know what you are doing.

Reference: Pinal Dave (http://blog.sqlauthority.com)

32 thoughts on “SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script

  1. We’ve had problems with ad-hoc queries and execution plans also. One of our servers is really old. It’s runnin Windows 2003 and Sql Server 2000 without service packs IIRC. The problem is that the cache which stores execution plans fills and overflows and starts causing hectic I/O on the disk. This grinds everything to a halt until someone runs DBCC FREEPROCACHE. We added scheduled job to run FREEPROCCACHE twice a day and it has alleviated the problem a bit.

    The real problem is that main bunch of the queries that one of our application runs are ad-hoc queries. If the vendor would’ve written them as stored procedures everything would be fine.

    Like

    • Please DONT say it is simply fantastic !!!

      Someone who learned swimming in the years of 3, Can not say i have 27 years of experience at the age of 30.
      He can say only if he was swimming daily or weekly 5 times.

      Now.. the man who learned swimming 5 years before and attending state meet can be a expert. But only in specific type only. Not in all

      So the experience person may not be expert in all but he knows more things than a expert.

      Experienct = General Doctor
      Expert = Specialist Dr.

      Like

  2. Hi Pinal,

    Do we really need to free memory from cache for those adhoc queries? or need to check as require in cache or not?

    Can you please suggest me with details?

    Thanks,

    Like

        • If everything runs smoothly then Sql Server is doing its job cleaning up the cache. I can assure you you will notice if cache fills up. In our case the server came almost to absolute halt. You can see this as heavy disk I/O load also if you are monitoring your server performance (we use Zenoss for that).

          I havent’ seen this happening in Sql Server 2005 or newer so if you aren’t running 2000 I think you are in the safe.

          What you absolutely don’t want to do is schedule unnecessary job to clean up the cache. Every execution plan has to recreated and every stored procedure recompiled. This brings temporary performance problems until everything is redone.

          Like

  3. Pingback: SQL SERVER – Find Automatically Created Statistics – T-SQL Journey to SQL Authority with Pinal Dave

  4. Hi Pinal,

    SQL server have so many cache plans for the stored procedures and also adhoc queries.

    I want to find out those stored procedures which are recompiled more time and not much using cache plans.
    Can you send me the script to find those stored procedures having more recompilation in cache plans?

    Paresh

    Like

  5. Pingback: SQL SERVER – Plan Cache and Data Cache in Memory Journey to SQL Authority with Pinal Dave

  6. I thought there was an algorithm that removed rarely used query plans based on the size of the plan. A large plan that is seldom used will be most likely to be purged. So although running adhoc queries on a production server has an effect, it is not a permanent effect.

    Like

  7. receiving below error.
    Msg 2583, Level 16, State 3, Line 1
    An incorrect number of parameters was given to the DBCC statement.

    running sql 2005 sp2

    Like

  8. Enter DB name and proc name and it will clear plan. (2008 + only)

    –clear specific procedure cache(query plan)
    –change variables
    declare @DBName varchar(50)
    declare @ProcName varchar(200)
    declare @PlanHandle varbinary(100)

    set @DBName = ‘Billing’
    set @ProcName = ‘usp_Billing’
    set @PlanHandle =
    (SELECT qs.plan_handle
    FROM sys.dm_exec_query_stats AS qs with(nolock)
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    inner join sys.procedures p on qt.objectid = p.object_id
    inner join sys.databases d on qt.dbid = d.database_id
    WHERE d.name = @DBName
    and p.name = @ProcName
    group by qs.plan_handle)

    DBCC FREEPROCCACHE(@PlanHandle)

    Like

  9. DBCC FREEPROCCACHE(0x0600010045ED162FB8C17683000000000000000000000000)

    An incorrect number of parameters was given to the DBCC statement.

    Like

  10. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

    • yes it will affect performance. remember sql server has to put a lot of effort in building that plan in the cache. it reuses it everytime a user runs the query that has a plan already built in the cache. you keep cleaning it everyday and you will surely feel the performance degradation.

      Like

  12. How i can check the performancey or how munch memory used or left before deleting the plan_handle and after deleted the plan_handl. Thanks in advance

    Like

    • the query that pinal mentioned shows the cache in size column for each plan. if you delete a specific plan, the value in that column is the space you get back.

      Like

    • that is absolutely true, thats why pinal mentioned dont use it, unless you know what you are doing. sometimes during testing etc. you know the plans that you created which are massive and stored in the cache. if they are massive, sql has probably given a higher cost number to it. and because you just compiled it the age is low too. so you can may be do the work for sql and make its life a little bit easier, but yes if left alone sql will ultimately clear it anyways….

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s