Friday, 20 January 2017


This is a hastily written post in progress but I'm putting it out there anyway. I've just asked via #sqlhelp on Twitter why that I after I run a DBCC DROPCLEANBUFFERS command I can then query the memory clerk and buffer descriptor DMV's and still see allocations to my database.

Here's what I've been doing:

First, run a checkpoint command to write any dirty pages prior to running DBCC DROPCLEANBUFFERS to remove all the buffers from the buffer pool.


After that we'll query the memory clerks DMV to see how memory is being allocated in my instance:

SELECT TOP(10) [type] AS [ClerkType],
SUM(pages_kb) / 1024 AS [ClerkMb]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]

What I can see here is that 2Mb is allocated to the Buffer Pool.

I can drill into this in a bit more detail by querying the buffer descriptors DMV where we see the page allocation detail per database.

SELECT DB_NAME(database_id) AS DatabaseName,

page_type, COUNT(page_id) * 8 As SizeKb
FROM sys.dm_os_buffer_descriptors
GROUP BY page_type, DB_NAME(database_id)

In this case the AdventureWorks2014 database has 360kb of data pages and 168kb of index pages; out of interest the NULL DatabaseName rows refer to the ResourceDb.

So my question is, what is persisted in memory after I have dropped the buffer pool?!


Spoke to a couple of people via #sqlhelp on twitter. We're none the wiser really and I will come back to this and use DBCC Page to look in a bit more detail but for now its a safe assumption that the pages in memory are there and related to SQL Server "doing things" behind the scenes!

No comments:

Post a Comment