笔记32-徐 内存压力分析

笔记32-徐 内存压力分析

--内存压力分析

--表现特征
--SQL经常触发lazy writer
--SQL需要经常从硬盘里读数据，会有很多硬盘读
--执行计划经常被清除，所以buffer pool里的stolen内存部分应该不会很多
--由于数据页经常被清除，所以page life expectancy不会很高，而且会经常下降

--page life expectancy：缓存页面生存时间

--如果数据页面 buffer pool内存有压力，SQL会优先清除内存里的执行计划

--解决办法：
--来自外部压力：Windows内存不够，SQL会压缩自己的内存，这时database page首当其冲被压缩
--建议SQL安装在专用服务器上


-------------------------------------------------------------------
--database page自身使用需求的压力
--1、32位服务器开启AWE
--2、增加物理内存
--3、如果scale up向上扩展不行，考虑scale out向外扩展，把SQL中的多个数据库移到其他服务器上，只留一个应用数据库或者分库处理
--4、使用表索引，减少不必要的读和表扫描

--1、2、3都需要新的硬件投资，而且到底加多少内存能够解决问题不好说，最好是最后一种方法

-----------------------------------------------------------------------
--来自buffer pool里的stolen压力
--如果声明了很多游标，用完了不关，或者prepare很多执行计划，不un-prepare,不登出SQL就会占用
--database page的空间
--使用sys.dm_os_memory_clerks的single_pages_kb字段查看那个clerk用掉了较多的stolen内存


-----------------------------------------------------------------------------------


--来自multi-page memtoleave的压力
--由于multi-page的量不大，所以一般这种问题较少
--1、32位系统没有开AWE，使用 /g 参数增大了multi-page的上限值
--2、64位系统multi-page没有上限，如果SQL调用了一些内存泄漏很厉害的第三方代码，
--64位系统就算内存再充裕也有漏完的可能性

----使用sys.dm_os_memory_clerks的single_pages_kb字段查看哪个clerk用掉比较多的内存


---------------------------------------------------------------------------------
--使用内存比较多的语句
--有一些语句在不停地调用数据，把读数据页面最多的语句找出来，基本就能够找到SQL内存压力
--的地方。分析一下这些语句是不是天生就要返回很多数据，为什麽读那么多数据页面


--例如：一个表有100W行记录，查询要返回70W行记录。那么这个语句在SQL使用很多内存是正常的
--在SQL上调整的余地不大，选择的方案有限：
--1、增加物理内存
--2、归档历史数据，把表里的数据降下来
--3、调整应用程序设计，避免不必要的大数据量查询

--因为没有索引，所以SQL不得不把表里的数据页面读到内存里，这样内存消耗是可以优化的
--最好的办法添加索引

--分析方法：
--使用DMV提取历史信息
--使用SQL Trace


--SQL2005以后有一个动态管理视图sys.dm_exec_query_stats,返回缓存查询计划的性能统计信息
--SQL会统计从上次SQL启动以来，一共做了多少次logical读写，多少次physical读，还记录执行所用的
--CPU时间总量。

--按照物理读的页面数排序 前50名
SELECT TOP 50
qs.total_physical_reads,qs.execution_count,
qs.total_physical_reads/qs.execution_count AS [avg I/O],
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

--上面语句的缺点是没有时效性，记录的生存期与执行计划本身相关联





--使用SQL Trace

--SQL Trace里面有一个reads字段，记录了某条语句完成过程中一共做了多少次读的动作，找到read最多的语句
--每个SQL Trace里有成千成万的语句，可以使用fn_trace_gettable 像一张表一样把trace文件里的记录查询出来
--可以用他将记录转入到SQLSERVER里，然后用查询语句进行统计分析。

--例如，在c:\sample目录下有一个问题时段的trace文件，叫a.trc.
--导入到SQLSERVER

SELECT * INTO #SAMPLE
FROM sys.fn_trace_gettable('C:\Users\Administrator\Desktop\1.trc',DEFAULT)
WHERE EventClass IN(10,12)

SELECT TOP 100 * FROM #SAMPLE
SELECT TOP 1000 TextData,DatabaseID,HostName,ApplicationName,LoginName,SPID,
StartTime,EndTime,Duration,reads,writes,CPU
FROM #SAMPLE



--(1)找到是哪台客户端上的哪个应用发过来的语句，从整体上讲在数据库上引起的读最多
--得到这个结果，就能大概知道哪些客户端要访问大量的数据页面，可能造成内存压力
SELECT * INTO #SAMPLE
FROM sys.fn_trace_gettable('C:\Users\Administrator\Desktop\1.trc',DEFAULT)
WHERE EventClass IN(10,12)

SELECT DatabaseID,HostName,ApplicationName,SUM(reads) AS reads
FROM #SAMPLE
GROUP BY DatabaseID,HostName,ApplicationName
ORDER BY SUM(reads) DESC



--(2)按照reads从大到小排序，最大的1000个语句 用这个方法可以找出最昂贵的单笔语句
SELECT TOP 1000 TextData,DatabaseID,HostName,ApplicationName,LoginName,
SPID,StartTime,EndTime,Duration,reads,writes,CPU
FROM #SAMPLE
ORDER BY Reads DESC




--利用readtrace这个工具自动分析trace文件，找出使用大量系统资源的语句
--如果只是要找到一段时间内系统资源使用比较多的语句，不用做详细分析，readtrace这个工具
--能够自动完成


--通过以上三种方法，DBA比较快地找到某个SQL上造成很多读操作的语句。这里需要说明的是
--SQL Trace里reads字段是逻辑读，而不一定是物理读，只有语句访问的数据不在内存里时，
--才有物理读！！！！！！！！




--stolen内存压力分析
--除了database pages，其他内存分配基本都是直接从地址空间申请，不是先reserve,后commit的
--stolen内存主要以8KB为单位分配，分布在buffer pool里

--stolen内存不缓存数据页面，任何一条语句的执行都需要stolen内存来做语句分析，优化，执行计划的缓存
--可能也需要内存来做排序，计算。任何一个连接的建立，需要分配stolen内存给他建立数据结构
--和输入输出缓存区。如果stolen内存申请不到，SQL任何操作都会遇到问题


--stolen内存有缓存：执行计划，用户安全上下文（每次执行sql语句之前都不需要重新建立安全上下文）
--这些缓存越多越好


--stolen内存没有缓存：使用完毕立刻释放，供其他用户使用，例如：语义分析，优化，做排序，做Hash等
--所以在32位SQL，虽然stolen内存只有不到2GB，但是很少有stolen内存不够用的情况



--在SQL2005 SP2以后,SQL产品组调小了执行计划的最高上限。这是因为如果一个SQL能够缓存这么多不同的执行计划
--说明他内部运行的大多数都是动态TSQL，很少能够重用。如果经常有执行计划重用的现象，SQL也就不需要每次都
--生成新的执行计划，从而缓存这麽多份了。在这样的SQL里，就算缓存再多的执行计划，重用的机会都很小。
--所以这么多的缓存对SQL的性能帮助不是很大，反而增加SQL的维护成本。从经验上看，SQL缓存1GB~2GB的执行计划
--基本足够了，更多的缓存基本上对性能帮助不大。在有些情况下，定期清空执行计划缓存，反而对SQL的健康起到
--帮助作用
CHECKPOINT
DBCC DROPCLEANBUFFERS



--stolen缓存区与数据缓存区相互关系
--stolen内存很多是用完就释放的，不会累积下来。
--对于有缓存机制的stolen内存，主要就是执行计划，也有清理机制，当buffer pool有内存压力的时候，SQL
--会同时清楚执行计划和database pages ,SQL都会把最久没有使用的对象清除

