Clear Procedure Cache

Question

Clear Procedure Cache

VastSQL

SSC-Dedicated

Points: 36097
More actions
September 12, 2014 at 4:30 am

#296095

Hi Experts,
Is there anyway to clear the procedure cache of a single procedure?
TIA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

EXEC sp_recompile 'ProcedureName'

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

Thanks a lot Gail.

Developers want to test performance of a particular procedure and requested to run DBCC DROPCLEANBUFFERS & DBCC FLUSHPROCINDB,is it neccessary to ran these. SP_RECOMPILE is enough to test the performance right?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Tell them that neither are required to test performance of their procedures.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

You can also use DBCC FREEPROCCACHE and pass it the plan handle of the plan you want removed from cache.

NOTE: If you don't pass it a plan handle, you remove ALL plans from cache for ALL queries.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

GilaMonster (9/12/2014)
Tell them that neither are required to test performance of their procedures.

Thanks Gail..

Can you help me on the other ways to test the performance?

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 6

Grant Fritchey (9/12/2014)
You can also use DBCC FREEPROCCACHE and pass it the plan handle of the plan you want removed from cache.
NOTE: If you don't pass it a plan handle, you remove ALL plans from cache for ALL queries.

Thanks Grant.

How can i get the plan handle of a particular procedure?

Found the below query but not able to find for a particular procedure.

SELECT cp.plan_handle, st.[text]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 7

If you're looking for a particular procedure name, then those extra spaces in the WHERE criteria are going to mess you up. Remove them and try again.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

The exec_sql_text DMV has dbid and objectid columns. Use those to identify particular procedures.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 9

True, and while we're on it, there's also the object_id in sys.dm_exec_procedure_stats. So you have a number of ways to get at the plan handle for a given procedure.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning