Buffer pool memory questions

Question

Buffer pool memory questions

Tac11

SSCertifiable

Points: 6976
More actions
August 24, 2016 at 11:39 am

#400621

Hi Gurus,
I have found that a database is taking more than 50% buffer pool memory and I even located that 4 clustered index taking top buffer pool memory around 4 GB.
My questions :
Is this an issue?
what's the next step should I take?
Thanks

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

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

Eric M Russell SSC Guru Points: 125522 More actions · Answer 1

Every time a data or index page is read, it's first copied to the page buffer pool. Pages are allocated and deallocated based on the frequency of their usage, and tt's normal for the page buffer pool to completely allocated. If this particular database and set of tables are the most frequently used, then I would expect them to consume 50% or more. The reason why an entire table would be held in the page buffer cache is probably due to full table scans. SQL Server reads every page in a table or index during a scan, so the entire objects gets buffered to the cache, at least temporarily until it is gradually cycled out to make room for pages from other objects.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Tac11 (8/24/2016)
Hi Gurus,
I have found that a database is taking more than 50% buffer pool memory and I even located that 4 clustered index taking top buffer pool memory around 4 GB.
My questions :
Is this an issue?
what's the next step should I take?
Thanks

You WANT SQL Server to use memory to store database data!! RAM access is tens of thousands of times (or more) faster than accessing rotating disk, and hundreds of times or more faster than accessing SSDs.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Eric M Russell SSC Guru Points: 125522 More actions · Answer 3

If a query is performing full table scans on some large tables, then you want those tables to be held in the buffer cache so they arn't read physically from disk. However, what's even better is for the table not to be scanned in the first place.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Tac11 SSCertifiable Points: 6976 More actions · Answer 4

Thanks Eric and SQL Guru.

Eric, so you think I can run "sys.dm_db_index_usage_stats" and find if there are index scan taking place?

or any other step should I take?

or this is not even a issue?

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

Tac11 (8/24/2016)
or this is not even a issue?

Yup. It's normal, expected, desired behaviour. You really don't want SQL fetching data from disk every time it needs to read a row or two. Disks are slow.

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Note that nothing I nor anyone else said means that you don't ALSO have suboptimal indexing on your system. In fact I can say that I have NEVER, in 20 years of consulting on SQL Server, met a client that had anything CLOSE to optimal indexing. But getting to that point on anything even moderately complex is a project unto itself and absolutely cannot be done via a forum. 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Eric M Russell SSC Guru Points: 125522 More actions · Answer 7

Tac11 (8/24/2016)
Thanks Eric and SQL Guru.
Eric, so you think I can run "sys.dm_db_index_usage_stats" and find if there are index scan taking place?
or any other step should I take?
or this is not even a issue?

There is nothing wrong with your page buffer; it just works as intended. If you are encountering performance issues, and it seems specific tables are consuming the bulk of your buffer pool, then focus on optimizing these objects and queries to reduce scanning operations.

Here is an explanation of table / index scans:

https://www.mssqltips.com/sqlservertutorial/277/index-scans-and-table-scans/

You can capture metrics about server level page scans / sec which will give you an idea of how much of an impact this is overall:

http://www.keepitsimpleandfast.com/2011/11/how-to-find-table-scans-in-your.html

Querying dm_db_index_usage_stats and sorting by user_scans will identify what table(s) are getting the most scans.

select object_name(object_id), user_scans, user_seeks, user_lookups, user_updates

from sys.dm_db_index_usage_stats

order by user_scans desc;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho