SQL Server Memory Internals – What you really need to know

I’ve written this article as a condensed version of all the things you really need to know about memory in SQL Server. Lot’s of good little nuggets of information in here and can be very helpful as refresher on all things memory. I have split this out into several sections:

• Main Areas Of Memory In SQL Server
  • Buffer Pool
  • Workspace Memory (Query grants)
  • Plan Cache
  • In-Memory Tables (not a deep dive into this topic)
  • Other smaller areas
• Monitoring Memory
  • Monitoring Internal Pressure
  • Monitoring External Pressure

Main areas of memory in SQL Server

Buffer Pool

• SQL Server and other RDBMS’ are designed to process data in memory. Disk I/O operations are very expensive for a database system. Due to this, SQL Server performs all operations strictly in memory, which is several times faster than disk (even SSDs).
• When a request is sent to SQL server to retrieve/update a piece of data, the data/index pages are read from disk into the ‘buffer pool’ portion of memory (if it was not already in memory) and continues processing the request in memory.
• Once the data is in the buffer pool cache, it tends to remain there – unless there is memory pressure and SQL Server decides to flush out the oldest pages from the cache to make space for newer pages.
• Subsequent requests that need the same data/index pages will directly access this data from memory, without having to get the data again from disk – this substantially improves response times and reduces disk IO.
• The Buffer pool is the largest section of memory for SQL Server, generally taking up 85%+ of the memory allocated to SQL Server

• There are several factors that can negatively impact the buffer pool:
  • Poorly written queries and improper indexing
    • Poorly written queries / stored procedures that pull / manipulate more data than really required
  • Lack of proper indexes can cause table scans (scanning the entire table) instead of more efficient index seeks. Table scans may scan the whole table into memory vs just a few records.
  • Fragmentation – more specifically internal fragmentation (free space within pages). External fragmentation has to do with data pages not being ordered contiguously.
    • Free space can be left within pages due to deletes that remove records from data pages, but leave some rows behind, leaving a lot of empty room within the page. Updates can do this too if it moves records from one page to another.
    • Page splits (a very expensive operation) happen when a page is full, and a new insert/update causes SQL server to ‘split’ the page to make room for the new insert. The split creates a new page, and rebalances the data from the old page into the new page so that the 2 pages have approximately 50% of data in each. This also adds to internal fragmentation, not to mention other problems associated with page splits (increased log IO to disk / external fragmentation)
    • Regular maintenance of indexes (rebuilds/reorgs) and proper padding of frequently fragmented indexes (using FILLFACTOR) can help keep these issues in check.
  • Compression
    • SQL Server tables are not compressed by default. Compressing tables reduces the footprint of data on disk, and more importantly in memory. This comes with a slight overhead in CPU, however most systems are IO/memory bound rather than CPU. It is generally a good practice to compress tables.
  • Large ad hoc queries
    • Ad hoc queries by nature don’t run often, which means the data required for the query is most probably not already in memory. This forces SQL Server to bring that data into memory from disk (and possibly flushing out other data). The larger the tables being queried, the greater the impact.
  • Table design
    • There may be cases where the size of a single row is 5KB, leaving 3KB free on a SQL data page(8KB). This means that only 1 row can fit per data page, leaving 3KB free in every data page. This is wasted space on disk and buffer pool.
    • There is no easy fix for this, but vertical partitioning of tables is one method to alleviate this issue.

---

Workspace memory (Query memory grant)

• When SQL Server receives a query request, the query needs to reserve some memory before it can even start executing. This memory is called the query memory grant. This section of memory is used for the query as a “workspace” to store temporary row data for sorting / hash joins / parallelism. This memory is reserved before actual run-time to improve reliability and reducing out-of-memory (OOM) errors during runtime.
• The memory granted to a query will depend on various factors such as the estimated number of rows returned, joins, parallelism settings, available memory, data types, etc.
• Memory grants are critical to monitor as this memory is taken out from the buffer pool. So several large memory grants = less space for buffer pool
• The total amount of space available for memory grants is dynamically managed between 25% and 75% of the buffer pool but it can grow larger than that if the buffer pool is not under pressure.
• In general, a single query cannot be granted more than 25% of SQL server’s allocated memory (can be overridden by using resource governor workload groups)
• There are 2 memory grant parameters:
  • Minimum required memory: The minimum memory required to run a sort / hash join – a query cannot start without being granted this memory
  • Additional memory: Amount needed to store all temporary rows in memory. This is additional because a query can still run without this memory by storing part of the temporary rows on disk (tempdb spills)