--就算一个查询一次访问上百MB的数据，但是他的执行计划却是非常小的。但是当lazy writer没有能清除
--正在被使用的stolen内存的时候，会发生stolen内存越积越多，最后侵占database pages空间的现象





--外部压力与内部压力
--外部压力：Windows通知SQL要压缩内存的时候，整个buffer pool里的所有内存都面临着清理

--内部压力：
--database pages挤压，当一个查询需要大量的data page的时候
--stolen内存内部一些始终未清理的对象，例如，打开了游标不关，或者prepare了一些语句没有unprepare,
--那么只要不logout,SQL就无法清理和释放这些对象





--解决办法:
--由于stolen内存是SQL自己申请使用的，可以用clerk看到
--特征：
--（1）返回错误信息701，在SQL 的errorlog里能看到错误信息
--（2）在sys.sysprocesses里的waittype字段不等于0X0000,而和stolen内存相关的等待状态：
SELECT spid,
blocked,
waittype,
DB_NAME(dbid)AS dbname,
open_tran,hostname,
program_name,
hostprocess,
cmd,
loginame
FROM sys.sysprocesses
ORDER BY spid ,dbid ASC
SELECT @@SPID
--waittype代码:

--cmemthread(0x00B9)
--当多个用户同时往同一块缓存区里申请或释放内存时，在一个时间点，只有一个连接可以做申请或
--释放内存动作，其他连接必须等待。这些连接的等待状态，就是cmemthread。这种等待状态发生得
--比较少，通常只会发生在并发度非常高的SQL里。而这些并发连接，在大量地使用需要每次做编译的
--动态TSQL语句发生得比较多。
--这个等待一般不是因为内存数量少，而是同时申请的人太多。所以解决的方法不是增加内存，而是修改
--客户连接的行为，尽可能更多地使用存储过程，或者是参数化的TSQL语句调用，减少语句的编译量，
--增加执行计划的重用，避免大量连接同时申请内存做语句编译
SELECT spid,
blocked,
waittype,
DB_NAME(dbid)AS dbname,
open_tran,hostname,
program_name,
hostprocess,
cmd,
loginame
FROM sys.sysprocesses
WHERE waittype=0x00B9
ORDER BY spid ,dbid ASC

--select * from sys.sysprocesses 的waittype字段显示0x40或0x0040(resource_semaphore) 等待资源

--SOS_RESERVEDMEMBLOCKLIST(0x007B)
--当用户发过来的语句内含有大量的参数,或者有一个很长的"in"的子句,他的执行计划在8KB的single pages
--可能会放不下，需要用multi-page来存储。所以SQL需要在MEMTOLEAVE申请空间。造成的后果是随着缓存
--的执行计划越来越多，不但buffer pool里的stolen内存不断增长，memtoleave里用来存储执行计划的stolen
--内存也在不断增长。由于在32位buffer pool可以比memtoleave大很多，所以buffer pool还没有压力，
--lazy writer不会被触发。但是memtoleave里的内存已经比较紧张了。当用户要申请这块内存而暂时不能得到
--满足时，他的等待状态就是SOS_RESERVEDMEMBLOCKLIST
--解决这种等待方法有三种：
--（1）避免使用这种带有大量参数，或者“in”子句的语句。这种语句的运行需要消耗比正常语句多得多的内存
--以及CPU资源，不是一种合理的设计。可以先把参数值存储到临时表里，用join临时表代替直接引用这些值。
--这是一种从根本上解决这种问题的方法

--（2）扩展memtoleave的大小，推迟SQL遇到瓶颈的时间


--（3）定期运行DBCC freeproccache语句，手工清除缓存的执行计划，缓解内存瓶颈。虽然没有第一种理想，
--但是在现实使用中，效果还不错，对SQL整体性能的影响不大

--type:USERSTORE_SXC:暂时存放正在执行中的语句的参数。例如，客户调用存储过程，或者用sp_executesql
--调用动态TSQL语句时，需要带入过程参数。如果参数过长，这部分内存的使用量就会比较大




--RESOURCE_SEMAPHORE_QUERY_COMPILE(0x011A)
--当一个batch或存储过程非常长和复杂的时候，SQL编译他所需要的内存可能超过你的想象。
--为了防止太多内存被用来做编译，SQL为编译内存设了一个上限。当有太多复杂的语句同时
--在做编译时，可能编译内存使用会达到这个上限。后面的语句将不得不进入等待状态，等前面
--的语句编译完成，把内存释放出来以后，后面的语句才能继续编译。这个等待状态被称为
--RESOURCE_SEMAPHORE_QUERY_COMPILE    SEMAPHORE（信号量）
--解决办法：
--是前两种的综合：
--（1）修改客户连接的行为，尽可能更多地使用存储过程，或者是使用参数化的TSQL语句调用，
--减少语句编译量，增加执行计划的重用，避免大量连接同时申请内存做语句编译的现象


--（2）简化每次需要编译的语句的复杂度，降低编译需要的内存量

--（3）当stolen内存使用总量比较大的时候，也可以考虑定期运行DBCC freeproccache语句,
--手工清除缓存的执行计划，保证stolen内存一直处在一个比较富裕的状态


--* DBCC FREEPROCCACHE
--从过程缓冲区删除所有元素


--虽然stolen内存没有database pages那么多，但也是SQL正常运行不可缺少的重要部分

--在64位系统，max server memory的设置仅对buffer pool起作用
--memtoleave默认是384，如果/g512 启动参数，那么memtoleave就是512+0.5*最大线程=640

--一般SQL根据负载量开启线程，大部分SQL的线程数目比256要少，如果地址空间被大量占用，-
--SQL可能无法再创建新线程，其中一个明显特征就是，新用户很难登入SQL

--使用大量multi-page的原因，multi-page只有384MB
--（1）SQL内部
--1大量参数或者长“in”

--2network packet size设成8KB或更高，而这种连接成百上千
--SQL默认的network packet size是4KB，这样每个连接的输入输出缓存都可以放在buffer pool里。
--在SSMS登录窗口，选项》 -》网络-》网络数据包大小：4096字节
--如果调高到8KB，或更高，每个网络包加上包头、包尾就要超过8KB。SQL只能把他们缓存在multi-pages里面
--如果连接非常多，会造成multi-pages大量使用

--有些应用例如：SAP  .NET应用等，需要提高网络交互效率


--SQL的确提供了比较丰富的XML处理功能，但是类似的功能在应用程序也可以通过直接调用一些XML的API
--解决。那么是放在SQL里，还是放在应用程序里做呢，需要设计者平衡一下。放在SQL里做可能比较方便
--但是如果处理的XML串比较长，很复杂，那么处理所要花费的资源是比较昂贵的，内存只是其中一部分，
--这个SQL还有其他用户要服务，那么难免会影响到用户的响应速度。

--（2）非SQL自己的代码
--SQL CLR
--LINKED SERVER
--EXEC sys.sp_OACreate 调用COM对象
--扩展存储过程EXEC sys.xp_cmdshell

--对multi-page来讲，只要用户调用，SQL就只好申请，所以从SQL的角度没有什么积极的办法。
--使用/g 参数或者 升级到64位系统


--常见内存错误和解决办法：
--主要有三类，这些内存错误跟内存瓶颈不同，有内存瓶颈SQL会发起很多paging 和lazy writer，但是
--内存错误轻则某些操作不能完成，重则整个SQL没有响应，不仅仅是性能问题

--1、OOM
--701错误：基本在32位机器，跟stolen内存有关，发现地址空间可供申请
--1、memtoleave地址段没有连续空间可供使用
--2、第三方代码申请太多内存没有释放掉
--SQL CLR
--LINKED SERVER
--EXEC sys.sp_OACreate 调用COM对象
--扩展存储过程EXEC sys.xp_cmdshell
--由于这些代码不是SQL自带的，所以在SQL这里做调整是很难解决问题的

