Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server Performance ,memory pressure and memory usage

04 June,2011 by Jack Vamvas

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 ;

stat type

Tool

Metric

value - 10 November 11.20 am

VAS:available memory in all regions

t-sql

Internal Virtual  Memory

295824 KB

VAS: largest available region

t-sql

Internal Virtual  Memory

86244 KB

Physical Memory : Available

Task manager

External Physical

2096536 KB

AWE allocated memory

t-sql

External Physical

3824 kb

Commit Charge (Total : Limit)

Task manager

External Virtual

12253108:66717076

Buffer counts : Target

dbcc memorystatus

Internal Physical Memory

2432000

Multipage allocator interface memory

t-sql

Internal Physical Memory

22896

Overall used w/o Bpool

t-sql

Internal Physical Memory

21440

Used by Bpool with AWE

t-sql

Internal Physical Memory

3978664

memory allocated through multipage allocator

t-sql

MEMORYCLERK_SQLSTORENG

576

memory allocated through multipage allocator

t-sql

OBJECTSTORE_SNI_PACKET

96

memory allocated through multipage allocator

t-sql

MEMORYCLERK_SQLOPTIMIZER

88

memory allocated through multipage allocator

t-sql

MEMORYCLERK_SQLGENERAL

2384

memory allocated through multipage allocator

t-sql

MEMORYCLERK_SQLBUFFERPOOL

7016

memory allocated through multipage allocator

t-sql

MEMORYCLERK_SOSNODE

10880

memory allocated through multipage allocator

t-sql

CACHESTORE_STACKFRAMES

16

memory allocated through multipage allocator

t-sql

MEMORYCLERK_SQLSERVICEBROKER

192

memory allocated through multipage allocator

t-sql

CACHESTORE_OBJCP

336

memory allocated through multipage allocator

t-sql

CACHESTORE_SQLCP

1280

memory allocated through multipage allocator

t-sql

MEMORYCLERK_SNI

32

memory allocated through multipage allocator

t-sql

MEMORYCLERK_HOST

 

 

 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

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Enjoy this post? Enter your email address for updates on new posts:

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer