Why SQL Server Doesn’t Give the Memory Back to OS?

The purpose of this post is to show you what SQL Server is storing in memory and to discuss why SQL doesn’t give the memory back to the OS. Today’s post is written by SQL Server Expert Brian Gale. He earlier read blog post here and wrote an amazing blog post article answering a very important question which we all keep on wondering.

Memory Pressure

If you use SQL Server, you have likely run across memory pressure at some point. It is where the processes on the server are competing for memory resources.  This can be SQL Server, the OS, the antivirus, the backup tool, etc.

Why Does This Happen?

Sometimes memory pressure is expected, particularly if you have limited resources and are using as much memory as the server allows. Other times, it occurs because a system has not been configured quite right. It might be due to over-allocating resources, such as having too many SQL instances on a single box.  It may also be the result of having SQL Server on the same box as other applications that need resources, such as IIS, SSRS, SSIS, etc.

How Is It Fixed?

When you run into memory pressure, you might be able to fix it by tweaking various settings.  If that doesn’t work, you may just need more RAM.

Max Memory Best Practices

By design, SQL Server will request memory as it needs it. Once obtained, it will hold onto the memory and only give it back to the OS in certain situations, such as an instance restart.

If your max memory setting is set to the default of 2 PB, your system will very likely experience memory pressure at some point. This is because SQL expects that if it hasn’t used 2 PB of memory, it can request more. If the OS has memory to give SQL, it will try.

If the OS has no more memory to give, SQL Server must remove some objects from memory. When it frees up the memory, it holds onto it for other purposes; it does not give it back to the OS.

When you restart the SQL instance service, that memory gets freed up and given back to the OS as SQL Server is no longer using it. Then, as queries run, the memory used by SQL Server will climb again until it hits the max memory limit, or the OS starts pushing back on memory requests. Therefore, you should always set your max memory to a realistic value.

SQL Server, Memory & Speed

SQL Server is greedy for memory because it is fast. Reading data out of memory is MUCH faster than reading from disk. SQL will store things in memory and if it sees a case where it can reuse them, it will pull them from memory instead of from disk. This makes everything faster!

Eventually, if the max memory value is hit or the OS refuses to give SQL more RAM, SQL Server will start purging things from memory to make room for fresh data. The older the data is and the less frequently it’s requested, the more likely it is to be pushed out.

What Is Stored In Memory?

One of the things stored in memory in the buffer pool. This is explained well on logicalread.com¹ but to summarize: this is a location where table data is stored in 8 KB pages. So repeated reads from the same page only need to hit the disk the first time the page is requested for as long as it exists in memory.

Having pages in the buffer pool for long periods of time is a good thing. This is the page life expectancy value you can find in a lot of SQL Server reporting and diagnostic tools. Pinal Dave explains the page life expectancy very well². To quote a small part of his blog post:

Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references. In simple words, if your page stays longer in the buffer pool (area of the memory cache) your PLE is higher, leading to higher performance as every time request comes there are chances it may find its data in the cache itself instead of going to the hard drive to read the data.

How To Investigate Buffer Pool Data

The next thing we likely want to know is what the actual data being stored in the buffer pool is. This data is stored in the sys.dm_os_buffer_descriptors view. Joining that to a few other tables gives you a nice picture of what your buffer pool looks like³:

SELECT
       COUNT([buf].[page_id])    AS [TotalPages]
       ,(COUNT([buf].[page_id])*8)/1024  AS [UsedSpaceInMB]
       , SUM([buf].[row_count])    AS [RowCounts]
       , OBJECT_SCHEMA_NAME([i].[object_id]) AS [SchemaName]
       , OBJECT_NAME([i].[object_id])  AS [TableName]
       , DB_NAME([buf].[database_id])  AS [DatabaseName]
       , [buf].[page_type]     AS [PageType]
FROM    [sys].[dm_os_buffer_descriptors] AS [buf]
  INNER JOIN [sys].[allocation_units]   AS [au]
    ON [au].[allocation_unit_id] = [buf].[allocation_unit_id]
  INNER JOIN [sys].[partitions]       AS [p]
    ON [au].[container_id]    = [p].[partition_id]
  INNER JOIN [sys].[indexes]       AS [i]
    ON [i].[index_id]     = [p].[index_id]
      AND [p].[object_id]   = [i].[object_id]
