April 7, 2009 at 2:30 pm
is there a way to run dbcc freeproccache for a specific table only?
April 7, 2009 at 2:43 pm
You would have to clear it for each plan handle that had the table name in the plan. The article on msdn has a good example for a specific query.
http://msdn.microsoft.com/en-us/library/ms174283.aspx
You could just alter the query to give you all the plans with that table.
SELECT plan_handle, st.text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE '%TableName%';
and then execte DBCC FREEPROCCACHE(PlanHandle) for each result.
April 7, 2009 at 2:44 pm
According to BOL
DBCC FREEPROCCACHE
"Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all workload groups from a specified resource pool."
So it wouldn't seem like it can be used on a single table.
However, if you're looking to have all the procedures and triggers recompiled for a specific table you can use sp_recompile instead.
April 7, 2009 at 3:09 pm
Why would you want to?
If you have changed the table it will recompile anyway.
If not why do you need to clear it?
.
April 7, 2009 at 3:14 pm
hi thank you guys
i am trying to execute this
dbcc freeproccache (0x050007002D47DA3D4063236D020000000000000000000000)
but i get this error:
Msg 2583, Level 16, State 3, Line 1
An incorrect number of parameters was given to the DBCC statement.
April 7, 2009 at 3:22 pm
DBA, there are a lot of posts at the moment about not understanding what the questioner is trying to do.
You haven't explained why you want to do this.
There is no reason to remove a query plan from cache usually. If it is bad then you probably change the indexes, statistics, or something else and then it removes from cache and recompiles anyway.
To help further, why are you trying to to this?
.
April 7, 2009 at 3:42 pm
run sp_recompile and pass it the table name as a parameter. This will mark all plans based on that table as invalid and force them to recompile the next time they run.
I'm also curious, why the need to do this?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2009 at 3:45 pm
Ken Simmons (4/7/2009)
You would have to clear it for each plan handle that had the table name in the plan.
That feature was only added in SQL 2008. In SQL 2005, freeproccache didn't take any parameters.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2009 at 4:58 pm
thank you all for your input, i am trying to do this because i was having issues with a stored procedure with parameter sniffing. I solved the problem by adding an index in one of the tables being used and declaring local variables. Does it make sense to run dbcc freeproccache?
April 7, 2009 at 7:39 pm
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
This was given by Gila Monster to one of my posts......it works perfect for parameter sniffing....
April 7, 2009 at 7:43 pm
GilaMonster (4/7/2009)
run sp_recompile and pass it the table name as a parameter. This will mark all plans based on that table as invalid and force them to recompile the next time they run.
OMG, I can't believe that you came up with this Gail! Incredible... :w00t:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 8, 2009 at 1:04 am
DBA (4/7/2009)
thank you all for your input, i am trying to do this because i was having issues with a stored procedure with parameter sniffing. I solved the problem by adding an index in one of the tables being used and declaring local variables. Does it make sense to run dbcc freeproccache?
Absolutely not. At best sp_recompile on that procedure, or create the procedure WITH RECOMPILE. Though, if the issue's fixed, no need. Running FREEPROCCACHE drops way too many plans from cache and will adversely affect the rest of the workload on the server as it has to recompile execution plans (a very CPU intensive process)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2009 at 7:13 am
Thank you!:-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply