美高梅官方网站SQL Server 内存管理

一.概述

  这篇介绍Stolen内存相关的主要三种等待类型以及对应的waittype编号,CMEMTHREAD(0x00B9),SOS_RESERVEDMEMBLOCKLIST(0x007B),RESOURCE_SEMAPHORE_QUERY_COMPILE(0x011A)。也可以通过sysprocesses里查看连接处于某个等待状态, waittype!=0x0000。

select * from sys.sysprocesses where waittype!=0x0000 and spid>50

   再次看下Stolen内存的分配场景:

美高梅官方网站 1

1. CMEMTHREAD内存 

  cmemthread是指多个用户同时往同一块缓存里申请或释放内存时,在一个时间点上, 只有一个连接可以做申请或释放内存动作, 其他连接必须等待。原因:出现这种等待的原因通常是发生在并发度非常高的sqlserver里,而这些并发的连接,在大量地使用需要每次都做编译的动态t-sql语句。 解决:修改客户连接行为,尽可能更多地使用存储过程, 或者使用参数化的t-sql语句,减少语句编译量增加执行计划的重用,避免大量连接同时申请内存做语句编译的现象。

美高梅官方网站 2

    在生产环境下cmemthread平均每次请求时间为0.20ms(1570876.0/7825922.0=0.20)

2.SOS_RESERVEDMEMBLOCKLIST

  sos_reservedmemblocklist是指当用户要申请MemtoLeave这块内存时而暂时不能满足就会出现等待。原因:当用户发过来的语句内含有大量参数,或者有一个in 子句,它的执行计划在8kb的singlepage里可能放不下,需要用multi-page来存储。当缓存的执行计划越来越多,multi-page里的内存也会越来越多。 解决:(1)避免使用带有大量参数或者长in子句的语句,这种语句需要消耗比正常语句更多的内存及cpu资源, 改变的方法是可以把参数值存储到临时表,用join来连接。(2)定期运行dbcc freeproccache 语句,手工清除缓存中的执行计划,缓存内存压力。

美高梅官方网站 3

-- 查看缓存占用空间
SELECT SUM(CONVERT(DECIMAL(18,4),size_in_bytes))/1024.0/1024.0 AS 'sizeMB'
FROM sys.dm_exec_cached_plans 

--查看缓存中的对象类型,重用次数,sql语句,缓存空间大小,可以根据几个维度来统计
SELECT  usecounts,size_in_bytes/1024.0 AS 'sizeKB',cacheobjtype,objtype,[text] 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 1  
ORDER BY usecounts DESC

  美高梅官方网站 4美高梅官方网站 5

3.RESOURCE_SEMAPHORE_QUERY_COMPILE

   resource_semaphore_query_compile是指:当编译的语句需要的内存达到了sqlserver的编译内存上限时(sqlserver会为编译内存设置一个上限),其它语句将进入等待状态,等前面的语句编译完成,把内存释放出来以后,后面的语句才能继续编译。解决(1)修改客户连接行为,尽可能更多地使用存储过程, 或者使用参数化的t-sql语句,减少语句编译量,增加执行计划的重用,避免大量连接同时申请内存做语句编译的现象.(2)简化每次需要编译语句的复杂度,降低编译需要的内存量。(3)当stolen 内存使用总量比较大的时候,也可以定期执行dbcc freeproccache 。

美高梅官方网站 6

总结:以上三种等待类型,当缓存的执行计划越来越多,存放buffer pool里的stolen内存在不断增长,当需要的内存超过8kb时,multi-page里的存储执行计划stolen内存也会越来越多 。能过sys.sysprocess.waittype字段,可以检查stolen内存上是否有瓶颈。通过sql server 内存初探 知道 sql server里的Consumer下的功能组件,第三方代码,线程都是能过stolen方式直接提交,并不需要先申请内存。

  查看内存使用情况

-- 按申请方式统计内存 (Reserve 再commit)(直接commit叫Stolen)   
SELECT 
SUM(virtual_memory_reserved_kb)/1024.0 AS 'reserved(MB)',
SUM(virtual_memory_committed_kb)/1024.0 AS 'committed(MB)',
(SUM(single_pages_kb)+SUM(multi_pages_kb))/1024.0 AS 'Stolen(MB)'
 FROM sys.dm_os_memory_clerks

 -- 按申请内存页大小统计内存    
SELECT 
(SUM(virtual_memory_committed_kb)+SUM(single_pages_kb))/1024.0 AS 'Buffer Pool(MB)',
SUM(multi_pages_kb)/1024.0 AS 'MemToLeave(MB)'
 FROM sys.dm_os_memory_clerks

   按申请方式统计内存,共申请了92576MB,提交了83621MB, 在Stolen中有9244MB。 如下图所示:

美高梅官方网站 7  按申请内存页大小(<=8kb  >8kb)统计内存:
  美高梅官方网站 8

美高梅官方网站 9

 

windows memory:  Memory: Cache Bytes 是系统的working set, 也就是系统使用的物理内存数目。 可以观察Windows用了多少物理内存。

  1. System Cache Resident Bytes

  2. System Driver Resident Bytes

  3. System Code Resident Bytes

  4. Pool Paged Resident Bytes

 

SQL Server 动态管理内存:

SQL Server 是通过以下的API去感知windows是否有内存压力:

APIQueryMemoryResourceNotification -》 windows memory -》 decrease target server memory.

 

Total server memory : SQL Server启动账户拥有 Lock pages in memory 权限, 锁定内存,避免windows抢夺内存

Target server memory:  SQL在启动时候, 比较AWE, max server memory, physical memory 三者选一个最小的值作为Target server memory.

comparison

Result

Remark

Total <   target

Windows   has enough memory, SQL can allocate new memory for new data

Total is   increasing

Total =   target

SQL   used all of memory, SQL don’t allocate new memory for new data

SQL   clean up memory for new data, such as Lazy writer

Total >   target

Windows   has memory pressure, SQL decrease Target

SQL   clean up memory for new data, such as Lazy writer to release buffer pool and   cached plan

 

SQL server 内存管理概念: DBCC memorystatus -- 查看内存使用情况

  1. Reserved memory: 

  2. Committed memory: = Physical memory + Page file = Shared memory + Private Bytes

  3. Shared memory:

  4. Private Bytes:

  5. Working Set: = shared memory + Private Bytes - Page file

  6. Page Fault(soft/hard)

 

32位SQL:

memToLeave: 256MB+256thread*521KB=384MB (SQL 启动的时候就计算好了,不能变大): extended stored procedure, third party dirver, and linked server : 启动参数 -g512 = 512MB+256thread*521KB

BufferPool = 2G-384MB=1664MB (包括 database cache + stolen memory)

AWE开启以后, 只能给先reserve再commit的部分使用,即只能给database cache来扩展使用(Physical memory-2GB就是AWE扩展后database cache所能用的内存数量),stolen memory只能用1664MB里的内存。

 

SQL内存使用情况分析:

  1. SQL性能计数器:

  memory manager:  

    Total server memory,

    target server memory, 

    Optimizer memory

    SQL cache memory

    Lock memory

    Connection memory

    Granted workspace memory:  hash, sort, bulk-insert, create index...

    Memory grants pending: 等待工作空间内存授权的进程总数, 不等于0, 意味着比较严重的内存瓶颈

  Buffer manager:

    Buffer Cache Hit Ratio: 应该>99%, 如果<95%, 通常就是内存不足的问题

    Checkpoint pages/sec: 这个和内存压力没有关系,和用户的行为有关。用户做很多insert/update/delete, 这个值就会很大,脏数据多。

    database pages: 就是 database cache

    free pages: 

    Lazy writes/sec: 如果SQL 内存压力不大,不会经常触发lazy writer。 如果经常触发, 那么就应该是有内存的瓶颈

    page life expectancy: 页不被引用,将在缓冲池中停留的秒数。只有Lazy writer 被触发, Page life expectancy 才会突然下降。如果总是高高低低的, 应该是有内存压力

    page reads/sec: 这个值正常情况下应该很低。 如果比较高, 一般page life expectancy 会下降, Lazy writes/sec 会上升。

    stolen pages: 所有非database pages, 包括执行计划缓存。

 

内存DMV:sys.dm_os_memory_clerk

  select type,

    sum(virtual_memory_reserved_kb) as [vm reserved],

    sum(virtual_memory_committed_kb) as [vm committed],

    sum(awe_allocated_kb) as [AWE allocated],

    sum(shared_memory_reserved_kb) as [SM reserved],

    sum(shared_memory_committed_kb) as [SM committed],

    sum(multi_pages_kb) as [Multipage allocator],

    sum(single_pages_kb) as [Singlepage allocator]

  from sys.dm_os_memory_clerks

  group by type

  order by type

 

内存中的数据页面由哪些表格组成,各占多少: sys.dm_os_buffer_descriptors

  dbcc dropcleanbuffers  --clean up data buffer

  go

  

  

declare @name varchar(100)

declare @cmd varchar(5000)

declare c1 cursor for select name from sys.sysdatabases

open c1

fetch next from c1 into @name

while @@FETCH_STATUS=0

begin

print @name

 set @cmd= 'select b.database_id, db=db_name(b.database_id), object_name(p.object_id), p.index_id, buffer_count_kb=count(*)*8 from '  +@name+'.sys.allocation_units a, '+@name+'.sys.dm_os_buffer_descriptors b,'+@name  +'.sys.partitions p where a.allocation_unit_id=b.allocation_unit_id and a.container_id=p.hobt_id '  +' and b.database_id=db_id('''+@name+''')'  +' group by b.database_id, p.object_id, p.index_id '  +' order by b.database_id, buffer_count_kb desc '

 print @cmd

 exec(@cmd)

 

fetch next from c1 into @name

end

close c1

本文由美高梅官方网站发布于数据统计,转载请注明出处:美高梅官方网站SQL Server 内存管理

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。