SQLactions.com   An action flick on SQL Server

I recently worked on an issue where SQL Server was experiencing memory pressure. During the troubleshooting, one of my colleagues asked an interesting question as how Stolen Potential is calculated and why is stolen potential negative? I thought to share what I researched and learned through a quick blog post.

In SQL Server 2008, when SQL Server experiences memory pressure (usually due to Buffer pool pressure on x64 instance) the following error is logged in ERRORLOG:

2012-10-30 15:50:42.21 spid67      Error: 701, Severity: 17, State: 123.
2012-10-30 15:50:42.21 spid67      There is insufficient system memory in resource pool 'default' to run this query.

 
The way you can tell it is a Buffer pool pressure is by looking at a message similar to this:

2012-12-18 16:37:01.22 spid20s      Failed allocate pages: FAIL_PAGE_ALLOCATION 1

 
As you see, it failed to allocate 1 page here. Single page allocation comes from Bpool. So that’s a Bpool pressure. Additionally, following messages also indicate a Bpool pressure:

BPool::Map: no remappable address found.
BufferPool out of memory condition
LazyWriter: warning, no free buffers found.

 
Had it a MTL (technically speaking, outside Bpool) pressure, SQL server would have printed messages like below:

Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 65536
WARNING: Failed to reserve contiguous memory of Size= 65536
Failed Virtual Allocate Bytes: FAIL_VIRTUAL_COMMIT 65536

 
Note that in the above message, size is in bytes. 65536 bytes = 64 K which is greater than 8K. That means SQL Server tried to reserve/commit 64K page which obviously has to come from outside Buffer pool (up until SQL 2012).

Coming back to the subject of this blog, in our case we had an OOM (Out of Memory) condition due to Buffer pool pressure. SQL Server printed DBCC MEMORYSTATUS output in the Errorlog a few times. From DBCC MEMORYSTATUS output, we get the distribution of various Buffer Pool counters. Here is one such snippet:

Buffer Pool                                   Value

---------------------------------------- ----------
Committed                                   1048576
Target                                      1048576
Database                                     917683
Dirty                                          7041
In IO                                            26
Latched                                          23
Free                                          96816
Stolen                                        34077
Reserved                                    1302240
Visible                                     1048576
Stolen Potential                            -340169
Limiting Factor                                  17
Last OOM Factor                                  17
Last OS Error                                     0
Page Life Expectancy                           4869

 
As we see, Stolen Potential is negative here. We also see another counter for Stolen pages here. According to KB907877 stolen memory describes 8-KB buffers that the server uses for miscellaneous purposes. These buffers serve as generic memory store allocations. Similarly, Stolen Potential shows the maximum pages that can be stolen from the buffer pool. Having said that, it is evident if the value of stolen potential goes negative, there won’t be any further pages that Engine can steal from the Buffer Pool. Hence in such cases where stolen potential is negative, SQL Server will throw an OOM.

Now, the outstanding question is how is this value of stolen potential calculated. So, here is the formulae as per my understanding:

Stolen potential = (95% of Visible) - Stolen – Reserved

In our case, Stolen Potential = (1048576 * 0.95) – 34077 – 1302240 = -340169.8 = the value we see above.

Note: Up until SQL 2008 R2, stolen potential memory counter was under “Buffer Pool” object. In SQL 2012, stolen potential is called Page Alloc Potential (KB) and resides under “Memory Manager” object. Please refer to Memory Manager surface area changes in SQL Server 2012 for more details.

Okay, now was the part to troubleshoot the issue. We know it was a Bpool pressure, so, look for the clerk having high “single page allocator”. Apparently, in our case MEMORYCLERK_SQLQERESERVATIONS appears to be the distinguished one.

MEMORYCLERK_SQLQERESERVATIONS (node 0)           KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
SinglePage Allocator                       10436736
MultiPage Allocator                               0

 
MEMORYCLERK_SQLQERESERVATIONS is a workspace for query executions. Usually there might be some memory intensive queries (due to inappropriate or missing indexes or bad cardinality estimates). Leveraging some of the useful queries from this blog we found there was one poor query which was alone taking 9 Gigs of memory for query execution. Tuned the query in DTA (basically a missing index was suggested and we also updated the statistics with full scan) and issue resolved.

Hope this post may help some.