Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.
Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.
DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERS
Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL , BOL
extremely helpful in understanding and solving the problems. Thanks for your utmost skill in this field.
Good info! I wonder though if it is possible to specify for an index/table to not cache? or alternatively remove from cache a certain object?
We have a log table which does not get queried at all, but it appears that primary key index of this table takes 56% of Buffer Cache.
Thanks.
andre.
if you have tables which dont get queried, will it not be better if you do not have any index on those tables?
How do we use the command? in the SQL or query? in the php page itself? im confused!
I have a stored procedure that calls another stored procedure in turn. The inner procedure returns an integer value using a SELECT SCOPE_IDENTITY().
The outer procedure also returns an integer value using SELECT SCOPE_IDENTITY().
Obviously the client is going to receive multiple result sets. Is there a way to “clear” the earlier resultsets and send only the last result set to the client ?
You are Great!!!!!!!!!!!Bhaji.I am your Great Great Fan…
Would you not be required to call the CHECKPOINT command before calling DROPCLEANBUFFERS?
hi
We developed system using .net frame work 2 with Sql server 2005. one location using 10 to 20 user we are not facing any kind of problem.
ONE OF MY CLIENT USE 100+ users
WINDOWS 2008 SERVER – 64 BIT + SQL SERVER 2005 64 BIt with 8 gb ram
but when 100+ user connecting to our server first 30 mins running system smooth and after that system getting slow, and approx after one hour client systems getting error :
TCP Provider, error: 0 – The specified network name is no longer available
again we have to restart the sql server or windows, then again its working fine..
please suggest me how to over come this and what could be the reason.
ONE MORE :
when 100 user connected to server, if any user using bulk transfers, memory goes up, after that it wont come down, just i want to know, there is any posibilities clearing the memory without disturbing my users work ?
Your immediate reply would be highly appreciate
Can somebody explain in detail the need of executing
the below commands.
DBCC FREEPROCCACHE,DBCC DROPCLEANBUFFERS
I am still not very clear.
Thanks
Prashant
SRI:
Can you resolve your problem with slow sql server ?
SRI – just a thought, but have you checked on the licensing for that SQL server? Maybe you’re getting slowed performance with 90-100 users due to load… Then getting the TCP provider error when user 101 attempts to connect if the SQL 2005 install is licensed by CAL rather than PROC. Not confident of this post, but a thought that might help…
If we are clearing procedure cache do we need to recompile all the procedures again or the sql server will automatically compile when a particular procedure runs ?
Is there any solution to clear data from cache for specific databse ?
wincek, you can use DBCC FLUSHPROCINDB(db_id), this will remove all elements from the plan cache on this DB only and not the whole Instance. It’s usually not recommended to run DBCC FREEPROCCACHE or DBCC FLUSHPROCINDB in a PRO Environment.
Is this a database level change or instance level change?
Please excuse an Oracle DBA invading this terrain but like most sites today, we are mixed platform and I have to know my way round SQLServer as a beta skill set (and am a regular and grateful reader of Pinal Dave’s very helpful website).
On SQLServer (SQLServer 2008 R2 enterprise), we have found that when a procedure is changed and recompiled, the old logic appears to run for some time (maybe 30 mins?) leaving the programmer doubting his sanity and tearing his hair out!!. This doesnt happen on Oracle but as a DBA I somethimes have to flush the library cache for other reasons. This lead me to research the same technique in SQLServer to force starting with a “clean sheet” when compiling SPs. Executing DBCC FREEPROCCACHE was exactly what was needed to deal with this problem. We will have to run this in a PROD environment when sp’s are upgraded for reasons above – executing the old SP could be majorly inconvenient to disastrous. On Oracle, a performace glitch and a few ropey stats may be expected until the cache refills. There is a beneficial side effect of freeing trapped space in the proc cache when this is done – I guess its the same in SQLServer?. In Oracle the proc is loaded to the cache when called but can be pinned so is permanently resident. Pinned procs are reloaded by the RDBMS and app procs when called, after a cache flush.