Clearing up objects from cache selectively

Question

Post reply

Clearing up objects from cache selectively

Negmat

SSC Eights!

Points: 863
More actions
November 20, 2006 at 4:33 pm

#62383

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

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

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

Johan Bijnens SSC Guru Points: 135344 More actions · Answer 1

- AFAIK clearing is a all or none operation.

- DON'T mess with systemtables !

Syscacheobjects isn't even a real table, but contains a systemstate.

- let sqlserver handle it's cache by itself ! It is desinged to do so ! If an object is in the cache, it has been used !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

I think there's a deeper problem here. You need a Developpement and QA server. On that machine you'll be able to audit perdormance and test whatever you like. You'll also be free to screw up as often as you need without affecting the users.

Did I get what you were trying to do or did I miss the nail by a mile?

Negmat SSC Eights! Points: 863 More actions · Answer 3

You are correct. We do have both a testing and production areas. And I do perform tests in the testing area, where the use of DBCC FREEPROCCACHE command is less harmful.

The problem that I noticed, however, is that since the data distribution between 2 areas is different, I cannot really rely on results in testing. That is, the sub-tree cost and/or the execution plan can be different between testing and production areas. And the data distribution is something which is out of my control.

That is why, for the final, and more trust-worthy test, I have to perform the tests in the production area.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I see. Here I have the luxury of being able to run a full backup / restore FROM production to developpement. The whole process is coded and takes around 2 minutes. Then I redeploy the changes on the dev box and test before the final run. Anyway you can setup something like this in your environement?

Negmat SSC Eights! Points: 863 More actions · Answer 5

That is correct. In your situation there is never a need to question the results of the testing area.

I will consult with my co-workers here and see whether we can set up something similiar.

Thanks a lot for the help.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 6

You can use DBCC FlushProcInDB to fee the proc cache within a single database. There are a couple of good articles on SQL-Server-Performance.com that you should read:

http://www.sql-server-performance.com/dbcc_commands.asp

http://www.sql-server-performance.com/rd_data_cache.asp

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Negmat SSC Eights! Points: 863 More actions · Answer 7

I just learned that the easiest way of removing the stored procedure from the cache is to recompile it as such:

sp_recompile 'stored procedure name'

I think that this is the best way to go, as no other object will be affected by this statement.

Thanks a lot

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Lol, why do we always forget about the simplest solution !?!?

Johan Bijnens SSC Guru Points: 135344 More actions · Answer 9

Indeed, sometimes we tend to overlook the obvious

Look what I found in my archives ...

Select 'print '' proc to be recompiled : ' + u.name + '.' + o.name + ' '' ' + char(10) + ' go '+ char(10)

+ 'exec sp_recompile ''['+ u.name + '].[' + o.name + ']''; ' + char(10) + ' go ' + char(10)

as cmd

from sysobjects o

inner join sysusers u

on (o.uid = u.uid)

where o.xtype in ( 'p' ) -- p = procedures

and u.name = 'dbo'

-- and o.name not in ('tobeexcluded')

order by u.name, o.name