【转】sql server内存分析,sql server磁盘分析,sql serverop分析

【内存全概念参考】

SqlServer 内存篇（六）—— sqlserver 2012内存管理新特性

SqlServer 内存篇（五）—— 常见内存错误与解决方法

SqlServer 内存篇（四）—— 各部分内存不足特征及解决方法

SqlServer 内存篇（三）—— SqlServer内存使用状况分析

SqlServer 内存篇（二）—— SqlServer内存管理模式及常见问题

SqlServer 内存篇（一）—— 从操作系统层面看SqlServer内存分配

参考自：CSDN

　　https://so.csdn.net/so/search?q=SqlServer%20%E5%86%85%E5%AD%98%E7%AF%87&t=&u=

 【内存概述】

转自：https://www.cnblogs.com/wy123/p/5516045.html

参考 内存案例：https://blog.csdn.net/kk185800961/article/details/46664421

在数据库服务器中，内存是数据库对外提供服务最重要的资源之一，
　　不仅仅是Sql Server，包括其他数据库，比如Oracle，MySQL等，都是一类非常喜欢内存的应用.
　　在Sql Server服务器中，最理想的情况是Sql Server把所有所需的数据全部缓存到内存中，但是这往往也是不现实的，因为数据往往总是大于可用的物理内存
　　可以说内存是否存在压力能够直接决定数据库能否高效运行，
　　同时，如果内存出现压力，同时也会影响到CPU的使用和存储性能，可以说是一损俱损，具有连带性。
　　那么，如何识别内存是否存在压力，如何判断一台服务器上是否存在内存瓶颈？

 

　　Sql Server 2012之后，对内存的管理进行了大刀阔斧的改革，所有的内存管理都受Max Server Memory的控制
　　如下截图所示的最大服务器内存设置（当然这个截图是我本机上一个测试实例，这里并不说明内存该怎么配置）

　　

而大多数的内存量化都涉及到Sql Server的Buffer Pool，一个内部的缓存管理器，

/*
　　20160525补充：
　　Sql Server 2012之前的版本，
　　对于Buffer Pool是存储Data Cache的，
　　另外一部分专用的内存称之为Memory To Leave（Sql Server 2012之后，叫做Stolen Memory），
　　这部分内存的使用也是一个非常大的话题，这里暂不展开论述。
　　32位操作系统下，这部分内存是Sql Server 启动后直接初始化分配的，64位操作系统是直接跟操作系统申请，
　　如果需要的Stolen Memory过大，同样会“挤压”Buffer Pool的内存
*/

　　但是，Sql Server2012之后，所有内存的管理都受到Max Server Memory的控制。
　　同时，Sql Server在运行的过程中，会将各种内存的参数情况记录下来，这对我们去判断Sql Server内存压力有着非常重要的参考意义
　　下面提到的部分计数器的就存储在sys.dm_os_performance_counters这个系统视图中

 

我们抽取其中最重要的几个来做解释说明：

【1】内存核心指标

  1. Page Life Expectancy
  2. Buffer Cache hit ratio
  3. Page reads / sec
  4. Page writes / sec
  5. Lazy writes / sec
  6. Total Server Memory
  7. Target Server Memory
  8. Paging File % Usage

需要注意的是，不能通过上述某一个值就武断地断定内存瓶颈，各个计数器之间是有一定的关系的，要结合多个值来做谨慎的分析判断。

 

Page Life Expectancy

　　Page Life Expectancy又简称位PLE，含义是内存页面在内存中停留的平均时间，是内存压力判断的一个重要参考值
　　在系统视图sys.dm_os_performance_counters中可以查到，单位是秒.
　　需要注意的是它不是指某一个page的最大值或者最小值，而是所有由所有页面停留在buffer pool中的时间计算出来的一个平均值
　　如果这个值越大，说明Sql Server在检索数据时候直接从buffer pool中获取数据的概率越大，
　　如果Sql Server直接从buffer pool中检索到数据，那么就不用去磁盘中去查询，因为直接从内存中获取数据的效率要远远高出从磁盘中去获取数据
　　因为从内存中查询数据的延迟是纳秒级的，而从磁盘获取数据的延迟是毫秒级的，这之间差了两个数量级，
　　可见从缓存中获取数据和从磁盘中获取数据，对性能的影响有多大
　　那么PLE这个值多少位正常呢？我发现很多资料上多这个值都有误解，说是300S，300S是在十多年前的一个参考值，
　　是基于当时的服务器内存受到4GB内存的限制的影响得到的，
　　目前服务器内存动辄超过100GB的情况下，用同样的标准，显然是不够准确的，这个值的计算是跟具体的服务器内存配置有关的
　　具体我就不做进一步的解释，可以参考如下链接

　　https://simplesqlserver.com/2013/08/19/fixing-page-life-expectancy-ple/

