By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,750 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,750 IT Pros & Developers. It's quick & easy.

Clearing up cache selectively

P: 6
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:

FROM master.dbo.Syscacheobjects

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


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
Nov 20 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.