Buffer memory usage

Question

Buffer memory usage

krypto69

SSChampion

Points: 13604
More actions
September 11, 2015 at 1:32 pm

#320528

Hi,
I have a SQL2014 server that I am noticing is using allot of system memory.
When I run this memory usage query:
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
AND counter_name = 'Total Pages';
;WITH src AS
(
SELECT
database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors
--WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
)
SELECT
[db_name] = CASE [database_id] WHEN 32767
THEN 'Resource DB'
ELSE DB_NAME([database_id]) END,
db_buffer_pages,
db_buffer_MB = db_buffer_pages / 128,
db_buffer_percent = CONVERT(DECIMAL(6,3),
db_buffer_pages * 100.0 / @total_buffer)
FROM src
order by db_buffer_MB desc
Many of my instances are using 25gigs of my 64G of system memory. I have identified why this is happening (index on varchar).
How do I clear out the buffer memory on a production server - without effecting users? Why doesn't SQL clear out the memory once it has completed? There are no other processes running - but it is still using much system memory.

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

Why do you want it to clear the memory out? What is the memory going to be used by if you do? There is no benefit to several GB of memory being unused on a server.

The buffer pool is there so that SQL doesn't have to read data off slow disks. If you clear the buffer pool, all queries will run slower until SQL can re-populate the buffer pool. And it will re-populate the buffer pool. All you'll achieve by clearing out the memory is making SQL run slower for a short period, after which all that memory will be reallocated back to SQL.

What you are seeing is normal, expected, documented behaviour.

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

krypto69 SSChampion Points: 13604 More actions · Answer 2

We had a one time run that filled a huge 45Mil table.

This caused the buffer usage to fill over 32GIG. Now several large queries are running slower.

We would like a way to clear it out so it releases the memory back so the other instances may use our limited server resources.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

SQL Server is designed to use a lot of memory to cache data.

The way SQL Server works is that as queries are run it loads the data into memory (physical reads) and then reads the data in memory (logical reads) to send to the client. Once data is memory it stays there until there isn't enough memory to fulfill a request and then SQL Server removes the least recently used pages from memory. So if I run a query that says "get all the customers from California" against a newly started SQL Server, it will first read the data from disk and load it into memory and then return it to the application. If a co-worker then runs the same query or a query like "get all the customers from Los Angeles" SQL Server doesn't even go to disk it just reads the data from memory because all the data is already there. This is much faster than going to disk for each request. There is almost no reason to clear the buffer cache on a production SQL Server. If all SQL Server is using is 25Gb of 64GB you have plenty of hardware because SQL Server Could be using over 50GB of memory if this is a server dedicated to SQL>

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

SQL has very good memory management. Set each instance's Max Server Memory so that they don't interfere with each other and that there's enough memory left for the OS. Let SQL manage the rest automatically

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

krypto69 SSChampion Points: 13604 More actions · Answer 5

Okay thanks guys. I'll leave it be.

Thanks again for all the info