　　　　一个可供参考的标准算法是 Max Buffer Pool(GB)/4*300(S)
　　　　这个值可以通过sys.dm_os_performance_counters 这个系统视图直接查询得出

select * 
from sys.dm_os_performance_counters 
where object_name like '%Buffer Manager%' and counter_name='Page life expectancy'

 

　　　　比如你的服务内存是64G，分配给Sql Server最大内存（上述Max Server Memory）是60G
　　　　那么PLE的参考值就是60/4*300=4500S，大概是75分钟，也就是说，最低限度是每75分钟，内存中的数据跟磁盘做一次完整的交换
　　　　如果你的服务器上的PLE值长期低于计算出来的这个参考值，或者这个值在某个时间段内有非常明显的变化，那么你就需要注意内存是否存在瓶颈了
　　　　如果你真的做过这方面观察的话，这个值在不同环境中差别是非常大的
　　　　当然对于测试服务器，经常没几个人用，或者压力非常小的服务器，内存没压力或者服务器根本没有负载的情况下，缓存进去的数据可能就一直存在于内存中
　　　　这个值有可能非常大，达到几万秒都是有可能的
　　　　不信我给你截个图，呵呵

　　

　　　　当然对于压力比较大的生产服务器，即便是有几十个GB的内存，这个值，也有可能小到几十秒钟，我所在的公司就是这个情况。
　　　　所以，PLE的值是作为判断内存是否存在瓶颈的最重要的指标之一。

Buffer Cache hit ratio

　　　　Buffer Cache hit ratio就是缓存命中率，字面上的解释就是一个查询过程中所需要的时候，直接从内存中读取出来的比例占所有数据的百分比
　　　　鉴于表现出来的值受到其算法的制约，反倒是在内存压力诊断的时候并不具备太多的参考意义，
　　　　既然Buffer Cache hit ratio不具备太多的参考意思，那么为什么把他放在这里呢？
　　　　因为这一个非常流行的参数，很多材料上都提到过这个参数
　　　　很多材料上都介绍其阈值是90%，95%之类的参考值，其实都是错误的，
　　　　其实真正观察过的人，如下链接，早就有人有此疑问了，从PLE和Buffer hit ratio得出根本不一致的结论，
　　　　有时候我们做学问还是要讲究严禁的，不能人云亦云

　　　　怎样理解Buffer hit ratio 是99%, 但Page life expectancy<200?

　　　　我这里不做详述，可以参考我的另一篇博文，里面有详述。http://www.cnblogs.com/wy123/p/5272675.html

　　　　另外，真的很佩服老外，从本质上阐述了Buffer Cache hit ratio，能把学问做的这么认真，真的不容易。

通过sql查询缓存命中率

SELECT 
　　CAST(CAST((a.cntr_value * 1.0 / b.cntr_value)*100 as int) AS VARCHAR(20)) as BufferCacheHitRatio
FROM (
        SELECT * FROM sys.dm_os_performance_counters
        WHERE counter_name = 'Buffer cache hit ratio'
        AND object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
        THEN 'SQLServer:Buffer Manager'
        ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +
        ':Buffer Manager' END 
    ) a
CROSS JOIN
(
    SELECT * from sys.dm_os_performance_counters
    WHERE counter_name = 'Buffer cache hit ratio base'
    and object_name = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
    THEN 'SQLServer:Buffer Manager'
    ELSE 'MSSQL$' + rtrim(@@SERVICENAME) +
    ':Buffer Manager' END 
) b

Page reads（writes） / sec 

　　这两个计数器分别是对应的平均每秒钟的物理读/写的数据量，这个计数器的是一个累计值，单位为page，而每个page又是8Kb，
　　可以换算成一个基于kb或者mb位单位数据
　　对于计数器中的类似累计值，并不妨碍我们通过计算得出某个时间间隔内的平均值。
　　也就是说，
　　对于 Page reads / sec，一个查询在执行过程中，发现需要的数据不存在于buffer pool中，需要到磁盘上去查询
　　对于 Page writes / sec ，就是在面临内存压力的时候，将内存页写入磁盘来腾出内存空间
　　上面说了，直接从buffer pool中，也就是内存中读取数据和从磁盘中读取数据的时间上的差别是巨大的，对性能的影响也是非常明显的
　　实际中我们也会经常遇到这种现象，有些SQL查询语句，第一次执行比较慢，但是再次执行的时候，就相对快了很多，
　　当然通过set statistics io 这个信息也能发现有第一次存在物理读的现象，这种速度上的差别，还是比较明显的
　　这两个值可以通过如下sql查询得到

select * 
from sys.dm_os_performance_counters 
where object_name like '%Buffer Manager%' and (counter_name='Page reads/sec' or counter_name='Page writes/sec' )

