您所在的位置:首页 > 新闻中心 > 性能调优,内存管理
公司要闻
性能调优,内存管理
发布时间:2020-03-26 05:07
访问量:359

一.概述

  这篇介绍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

关于等待事件RESOURCE_SEMAPHORE_QUERY_COMPILE,官方的介绍如下:

  2. System Driver Resident Bytes

 

  3. System Code Resident Bytes

       Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.

  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抢夺内存

内存授予的等待类型叫做“RESOURCE_SEMAPHORE”.在理解这个等待事件前,我们先来了解一下查询内存授予(query memory grant),它是用于在排序或连接时存储临时数据的服务器内存的一部分。查询在实际执行前需要先请求保留内存,所以会存在一个授予的动作。这样的好处是提高查询的可靠性和避免单个查询占用所有的内存。

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在收到查询时,会执行3个被定义好的步骤来返回用户所请求的结果集。

 

  

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)

  1. 生成编译计划(compiled plan)。它包括各种逻辑指令,如怎么联接数据行。

 

2. 生成执行计划(execution plan),它包含将编译计划中的各种逻辑引用转换成实际的对象的指令和查询执行的跟踪机制。

32位SQL:

3. 从指令树的顶端开始执行。

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)

生成编译计划是件开销较大的事情,因为它需要在数以百计的编译计划中找出较优的一个。它的时间通常很短,因为优化器会在找到最优的编译计划后便马上释放内存。编译主要使用内存和CPU资源。缺少可用内存可能会导致编译延迟和得到非最优的编译计划。    

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

 

 

 

SQL内存使用情况分析:

当SQL Server创建编译计划时,会计算两个参数:必须内存(Requeried memory)和额外内存(Additional memory)。

  1. SQL性能计数器:

  

  memory manager:  

  必须内存:执行排序和哈希联接所需的最少内存。这部分内存是“必须”的,它用来创建处理排序和哈希所需要的内部数据结构。

    Total server memory,

  额外内存:存储所有临时数据行所需的内存。它的大小由基数评估(Cardinality estimate,如行数和行大小)决定。“额外”,顾名思义在缺少这部分内存时,将会将临时数据行存到硬盘上,并不会导致查询失败。一个查询的额外内存大小如果超过预设的限制,它实际得到的内存量并不一定会跟请求量一样。

    target server memory, 

 

    Optimizer memory

例如,对行大小为10byte的100万行数据进行排序,此查询的必须内存为为512KB(此值是SQL Server处理一个排序操作创建内部数据结构所需的最小内存量)。为了存储所有数据行,额外内存可能是10MB。

    SQL cache memory

 

    Lock memory

  当编译计划中含有多个排序和联接操作时,额外内存的计算就变得复杂了。因为SQL Server要考虑所有操作符如何高效地使用内存。可以查看ShowPlan XML中的<MemoryFractions>标记部分内容,获取更多内存使用的信息。

    Connection memory

 

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

RESOURCE_SEMAPHORE_QUERY_COMPILE等待事件一般是查询正在等待授予内存以开始进行编译时发生。编译内存来自缓冲池(buffer pool),并需要保留足够的时间以完成编译过程。 对于多个并发编译而言,占用太多内存页可能会导致内存压力。 为了缓解这种情况,SQL Server启动编译过程,确定哪些查询需要大量的页面,并迫使某一些查询会话等待。 同样,如果内存压力已经存在,SQL Server将限制可以同时编译的资源密集型查询的数量。

    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 才会突然下降。如果总是高高低低的, 应该是有内存压力

  1. Decrease query complexity 降低查询语句的复杂度。

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

 

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

  1. Appropriate indexing could reduce plan complexity  合理创建索引减少执行计划复杂度

 

 

内存DMV:sys.dm_os_memory_clerk

  1. Improve plan reuse (therefore compilation can be avoided)  改善执行计划重用(因此可以避免编译)

  select type,

 

    sum(virtual_memory_reserved_kb) as [vm reserved],

  1. kill掉一些糟糕的SQL语句(内存资源密集型SQL),当然这个要看是否可行。

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

个人曾遇到过这样一个案例,由于过度灵活设计,导致很多报表需要在SQL中大量关联相关表,更糟糕的是,由于开发人员大量使用视图,尤其是还存在视图嵌套视图的情况,所以在这样一个系统中,一些查询语句往往需要授予大量的内存,尤其是当出现一个或一些写的很糟糕的SQL语句时,就会经常看到一些会话处于RESOURCE_SEMAPHORE_QUERY_COMPILE的等待状态,而且当大量会话处于RESOURCE_SEMAPHORE_QUERY_COMPILE等待时,还有一个特殊现象就是活动的会话数量会彪增,此时,可以找到消耗内存最多的SQL,然后Kill掉后,活动的会话就会立即降下来。下面就是我遇到案例的一个截图。

    sum(multi_pages_kb) as [Multipage allocator],

 

    sum(single_pages_kb) as [Singlepage allocator]

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan 

