kohera-logo-regular.svg

Under (memory) pressure

Vector_BG.png

Under (memory) pressure

Vector_BG.png

A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it in a good way in the future. It appears a small and simple question, but to give an appropriate answer we first need to take a few steps back and look what SQL Server memory actually is.

What is my SQL memory made of?

This is actually very simple. SQL memory consists of the same building blocks that you use in your database files: pages. Each page is an 8kb block containing data, but also meta-data about the page in the form of a page header. A page in memory is also called a buffer.

What are my different memory types?

The biggest and most popular type SQL Server memory is the buffer cache. It is the one usually referred to when talking about SQL Server memory, but many more memory types exist. Let’s see what we have under the hood.

Buffer cache or Buffer pool (MEMORYCLERCK_SQLBUFFERPOOL)

When reading data, all data pages are first retrieved into the buffer cache from disk before being processed by the SQL engine. Data pages are never read directly from disk. When there is not enough space in the buffer cache for new pages, the oldest ones are evicted to make place for the new ones.

When data is modified, the pages containing this data are invalidated in the cache (marked as dirty). Only when the changes are correctly inserted in the transaction log, the buffer manager will write the modified data to disk and remove the pages from the buffer pool.

In the following conditions, all (clean) pages in the buffer cache might be removed:

  • Running DBCC DROPCLEANBUFFERS: This command can have a negative effect on the performance of your queries, don’t run it in a production environment.
  • When executing the RECONFIGURE statement after changing the min or max memory configuration.
  • When the OS asks SQL Server to release some of its memory, for example because another application on the same server needs some. This is dependent on the configuration of the minimum memory.

Plan cache (CACHESTORE_xxx)

When a SQL statement is executed for the first time, the SQL engine needs to compile a query plan first to see how the data needs to be retrieved. A compiled plan, in xml form, is kept in the plan cache so that it can be reused the next time the same query is executed. With “the same query” I mean the exact same query text. A similar query that fetches data with a different value for a where clause, a different column alias, changes in commented text, all this results in different queries and therefore different instance of plans.

There are limits for the volume of cache used for the cache store (based on the max memory server configuration) as well as a limitation on the number of items/plans present in the cache. It is therefore important to keep an eye on your plan cache.

Some things you can do if you are hitting plan cache limitations, to use with caution.

  1. Look at the queries in the cache. Are they parameterized? If your server is hosting custom made databases, you might need to ask your developers to help changing the queries. If the queries cannot be changed you can change the PARAMETERIZATION setting of your database. Test this change extensively, in some situations it can change the behavior of the queries drastically and it affects all applications connecting to your database.
  2. If you have a lot of single-use queries, and you cannot change the “parameterization” setting of your database, you might want to look into the optimize for ad hoc With this option, the query plan xml is not stored in cache on first execution, but instead a compiled-plan stub is generated. This is marginally smaller than a query plan, and you might keep a better control of the volume of your query plan cache. On a second execution, if a compiled-plan stub is present for the query, then the query is compiled again and this time the query plan xml is stored. You gain a bit of memory, but it could have a cpu penalty because of the eventual extra compilations.
  3. As mentioned in Erins blog about plan cache limits, there are trace flags that you could use to have a higher boundary for both limitations, but they are seldomly used and I personally would not recommend them.

When a new query plan is compiled and there is not enough free space in the plan cache, the oldest plan in the cache is evicted. Query plans can be evicted for the following reasons:

  • Statistics were updated (manually or automatically) for objects used in the query plan.
  • Structure for objects used in the query plan are changed, this includes creating, dropping, and rebuilding indexes.
  • Changes made to stored procedures referenced in the query plan.
  • A sp_recompile was executed (takes any object as parameter, also tables).
  • Several DBCC command, such as FREEPROCCACHE, FREESYSTEMCACHE, FLUSHPROCINDB (all never to be executed on production machines), were used.
  • When executing the RECONFIGURE statement after certain configuration changes.

The most important plan cache types:

  • CACHESTORE_OBJCP: plans for stored procedures, functions and triggers.
  • CACHESTORE_SQLCP: plans for ad-hoc queries, prepared statements and server-side cursors.
  • CACHESTORE_PHDR: temporary space for bound trees (views, constraints and defaults).
  • CACHESTORE_XPROC: extended stored procedures such as some predefined system procedures (predecessor of CLR).
  • CACHESTORE_TEMPTABLES: Allocations related to temp tables and table variables.

Other memory types worth mentioning

  • MEMORYCLERK_XTP: Part of memory reserved for in-memory OLTP.
  • CACHESTORE_COLUMNSTOREOBJECTPOOL: Cache for columnstore data.
  • OBJECTSTORE_LOCK_MANAGER: For ongoing locks.
  • MEMORYCLERK_SQLOPTIMIZER: For query optimization by the SQL engine (“how things need to be done for query”).
  • MEMORYCLERK_SQLQUERYCOMPILE: For query compilation by the SQL engine (“what needs to be done for query”).
  • MEMORYCLERK_SQLCONNECTIONPOOL: For maintaining connections.
  • MEMORYCLERK_SQLRESERVATIONS: For memory grant allocations.
  • MEMORYCLERK_SQLQUERYEXEC: For batch mode processing, parallel query execution, spatial index operations, update statistics, query execution context.
  • MEMORYCLERCK_SQLCLR: allocations by SQLCLR.
  • MEMORYCLERCK_SQLCLRASSEMBLY: SQLCLR assemblies.
  • … Information about the other types can be found on dm_os_memory_clerks