• When a new request is made, SQL server will calculate the ‘ideal’ memory based on statistics / row count estimates / parallelism and other factors – this will be the actual requested memory for the query
  • Queries will not start unless the available memory grant on the server is 150% of the requested query memory. The 150% is required as to leave some room for concurrency for other queries during execution of this query.
  • When 150% of memory is not available, SQL will place the query in a waiting queue with RESOURCE_SEMAPHORE wait type
  • This query will wait until it reaches its memory grant timeout:
    • Default timeout is 25x the query cost, in seconds (max 86400 == 24 hours)
    • The default timeout can be overridden using resource governor workload group or instance level setting ‘query wait (s)’
  • When a query reaches its memory grant timeout, sql server will THEN attempt to run the query by:
    • Reducing the memory grant to the minimum required grant for the plan (this will cause spills to disk – tempdb spills – and can cause the queries to run much slower)
    • Or throw an OOM error – error 8645
  • All these metrics like requested / required / granted memory / timeout / query cost are all visible in sys.dm_exec_query_memory_grants
• Keep in mind that not all spills mean that SQL had to reduce memory grant due to memory pressure. Spills can also happen when a plan cost was not accurate – where the plan requested only a small amount of memory, but actually ended up needing a lot more.
• Increased parallelism (DOP) will increase requested memory grant.
• Memory grants can be throttled more granularly by using resource governor.
  • Memory grant priority and timeout can also be set on workload groups
• Large query memory grants can be mitigated by:
  • Having statistics up to date. Bad stats –> Bad plans –> suboptimal memory grants
  • Checking for parameter sniffing
    • Plan may have initially compiled using an expensive parameter, when most executions may really need another plan
    • You can use OPTIMIZE FOR or recompile hints to get around this
  • Not selecting more columns than required
  • Eliminating unnecessary rows early in the query
  • Proper indexing to reduce the amount of data being processed and more optimal joins / plans
  • Limiting parallelism
  • Avoiding sorting if possible
    • Sorting is very expensive and requires a lot of workspace memory
• Too much memory grant and too little memory grants can both be a problem
  • Too much –> less concurrency for other queries needing memory and negative impact on buffer pool
  • Too little –> more spilling to disk, slowing down queries

For more detailed info, check out the MSDN article.

---

Plan Cache

• SQL Server compiles execution plans for queries / procs / functions, which is essentially the SQL Server’s ‘map’ on how to get your data and process it.
• Compiling execution plans can be time consuming and resource intensive, therefore, once an execution plan is compiled for a statement, it is cached in memory to be reused for subsequent requests.
• The reuse works by SQL server creating a hash of your actual SQL statements (or stored procedure name) and looking if a plan already exists in cache for that exact same hash.
• Maximum plan cache size for SQL Server is calculated as below:
  • 75% of server memory from 0-4GB +
  • 10% of server memory from 4GB – 64GB +
  • 5% of server memory > 64GB
• The plan cache can get bloated due to a large amount of ad-hoc SQL statements that are only run once
  • This causes the plan to be cached, but never used again
  • This can be caused by TSQL statements that use literal values instead of parametrization. Using literal values causes SQL Server to get a different hash for the TSQL statement – which prevent re-use of plans.
    • Ex. –> select name from dbo.Employees where name = ‘John’ 
      • Literal. Can only be reused if the exact same statement runs again
    • select name from dbo.Employees where name = @name
      • Parameterized and can be reused for different names
  • This is common for TSQL that is generated dynamically and can be minimized by using sp_executesql instead of EXEC. This method also prevents SQL injection, so it’s definitely the recommended option for dynamic SQL.
  • Forced Parametrization database option can also be used to force SQL Server to parameterize any literal values when compiling the plan. This option is not very commonly used, but can be helpful as a tool to force parameterize without making code changes.
  • Optimize for Ad-hoc server setting will also help alleviate issues by only storing a small ‘stub’ of the plan during the first run. It will only store the actual plan in cache if it was run a second time. This saves a lot of space on unique TSQL statements that run only once.
  • Plan cache bloat can cause other plans to be flushed out, causing SQL Server to have to compile these again the next time it runs
• Plan cache related clerks in sys.dm_os_memory_clerks (see monitoring section later in this article)
  • CACHESTORE_OBJCP — Compiled plans for objects such as stored procedures, functions, and triggers
  • CACHESTORE_SQLCP — Cached plans for SQL statements or batches that aren’t in stored procedures. They are much less likely to be reused than stored procedure plans, which can lead to a bloated cache taking a lot of memory
  • CACHESTORE_PHDR — Algebrizer trees for views, constraints, and defaults. An algebrizer tree is the parsed SQL text that resolves table and column names.

