前言微软工程师中的一位工程师曾经对性能调优有一个非常形象的比喻：剥洋葱。我也非常赞同，让我们一层层揭开它神秘的面纱。下面六大因素是我们为客户分析数据库性能问题时最常用的图表。看完这张图，你是不是对性能调优有了一个基本的概念？一般来说，我们会按照以下顺序进行分析：硬件能力、系统规模、数据库内部因素、软件环境，这四者的顺序可以调整或调换，但系统的性能优化必须从全局着手。不要深入到某个SQL语句的优化，因为你可能花很多时间把一个SQL从20s优化到1s，但是整个系统的慢还是存在的。最重要的是：业务模型和架构代码设计实战案例废话不多说，直接上手整篇，直奔干货。时间：2018年1月某天事件：下午4点，某医院客户突然出现大面积卡慢。整个系统出现了严重的问题，信息中心嗡嗡作响，医院的工程师们手足无措。幸运的是，我们为数据库安装了一个“摄像头”，让我们看看监控视频发送的内容。然后修复它。在出现问题的时间段内，硬件能力CPU的CPU使用率在20%以下，属于正常现象。内存如下图所示，内存占用正常。页面生命周期可用内存IOIO队列的平均值很低，在15.48附近出现瞬时高点。可以关注一下这段时间是否有批量写入。总的来说，硬件资源是足够的。当系统规模出现问题时，每秒批处理请求数不是上升趋势，而是下降趋势。这是因为系统拥塞、等待，影响了系统的吞吐量。数据库内部因素在等待慢语句。从会话和慢语句的趋势图可以看出，问题发生的时间与客户的描述完全吻合。我们可以得出结论，事故确实是数据库慢了。慢是什么原因查看这段时间运行的语句，可以发现下午15.58左右，数据库中开始出现越来越多的CMEMTHREAD等待。直到1900页的16.08分钟，同时出现***多达100个并发CMEMTHREAD等待。什么是CMEMTHREAD等待微软官方描述：当任务正在等待一个线程安全的内存对象时发生。当多个任务尝试从导致争用的同一内存对象分配内存时，等待时间可能会增加。这个描述很晦涩，感觉自己还是不知道waiting类型是什么，遇到这样的问题应该怎么处理。其实从官方的描述来看是内存争用的问题，但其实这个问题的关键在于多个任务的争用，其实是并发执行的问题。当编译或重新编译数据库时会发生这种情况，并且临时计划被插入到计划缓存中。NUMA架构下，内存对象是按照节点划分的。存在三种类型的内存对象（全局、PerNumaNode、PerNumaCPU）。SQLServer将允许对内存对象进行碎片化，以便只有同一节点或CPU上的线程具有相同的底层CMemObj，减少与其他节点或CPU的线程交互，从而提高性能和可扩展性。减少内存的并发争用。SELECTtype,pages_in_bytes,CASEWHEN(0x20=creation_options&0x20)THEN'GlobalPMO.CannotbepartitionedbyCPU/NUMANode.TF8048notapplicable.'WHEN(0x40=creation_options&0x40)THEN'PartitionedbyCPU.TF8048notapplicable.'WHEN(0x80=creation_options&0x80)THEN'PartitionedbyNode.UseTF8048tofurtherpartitionbyCPU'ELSE'UNKNOWN'ENDfromsys.dm_os_memory_objectsorderbypages_in_bytesdesc如果发现PartitionedbyNode的内存开销排在第一位，可以使用TRACEFLAG8048来减少CMEMTHREAD等待。从图中可以看出，客户的PartitionedbyNode比较低，排在第14位。3.补丁这种情况是最常见的。如果发现系统有大量的CMEMTHREAD等待，首先要考虑数据库是否打了最新的补丁。2008r2：FIX：SQLServer2008R2在大多数线程等待CMEMTHREAD等待类型时性能不佳如果线程使用表变量或临时表加载或更新行集2012，2014当您在SQLServer2012或SQLServer2014中执行许多特殊查询时CMEMTHREAD正在等待。当前软硬件环境中的数据库版本为11.0.5556.0加上上面提到的补丁，安装后的版本为：11.0.5623.0代码设计中的什么语句产生了wait。它们都类似于下面的语句。攻击时并发超过100。SELECT*INTO#TmpfromTBwhere1=2的特点如下：1.语句简单，开销小于5，不会产生并行。2.都采用selectinto#temptable的形式。上面分析过，CMEMTHREAD等待是并发问题，不是内存问题。当其他解决方案不起作用时，我们可以通过调整此类语句的写法来减少对CMEMTHREAD的等待。业务模型和架构现在的系统都是单机运行，这其实很少见。有少量的OLAP和OLTP混合服务。未来我们会为客户规划读写分离或者负载均衡方案。解决方案要安装最新的补丁，至少需要安装解决之前发布的等待问题的FIX。建议直接安装目前为止最新的2012SP4补丁。将参数optimizeforadhocworkloads从0修改为1。针对adhocplan插入plancache的场景，减少adhocquery占用的内存。增加TEMPDB数据文件select*into#temptable的个数会产生大量的latchcontention，防止CMEMTHREAD等待消除后产生大量的pagelatchlatchcontention。我经历过很多案例。解决了之前的拥塞后，后面又产生了新的等待，导致性能变差。请记住，优化是一个长期的、渐进的过程。迁移TEMPDB数据文件的位置目前有一些tempdb文件放在S盘，一般放在D盘。建议迁移到S盘（上面的存储），提高tempdb的响应速度。如果可能的话，使用SSD来最大化tempdb的性能会是一个不错的选择。优化程序的代码修改代码通常放在最前面，因为涉及的情况比较多。80%的情况下，前面的手段都可以解决问题。对于剩下的20%，我们需要检查程序中的逻辑，看看是什么业务产生了这些语句。什么条件会触发这类业务。对应下面类似的语句，使用存储过程或者参数化的方法来减少编译和重编译的次数。此外，此类语句将同时创建临时表。可以通过调整tempdb设置来加快此类语句的执行速度，同时减少此类语句的并发数。优化效果经过前面几次优化方法，从第二天开始，就没有再等待CMEMTHREAD了。等待慢语句总结通过这篇文档，你应该已经充分了解了数据库性能调优的思想。他告诉我们出现问题时如何一步步解决问题，像剥洋葱一样一层一层地剥。此类等待的原理及调试方法可参考微软官方博客：HowItWorks:CMemThreadandDebuggingThemSQLServer2016进一步优化了该问题。详情参考：SQL2016–ItJustRunsFaster:DynamicMemoryObject(CMemThread)Partitioning
