As a consequence of another action I was prompted to want to clear the buffer cache – or to be more precise I tried to clear the buffer cache.
So stepping back, what happens if you install more memory than the combined size of your databases? Most would probably suggest that it’s a waste of time, however what I’ve found is that tempdb makes use of this memory, and it was my initial investigation of memory usage which prompted all this, in fact I was quite surprised as to how much cache tempdb would use.
With SQL 2005 it’s quite easy to see which database is using buffer cache and how much, which is where the dropcleanbuffers comes in, I have a x64 box with 8Gb memory allocated to SQL Server, we’re involved with quite a bit of monitoring on this server which is testing some critical business processes, I noted that just over 4.5Gb of buffer cache was allocated to Tempdb, essentially most of the data cache, so I decided to clear the cache and watch utilisation based upon running server processes, in effect I wished to see which of the many databases or processes was making such heavy use of tempdb.
So here’s the crunch, try as I can I am unable to flush the dirty pages used by tempdb. Tony kindly reminded me of his blog entry about tempdb
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/24/958.aspx
which I’d only re-read a few days earlier as a post concerning use of solid state disks for tempdb, you can check out some info on solid state disks here
http://www.dvnation.com/nand-flash-ssd.html#MTRON
A quick check through TechNet and msdn suggest that you should checkpoint your database before clearing the buffers , a test actually showed that the checkpoint doesn’t actually happen at once and dropcleanbuffers after a checkpoint doesn’t necessarily clear the buffers as you’d quite think.
There is a parameter which can be added to the checkpoint command, the number of seconds in which you wish the checkpoint to occur e.g. checkpoint 10 , SQL Server will then attempt to flush pages within 10 seconds.
If you don’t specify a value “When this parameter is omitted, SQL Server 2005 automatically adjusts the checkpoint duration to minimize the performance impact on database applications.”
Ref: http://msdn2.microsoft.com/en-us/library/ms188748.aspx
Using this option and then dropcleanbuffers certainly cleared out the user databases but did not clear tempdb.
Tony tells me you can’t checkpoint tempdb, basically there’s little point, I agree but in saying that I’d still like to remove the buffers used by tempdb, I have no running processes so there’s no user data within tempdb, well technically.
But there is a point about tempdb and checkpoint, how often after tempdb has become bloated have you had to issue a checkpoint to allow a shrink to occur? Certainly for myself and my fellow DBA we are agreed that we have both issued checkpoint against tempdb.
Tony also says that there’s little point in writing tempdb pages to disk as writing the pages to disk only aids recovery, and tempdb is recreated at startup, so as my buffer cache does fill what happens to the tempdb dirty pages, I can see after a day that tempdb is now only using 1.5Gb so 3Gb has been flushed or cleared.
Tony further says is what I’m really asking is to limit what resource tempdb can use – and I guess he’s right.
So if you’re using dropcleanbuffers within your testing, it doesn’t actually flush your user database pages unless you’ve actually forced a checkpoint and you may or may not be affected by usage of tempdb.
Here’s the code I used to extract the data, Idera SQL Diagnostic Manager confirms the values/sizes of memory used, the version of Diagnostic Manager being currently used doesn’t split the buffer cache by database.
select db_name(database_id) as 'Database Name',
convert(numeric(8,2),count(page_id)/128.0) as Mb
from sys.dm_os_buffer_descriptors with (nolock)
where database_id !=32767
group by database_id
order by count(page_id) desc;
this query counts dirty pages:
select count(page_id) as 'dirty pages'
from sys.dm_os_buffer_descriptors
where is_modified =1;