FROM sys.dm_exec_query_memory_grants AS mg

CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp

ORDER BY 1 DESC OPTION (MAXDOP 1)

  from sys.dm_os_memory_clerks

澳门新濠登录网址 10

  group by type

 

  order by type

另外,内存紧张也会导致RESOURCE_SEMAPHORE_QUERY_COMPILE的出现的概率增加,那么是否增加内存就有效解决RESOURCE_SEMAPHORE_QUERY_COMPILE等待事件呢?答案是否定的,但是能缓解。如下描述:

 

 

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

     This wait occurs when queries cannot be compiled due to the amount of compile memory currently available. This mostly occurs due to large queries requiring an excessive amount of memory. SQL Server caps the amount of complex queries that can be compiled at once, so increasing the memory allocation will not solve the problem effectively (it will only increase the amount of memory that can be allocated, not the number of queries)

  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

https://blogs.msdn.microsoft.com/sqlqueryprocessing/2010/02/16/understanding-sql-server-memory-grant/

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

deallocate c1

go

 

执行计划都缓存了些什么? 哪些比较占内存? 

  select objtype, sum(size_in_bytes) as sum_size_in_bytes, count(bucketid) as cache_counts

  from sys.dm_exec_cached_plans

  group by objtype

 

  select usecounts,refcounts, size_in_bytes, cacheobjtype, objtype, text

  from sys.dm_exec_cached_plans cp

  cross apply sys.dm_exec_sql_text(plan_handle)

  order by objtype desc;

  go

 

-----------------华丽的分割线: 下面开始分析 数据页面(database page),  buffer pool 里的stolen, multi-page 这三部分------------------------------------

数据缓冲区压力分析:

  Lazy writes/sec 高

  Page life expectancy 低

  Page reads/sec 高: 正常这个值应该接近0, 指从数据文件读取的数据量

  Stolen pages 降低

  sys.sysprocesses 中出现一些连接等待I/O完成的现象: PAGEIOLATCH_SH

 

  使用DMV分析启动以来做read最多的语句:

  --按照物理读的页面数排序,前50名:

  select top 50

  qs.total_physical_reads, qs.execution_count,

  qs.total_physical_reads / qs.execution_count as [avg IO],

  substring(qt.text, qs.statement_start_offset/2,

    (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 

      else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text,

  qt.dbid, dbname=db_name(qt.dbid),

  qt.objectid, qs.sql_handle, qs.plan_handle

  from sys.dm_exec_query_stats qs

  cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

  order by qs.total_physical_reads desc

 

  --按照逻辑读的页面数排序,前50名

  select top 50

  qs.total_logical_reads, qs.execution_count,

  qs.total_logical_reads / qs.execution_count as [avg IO],

  substring(qt.text, qs.statement_start_offset/2,

    (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 

      else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text,

  qt.dbid, dbname=db_name(qt.dbid),

  qt.objectid, qs.sql_handle, qs.plan_handle

  from sys.dm_exec_query_stats qs

  cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

  order by qs.total_logical_reads desc

 

  --使用trace 文件分析做read最多的语句

  select * into sample

  from fn_trace_gettable('c:\sample.trc',default)

  where eventclass in (10, 12)  -- 10: RPC completed    12: SQL batch completed

 

  select top 1000 textdata, databaseid, hostname, applicationname, loginname, spid

    starttime, endtime,duration,reads,writes,cpu

  from sample

  order by reads desc

 

  --使用ReadTrace工具分析trace文件,找出使用大量系统资源的语句

  使用方法见稍后发出。。。

 

Stolen Memory缓存压力分析:

  Stolen memory 凡是以8K为分配单位的,保存在buffer pool里。 大于8K的,保存在MemToLeave里。 

  缓存: 执行计划,用户安全上下文,连接的数据结构和输入/输出缓冲区

  没有缓存: 语义分析,优化,排序,Hash,计算

 

  Stolen memory 在不同的SQL版本,最大的限制是不同的,见下表:

  澳门新濠登录网址 11

 

  表征与解决方法:查看 sys.sysprocesses 里面的连接等待waittype字段不等于0x0000: (可以手动 DBCC FREEPROCCACHE)

  1. CMEMTHREAD (0x00B9)

  2. SOS_RESERVEDMEMBLOCKLIST (0x007B)

  3. RESOURCE_SEMAPHORE_QUERY_COMPILE(0x011A)