　　　　如果一台服务器上经常发生大批量的物理性IO操作，你就要注意是否存在内存问题，
　　　　因为经常性的大批量的物理IO会严重拖慢SQL的执行效率，理想情况下，这个值不应该过大，也有材料上说不能持续大于0，我个人觉得有点绝对了
　　　　其实也没有一个绝对的标准，只要这个值能够稳定在一个较低的水平，没有持续性的大批量数据的写入（磁盘）于读取（从磁盘载入内存），都可以接受
　　　　相反，如果长期在一个高位水平，并且观察到PLE不能稳定在参考值范围内，说明内存可能存在瓶颈。

Lazy writes / sec

　　　　Lazy writes 是每秒被缓冲区管理器的惰性编写器（lazy writer）写入的缓存区的数据page信息。
　　　　Lazy writer是一个系统进程，用于批量刷新内存中的脏页到磁盘，并且将原来脏页占用的内存空间清理的一个动作。
　　　　如果存在内存压力，Lazy writer会被触发，将脏页和长时间没有用到的计划缓存清理出内存，
　　　　如果经常被触发，那么说明内存可能存在瓶颈
　　　　需要注意的是，通过如下 sys.dm_os_performance_counters 查询出来的Lazy writes/sec值是一个累计值
　　　　但是这也不妨碍我们得出某一个时间间隔内发生的Lazy writes/sec的数据，相信聪明的你一定可以算出来　

select * 
from sys.dm_os_performance_counters 
where object_name like '%Buffer Manager%' and counter_name='Lazy writes/sec'

 

　　　　对于脏页以及老化的缓存计划，有其他机制去实现写入磁盘存储并清理器占用的内存空间
　　　　Lazy Write是在面临内存压力的情况下触发的，
　　　　如果某一个时间间隔内，Lazy Write持续不为零，就要结合PLE以及Page reads（writes） / sec 来判断分析内存是否存在不够用的情况了。

说完PLE和Page reads(writes) / sec 以及Lazy writes / sec之后，就可以做一个小小的总结了

　　上面说了，衡量内存瓶颈的时候，通常要结合多个值来做出判断，
　　如果你的PLE不在计算出来的参考值预期之内，同时又伴随着大量的Page reads（writes） / sec
　　那么就几乎可以断定你的服务器存在内存瓶颈了
　　因为PLE达不到预期值，也就是说可能有大量所需要的数据不存在于缓存中，
　　而去读这些数据，又要进行从磁盘上的物理读取，那么就会出现Page reads（writes） / sec 较高的现象
　　物理读取出来的数据要占用缓存空间（之后才能返回给查询的客户端），
　　而原来缓存空间中的数据是通过Lazy writes被清理出内存，这样数据从磁盘进入缓存，而缓存中的数据又被清理出去，造成的结果就是PLE上不去
　　所以结合这三个值的信息，基本上就可以断定你的内存是否存在瓶颈。

 

　　当然除了上述三个计数器，还有其他更多的信息去对内存做诊断，我们继续。

Total Server Memory/Target Server Memory

　　　　Total Server Memory是Sql Server内存管理器“已提交”内存，说白了就是已经占用了的内存，
　　　　 而Target Server Memory则是Sql Server内存管理器可用的最大内存
　　　　这两个值也可用通过sys.dm_os_performance_counters 查询出来

select * 
from sys.dm_os_performance_counters 
where object_name like '%Memory Manager%' and counter_name in ('Target Server Memory (KB)','Total Server Memory (KB)')

　　当Total Server Memory小于Target Server Memory的时候，Sql Server还知道系统还有可用内存，在需要内存中的时候，直接跟系统申请，
　　此时Total Server Memory会逐渐变大。
　　但是，当Total Server Memory接近于或者等于Target Server Memory的时候，Sql Server会意识到已经用完了系统的可用内存，
　　如果在需要内存的时候，系统已经无法继续分配新的内存，它就需要清理已用的内存空间，将新清理出来的空间给新的数据使用
　　这个似乎又要跟上面说的PLE以及lazy write联系上了，
　　当然，系统内存空间往往是小于数据的空间的，比如有可能你的数据库文件大小是500GB，而内存之后32G或者64G，
　　数据库经过一段时间的运行后，Total Server Memory总是接近于或者等于Target Server Memory的，
　　那么我们说Total Server Memory和Target Server Memory的意义何在？
　　上面说了，鉴于数据文件中的数据往往都是大于可用物理内存的（当然极端例子也有，你数据库只有2GB，内存32GB）
　　数据很有可能不完全都能缓存在内存中，但是最起码，要缓存持续到一定时间再去释放空间（给新的数据使用），而不是不停地直接去读磁盘，这就要求有一个度
　　你不能说Total Server Memory总是接近于或者等于Target Server Memory，没内存用了，清理内存是正常的
　　如果缓存75分钟是正常的，
　　发现Total Server Memory持续性接近或者等于Target Server Memory，而PLE明显低于计算出来的参考值,低到几分钟甚至一两分钟，
　　同时观察到内存跟磁盘之间频繁地、大量地物理性交换数据，这也说明，内存极有可能存在瓶颈。　　　　

 Paging File % Usage

