SQL Server Performance ,memory pressure and memory usage

SQL server performance and memory pressure, is regular problem for the SQL Server DBA. How often have you seen messages in the logs such as :

 Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 122880

 Following on from my earlier post – Sys.dm_os_memory_clerks and AWE memory allocation  

This post presents a series of queries to drill into SQL Server memory usage.

 The approach is particularly relevant to SQL Server on 32-bit – where on large production systems – the limit is often met- normally requiring a SQL Server restart to resolve the problem. On a production environment , this can reflect negatively on systems administrators.

 The  post SQL server OLE Automation memory pressure , outlined the problems involved with OLE automation and memory pressure.

Factors contributing to VAS pressure.

1)  OLE Automation usage that is causing VAS pressure\fragmentation

2)  Excessive Linked Server Usage

3)  Custom Extended Stored Procedure

4)  3rd Party Backup Application (such as  Redgate SQL Backup) using to large of a Network Packet Size.

5)  Your applications are connecting with a Network Packet Size exceeding 8KB and going into VAS/Multi-page allocations. 

6) Adhoc (non-parameterized) workload leading to  plan cache expansion

7) SQL CLR usage that is causing VAS Pressure\fragmentation.

To create figures in analysing the source of memory pressure , these queries give a good insight. Thank you to Microsoft for the VIEW

 To allow regular comparisons , particularly if I’m troubleshooting a memory pressure problem , I set up a spreadsheet to record the information, adding a column for every snapshot .

-- virtual address space summary view

-- generates a list of SQL Server regions

-- showing number of reserved and free regions of a given size 

CREATE VIEW VASummary AS

SELECT

    Size = VaDump.Size,

    Reserved =  SUM(CASE(CONVERT(INT, VaDump.Base)^0)

    WHEN 0 THEN 0 ELSE 1 END),

    Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0)

    WHEN 0 THEN 1 ELSE 0 END)

FROM

(

    SELECT 

        CONVERT(VARBINARY, SUM(region_size_in_bytes))

        AS Size, 

        region_allocation_base_address AS Base

    FROM sys.dm_os_virtual_address_dump 

    WHERE region_allocation_base_address <> 0x0

    GROUP BY region_allocation_base_address 

 UNION  

 SELECT CONVERT(VARBINARY, region_size_in_bytes),

 region_allocation_base_address

    FROM sys.dm_os_virtual_address_dump

    WHERE region_allocation_base_address  = 0x0

)

AS VaDump

GROUP BY Size

 --available memory in all free regions

 SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB] 

FROM VASummary 

WHERE Free <> 0

 --largest available region

SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB] 

FROM VASummary 

WHERE Free <> 0

 --You can use the sys.dm_os_memory_clerks DMV as follows to find out how much memory SQL Server has allocated through AWE mechanism.

 select  sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] 

from  sys.dm_os_memory_clerks

--Internal physical

dbcc memorystatus

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

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

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

--cache

 

select *

from 

    sys.dm_os_memory_cache_clock_hands

where 

    rounds_count > 0

    and removed_all_rounds_count > 0

 --returns information about all entries in caches

select name, count(*)

from sys.dm_os_memory_cache_entries

 group by name

 order by count(*) desc ;

 SQL server performance and memory pressure is caused by many reasons – often difficult to track down. I’ve had good success with the tools above, presenting SQL Server Memory usage as used by the different services.

Read More on SQL Server and memory pressure

SQL Server – Memory: Pages sec, memory pressure and thrashing

Sys.dm_os_memory_clerks and AWE memory allocation

SQL Server – Windows Virtual memory and paging file location