SQL Server memory cache just like an apartment(or house?), before we start testing , we'd better clean the memory cache first. just like we clean the house, we use tools to clean cache as well.
1. DBCC FREESYSTEMCACHE
BOOK ONLINE: manually remove unused entries from all caches or from a specified Resource Governor pool cache.
it has 2 parameters, the format is like:
DBCC FREESYSTEMCACHE ('ALL','default');
this is only the sample in BOOK online, there is no more description of the parameter. then I searched the parameter, here are some findings:
- Clean all caches
sometimes if you can not shrink the tempdb log file, and get the error below:
“DBCC SHRINKFILE: Page X:xxxxxxx could not be moved because it is a work table"
try this command first, but note, this command will clear all cache and cause your system slower for a period of time.
- Clean cache for a specific database:
- Clean adhoc queries from cache
you can use the query below to check the adhoc queries status
count(*) as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts) as avg_use_count
group by objtype
sometimes a large number of adhoc query plans in the cache will cause performance issue:
clean the sql plan cache is one of the resorts.
- Clear all table variables
- Clean TokenAndPermUserStore
there is a KB descript it.
- Other Cache Object
select name from sys.dm_os_memory_clerks group by name
2. DBCC DROPCLEANBUFFERS
Removes all clean buffers from the buffer pool.
please remember it only remove the "CLEAN" buffer from buffer pool, for what is "CLEAN" buffer, please refer to http://blogs.msdn.com/b/psssql/archive/2009/03/17/sql-server-what-is-a-cold-dirty-or-clean-buffer.aspx
so it is better to run checkpoint before run DBCC DROPCLEANBUFFERS, checkpoint will write all dirty pages back to disk, so you can release more buffer pool space
3. DBCC FREEPROCCACHE
Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool
I think it is similar with "DBCC FREESYSTEMCACHE ", but it can only clean plan cache, and it provide parameter to let you control the clean more detail. you can specify the planid and pool name.
also this command has less impact than "DBCC FREESYSTEMCACHE ", MVP Glenn Berry mentioned the impact of FREEPROCCACHE is "pretty minor", and it is useful for some senarios
4. DBCC FLUSHPROCINDB (@intDBID);
Flush the procedure cache for one database only
5. DBCC FREESESSIONCACHE
Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.
so if you want to make a completely clean on the cache, you can try Rajesh Chandras 's script
DBCC FLUSHPROCINDB( db_id )