　　Paging File也即缓存文件，另外一个名字叫做虚拟内存，你一定听说过，就是用拿物理磁盘空间当做内存空间使用，
　　Windows系统的虚拟内存文件一般是存储在C盘的，一个叫pagefile.sys的文件，默认是隐藏的
　　如下截图

　　

 　  

　　这里先说明两个问题：
　　1，Sql Server会用到缓存文件吗?
　　答案是：会
　　2，Sql Server能否控制使用物理内存还是page file?
　　答案是：不能，一个windows上的应用，使用物理内存还是page file，是由操作系统决定的，应用程序本身无法决定自身去使用哪一部分内存

 

　　那么如何知道使用了多少缓存文件空间，通过sys.dm_os_sys_memory这个视图可以查询出来。
　　当然我这个截图是在我本机，看不出来有什么特别大的使用了，一个字段是total_page_file_kb，一个是available_page_file_kb
　　顾名思义，总的减去可用的，就是已用的

　　

　 　那么，文件缓存跟内存瓶颈有什么关系呢？
　　应用程序对文件缓存的使用时不受自身因素控制的，完全是由操作系统来决定的，Sql Sever也不例外，文件缓存使用的多少当然也是由操作系统来调度
　　文件缓存的使用多少能反映什么？
　　如果说文件缓存使用的越多，从侧面可以反映出来服务器上当前物理内存和实际需求内存之间的差距，当然这个差距越大，说明内存缺乏程度越高
　　文件缓存的使用是受到Windows操作系统调度的，这一点Sql Server无法决定自己的缓存数据是存放在屋里内存中或者是page file中，
　　这一点就是一个黑盒了，具体算法我无从得知
　　从实际测试来看，物理内存的消耗和page file的消耗是同步的，
　　举个例子，执行一个非常大的查询，通过 sys.dm_os_sys_memory可以非常清楚地观察到，
　　在消耗物理内存的同时，也伴随着虚拟内存的消耗，这两者到底怎么分配，或者这之间有什么线性关系，我目前还不清楚，也希望有高人指点
　　可以很明确地说，某些生产服务器，因为缺乏物理内存，32GB的物理内存的机器，
　　对于文件缓存的使用达到了一个非常高的程度（30多个GB），超过了物理内存本身
　　这应该就是一种非正常状态，不过这个值也没有一个权威的数据，也希望有了解的可以留言贡献
　　当然获取某些环境下有更大的文件缓存使用，我只是没见过而已。
　　根据page file的使用情况，发现如果大量使用page file，甚至超过了物理内存本身，
　　可以大概了解到Sql Server服务器实际所需内存与现有内存的差异程度。
　　也可以在进行内存瓶颈判断的时候，作为参考指标之一。

 

 总结

　　 　  林林总总阐述了上述几个内存瓶颈压力判断指标，也仅仅是涉及到了一部分跟内存有关的计数器，当然包括但不限于上述几个值。
　　　　如果做内存瓶颈判断，可以有更多的参考值，
　　　　前文也说了，内存压力下，
　　　　Sql Server是一个具备自我调节（self tuning）的应用系统，各个计数值的值是具有一系列的相关性的，往往多个性能计数器会表现出来一些一致性的特征
　　　　比如内存不足的情况下：PLE上达不到预期值，Page reads（writes） / sec 又持续保持在一个较高的水平，同时伴随着Lazy Writer / sec 持续性的发生
　　　　如果有更多的其它参考的判断指标，当然更具备说服力
　　　　但是如果通过上述值，也能将内存的压力是否存在瓶颈定位个八九不离十。
　　　　对于其他的内存相关的计数器，有时间会继续总结。
　　　　其实说了这么些相关内容，也仅仅是对Sql Server内存一个做了一个非常粗略的分析，当然也可以对各个部分的内存分类进行进一步细化的分析和论述。

 

本文粗浅第分析了判断Sql Server内存瓶颈的一些知识点，尚有不足的地方还请指出，谢谢。希望能够帮到各位对Sql Server感兴趣的看官，共同学习。

 【内存核心排查与dmv思路】

（1）内存相关DMV

-- SQL SERVER 2016
select * from sys.dm_os_memory_brokers  --代理
select * from sys.dm_os_memory_cache_clock_hands
select * from sys.dm_os_memory_cache_entries -- 缓存实际项目的实际体质
select * from sys.dm_os_memory_clerks  order by type,pages_kb desc
select * from sys.dm_os_memory_nodes   -- numa内存节点，一般64为dac管理员专用连接
select * from sys.dm_exec_query_memory_grants
select * from sys.dm_os_memory_pools
select * from sys.dm_os_nodes
select * from sys.dm_os_process_memory --查看操作系统内存

