Memory Usage
Overview
The Memory report answers one question: where did the memory go?
It answers it in three nested bands drawn on a single byte scale, so each band is the band above it broken down.
Physical RAM is the machine. The solid part is what the SQL Server process is holding; the hatched part is Windows, other services, and any other instance on the box.
Committed by SQL is that solid part, split into the pages actually caching data, the engine memory going to the plan cache, query memory grants, the lock manager and every other clerk, and whatever is still free.
Cached by database is the cached part again, split across the databases holding it.
Because the bands nest, one database owning half the buffer pool is a shape rather than a number you have to compare against another number.
Underneath the bands is a line of findings: max server memory left at its default, too little memory left for Windows, page life expectancy under what a pool this size should manage, queries queued waiting for a memory grant.
What this fixes
The report this replaces was a treemap of mixed-up numbers over a four column grid. The presentation was the least of it.
Every percentage was 28% too high. The old query divided 8 KB page counts straight into a megabyte total, which drops the divide by 128 and the multiply by 100. The Available row was the only one computed correctly, so it was on a different scale from every row above it and the column could not be made to add up to 100. Nothing on the page computes a percentage in T-SQL any more. Every share is taken from bytes in one place, so the chart, the grid and the headline cannot disagree.
Memory clerks appeared several times each. The clerk query grouped by type and name and then selected only the type, so a clerk with six named allocations came back as six rows and was drawn as six identical tiles. Grouping by type alone also sums a clerk correctly across NUMA nodes, which is the other reason sys.dm_os_memory_clerks returns more than one row per clerk.
“Available” was not available memory. It was physical RAM minus what SQL Server was using, which counts memory belonging to Windows, to other services and to any second instance as though SQL could have it. It never read max server memory, which is the setting that actually decides. It was not floored, so on a box where SQL holds most of the RAM it could go negative and be handed to a treemap as an area. It is gone. What replaces it is the hatched band, labelled for what it is, and a dotted max server memory marker drawn against the machine it constrains.
Small databases rounded away. BufferPages / 128 is integer division, so anything cached under a megabyte reported 0 MB, and the chart then drew the zero as a one. Everything is carried in bytes end to end.
None of the numbers a DBA opens this page for were on it. No max or min server memory, no target against total, no page life expectancy, no memory grants pending, no Lock Pages in Memory, no engine memory. All of them are on the Pressure view now.
Where to find it
An instance-level report. Right click an instance, open Instance Level Reports, then Memory. The page title reads Memory for <server name>.
It is also reachable from the Server Overview recommended reports list, and from the Related strip on CPU by Database, CPU Load and SQL CPU Schedulers.
Requirements
- SQL Server 2012 or newer.
- The login needs VIEW SERVER STATE on the instance.
Three parts degrade on their own rather than failing the page. If the login cannot read sys.dm_os_process_memory, the top band falls back to what SQL has committed. If it cannot read sys.dm_os_buffer_descriptors, or that scan runs out of time, the bottom band says so and the rest of the page is unaffected. Buffer pool extension is only read on SQL Server 2014 and newer.
The buffer pool scan
Everything on this page is read from counters that cost nothing, with one exception: the breakdown of the buffer pool by database. sys.dm_os_buffer_descriptors returns one row per 8 KB page in the pool, so a 100 GB pool is thirteen million rows to walk, and that walk is the whole wait on an instance with a lot of memory.
It is read last for that reason. Everything else is on screen-ready before the walk starts, so a walk that runs out of time costs the database rows and nothing else.
Past 64 GB of cached data it is not read at all by default, and the toolbar grows a Scan buffer pool button that reads it on demand. When that happens the subtitle under the chart says so, with the size it saw and the limit it was measured against. Nothing else on the page changes: the bands, the Pressure view, every clerk and every finding come from the cheap counters.
To move the limit, set MemoryPoolScanLimitGb in the settings file to a larger number of gigabytes, or to 0 to always walk the pool however large it is.
The four views
Buttons on the toolbar. All four are drawn from one load, so switching between them does not go back to the server. Your choice is remembered between sessions.
Ladder
The default, and the answer to “where did my memory go”. Three nested bands, described above.
Hover any segment for what it is, what it holds, and what share of the machine that is. Click a database segment to select its grid row; double click to open that database’s Table Size report.
Map
A squarified treemap in two labelled panes: the buffer pool by database, and engine memory by clerk. Two panes rather than one, because a database’s data pages and the lock manager are not comparable quantities and a single plane would claim they are. The panes are sized against each other, so the comparison between the two is still there.
Anything under half a percent of its pane is rolled into one grey tile, so the tiles still sum to the pane. On the larger database tiles, the third line is the share of that database’s cached pages that has been modified.
Ranked
One row per consumer, biggest first, bar length scaled against the largest single consumer. This is the view that survives a consolidation server with two hundred databases, where any treemap is slivers.
Cached data and engine memory are ranked together, because the question here is which single thing is holding the most memory. The kind is never in doubt: the stripe colour and the ENGINE chip both say it. On a database row, the darker segment at the end of the bar is the modified part.
Pressure
Six cards, each with its number, the test it was measured against, and a verdict.
| Card | What it says |
|---|---|
| Max server memory | The setting, or Not set when it is still the default 2,147,483,647 MB. |
| Left for Windows | Physical RAM minus what SQL is allowed, against a floor of 10% or 4 GB, whichever is larger. |
| Page life expectancy | The counter, against 300 seconds per 4 GB of cached data. Carries a 7 day sparkline when a history database is available. |
| Memory grants pending | Queries queued waiting for workspace memory, plus grant timeouts since startup. |
| Engine memory | Memory not caching data, as a share of what SQL has committed, and how much of it is plan cache. |
| Lock Pages in Memory | Whether anything is locked, and how much cached data is exposed if not. |
Reading the grid
One row per consumer, sorted largest first.
| Column | Meaning |
|---|---|
| Kind | Data cache for a database’s data pages, Engine for a memory clerk. Engine rows are tinted. |
| Name | The database, or the clerk under a readable name. |
| Memory | Bytes held, drawn as a bar scaled against the largest single consumer. |
| Share of Kind | Share of the buffer pool for a database, share of engine memory for a clerk. Deliberately not a share of the two together, which would be a number about nothing. |
| % of RAM | Share of the RAM in the machine. The one column that compares the two kinds. |
| Modified | Databases only. Cached pages changed since they were read in, which a checkpoint still has to write out. |
| Detail | The sys.dm_os_memory_clerks type behind a clerk’s friendly name, or how much of a database’s cache is modified. |
The size columns hold raw byte counts and are painted formatted, so sorting by size sorts by size. Storing the formatted string is what makes a grid rank 1.9GB below 1500MB.
Right click a database row for Table Size and Disk Space for that database.
How to read the report
Start with the findings line under the ladder. If it is empty, nothing about this instance’s memory settings or counters is worth acting on and the bands are just information.
max server memory not set is the most common real finding. SQL Server will grow until Windows pushes back, and Windows pushing back on a machine that is also running the application, a backup agent and a monitoring service is not a graceful process. The Pressure card suggests a starting figure.
A thin hatched band on row one is the same finding from the other direction. The floor is 10% of the box or 4 GB, whichever is larger.
A wide violet band on row two means memory is not caching data. Over a quarter of committed memory going to the engine is worth a look, and the Map view says which clerk. A large plan cache usually means single use ad hoc plans, and optimize for ad hoc workloads is the usual answer.
Low page life expectancy with a healthy sized pool means churn, not shortage. Something is reading more data than fits. The Missing Indexes and Problem Indexes reports are the next stop, not a memory upgrade.
Memory grants pending above zero is the one unambiguous signal. Queries cannot start at all until they get workspace memory. Look for large sorts and hash joins, and for grants far larger than the rows they process.
One database owning the pool is not automatically wrong. On a single application instance it is expected. On a shared one it means the other databases are being read from disk.
Where the data comes from
| Source | Used for |
|---|---|
sys.dm_os_sys_info |
Physical RAM, committed memory, commit target, service start time |
sys.dm_os_process_memory |
Working set, locked pages, large pages, Windows low memory signals |
sys.configurations |
max server memory (MB), min server memory (MB) |
sys.dm_os_performance_counters |
Page life expectancy, memory grants pending, total and target server memory, database cache, stolen and free memory |
sys.dm_os_memory_clerks |
Engine memory by clerk |
sys.dm_os_buffer_descriptors |
Cached pages and modified pages per database |
sys.dm_exec_query_resource_semaphores |
Grant waiters and grant timeouts |
sys.dm_os_buffer_pool_extension_configuration |
Buffer pool extension state and size, SQL Server 2014 and newer |
DBHealthHistory.dbo.PerfCounterOverTime |
The page life expectancy sparkline, when a history database is present |
Only the Buffer Manager and Memory Manager counter objects are read. The per NUMA node Buffer Node and Memory Node objects carry the same counter names and would double every figure.
sys.dm_os_buffer_descriptors walks every descriptor in the pool and is the slow part of the query, which is why this report runs on a longer timeout than most.
Messages you may see
“The memory scan did not finish in time.” The buffer descriptor scan ran past the timeout, which happens on instances with a great deal of memory and a busy pool. Press F5 to try again.
“No memory information came back from this instance.” The login almost certainly lacks VIEW SERVER STATE.
“No buffer descriptors were readable on this instance.” on the bottom band only. Rows one and two are fine; only the per database split is missing.
“Counters are still warming up” in the findings. SQL Server has been up for under four hours. Page life expectancy, what is in the pool and every clerk reading are all cumulative since startup, so they do not mean anything yet. This is a note, not a problem.
Related reports
- Databases by Size for what those databases are on disk, as opposed to in memory.
- CPU by Database for the same instance split by a different resource.
- SQL CPU Schedulers for the other half of the pressure picture.
- Waits for what queries are actually waiting on.
RESOURCE_SEMAPHOREandPAGEIOLATCH_*are the two families memory pressure shows up in. - Table Size, from a right click on any database row, for what inside that database is being cached.
Frequently asked questions
Why do the three bands not line up exactly? Row two comes from the Memory Manager counters and row three from the buffer descriptors, and the two are sampled a moment apart on a server that does not stop working while it is being measured. The bottom band is drawn against its own total for that reason. Forcing it to the counter’s scale would leave a gap at the end of the row that means nothing.
What counts as “engine memory”? Any memory a clerk has taken out of the buffer pool for something other than caching data pages: the plan cache, query memory grants, the lock manager, the optimizer, the schema and metadata caches, CLR, In-Memory OLTP. All of it is SQL Server doing its job. Only the amount is worth watching, because every byte of it is a byte not caching your data.
Why does perfmon call this “stolen”? Because the counter is named Stolen Server Memory (KB), and the engine’s internals talk about stolen pages. The word is written from the buffer pool’s point of view: the pool owns SQL Server’s memory by default, so any other component that needs a page takes one away from it. It describes which allocation a page came out of, not whether anything is wrong.
This report says engine memory instead, because “stolen” reads as an accusation against the plan cache and the lock manager, which are core parts of the engine behaving normally. The two words mean exactly the same number, so if you are cross-referencing this page against perfmon or against an article that says “stolen”, you are looking at the same thing.
Why is the Resource database in the list? mssqlsystemresource holds the system objects and is always in the pool. It is shown because leaving it out would mean the parts no longer add up to the whole, but it is never something to act on.
Why is there no buffer cache hit ratio? Because read-ahead inflates it to 99% and above on nearly every instance, healthy or not. Page life expectancy answers the same question and can actually go down.
What is a good page life expectancy? The card measures against 300 seconds per 4 GB of cached data, which is the long-standing rule of thumb. It is a starting point, not a law. The trend matters more than the number, which is what the sparkline is for.
Should I turn on Lock Pages in Memory? It stops Windows paging the buffer pool out, which matters most on instances holding a lot of cached data. The report raises it as a note above 32 GB cached, and only when nothing is currently locked. It needs the privilege granted to the SQL Server service account.