SQL Server is using more memory then assigned is this normal

SQL Server is using more memory then assigned is this normal?

First of all make sure the minimum memory of SQL Server must be set to 0 MB because this will ensure that even in worst of a scenario of memory crunch SQL Services will come up . Please keep this thing in mind that by default SQL will consume as much memory you allow and apart from SQL Server there are other components as well that needs to be considered like SSIS, SSRS, SSAS because they consumed memory apart from SQL Server. So if you are running these components on Server you have to ensure that you have at least (4 OS + 2 GB for these components – excluding max memory of SQL). In your case SQL is assigned with 13 GB and total is 16 GB, so here SQL will try to capture all 13 GB by design and the most important and interesting thing is that when you assign MAX memory limit you are ONLY defining the buffer pool size, so if you are a DBA and windows team is bugging you that your SQL is using more memory then assigned then you become deaf for windows team for some time and investigate if they transferred ticket to you.

MinMAx

PErf

As I said above you are only defining MAX buffer pool size not complete memory for SQL, so there are other memory allocations required apart from buffer pool for SQLOS and internal operations of SQL Server and that allow it to be able to allocate more than max server memory. So Logically SQL 2008 R2 and all below version MAX memory just control the size of buffer pool or you can say you are defining an upper limit of Buffer pool where it stores data pages and procedure cache, but there are other memory clerks within in SQL Server which also uses memory. Hence sometime its normal when you see SQL Server is using more memory than the assigned.

Now question comes what action the DBA should take when he see that the server memory is 98%.

Don’t come to conclusion directly that there is a memory pressure on server, first we need to consider some performance counters.

  • Target vs Total SQL Memory

Most of the blogs will point on the Target Vs Total memory of SQL Server, however I would say it’s not a reliable counter which will indicate that there is a memory pressure on SQL Server when Target Server Memory > Total Server memory.

ToalTarget

  • Page Life Expectancy

It is the expected time, in seconds, that a data file page read into the buffer pool (the in-memory cache of data files pages) will remain in memory before being pushed out of memory to make room for a different data file page. Another way to think of PLE (Page Life Expectancy) is an instantaneous measure of the pressure on the buffer pool to make free space for pages being read from disk. For both of these definitions, a higher number is better.

However it is very tricky when you have a NUMA configuration the buffer pool is split up into buffer nodes, with one buffer node per NUMA node that SQL Server can see. Each buffer node tracks PLE separately and the counter Buffer Manager: Page life expectancy counter is the average of the buffer node PLEs

Roughly we can say that the value of 300 sec or in minute scale 5 Min is fine.

  • Memory Grant Pending

Every query needs memory to execute and query memory grant is a part of server memory used to store temporary row data while sorting and joining rows. It is called “grant” because the server requires those queries to “reserve” before actually using memory. This reservation improves query reliability under server load, because a query with reserved memory is less likely to hit out-of-memory while running, and the server prevents one query from dominating entire server memory.

If Memory Grants Pending is averaging over 0, queries can’t even start because they can’t get enough memory. You may have queries that have incorrect memory grants, or you may just desperately need more RAM.

In below graph you can see the yellow line always 0 which means sufficient memory available all the time, but if you closely see that my page life expectancy is varying from 1 min to 200 min, which defines that there is something happening on my server.

PLE_MGP

  • Buffer Cache Hit Ratio

It gives the ratio of the data pages found and read from the SQL Server buffer cache and all data page requests. The pages that are not found in the buffer cache are read from the disk, which is significantly slower and affects performance – This value should always be close to 100,

BC

I have mentioned some important performance counters in one of my blog below is the link where you get more information.

https://coresql.com/2013/10/23/how-to-handle-memory-pages-per-second-is-too-high/

Advertisements