--3、buffer pool里的stolen内存申请不到
--常见的会使用很多stolen内存的memory clerk有如下几个：




--17803错误
--解决方法：
--使用/g 参数扩大memtoleave的大小，延缓问题发生频率
--定期重启SQL，延缓问题发生频率
--升级SQL到64位
--前两种不是长久之计

--memoryclerk_sqloptimizer和cachestore_phdr:语句指令做编译时使用的内存，如果用户一次发过来的批指令太长，这段内存使用会较多

--memoryclerk_sqloreservation:语句运行的时候在内存里存储临时数据的地方
--cachestore_sqlcp:缓存动态TSQL语句执行计划的地方。记得释放游标很unprepare语句

--user_tokenperm:缓存用户的安全上下文。SQL在buffer pool没有压力的时候通常是64位机器可以涨到上百MB。在SQL2005中
--维护这么大的安全上下文缓存会影响SQL的整体性能

--objectstore_lock_manager:SQL里的锁结构使用的内存。SQL里有严重的阻塞问题，一些用户申请了大量的锁却不释放
--而另一些用户想要申请大量的锁却拿不到，造成锁的数目上百万，这时这段内存的使用量会很多





--2、语句运行时没能及时申请到内存，最常见的错误是8645
--这个错误通常发生在一个需要申请内存做排序或者hash等操作的查询里，在规定时间里没有能得到足够内存
--申请的内存基本都是buffer pool里的内存

--SQL内存本身有压力，现在再发过来哪怕很小的内存申请数也不能满足
--用户突然发来一个或几个需要大量内存非常复杂的语句，一下子把SQL内存资源搞得非常紧张


--现象：
--login failed
--select * from sys.sysprocesses 的waittype字段显示0x40或0x0040(resource_semaphore) 等待资源
--性能计数器:sql mamager:memory grants pending对象值不为0


--解决办法:
--避免其他程序把SQL内存侵占
--收集buffer manager 和memory manager性能计数器
--检查一下SQL内存参数
--max server memory
--min server memory
--awe enabled
--min memory per query
--lock page in memory

--检查各个memory clerk的内存申请
--检查工作负荷，例如：并发会话数，当前执行的查询，有可能的话开启sqltrace

--为SQL提供更多内存：
--移开占用资源的应用程序到别的服务器
--设置max server memory
--运行下面DBCC命令释放SQL内存缓存
DBCC freesessioncache
DBCC freeproccache



--找出使用内存比较多的语句，简化他们，调整应用程序行为，减少工作负荷
--检查动态管理视图,了解每个查询资源信号量的状态信息。（SQL里默认有两个查询资源信号量，分别处理复杂度不一样
--的查询，这样的设计有助于防止几个超大的查询把整个SQL资源用尽，连一些很简单的查询都不能响应的现象发生）

--检查语句是：
SELECT CONVERT(VARCHAR(30),GETDATE(),121) AS runtime,
resource_semaphore_id,
target_memory_kb,
total_memory_kb,
available_memory_kb,
granted_memory_kb,
used_memory_kb,
grantee_count,
waiter_count,
timeout_error_count
from sys.dm_exec_query_resource_semaphores

--resource_semaphore_id:资源信号量的非唯一ID，0表示常规资源信号量，1表示小型查询资源信号量
--target_memory_kb:该资源信号量能够授予使用的内存目标，也就是当前的使用上限
--total_memory_kb:资源信号量现在所持有的总内存，是可用内存和被授予内存的和。如果系统内存不足或频繁强制缩小内存，该值可以
--大于target_memory_kb值，但意味着这个资源信号量有内存压力
--available_memory_kb:可用于新授予的内存
--granted_memory_kb:授予的总内存
--used_memory_kb:授予内存中实际使用的部分
--grantee_count:内存授予得到满足的活动查询数
--waiter_count:等待内存授予得到满足的查询数，如果不为0，意味着内存压力存在
--timeout_error_count:自服务器启动以来的超时错误总数，对于小型查询资源信号量，该值为null



