Clearing up cache selectively

[negmat]

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

[negmat]

As I learned from another place, this can be achieved as such:

exec sp_recompile 'stored procedure name'

[hjbs]

Yes.. that's true!

But in case of Dynamic SQL.. does anyone knows how to avoid the ad-hoc queries to be stored in cache?

Thanks!

[iburyak]

Not sure if it is solution for you and don't know why you need this.
But try this:

[PHP]
Declare @SQL varchar(4000)
Set @SQL = 'Select * from some_table'

Execute (@SQL)[/PHP]

In this case text of select statement itself will not be compiled.