Part two of debugging SQL server issues…. from the Partner managed newsgroups.
(There’s good stuff in there)
(II)
*Internal Physical memory pressure:
SQL Server detects high memory consumption internally, causing redistribution of memory between internal components.
Internal physical memory pressure may be a result of:
• Responding to the external memory pressure (SQL Server sets lower memory usage caps).
• Changed memory settings (e.g. ‘max server memory’).
• Changes in memory distribution of internal components (due to high percentage of reserved and stolen pages from the buffer pool).
General troubleshooting steps:
• Identify major memory consumers inside SQL Server.
• Verify server configuration.
• Further actions depend on the investigation: check for workload; possible design issues; other resource bottlenecks.
As internal memory pressure is set by SQL Server itself, a logical step is to look at the memory distribution inside SQL Server by checking for any anomalies in buffer distribution. Normally, the buffer pool accounts for the most of the memory committed by SQL Server. To determine the amount of memory that belongs to the buffer pool, we can take a look at the DBCC MEMORYSTATUS output. In the Buffer Counts section, look for the Target value. The following shows part of DBCC MEMORYSTATUS output after the server has reached its normal load.
Buffer Counts Buffers
—————————— ——————–
Committed 201120
Target 201120
Hashed 166517
Reserved Potential 143388
Stolen Potential 173556
External Reservation 0
Min Free 256
Visible 201120
Available Paging File 460640
Target is computed by SQL Server as the number of 8-KB pages it can commit without causing paging. Target is recomputed periodically and in response to memory low/high notifications from Windows. A decrease in the number of target pages on a normally loaded server may indicate response to an external physical memory pressure.
If SQL Server consumed a lot of memory (as determined by Process: Private Bytes or the Mem Usage column in Task Manager), see if the Target count amounts for a significant portion of the memory. Note that if AWE is enabled, you have to account for AWE allocated memory either from sys.dm_os_memory_clerks or DBCC MEMORYSTATUS output.
Consider the example shown above (AWE not enabled), Target * 8 KB = 1.53 GB, while the Process: Private Bytes for the server is approximately 1.62 GB or the Buffer Pool target accounts for 94% of the memory consumed by SQL Server. Note that if the server is not loaded, Target is likely to exceed the amount reported by Process: Private Bytes performance counter, which is normal.
If Target is low, but the server Process: Private Bytes or the Mem Usage in Task Manager is high, we might be facing internal memory pressure from components that use memory from outside the buffer pool. Components that are loaded into the SQL Server process, such as COM objects, linked servers, extended stored procedures, SQLCLR and others, contribute to memory consumption outside of the buffer pool. There is no easy way to track memory consumed by these components especially if they do not use SQL Server memory interfaces.
Components that are aware of the SQL Server memory management mechanisms use the buffer pool for small memory allocations. If the allocation is bigger than 8 KB, these components use memory outside of the buffer pool through the multi-page allocator interface.
Following is a quick way to check the amount of memory that is consumed through the multi-page allocator.
— amount of mem allocated though multipage allocator interface
select sum(multi_pages_kb) from sys.dm_os_memory_clerks
You can get a more detailed distribution of memory allocated through the multi-page allocator as:
select
type, sum(multi_pages_kb)
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type
type
—————————————— ———
MEMORYCLERK_SQLSTORENG 56
MEMORYCLERK_SQLOPTIMIZER 48
MEMORYCLERK_SQLGENERAL 2176
MEMORYCLERK_SQLBUFFERPOOL 536
MEMORYCLERK_SOSNODE 16288
CACHESTORE_STACKFRAMES 16
MEMORYCLERK_SQLSERVICEBROKER 192
MEMORYCLERK_SNI 32
If a significant amount of memory is allocated through the multi-page allocator (100-200 MB or more), further investigation is warranted.
If you are seeing large amounts of memory allocated through the multi-page allocator, check the server configuration and try to determine the components that consume most of the memory by using the previous or the following query.
If Target is low but percentage-wise it accounts for most of the memory consumed by SQL Server, look for sources of the external memory pressure as described in the previous subsection (External Physical Memory Pressure) or check the server memory configuration parameters.
If you have the max server memory and/or min server memory options set, you should compare your target against these values. The max server memory option limits the maximum amount of memory consumed by the buffer pool, while the server as a whole can still consume more. The min server memory option tells the server not to release buffer pool memory below the setting. If Target is less than the min server memory setting and the server is under load, this may indicate that the server operates under the external memory pressure and was never able to acquire the amount specified by this option. It may also indicate the pressure from internal components, as described above. Target count cannot exceed the max server memory option setting.
First, check for stolen pages count from DBCC MEMORYSTATUS output.
Buffer Distribution Buffers
—————————— ———–
Stolen 32871
Free 17845
Cached 1513
Database (clean) 148864
Database (dirty) 259
I/O 0
Latched 0
A high percentage (>75-80%) of stolen pages relative to target (see the previous fragments of the output) is an indicator of the internal memory pressure.
More detailed information about memory allocation by the server components can be assessed by using the sys.dm_os_memory_clerks DMV.
— amount of memory consumed by components outside the Buffer pool
— note that we exclude single_pages_kb as they come from BPool
— BPool is accounted for by the next query
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as [Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> ‘MEMORYCLERK_SQLBUFFERPOOL’
— amount of memory consumed by BPool
— note that currenlty only BPool uses AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = ‘MEMORYCLERK_SQLBUFFERPOOL’
Detailed information per component can be obtained as follows. (This includes memory allocated from buffer pool as well as outside the buffer pool.)
declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
— note that this total excludes buffer pool committed memory as it represents the largest consumer which is normal
select
@total_alloc =
sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> ‘MEMORYCLERK_SQLBUFFERPOOL’
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
‘Total allocated (including from Buffer Pool): ‘
+ CAST(@total_alloc as varchar(10)) + ‘ Kb’
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> ‘MEMORYCLERK_SQLBUFFERPOOL’
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/(@total_alloc + 0.0)) >= 0.05
then type
else ‘Other’
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> ‘MEMORYCLERK_SQLBUFFERPOOL’
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <>
‘MEMORYCLERK_SQLBUFFERPOOL’ THEN virtual_memory_committed_kb ELSE 0 END) +
shared_memory_committed_kb)) desc
select * from @tab
Note that the previous query treats Buffer Pool differently as it provides memory to other components via a single-page allocator. To determine the top ten consumers of the buffer pool pages (via a single-page allocator) you can use the following query.
— top 10 consumers of memory from BPool
select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc
You do not usually have control over memory consumption by internal components. However, determining which components consume most of the memory will help narrow down the investigation of the problem.
System Monitor (Perfmon)
You can also check the following counters for signs of memory pressure (see SQL Server Books Online for detailed description):
SQL Server: Buffer Manager object
• Low Buffer cache hit ratio
• Low Page life expectancy
• High number of Checkpoint pages/sec
• High number Lazy writes/sec
Insufficient memory and I/O overhead are usually related bottlenecks. See I/O Bottlenecks in this paper.
* External Virtual memory pressure:
Running low on space in the system page file(s). This may cause the system to fail memory allocations, as it is unable to page out currently allocated memory. This condition may result in the whole system responding very slowly or even bring it to a halt.
General troubleshooting steps:
• Increase swap file size.
• Check for major physical memory consumers and follow steps of external physical memory pressure.
You need to determine if page file(s) have enough space to accommodate current memory allocations. To check this, open Task Manager in Performance view and check the Commit Charge section. If Total is close to the Limit, then there exists the potential that page file space may be running low. Limit indicates the maximum amount of memory that can be committed without extending page file space. Note that the Commit Charge Total in Task Manager indicates the potential for page file use, not the actual use. Actual use of the page file will increase under physical memory pressure.
Equivalent information can be obtained from the following counters: Memory: Commit Limit, Paging File: %Usage, Paging File: %Usage Peak.
You can roughly estimate the amount of memory that is paged out per process by subtracting the value of Process: Working Set from the Process Private Bytes counters.
If Paging File: %Usage Peak (or Peak Commit Charge) is high, check the System Event Log for events indicating page file growth or notifications of “running low on virtual memory”. You may need to increase the size of your page file(s). High Paging File: %Usage indicates a physical memory over commitment and should be considered together with external physical memory pressure (large consumers, adequate amount of RAM installed).
Internal Virtual memory pressure:
Running low on VAS due to fragmentation (a lot of VAS is available but in small blocks) and/or consumption (direct allocations, DLLs loaded in SQL Server VAS, high number of threads).
SQL Server detects this condition and may release reserved regions of VAS, reduce buffer pool commit target, and start shrinking caches.
General troubleshooting steps:
• Identify major memory consumers inside SQL Server.
• Verify server configuration.
• Further actions depend on the investigation: check for workload; possible design issues; other resource bottlenecks.