--检查sys.dm_exec_query_memory_grants,返回已经获得内存授予的查询的有关信息，或依然在等待内存授予的查询的
--有关信息。无须等待就获得内存授予的查询将不会出现在此视图中。所以对一个没有内存压力的SQL，这个视图应该
--是空的

SELECT GETDATE() AS runtime,
session_id,
scheduler_id,
dop,
request_time,
grant_time,
requested_memory_kb,
granted_memory_kb,
used_memory_kb,
timeout_sec,
query_cost,
resource_semaphore_id,
wait_order,
is_next_candidate,
wait_time_ms,
REPLACE(REPLACE(CAST(s2.text AS VARCHAR(4000)),CHAR(10),''),CHAR(13),'') AS sql_statement
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2

--session_id:正在运行查询的会话ID（spid）
--scheduler_id：正在计划查询的SQL Processor调度的ID
--dop：查询的并行度
--request_time：查询请求内存授予的日期和时间
--grant_time：向查询授予内存的日期和时间。如果尚未授予内存，则此值为null
--requested_memory_kb:请求的内存总量
--granted_memory_kb:实际授予的内存总量。如果尚未授予内存，该值为null。在典型情况下，该值应该与requested_memory_kb相同
--创建索引时，除了初始授予的内存外，服务器还允许增加按需分配的内存
--used_memory_kb:此刻使用的物理内存
--query_cost:估计查询开销
--timeout_sec:查询放弃内存授予请求前的超时时间
--resource_semaphore_id:查询正在等待的资源信号量的非唯一ID
--wait_order:等待查询在指定的queue_id中的顺序，如果其他查询获得内存授予或超时，则给定查询的该值可以更改。如果已授予内存，则为null
--is_next_candidate:下一个内存授予的候选对象：1：是  0：否 null：已授予内存
--wait_time_ms:等待时间。如果已经授予内存，则为null
--plan_handle:查询计划的标志符。使用sys.dm_exec_query_plan可提取实际的xml计划
--sql_handle:查询的TSQL文本标志符。查询中使用他链接sys.dm_exec_sql_text获取实际的TSQL文本



--3、SQL无法创建新线程供新连接使用
--当SQLSERVER上所有的进程都被用户请求占据，而又有新的客户端发出连接请求时，SQL需要创建一个新的线程
--来响应这个请求。如果连接创建不出来，会出现SQL断断续续地连接不上的现象，已经在SQL里的连接还能继续工作
--只要有线程创建不出来的问题发生，SQL errorlog里就会有记录

--17802错误
--17189错误

--解决办法：
--（1）：检查SQL使用了多少线程，是不是的确到了上限
--运行下面的查询，检查有多少个KPID<>0的SPID。当一个SPID的KPID不为0的时候，就说明他正在使用线程
--运行中。再加上SQL自己运行所需要的线程（一般10到20个），就差不多是SQL使用的线程数目
SELECT COUNT(*) FROM sys.sysprocesses WHERE kpid<>0

--（2）：如果线程的数目远小于设置的最大数，那就要考虑是不是memtoleave有压力了
--由于线程使用的是memtoleave的内存，确认SQL还有足够的memtoleave
SELECT  type ,
        SUM(virtual_memory_reserved_kb) AS [vm reserved] ,
        SUM(virtual_memory_committed_kb) AS [vm commited] ,
        SUM(awe_allocated_kb) AS [awe allocated] ,
        SUM(shared_memory_reserved_kb) AS [sm reserved] ,
        SUM(shared_memory_committed_kb) AS [sm committed] ,
        SUM(single_pages_kb) AS [singlepage allocator],
        SUM(multi_pages_kb) AS [multi page allocated]
FROM    sys.dm_os_memory_clerks
GROUP BY type
ORDER BY type