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)
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…
[...] 10, 2010 by pinaldave Earlier, I wrote about my experience at an organization here: SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script. This blog post briefly narrates another experience I had at the same [...]
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
[...] Further explanation of this script is over here: SQL SERVER – Plan Cache – Retrieve and Remove – A Simple Script [...]
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
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)