Saturday, August 8, 2009

How Long Stored Procedure stay in Sql Server 2000 cache?

SQL Server 2000

once the execution plan is generated for a Stored Procedure, it stays in the procedure cache. Lazy writer only keep looking and throwing out unused plans out of the cache "only when space is needed in cache".

Below are some documented and undocumented DBCC commands available in SQL Server 2000 to deal and find more information about SQL Server cache.

To Monitor the cahce:

DBCC SQLPERF (LRUSTATS)
DBCC CACHESTATS
DBCC MEMORYSTATUS
DBCC PROCCACHE
To clean the cache:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

You can read more from SQL 2000 Topic under "Lazy Writer", 'Freeing and Writing Buffer Pages' at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_8unn.asp

3 comments:

mindbees said...
This comment has been removed by a blog administrator.
fgdg said...

Life is too short to wake up in the morning with regrets. So, love the people who treat you right and forget about the ones who do not Sell Runescape Gold, In the Orient young bulls are tested for the fight arena in a certain manner. Each is brought to the ring and allowed to attack a picador who pricks them with a lance Sell RS Gold, the bravery of each bull is then rated with care according to Sell Runescape Gold the number of times he demonstrates his willingness to charge in spite of the sting of the blade.

fgdg said...

I know someone in the world is waiting for me, although I've no idea of who he is. But I feel happy every day for this 4story Gold, If a woman is not sexy, she needs emotion; if she is not emotional, she needs reason 4story Luna, in this world, only those men who really feel happy can give women happiness 4story Gold.