前言

微软工程师的一位工程师曾经对性能优化有一个非常生动的比喻：剥洋葱 。我也同意，让我们一层一层地打开它神秘的面纱。

六大因素

以下是我们分析数据库性能问题最常用的图片。

看完这张图，你对性能调优有基本概念吗？.一般来说，我们会按照以下顺序进行分析：

硬件能力

系统规模

数据库内部因素

软件环境

这四个顺序可以调整或交换，但必须优化系统的性能从全局出发。不要一来就深入到某个地方SQL语句的优化，因为可能你花费大量的

时间吧一个SQL从20s 优化到1s，但整个系统的卡慢仍然存在。

最后才是

业务模型和架构

代码设计

实战案例

不要胡说八道，整，直接上干货。

时间:2018年1月的某一天:

事件:某医院客户 下午4点 突然出现大面积卡慢。整个系统出现严重问题，信息中心电话爆炸，医院工程师不知所措。

幸运的是，我们在数据库中安装了摄像头。让我们从监控录像中看看发送了什么。然后解决它

硬件能力

CPU

在问题发生的时间段内CPU利用率低于20%是正常的。

Memory

根据下图，内存正常使用。

页生命周期

可用内存

IO

IO队列平均值很低，15.48 左右有一个瞬时高点，可以注意这段时间是否批量写入。

总的来看，硬件资源是足够的。

系统规模

问题发生时，每秒的批准请求不是上升趋势，而是下降。这是因为系统拥堵，等待 ，影响系统吞吐量。

数据库内部因素

等待

慢语句

从会话和慢语句的趋势图可以看出，问题发生的时间与客户描述完全一致，我们可以得出结论，事故在数据库中确实很慢。

慢是什么导致的？

检查员在一段时间内运行的句子可以在下午找到15.58左右数据库越来越多CMEMTHREAD等待。

一直到1900页16.08最多100个并发同时出现CMEMTHREAD等待

什么是CMEMTHREAD等待

微软的官方描述发生在等待线程安全的内存对象时。当多个任务试图从同一内存对象分配内存时，等待时间可能会增加。

这个描述很晦涩，还是不知道等待类型是怎么回事，应该怎么处理。

事实上，从官方描述来看，这是一个内存争议的问题，但事实上，这个问题的关键在于争论多个任务实际上是并发执行的问题。

场景

1. 即席执行计划出现在数据库编译或重编时ad hoc plans 插入计划缓存时
2. NUMA架构下，内存对象是按照节点来分区的

内存对象有三种类型（Global，Per Numa Node，Per CPU）。SQL Server只有同一节点或cpu上线程底层相同CMemObj，从其他节点或其他节点减少cpu线程交互可以提高性能和可伸缩性。减少内存并发争议

SELECT type,pages_in_bytes,CASE WHEN (0x20 = creation_options & 0x20) THEN 'Global PMO. Cannot be partitioned by CPU/NUMA Node. TF 8048 not applicable.' WHEN (0x40 = creation_options & 0x40) THEN 'Partitioned by CPU.TF 8048 not applicable.' WHEN (0x80 = creation_options & 0x80) THEN 'Partitioned by Node. Use TF 8048 to further partition by CPU' ELSE 'UNKNOWN' END from sys.dm_os_memory_objects order by pages_in_bytes desc

假如你发现了，Partitioned by Node 内存费用排在前面，可以使用TRACE FLAG 8048来减少CMEMTHREAD等待.

从图中可以看出，客户 Partitioned by Node 相对落后，排名14位。

            3. 补丁

这种场景是最常见的。如果在系统中发现大量的场景，CMEMTHREAD等待，优先考虑数据库是否安装了最新的补丁

2008 r2: FIX: SQL Server 2008 R2 performs poorly when most threads wait for the CMEMTHREAD wait type if the threads use table variables or temp tables to load or update rowsets

2012 ，2014 当您执行许多特殊查询时SQL Server 2012年或 SQL Server 2014 CMEMTHREAD 等待^[1]

软硬件环境

目前数据库的版本是 11.0.5556.0 上述补丁安装后的版本为：11.0.5623.0

代码设计

是什么句子产生了等待

它们与下面的句子相似。最高时，并发超过100.

SELECT * INTO #Tmp from TB where 1=2

特点如下：

1.简单的句子 开销小于5，不会平行产生

2.都采用了select into #temptable的形式

就像上面的分析一样，CMEMTHREAD等待是一个并发问题，而不是内存问题。当其他方案不起作用时，我们可以通过调整这些句子的写作方法来减少CMEMTHREAD等待.

业务模型和架构

目前，该系统处于单机运行状态，实际上非常罕见。OLAP 和OLTP业务混合。今后，我们将为客户规划 读写分离 或负载平衡的解决方案。

解决方案

安装最新补丁

至少需要解决安装前发送的等待问题FIX。建议直接安装到目前为止最新的2012 SP4补丁。

修改参数

optimize for ad hoc workloads 从0修改为1 。针对即席执行计划ad hoc plans 插入计划缓存时 场景减少ad hoc 查询占用的内存。

增加TEMPDB数据文件的数量

select * into #temptable 会产生大量的锁纠纷，以防止CMEMTHREAD 等待消除后，大量出现pagelatch 闩锁争用。我经历过很多案例，解决了前面的一个拥堵之后，

后面有新的等待，导致性能更差。请记住，优化是一个长期的、循序渐进的过程。

迁移TEMPDB数据文件的位置

目前部分tempdb文件放在S，一般分放在D盘。所有建议都迁移到S盘(以上存储)增加tempdb响应速度。如有可能，使用SSD来最大化tempdb性能，将是一个不错的选择。

优化程序代码

修改代码通常放在最后，因为涉及的情况很多。在前80%的手段下，问题可以解决。剩下的20%，我们需要检查程序中的逻辑，看看这些陈述是什么业务产生的。什么条件会引发这类业务？.以下类似的句子使用存储过程或参数化，以减少编译和重编的次数。此外，通过调整，这些句子将并发创建临时表tempdb设置，加快此类句子的执行速度，同时减少此类句子的并发数量。

优化效果

经过前面的优化手段，第二天就没有再出现了CMEMTHREAD的等待。

等待

慢语句

总结

通过这篇文件，你应该已经完全学会了数据库性能调整的想法。他告诉我们如何一步一步地调查问题，就像剥洋葱一样。

参考

如何调试微软官方博客等待的原则以及如何调试How It Works: CMemThread and Debugging Them?^[2]

参考资料

当您执行许多特殊查询时， SQL Server 2012年或 SQL Server 2014 CMEMTHREAD 等待:?https://support.microsoft.com/zh-cn/help/3074425/fix-cmemthread-waits-occur-when-you-execute-many-ad-hoc-queries-in-sql

How It Works: CMemThread and Debugging Them:?https://blogs.msdn.microsoft.com/psssql/2012/12/20/how-it-works-cmemthread-and-debugging-them/