如有翻译不对或不好的地方,关于等待事件RESOURCE

  这篇介绍Stolen内存相关的主要三种等待类型以及对应的waittype编号,RESERVEDMEMBLOCKLIST(0x007B),等待事件介绍,关于等待事件RESOURCE,SQL Server 是通过以下的API去感知windows是否有内存压力,SQL Server启动账户拥有 Lock pages in memory,我们必须找出是什么导致我们的实例出现内存紧张,如有翻译不对或不好的地方

图片 12

一.概述

  这篇介绍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平均生次请求时间为4.98ms(7825922.0/1570876.0=4.98)

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

 

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

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

 

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

下一步

 

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

 

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

declare c1 cursor for select name from sys.sysdatabases

 

 

 

 

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

    sum(single_pages_kb) as [Singlepage allocator]

识别RESOURCE_SEMAPHORE等待

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

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

 

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

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

  • 使用此提示来识别由于内存不足而消耗更多内存并将剩余的事务置于等待状态的查询。
  • 还要查看上述DMV的其他列,并将它们联系起来,以便更好地分析和了解性能问题。
  • 这些DMV应提供大量信息,以便您能够识别问题。
  • 阅读有关性能调优的更多提示,以提高系统性能。

 

 

 

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

  memory manager:  

现在我们将使用上面查询所获得的plan_handle和sql句柄来获取SQL代码。

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

32位SQL:

该DMV的输出返回两行,一个表示大型查询(resource_semaphore_id为0),另一个表示小型查询(resource_semaphore_id为1),小于5
MB。在这里,您可以获得实例的总授予内存和总可用内存。请参阅grantee_countwaiter_count上的数字,grantee_count是已经分配了内存的总查询数,waiter_count是队列中等待获取内存的总查询数量。所以在这里我们可以看到大约100个查询正在等待获得他们要求的内存。

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

  图片 10

 

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

    sum(virtual_memory_committed_kb) as [vm committed],

现在我们将获取所有正在等待队列中获取所请求的内存的所有查询的详细信息,我们将使用DMV
sys.dm_exec_query_memory_grants来获取队列中等待分配内存的查询总数。对于等待获取其请求的内存的查询,grant_timegranted_memory_kb列将为NULL。您可以在下面的截图中查看所请求的内存量及其等待状态,因为它们的grant_time和granted_memory_kb值为NULL。我们还可以使用该DMV获取所有查询的plan_handle和sql_handle。稍后我们将使用这些值来获取确切的查询。

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

 

 

 

    Connection memory

步骤5

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

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

资源信号量等待

 

    sum(shared_memory_reserved_kb) as [SM reserved],

 

SQL
Server在收到查询时,会执行3个被定义好的步骤来返回用户所请求的结果集。

open c1

SELECT
* FROM sys.dm_exec_query_memory_grants

  

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

注意:有太多列要显示,可以只显示部分所需的列。

       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.

  Buffer manager:

 

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

    sum(multi_pages_kb) as [Multipage allocator],

图片 11

等待事件分析

    Lock memory

 

 

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

 

 

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

** 

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

** 

 

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

问题描述

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

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

** 

 

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

 前言: 本文是对博客的翻译,本文基本直译,部分地方读起来有点不自然。
如有翻译不对或不好的地方,敬请指出,大家一起学习进步。尊重原创和翻译劳动成果,转载时请注明出处。谢谢!

 

 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 ‘

** 

 

  order by type

现在,我们将找到内存密集型查询。我们可以看到所有等待查询的请求内存。在这里我们可以看到所请求的内存对于大多数事务来说太大了。我们将获得所有这些查询的plan_handle,以获得确切的SQL文本来查看查询计划。

 

  go

首先,我们需要研究我们的实例,弄清楚为什么在SQL
Server中出现内存压力。要查看所有事务的大概信息,我们可以查询sys.sysprocesses,或者我们可以使用sys.dm_exec_requests
DMV。

该等待事件在并发查询编译的数量达到阀值限制时出现。 等待时间较长或等待次数较多可能表明编译、重新编译或不能缓存的计划过多。

    Optimizer memory

 

 

内存DMV:sys.dm_os_memory_clerk

** 

 

  from sys.dm_exec_cached_plans cp

在继续之前,我想对资源信号量(Resource_semaphore)等待进行一些说明,以便您可以更好地了解SQL
Server是如何将内存授予SQL Server查询语句的。

 

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

当资源信号量(Resource_semaphore)接收到新的请求时,它首先检查是否有任何查询正在等待中。只要发现存在即是一个等待查询,那么会将新查询(新请求)放入队列中,因为等待队列是以先到先得的方式设计的,并且有小权重以支持于小型查询。当没有等待查询或查询返回保留的内存时。资源信号量尝试授予内存。如果找到足够的内存,那么请求内存被授予并且查询可以开始运行,并且如果没有找到足够的可用内存来授予所请求的内存,那么它将当前查询放入等待队列中,并且给当前会话RESOURCE_SEMAPHORE等待类型,
此时服务器开始面临内存压力。

 

    sum(virtual_memory_reserved_kb) as [vm reserved],

图片 12

 

    SQL cache memory

今天,我们的一个SQL
Server实例性能变得非常慢。当我登录到数据库服务器进行一些初始检查时,我最初始观察、注意到的是它的内存压力(memory
pressure)。接下来,我们必须找出是什么导致我们的实例出现内存紧张。当我检查事务的等待类型时,RESOURCE_SEMAPHORE等待是大多数事务的问题。在这篇文章中,我将描述这个问题,以及如何查找哪个查询语句或事务导致了内存压力

 

 

当SQL
Server收到用户发送的查询请求(查询语句)时,它首先创建一个编译过的计划,然后在这个基础上创建一个执行步骤(个人觉得执行步骤比执行计划要合适)。当SQL
Server创建一个编译过的计划时,它会计算两个内存授予参数,称为“请求内存”(required
memory)和“附加内存”(additional
memory)。请求内存是运行排序和HASH连接所需的最小内存。它之所以是 “必需”的, 是因为如果没有可用的“请求内存”,
查询将无法启动。附加内存(additional
memory)是在内存中存储临时行(个人觉得翻译为中间结果可能更合理)所需的内存量。这被称为额外(附加)的,因为如果没有足够可用的“附加内存”可以将查询的中间结果存储在磁盘上。