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 , 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 (https://blog.sqlauthority.com)
28 Comments. Leave new
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.
Your statement on “Experience vs Expertise” is simply fantastic!!!!!
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.
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,
If you are experiencing troubles with proc cache (like in our case) then yes, you need to free them by hand. Otherwise Sql Server should be able to clean up unused plans from the cache.
how can come to know , SQL server is cleaning up the cache for that regular?.
If not, then can i schedule the job for that?
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.
Thanks, Great info…
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
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.
Hi,
What does the column Plan_Handle specifies. What does that HexCode Indicates
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
I’ve found the same problem that you. Pin, what can we use to replace DBCC FREEPROCCACHE(parameter) in sql server 2005?
Above script is for Sql Server 2008 and above
Brilliant. This is exactly what I was looking for. I am dealing with an old database server that is having memory problems but only when a certain app is run.
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)
DBCC FREEPROCCACHE(0x0600010045ED162FB8C17683000000000000000000000000)
An incorrect number of parameters was given to the DBCC statement.
Both Plan Cache and Procedure cache are same or different..
The same.
plan cache is the name given to the cache where the plan is stored. but the plan can be of type procedure, adhoc, prepared, usrtab, view and check. so procedure cache is a type of plan cache. hope it helps.
Can i clean total plan_cache everyday, will it effect anything, Thanks in advance
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.
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
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.
SQL Server itself has Execution plan clearing algorithm which will clear the plans based on their COST factor and AGE.
I agree with you but… you know that in the practice not always happen. Cheers.
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….
Hello,
Can I save the Query cache plans and how ? Please share the code, thanks
Dave