---

In-Memory Tables

• As the name suggests, all data and indexes for memory optimized tables reside fully in memory
• Resource governor can be leveraged to ‘bind’ a database to a resource pool
  • Min/max memory limits can be set on this pool to prevent a table from growing uncontrollably
• SQL Server uses an internal threshold as a safety mechanism to not allow in memory tables to use 100% of SQL Server memory or a resource pool. Generally, in memory tables can only use up to 80 – 90% of a resource pool before it is full. Check out this link for more info on binding databases to a resource pool – very helpful on setting hard limits on your in-memory tables.

---

Some Others (smaller footprint)

1. Lock memory
   • SQL Server manages locks in memory. The more locking on the server, the more lock memory used.
   • The memory usage here can be increased if using query hints like ROWLOCK or turning off lock escalation
2. Optimizer Memory
   • Memory required for the SQL Optimizer to compile/recompile a query plan
3. Log Pool Memory
   • Transaction log records are first written in the log cache before it is flushed to disk.
   • Some related info:
     • SQL Server implements WAL (write-ahead logging) protocol to guarantee durability of transaction. This means that log records will need to be flushed to disk, before any modified data/index pages can be written to disk. Having the log records written first ensures that transactions can be rolled back/forward in case of a failure.
     • Cool Fact: For In-Memory tables, log records are always written to the log cache and ONLY flushed to disk on COMMIT.
       • Therefore, transaction log space does not need to be pre-allocated for rollback like disk-based tables (you need approximately the same amount of space to rollback a transaction in disk-based tables because even rollbacks are a logged operation)
       • Since only committed records and Tlogs are flushed to disk, there is ONLY redo operation for in-memory tables (no undo required) during recovery.
4. Connection Memory
   • A small amount of memory is required to manage connections to SQL Server

---

Monitoring Memory

Internal Pressure

• Page Life Expectancy (PLE)
  • This is by far the most important metric to monitor the health of SQL Server memory. It basically tells you how many seconds a database page lives in the buffer pool, before being forced out for other needs.
  • If you had a 4GB buffer pool, the recommended PLE is 300 seconds – this means 4GB of data is flushed from memory to disk every 300 seconds (an average of 13MB / sec churn).
  • The question of what is the right PLE really depends on the memory allocated to SQL and how much churn you want your I/O subsystem to go through.
  • In general, the more memory you have, the more PLE you should target for. If you go by the Microsoft recommendation of 300 seconds for 4GB of memory, you can calculate your target PLE by using this formula:
    • Target PLE = ( SQL Max Server Memory / 4 ) * 300
    • This isn’t a hard rule, but following this rule will keep your churn on average to 13MB / sec.
    • Higher end systems with more memory and better disks should be able to live with much higher churn rates – so you can go with a lower target PLE than recommended by the formula

• Lazy Writes / sec
  • This metric basically says how many times SQL Server evicts data pages out of the buffer pool due to pressure. Lazy writes can evict both dirty and clean pages.
  • Lazy writes are bound to happen anytime the buffer pool is full and a new data page is requested to memory, so it’s a normal process and can vary depending on the workload. However, a constant elevated number for this metric can indicate a constant pressure – but, as long as your system’s PLE remains in the target range, it should be ok.
  • The recommended range for Lazy Writes is <20 on average.

• Memory Clerks
  • One of the best ways to monitor the different areas of memory in detail is to look at the memory clerks – sys.dm_os_memory_clerks. This will give you better insight to how much memory is used by each area – load this into a table in intervals to track this over time.
  • A quick google search should give you the description of a particular clerk.

select top (20) getutcdate() as DateValue
                ,@@SERVERNAME as ServerName
                ,mc.[type] as [MemoryClerkType]
                ,cast((sum(mc.pages_kb) / 1024.0) as decimal(15, 2)) as [MemoryUsageMB]
from sys.dm_os_memory_clerks as mc with (nolock)
group by mc.[type]
order by sum(mc.pages_kb) desc;

External Pressure (other apps / OS)

I thought it was just best to paste a snippet straight out of the SQL Internals book.

Keep in mind that the ‘pressure’ that’s mentioned in the snippet is talking about external windows pressure. So the Target server memory can drop if there is external pressure from windows, and I believe this can happen even with ‘lock pages in memory’ – but the trimming down of the buffer pool will be done by SQL – not Windows. SQL can keep track of windows memory pressure and throw errors in error log.
sys.dm_os_sys_info DMV has this info so it can be monitored without perfmon.

• committed_kb – total server memory
• committed-target_kb – target server memory