We all know that SQL Server stores data in memory in a place it calls the Buffer Pool (and if you don’t, then speak up and I’ll explain) but, for a lot of us, that’s where our knowledge ends.
However, this is most definitely not where our interest should end… wouldn’t you like to know what’s in the Buffer Pool? Ie. What’s taking up this most precious of space? Here’s how you know…
We all clamour for RAM in our SQL Servers, but rarely do we stop and ask ourselves why we’re invariably asking our Infrastructure Admins for more of this magic material… is it because we have actually run out of RAM or, more likely, is it that some queries have gone mad and hidden table scans are causing tables to clog up the buffer pool and leave us no RAM for the tables and queries we wish to be fast?
Luckily, in order to answer that exact question, SQL Server provides us with some a very useful DMV and, as long as you know how to use it, we can easily find the answers to this question.
The query you need to know is as follows:
with bufferPool as
(
select case when is_modified = 1 then 'Dirty' else 'Clean' end pageType,
case database_id when 32767 then 'Resource' else db_name(database_id) end databaseName,
count_big(*) noPages
from sys.dm_os_buffer_descriptors
group by database_id, is_modified
)
select *, (noPages * 8) / 1024 / 1024. usedGB,
case when noPages = 0 or (noPages * 8) / 1024 / 1024. = 0 then 0
else convert(decimal(5, 2), ((noPages * 8) / 1024 / 1024. / sum((noPages * 8) / 1024 / 1024.) over()) * 100)
end percentBufferPool
from bufferPool
order by usedGB desc
Now, I need to explain a couple of points here… firstly, this query is at the database level… it’s going to tell you exactly which of your databases is hogging the Buffer Pool and just how much it’s using. What it also tells you (and which you can feel free to remove if it doesn’t interest you) is whether your pages are dirty or clean… basically whether the pages in memory have been updated or not. This is a very good guide to letting you know if any buffer pool hogs are due to large updates or simply because the pages are being read.
Let’s take a couple of examples…
Here is a screenshot of my buffer pool after running both a checkpoint (to write out any dirty pages to disk) and a DropCleanBuffers… there I’m hoping it’ll be pretty empty (fingers crossed):
All good. So, what can we do next? Well, let’s read some data from good old AdventureWorks:
select *
from sales.SalesOrderDetailEnlarged
Now we can check that this has all been pulled into our Buffer Pool:
As we can see… we’ve pulled the whole table into the Buffer Pool… and it’s the only table we have… and the “Clean” pageType denotes that we have read data rather than updated… this can help because if we don’t expect large datasets from this database to be pulled into cache then we know that there is a rogue query plan somewhere that we need to hunt down.
Likewise, if we were to see a high number of “Dirty” pages appearing in the output then we know that a very large update has happened… this could imply an inadequately filtered update query.
Either way, it will give us a good inkling that there’s something wrong and help direct us where we need to look.
Sadly this only helps to narrow down in respect to database… in the next post I’ll help break this down further to show how to get down to the specific table and even index that SQL has pulled into its memory.