select * from sys.dm_resource_governor_resource_pools --【重要】资源池
select type,name,sum(pages_kb)/1024 as size_mb from sys.dm_os_memory_clerks group by type,name order by size_mb desc  -- 【重要】实际所有内存分配类别
select * from sys.dm_os_memory_cache_counters order by pages_kb desc  -- 【重要】缓存项目分配情况，查询计划、登录缓存等等计数器
select * from sys.dm_exec_cached_plans -- 【重要】执行计划缓存
select * from sys.dm_os_buffer_descriptors --【重要】查看各数据库占用的缓冲池,也可以具体到表
select * from sys.dm_os_memory_objects  -- 【重要】，实际的内存分配到表

（2）内存排查思路

排查思路：

Stolen Server Memory  高，你可以通过 sys.dm_os_memory_clerks 查看各个内存管理员管理的内存分配情况；除了 BUFFERPOOL 的，看看其他的内存占用是什么最多；

可以通过 sys.dm_os_memory_cache_counters 查看缓存分配情况；

可以通过 sys.dm_os_buffer_descriptors 查看各数据库占用的缓冲池；

可以通过 sys.dm_exec_cached_plans 查看计划缓存的分配情况。

确认打了最新补丁，其实  sys.dm_os_memory_clerks  看到的对象，不影响的话可以使用 DBCC FREESYSTEMCACHE('XXXXX') 清除指定内存管理员的内存分配。

（3）内存情况查询

--查看设置的最大最小每次  
exec sp_configure 'max server memory (MB)'  
exec sp_configure 'min server memory (MB)'  

--SqlServer目标内存、当前内存 、数据库内存页数  
SELECT object_name,counter_name,cntr_value,cntr_value/1024/1024 AS [cntr_value(GB)]  
FROM sys.dm_os_performance_counters  
WHERE counter_name in('Target Server Memory (KB)','Total Server Memory (KB)','Database pages')  

--系统物理内存使用情况  
SELECT * FROM sys.dm_os_sys_info  
SELECT * FROM sys.dm_os_sys_memory  
SELECT * FROM sys.dm_os_process_memory  

--查看当前是否有等待内存授予的进程  
Select a.*,b.text, c.query_plan   
from sys.dm_exec_query_memory_grants a  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) b   
CROSS APPLY sys.dm_exec_query_plan (plan_handle) c 
 
--SqlServer预留和提交内存情况  
DBCC MEMORYSTATUS; --旧的方法  
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 [Stolen in Buffer Pool]    
,SUM(multi_pages_kb) AS [MemToLeave]    
,SUM(single_pages_kb) + SUM(multi_pages_kb) AS [Stolen]    
,SUM(virtual_memory_committed_kb) + SUM(multi_pages_kb) AS [Buffer Pool]    
FROM sys.dm_os_memory_clerks   
GROUP BY [type]    
ORDER BY [type] desc  

--当前各数据库buffer pool的分配情况  
SELECT   
CASE database_id   
WHEN 32767 THEN 'ResourceDb'   
ELSE db_name(database_id) END AS Database_name  
,count(*) AS cached_pages_count  
,count(*)*8/1024 AS cached_space_in_mb  
,sum(convert(bigint,free_space_in_bytes))/1024/1024 AS free_space_in_mb  
FROM sys.dm_os_buffer_descriptors(nolock)  
GROUP BY db_name(database_id) ,database_id  
ORDER BY cached_pages_count DESC;  

--当前数据库各表buffer pool的分配情况  
SELECT top(20) name ,index_id ,count(*)AS cached_pages_count   
,count(*)*8/1024 AS cached_space_in_mb   
FROM sys.dm_os_buffer_descriptors AS bd   
INNER JOIN (  
SELECT object_name(object_id) AS name ,index_id ,allocation_unit_id  
FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p   
ON au.container_id = p.hobt_id  AND (au.type = 1 OR au.type = 3 )   
UNION ALL  
SELECT object_name(object_id) AS name,index_id, allocation_unit_id  
FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p   
ON au.container_id = p.hobt_id  AND au.type = 2  
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id  
WHERE database_id = db_id() AND obj.name NOT LIKE 'sys%'  
GROUP BY name, index_id   
ORDER BY cached_pages_count DESC  

-- 当前各表或索引在缓冲池中的页数量及大小  
SELECT obj.name ,obj.index_id,i.name ,count(*)AS cached_pages_count ,count(*)*8/1024 AS cached_space_in_mb  
FROM sys.dm_os_buffer_descriptors AS bd   
INNER JOIN (  
SELECT object_name(object_id) AS name,object_id,index_id ,allocation_unit_id  
FROM sys.allocation_units AS au  
INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)  
UNION ALL  
SELECT object_name(object_id) AS name,object_id,index_id, allocation_unit_id  
FROM sys.allocation_units AS au  
INNER JOIN sys.partitions AS p   ON au.container_id = p.partition_id AND au.type = 2  
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id  
LEFT JOIN sys.indexes i(nolock) on obj.object_id = i.object_id AND obj.index_id = i.index_id    
WHERE database_id = db_id() AND obj.name NOT LIKE 'sys%'  
GROUP BY obj.name ,obj.index_id,i.name  
ORDER BY cached_pages_count DESC;  

-- 统计当前内存分配情况  
select   
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,  
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,  
bpool_committed * 8 / 1024 as bpool_committed_mb,  
bpool_commit_target * 8 / 1024 as bpool_target_mb,  
bpool_visible * 8 / 1024 as bpool_visible_mb  
from sys.dm_os_sys_info  

--当前内存脏页数量及大小  
SELECT db_name(database_id) AS 'Database'  
,count(page_id) AS 'Dirty Pages'  
,count(page_id)*8/1024 AS 'Dirty Pages(MB)'  
FROM sys.dm_os_buffer_descriptors(nolock)  
WHERE is_modified =1  
GROUP BY db_name(database_id)  
ORDER BY 'Dirty Pages' DESC  

--缓存类型数量大小  
select cacheobjtype as [Cached Type]  
,COUNT(*) [Number of Plans]   
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]  
from sys.dm_exec_cached_plans   
group by cacheobjtype   
order by [Plan Cache Size(MB)] desc  

