Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


What's Using the Buffer Pool?
Published: Nov 27, 2020
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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.