Small Business Susan

How to troubleshoot SQL memory issues part II

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

 

print

    ‘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.