WHERE    [buf].[database_id] = DB_ID()
--AND OBJECT_NAME(i.object_id)='TABLENAME'
GROUP BY   OBJECT_SCHEMA_NAME([i].[object_id])
       , OBJECT_NAME([i].[object_id])
       , [buf].[database_id]
       , [buf].[page_type]
ORDER BY   OBJECT_NAME([i].[object_id])
       , [TotalPages] DESC;

The above query will give you the total number of pages in that database that are in the buffer pool, the space used in MB, the number of rows per table that are in memory, the schema name, table name, database name, and the page type. The space used is a calculation of the number of pages multiplied by 8 (because each page is 8 KB) and then divided by 1024 (because there is 1024 KB per MB).

If you want to get information about the buffer pool for a specific table, uncomment the line in the WHERE clause and it will show you the data for a specific table. If you want to run it for all databases, uncomment that part on the WHERE clause.

The schema, table, and database name are all self-explanatory, but page type is an interesting data point. It can be several different values. Common values you will see are:

• DATA_PAGE – this is a Data Row with all data except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and XML data
• INDEX_PAGE – this is an index entry
• IAM_PAGE – this is information about extents used by a table or index per allocation unit
• TEXT_MIX_PAGE or TEXT_TREE_PAGE – this is the large object data types and MAX columns that don’t show up in the DATA_PAGE.

More information on the page types can be found from Microsoft Docs⁴.

Using the above query, you can see what is in memory, how much memory each table is using, how many rows are being used in memory, and the page type. If you just want to know how much memory is used by a specific database in the buffer pool, you can adjust the query so you don’t return the schema and table and adjust the grouping like so:

SELECT
       COUNT([buf].[page_id])    AS [TotalPages]
       ,(COUNT([buf].[page_id])*8)/1024  AS [UsedSpaceInMB]
       , SUM([buf].[row_count])    AS [RowCounts] 
       , DB_NAME([buf].[database_id])  AS [DatabaseName]
       , [buf].[page_type]     AS [PageType]
FROM    [sys].[dm_os_buffer_descriptors] AS [buf]
  INNER JOIN [sys].[allocation_units]   AS [au]
    ON [au].[allocation_unit_id] = [buf].[allocation_unit_id]
  INNER JOIN [sys].[partitions]       AS [p]
    ON [au].[container_id]    = [p].[partition_id]
  INNER JOIN [sys].[indexes]       AS [i]
    ON [i].[index_id]     = [p].[index_id]
      AND [p].[object_id]   = [i].[object_id]
WHERE    [buf].[database_id] = DB_ID()
--AND OBJECT_NAME(i.object_id)='TABLENAME'
GROUP BY   [buf].[page_type]
          ,[buf].[database_id]
ORDER BY   [TotalPages] DESC;

The Tables\Views In Use Are

• dm_os_buffer_descriptors – Returns information about all the data pages that are currently in the SQL Server buffer pool⁵
• allocation_units – Contains a row for each allocation unit in the database⁶
• partitions – Contains a row for each partition of all the tables and most types of indexes in the database⁷
• indexes – Contains a row per index or heap of a tabular object, such as a table, view, or table-valued function⁸

The first query is useful if you know which database is using the memory in the database; the second is useful to determine which database is using the most of it. If you have a lot of databases on a single instance, it can be helpful to know which one is the memory killer.

Now you know more about what the buffer pool is and how to investigate it!

Author Credit

My name is Brian Gale and I have been working with SQL server since 2010.  I have worked with various versions ranging from SQL Server 2000 through 2017.  My primary role is a database administrator; so I handle the backups, maintenance, tuning, installation, and upgrades.  On top of that I work as a developer – both in SQL server and in .NET, a server admin, data architect, data analyst, and an IIS admin.  A jack of all technical trades if you will.  I am well versed in scripting languages in Windows such as bat files, PowerShell, and VBScript which we currently use for our AD logon scripts.

Outside of work, I am a happy husband who enjoys video games, listening to records, and working on little side projects.  Recently, I built my own tube pre-amp for my turntable to get a nicer sound out of it.  Soldering DIY kits are a fun side-project for me and a nice stress reliever. Connect with me on LinkedIn.

SOURCES

1 – Link, 2 – Link, 3 – The query was mostly developed by Pinal Dave, I just did a small bit of tweaking to it, 4 – Link, 5 – Link, 6 – Link, 7 – Link, 8 – Link

Reference: Pinal Dave (https://blog.sqlauthority.com)