Dropcleanbuffers

Question

Post reply

Dropcleanbuffers

newbieuser

SSCrazy Eights

Points: 9423
More actions
September 28, 2015 at 8:01 pm

#311915

Hi friends,
We are troubleshooting a performance problem and the test result is slow the 1st time but the subsequent runs are faster.. Logging out of application and log back in ( connecting to a new database session) did not clear the buffer cache as I thought it would.. When does the database clear the buffer cache? Is it not per database session?
I googled a bit on this and I understand that I can issue CHECKPOINT and then run DBCC DROPCLEANBUFFERS to clear the buffers in the disk. But since we are testing from the application,do we need to run these commands via application code to clear buffer/per database session OR can we run these commands from a management studio session?
THank you so much

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The data cache is not per-session. It's one data cache for the SQL Server instance.

DBCC DROPCLEANBUFFERS clears the entire data cache for the instance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

If the buffer cache is for the Instance, when does the clear cache happen? Is checkpoint an internal process that happens after a specific time?

Also, is there a way to clear the cache for a session not the entire instance?

Thank you for your reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

newbieuser (9/29/2015)
If the buffer cache is for the Instance, when does the clear cache happen?

The cache is not cleared completely during regular operation, it would be inefficient to do so since the cache is there to avoid having to read from slower disks. Data pages may get aged out of cache if they haven't been used for a while and other pages are needed, a restore or some other database operations will clear the cache for a database. Commands like DROPCLEANBUFFERS will clear the entire data cache.

Also, is there a way to clear the cache for a session not the entire instance?

The data cache is *not* per session. It's for the instance as a whole.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 4

Thank you for the info.

When you mentioned database restore will clear cache, will making database offline/online clear cache for that database as well?

Thanks a lot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

On second thoughts, I think they clear the data cache for the entire instance. It's the plan cache that gets cleared per database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 6

Thanks..

I ran the below commands to clear the cache and ran the test again but we did not see any difference to the previous test(with cache). But the test was not slow after the cache was cleared... Do we need to log off and log back onto a new database session to perform the test after clearing the data cache?

CHECKPOINT

--Execute DBCC

DBCC DROPCLEANBUFFERS

Thanks a lot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

No.

That command removes all clean data pages from the cache immediately. If your slowdown was caused by the overhead of loading data from disk, then the next execution will be slow as it will have to load the data from disk.

If the next execution isn't slow, then that suggests that the loading of data from disk is not the problem. You may need to investigate further.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 8

Thank you again. It is possible that the slowness is not caused due to the cache. But in any case, to rule out it is not cache, I'm trying to see the results of test run before and after cache. I ran the below SQL to see the current buffers:

select sysObj.name,*
from sys.dm_os_buffer_descriptors bufferDescriptors
INNER JOIN sys.allocation_units AllocUnits ON bufferDescriptors.allocation_unit_id = AllocUnits.allocation_unit_id
INNER JOIN sys.partitions Partitions ON AllocUnits.container_id = Partitions.hobt_id
INNER JOIN sys.objects sysObj ON Partitions.object_id = sysObj.object_id
WHERE bufferDescriptors.database_id = DB_ID()
AND sysObj.is_ms_shipped = 0

Get the results from application - Test1

Then I run the below commands from management studio

CHECKPOINT

--Execute DBCC

DBCC DROPCLEANBUFFERS

Ran the same SQL query to see the buffers, there are no buffers currently. Get the results from application - Test2.

I would expect Test2 to be slower than Test1 without caching but we did not see much difference. I did not log off and log back in prior to clearing the cache for test2.. NOt sure if that is required..

Are there any other ways to make sure cache is cleared? Also, are there additional steps to clear the data cache other than 'checkpoint and dropcleanbuffers'?

Thanks much