--缓存对象数量大小  
select objtype as [Cached Object Type]  
,COUNT(*) as [Number of Plans]   
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]  
,avg(usecounts) [Avg Use Count]   
from sys.dm_exec_cached_plans   
group by objtype   
order by [Plan Cache Size(MB)] desc  

--内存对象  
SELECT top 10 * FROM sys.dm_os_memory_cache_counters(nolock)  
ORDER BY entries_count DESC;  
SELECT * FROM sys.dm_os_memory_clerks a  
WHERE name='TokenAndPermUserStore'  
SELECT * FROM sys.dm_os_memory_cache_entries(nolock)  
WHERE [type]='USERSTORE_TOKENPERM' and name='TokenAndPermUserStore'  

--缓存分析对象  
SELECT * FROM sys.syscacheobjects   
SELECT * FROM sys.dm_exec_cached_plans  

--即时查询优化（运行即生效）  
EXEC sp_configure 'show advanced options',1  
RECONFIGURE  
EXEC sp_configure 'optimize for ad hoc workloads',1  
RECONFIGURE

 

 

【2】磁盘IO概述 

以下部分转自：https://www.cnblogs.com/ljhdo/tag/Performance%20Monitor/

SQL Server数据库系统的IO性能受到物理硬盘的IO延迟和SQL Server请求执行的IO操作的影响。在监控硬盘性能时，最主要的度量值（metric）是IO延迟，IO延迟是指从应用程序创建IO请求，到硬盘完成IO请求的时间延迟。如果物理硬盘不能及时完成IO请求，跟不上请求负载的速度，那么SQL Server就容易出现性能问题。SQL Server内部在执行一些特定的操作时，会和硬盘做读写交互，这也会影响物理硬盘响应SQL Server的IO请求的性能，使查询进程处于PageIOLatch或WriteLog等待。

【2.1】使用性能计数器（Performance counter）监控硬盘 IO

首先要了解操作系统的存储管理，硬盘在操作系统分为：物理硬盘（Physical Disk）和逻辑硬盘（Logical Disk）。Windows可以在一个Physical Disk上划出若干个逻辑分区，每一个逻辑分区是一个Logical Disk。对于分配在同一个Physical Disk上的Logical Disks，其读写操作共享Physical Disk的IO带宽。Windows给每一个Logical Disk分配一个盘符，App通过盘符来读写数据。

对应地，硬盘的性能有两组计数器：Logical Disk 和 Physical Disk，其中逻辑硬盘是物理硬盘的逻辑分区：

  • Logical Disk 计数器按照逻辑分区记录每个逻辑分区的读写IO信息，由于App通过盘符来读写数据，通过logical Disk 计数器可以了解不同App向不同盘符发出的读写请求有多少。
  • Physical Disk 计数器是按照物理磁盘，记录每一个物理磁盘的读写IO，如果该物理硬盘上有多个逻辑分区，那么把所有逻辑分区的IO加和到一起，统计物理硬盘的IO性能，监控硬盘的响应速度，能够真正了解硬件的实际IO量。推荐监控某一块Physical Disk，而不要粗暴地监控所有的物理硬盘，多数情况下，一块Physical Disk的IO很忙，而其他Physical Disk很空闲，原因是数据库文件分布的逻辑硬盘是同一块物理硬盘，导致单块物理硬盘需要处理的IO请求过多，为了避免这种情况的发生，需要把数据库文件均匀分布在不同的物理硬盘上。 

那么，Windows性能监控器都有哪些硬盘的性能计数器了？

1，Disk Queue Length

