Friday, December 9, 2011

DBCC Commands For Free SQL Server Memory Caches

DBCC Commands for free SQL Server memory caches:

1.) DBCC FREESYSTEMCACHE
Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For executing this you have requires ALTER SERVER STATE permission on the server.


Below Script will show what's there?

select count(*) as [count], cp.cacheobjtype, cp.objtype,st.dbid
from sys.dm_exec_cached_plans cp cross apply
sys.dm_exec_sql_text(cp.plan_handle) st
group by cp.cacheobjtype, cp.objtype,st.dbid
order by cp.objtype;
GO

For clearing cache for a specific database, run below query

DBCC freesystemcache ('tempdb');

For clearing all ahoc queries from cache

DBCC freesystemcache ('sql plans');

For clearing all table variables
 
DBCC freesystemcache ('Temporary Tables & Table Variables');

2.) DBCC FREEPROCCACHE
Removes all elements from the procedure cache.
For executing this you would need sysadmin fixed server role access.

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

3.) DBCC FREESESSIONCACHE
Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server. For executing this you would need sysadmin fixed server role access.


USE AdventureWorks; 
GO 
DBCC FREESESSIONCACHE WITH NO_INFOMSGS; 
GO

Clear SQL Server Memory Caches. Display SQL Server RAM memory buffer cache. Microsoft SQL Server Performance Solutions. Microsoft SQL Server Performance tuning. SQL Server Memory Monitoring. SQL Server CacheMonitoring. Understanding SQL Server Memory Internals & Cache. What Query Plans are in SQL Server's Memory. Great SQL Server Debates. SQL Server memory configurations for procedure cache and buffer. DBCC MEMORYSTATUS to Monitor SQL Server Memory. A Sysadmin's Guide to Microsoft SQL Server Memory. Understanding SQL server memory grant. SQL Cache Memory Alarm. Clean SQL Server Cache. Plan Caching and Query Memory . Plan Caching in SQL Server 2008. Plan Caching in SQL Server 2012. Plan Caching in SQL Server 2005. Data Pages in Buffer Pool. Data Stored in Memory. Plan Cache and Data Cache in Memory. Best Practice for Memory Limit for SQL 2005. Plan Caching and Query Memory. SQL Server Buffer Pool Or Memory Cache Issues. DBCC Commands to Free SQL Server Memory Caches.

0 comments:

Post a Comment