How do I configure my SQL Server?

There are two main configurations concerning memory: the minimum and maximum memory. At start up, SQL Server will not commit immediately to the configured minimum. Instead it will ask the OS gradually for more memory when more is needed. If SQL Server needs to give back some memory to the OS, then the value dropped won’t drop below the configured minimum.

The configured maximum amount of memory includes all memory types described above. Depending on your edition of SQL Server there are limitations in place, but there is also good news! All limitations concerning memory are for specific memory types.

For a standard edition installation, you have the following limitations:

  • 128 Gb for the buffer cache
  • 32 Gb for the columnstore cache
  • 32 Gb for the OLTP cache

All other types of memory – plan cache for example – are not included in those numbers.

This means that, even if you have a Standard Edition installation, it is worthwhile to have your maximum amount of memory higher than 128Gb (depending on the type of features used).

Where are my metrics?

The dmv sys.dm_os_memory_clerks gives you the usage for all memory types at that time. In the SQL Server Management Studio, the same information is also available in the report “Memory Consumption”.

This report gives you a visual overview of the most-used types, the current page-life expectancy and memory grants, the changes in allocated memory and a detailed table listing all types and their usage.

How do I monitor all this?

The dmv and the report in SSMS only gives you a static view of your memory at a certain point of time. Collect these metrics regularly to be able to see the evolution and to set a baseline. This is, of course, only a part of the story.

To have a more complete view of your situation you can use performance metrics, which are collected by a lot of monitoring tools present on the market. The current values of the metrics are also available with the dmv sys.dm_os_performance_counters, and you can use them to configure a SQL Server Alert.

Performance metric Description
Monitoring the buffer cache: is your configuration and set-up optimal for the amount of data that needs to be processed?
SQLServer:Memory Manager – Total Server Memory (KB) Total memory allocated to SQL Server by the OS. Big jumps in this metric indicates that SQL Server had to release memory to another application but needed it again (soon) afterwards. Each time this happens, the buffer pool is flushed and you can experience performance problems.
SQLServer:Memory Manager – Database Cache Memory (KB) Total usage of memory for the buffer cache compared to the total server memory to see if you would need to configure a higher amount of maximum memory.
SQLServer:Memory Manager – Stolen Server Memory (KB) Total usage of memory except the buffer cache.
SQLServer:Buffer Manager: Buffer Cache Hit Ratio Percentage describing if ‘read’ requests were able to pull pages out of memory, or if it needed to retrieve them from disk first. Aim for a value of 95%.
SQLServer:Buffer manager – Page life expectancy Number of seconds the oldest page lived in the buffer cache. There isn’t a ‘correct’ number for this, you should baseline this per server to see which values are normal. What could be helpful is to monitor the number of sudden page life expectancy drops you have per day/hour.
SQLServer:Buffer Manager – Page reads/sec Number of physical pages read per second. This value gets high when sql server needs to retrieve data from disk because it could not be found in the cache.
SQLServer:Access Methods:Full Scans/sec Number of times an object (index/heap) was scanned. This value needs to be baselined and used together with the disk read activity. For example, if you have a lowish number of scans but a high number of pages read, then you might have performance problems with specific queries that don’t have the adequate index to use. On the other side, a high number of scans on a very small object is not necessarily a problem.
Monitoring the plan cache: are you hitting certain limitations volume or n° of plans in the plan cache, or is plan cache released constantly for usage on other memory types?
SQLServer:Plan Cache – Cache Pages Total usage of memory for the plan cache.
SQLServer:Plan Cache – Cache Object Counts Total number of items per plan cache type. A high number of objects in the cache might indicate a problem with ad-hoc queries or non-parameterized queries. This can become an issue when the number of compiles gets too high (high use of cpu).
SQLServer:SQLStatistics – SQL Compilations/sec This value needs to be baselined. A query is normally only compiled the first time that it is executed. After this, consecutive executions don’t need to perform a compilation if the query plan remains in the cache. A lot of compiles might indicate a lot of ad-hoc queries being executed, or the plan cache being released because memory was needed for other purposes.
SQLServer:SQL Statistics – Re-Compilations/sec This happens when the query engine finds a query plan in the cache but finds it no longer valid. Part of or the whole plan is then recompiled. This can happen when, for example, a table structure used by a stored procedure has changed (including rebuilding of index/update of statistics).
Can be helpful for both plan cache and buffer cache monitoring
SQLServer:Memory Manager – Free Memory The amount of dynamic memory SQL Server has allocated, but that is not used. It is therefore free to be used by any pool (depending on the limitation of the pool). If this number is very low, this means that there is no margin, and if a pool needs more space, another pool will have to release some of its content.
Memory grants: excessive waits or wait volume might indicate performance issues on queries with sorting, hashing, …
SQLServer:Memory Manager – Memory Grants Pending Number of queries that are waiting for a memory grant. You want this number as low as possible
SQLServer:Memory Manager – Granted Workspace Memory(KB) Total amount of dynamic memory the server is using during memory grants (sorting, hashing, index creation)

Author

Michèle Tahay, Data Wizard at Kohera.

Photo of successful woman coder hacker web creator sitting armchair comfortable workspace workstation indoors.
The hurdles and pitfalls of moving or migrating a System-versioned temporal table cross database
Maybe you already have your own way of doing this and are wondering about alternative methods, or maybe you are...
Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
2401-under-memory-pressure-featured-image
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
2402-fabric-lakehouse-featured-image
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...