硬盘队列长度是等待被Physical Disk处理的IO请求的数目。如果一个App发出一条读请求，但是目标Disk正在处理其他IO Task，那么这个新的请求就会被放在Disk queue中，Disk queue Length就是1，硬盘的请求队列的长度，能够衡量硬盘的工作负载，队列长度越长，说明硬盘接收到的IO请求越多，完成一次IO请求需要的处理时间就越长，从一定程度上表明，硬盘性能不能满足业务的需求了。

  • Avg. Disk Queue Length
  • Avg.Disk Read Queue Length
  • Avg.Disk Wirte Queue Length
  • Current Disk Queue length

2，传输（Transfer）时间

Transfer是Disk 的一次完整的I/O动作，表示从寻道，读写数据，到传输完成。在统计时，Transfer 是 Read 和 Write的加和。

  • Avg. Disk sec/Transfer ： 磁盘每一次读写所用的平均时间。
  • Disk Transfers/sec ： 磁盘每秒处理的读写次数。
  • Avg.Disk Bytes/Transfer：Disk 每次IO传送的Bytes数

3，读写时间百分比 

硬盘的工作时间（elapsed time）是指硬盘用于执行read/write操作的时间

  • % Disk Time：硬盘的工作时间（ elapsed time）和处理IO请求的总时间的比值。
  • % Disk Read Time
  • % Disk Write Time
  • % Idle Time

4，IO拆分

一次IO拆分成多次IO来实现，IO拆分的原因是文件出现碎片，一次IO请求读取的非连续的数据段，那么硬盘子系统会把该请求分成多次执行，测量IO拆分的比例能够反映文件存储的分散程度。

还有一个原因会导致IO拆分，这就是一次读取的数据过大，导致无法通过一次IO请求返回，这就需要把IO请求拆分成多次。

Split IO/Sec reports the rate at which I/Os to the disk were split into multiple I/Os. A split I/O may result from requesting data of a size that is too large to fit into a single I/O or that the disk is fragmented.

【2.2】在系统级别监控物理Disk的IO性能

使用性能监控器来侦测IO性能，用于监控IO性能的计数器主要是物理硬盘的读写：

  • Physical Disk\Disk Reads/sec
  • Physical Disk\Disk Writes/sec

这两个计数器的性能指标：

  • <10 ms 没有性能问题
  • 10~20ms 存在问题
  • 20~50ms 性能较低
  • >50ms 存在严重的性能问题

1，监控物理Disk的IO延迟

在Windows级别上对Physical Disk的IO延迟进行分析，主要依赖于Performance Monitor的计数器，衡量物理Disk的IO延迟的计数器主要有三个：

  • Avg. Disk sec/Transfer：Disk每一次读写操作所用的平均时间
  • Avg. Disk sec/Read：Disk每一次读操作所用的平均时间 
  • Avg. Disk sec/Write：Disk每一次写操作所用的平均时间

avg.Disk sec/(Transfer,Read,Write)，能够很好的反映Disk的IO速度，推荐的衡量Disk的IO速度的基线（baseline）:

  • 很快：<10ms
  • 一般：10-20ms
  • 有点慢：20-50ms
  • 非常慢：>50ms

2，分析Data Collector收集的计数器数值

下图是产品环境中一台Server的计数器数值图表，将IO延迟的度量值按比例放大1000倍，这样图表显示的单位就是ms。

  • %Idle Time：在60%左右浮动，说明Disk不是很忙碌
  • Avg.Disk sec/Write：大多数情况下都是10ms以下，偶尔波动，说明Disk的写延迟比较低
  • Avg.Disk sec/Read：读延迟大多数情况下都是在40ms以上，鲜有低于40ms，偶尔达到峰值，说明Disk的读延迟非常高
  • Avg.Disk sec/Transfer：读写延迟的均值在30ms左右，时有波动，在%Idle Time曲线不波动时，Disk的读写延迟也有波动，说明Disk的读写延迟不稳定

初步判断，Disk的读写延迟非常高，Disk的IO性能较差，IO速度慢

3，监控物理Disk的IO次数

根据Disk的IO次数来界定Disk性能，没有统一的阈值，一般通过监控计数值来获取一个趋势，设置一个基线，如果在Disk比较忙碌时，遇到异常的谷值，那么就需要查看是否出现参数嗅探问题和Disk IO密集的查询，异常的谷值一般是由查询语句请求的数据量太多造成的，需要对查询语句进行性能调优。

系统级经常用到的Disk性能计数器是PhysicalDisk计数器：

  • Avg. Disk Queue Length ：提供Disk阻塞程度的主要度量值，表示在 sample interval期间，Disk等待处理的IO请求队列的平均长度，即等待被Disk处理的IO请求的数量
  • % Idle Time：Disk的空闲程度，可以反推出Disk的忙碌程度
  • Disk (Reads/Writes/Transfers)/sec：每秒Disk执行读写操作数量

队列长度波动很大，在%Idle Time 升高时，IO数量降低，没有发现明显的异常谷值。

4，监控物理Disk读写的数据量

