Removing objects from cache selectively

Hello everyone,

I have the following question:


I am aware that the objects currently in the cache can be viewed by issuing the following command:

SELECT *
FROM master.dbo.Syscacheobjects

Further, cache contents can be cleared by issuing the following command:

DBCC FREEPROCCACHE

However, I know that clearing cache using this command is not advisable, especially in production environment, as everything is cleared up (which would adversely affect performance of other stored procedures).

Is there a way to selectively clear things up? This selective clear up is always necessary when I am trying to find the true stored procedure performance.

I know that with tables we can use something called 'UNPINTABLE'. Is there something similar for stored procedures?

I tried the following command:

DELETE FROM master.dbo.Syscacheobjects
WHERE SQL = 'My_SP_Name'

but got the following error:

Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.


Is there another way of clearing specific data (stored procedure execution plans) from cache?

And if not, how exactly do I enable updates to system catalogs and what are the dangers associated with doing so?


Thanks a lot

Best way to handle this is recompile the procedure....

It is not advisable to delete the data from system tables... Any way if you want to do it you have to run ...

sp_configure 'allow updates', 1
reconfigure WITH OVERRIDE
GO
Run your delete statement...
GO
sp_configure 'allow updates', 0
reconfigure WITH OVERRIDE

Thanks guys - the comments are very helpful.

I have a few follow up questions now:

1. I know that I could add WITH RECOMPILE to the stored procedure definition during testing (to ensure that it never even gets to the cache). But what if its execution plan is already there - Is there a way to manually RECOMPILE the stored procedure (i.e. in order to remove it from the cache)?

2. Out of pure curiosity, where exactly is the execution plan stored if one wants to look at it?



Thanks a lot

Thanks Frank, I totally forgot that syscacheobject is also Pseudo-table.

1. The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.

2. It is stores in procedure cache(memory).

Read the following article for more info...
http://www.sql-server-performance.com/rd_data_cache.asp

Mohammed.

Guys,

sp_recompile is exactly what I was looking for. I learned that this command is also useful when an index is added to a table to ensure that the associated execution plans (i.e. of stored procedures using this table) get re-created, ensuring optimality (under the assumption that addition of an index was a wise decision).

As for the 2nd question, however, I did not find the information in the article specified above. My explanation was probably very not clear. I'd like to see the execution plan itself, as I would if I ran the query in the Query Analyzer, viewing the text output of the execution plan. I'm thinking that this plan must be stored somewhere, in some system table (And this is out of pure curiousity).


Thanks a lot

You can't see the exact plan details in any where in any system table because it stores in memory...

"It would take too long retrieving the plan from anywhere else but the cache"

Does the above statement imply that the plan is stored in cache? If so, is there a way to retrieve it from cache?


Thanks a lot

Great.


Thank you!

You don't have any system tables to query but as Frank mentioned...
You should run profiler when you execute your procedure to get the execution plan.

Read the following article for more info...
http://www.quest-pipelines.com/newsletter-v4/0303_B.htm

Thanks guys.

And for those reading this thread in the future, and possibly wondering where in Profiler this could be seen:

When creating a Profiler trace, choose "Performance" Event in the "Event Selection" tab and then choose Execution Plan.

What exactly are the locking hints?

Where are the locking hints added?


Thanks a lot