这几个计数值，对监控物理Disk的读写性能，意义不大，仅仅作为参考。

  • Avg.Disk Bytes/(Read,Write,Transfer)表示：在物理Disk执行读写操作时，物理Disk从Disk读取到内存的字节数量，从内存写入到Disk的字节数量，以及两者的总字节数量
  • Disk Bytes/sec：在物理Disk执行读写操作时，数据从Disk传输到内存，或从内存写入到Disk的字节速度，好的Disk，其值在20-40MB之间，一般Disk，其值在20MB以下

【2.3】SQL Server内部操作对Disk IO性能的影响

SQL Server能够缓存从Disk加载的数据页，正常情况下，大部分操作不需要任何物理读操作，不需要Disk的物理IO参与就能完成，但是，有一些操作，必须和物理Disk进行IO操作，才能完成。SQL Server和物理Disk进行IO交互的操作：

  • 对于内存中没有缓存的数据，第一次访问时，需要将数据从数据文件读取到内存中，SQL Server访问的任何数据必须缓存到内存中，如果不在内存中，SQL Server发送读请求，将数据页从物理Disk读取到内存中，这个过程叫做物理读；如果数据存在于内存中，SQL Server直接访问，这个过程叫做逻辑读。
  • 在任何修改操作提交之前，预写事务日志记录到日志文件，在CheckPoint和LazyWriter运行时，数据被写入数据文件。
  • 执行CheckPoint时，将缓存中的脏页写入数据文件，脏页是指载入内存之后被修改过的数据页，内存中的数据和数据文件中的数据不一致，由CheckPoint触发的物理写操作和内压没有关系，和用户修改的数据量有关，用于控制还原的时间间隔。
  • 当Buffer Pool空间不足，Free Buffer List减少到临界值时，LazyWriter进程主动将内存中的一些很久没有访问过数据页面和执行计划清空，如果数据页面是脏页，那么将其写入到数据文件，LazyWriter和内存压力有关，由于内存可用的Free Buffer不足导致LazyWriter进程执行清理操作。
  • IO密集型操作，比如检查数据库的一致性（DBCC CheckDB），重建索引，更新统计信息，数据库备份等，会带来大量的Disk IO操作

SQL Server只会读取数据文件，只要数据缓存在内存中，理想情况下，SQL Server不会执行任何物理读操作，也不需要从物理Disk加载数据到内存，SQL Server执行读取操作性能和内存的缓存能力有直接关系，也和用户读取的数据量有关。

SQL Server的写操作分为写数据文件和写日志文件。写入日志文件的数据量，完全由数据修改量决定，和内存压力没有关系；写入数据文件的数量，主要和修改量有关。LazyWriter和内存压力有关系，一旦内存有压力，LazyWriter自动启动，负责清理最久未被访问的缓存，释放内存，增加可用的Free buffer数量。

因此，SQL Server请求的物理Disk的读操作数量和内存有直接关系，内存越充足，缓存的数据量越多，物理Disk的读操作的数量就会越少，逻辑读的数量不会减少；SQL Server请求的物理Disk的写操作数量和用户执行的数据修改量有直接关系，和内存是否存在压力关系很微小。在执行物理disk的读写请求时，SQL Server的查询进程产生PageIOLatch等待，表示进程正在执行物理读操作，该等待可以从DMV：sys.dm_exec_requests 查看到：

复制代码
select  r.session_id,
        r.blocking_session_id as blocking,
        r.wait_type as Current_Wait_Type,
        r.wait_resource, 
        r.last_wait_type,
        r.wait_time,
        r.status,
        r.command,
        r.cpu_time,r.reads,r.writes,r.logical_reads,
        r.total_elapsed_time,
        r.start_time,
        db_name(r.database_id) as database_name,
        SUBSTRING(  st.text, 
                    r.statement_start_offset/2+1,
                    (    CASE WHEN r.statement_end_offset = -1 
                                THEN LEN(CONVERT(NVARCHAR(MAX), st.text))
                            ELSE (r.statement_end_offset - r.statement_start_offset)/2
                        END 
                    )
                ) as IndividualQueryStatement
from sys.dm_exec_requests r
outer APPLY sys.dm_exec_sql_text(r.sql_handle) as st
where (r.wait_type<>'MISCELLANEOUS' or r.wait_type is null)
    and r.session_id>50
    and r.session_id<>@@spid
复制代码

PageIOLatch 等待：表示进程正在从物理Disk加载数据到内存，即进程在进行物理读操作，从Reads字段能够看到物理读的数量

WriteLog 等待：表示事务正在修改数据，SQL Server将预先将事务日志记录写入到事务日志文件

 

参考文档：

Memory - Lazy Writer and Checkpoint

SQL Server disk performance metrics – Part 1 – the most important disk performance metrics

Measuring Disk Latency with Windows Performance Monitor (Perfmon)

posted @ 2020-12-13 16:10  郭大侠1  阅读(820)  评论(0)  编辑  收藏  举报