<b>摘要：</b>在阿里云sql server最佳实践系列在线直播中，阿里云数据库专家汪建明总结了7大问题并结合案例为大家分享了阿里云sql server高cpu使用率问题排查的实践经验。
<b>以下内容均根据演讲视频以及ppt整理而成。</b>
<b>演讲者简介</b>
<b>汪建明（花名：风移）</b>，近10年sql server数据库dba经验。曾就职于新蛋中国6年、新蛋美国3年半。现任阿里云数据库专家，负责sql server产品线。
<b>分享议程</b>
本文将按照sql server高cpu使用率问题排查的7个方面进行分享：
缺失索引 (missing indexes)
索引碎片 (indexes fragmentation)
数据类型转换 (data conversion)
非sarg查询 (non-sarg query)
参数嗅探 (parameter sniffing)
统计信息 (statistics)
top cpu查询 (top sql)
<b>一、缺失索引 (missing indexes)</b>
<b>为什么索引缺失会降低sql server的cpu使用率？</b>
真正排查出的高cpu使用率的第一大因素就是missing indexes，那么为什么索引的缺失是sql server cpu使用率的第一大杀手呢？要回答这个问题就需要首先回答什么是索引。索引的结构其实是基表的某一列或者某几列数据的投影，并且这些列的数据是按照升序或者降序排列完毕之后的特殊结构，这个特殊结构使得查询的性能会更加高效，特别是对于经常会使用到的查询语句。既然索引特殊的结构已经排序完成了，那么在进行检索的时候效率就会非常高，可以很快地定位到数据所在的位置，这样就能够大大降低sql server本身的io的消耗，io的消耗降低之后cpu的使用率自然也会下降。
<b>发现缺失索引的方法</b>
如何发现哪些表中又缺失了哪些索引呢？第一种方法就是dta (database tuning advisor)。第二种方法就是执行计划中存在索引缺失的警告，也就是当执行某一条语句的时候，执行计划会报出一个警告提示这里缺少一个索引，这个时候就可以将缺失的索引找出来并创建它。第三种方法就是访问系统的动态视图，大致有sys.dm_db_missing_index_group_stats、sys.dm_db_missing_index_group_stats以及sys.dm_db_missing_index_details这三个视图，具体怎样去使用大家可以查阅帮助文档。
<b>不要盲目地创建缺失的索引</b>
在创建缺失索引时一定不要盲目，一定要确保创建的缺失索引是有效的，这样做的第一个原因是创建索引会导致一定的存储开销，因为索引的数据结构也会占用数据文件空间。第二个原因是dml操作会导致索引的维护成本增加，因为索引的结构是基于表的某列或者某几列组合出来的数据结构，这个数据结构的一致性一定是随着基表的数据变化而变化的，当我们进行delete、insert以及update操作的时候也需要去维护索引的数据结构，因为需要保证索引结构数据与基表数据的一致性，所以就会带来索引维护成本的上升。
<b>二、索引碎片 (indexes fragmentation)</b>
刚才提到了索引缺失会导致cpu使用率的升高，而另外一个问题是：是不是索引创建以后cpu的使用率就一定会降低呢？或者是说在索引不缺失的情况下，cpu的使用率就一定不会上升呢？这两个问题的答案都是否定的。这里涉及的话题就是索引碎片，这里的索引碎片可以理解为索引数据页中的一些空隙，这应该如何理解呢？假如某一个页里面是满的，比如是8k，如果存在25%的空隙，那么真正有效的数据只有75%，举个简单的例子比如某个表格的索引数据有100个页，但是碎片率是25%，所以这100个换页面里面只有75个页面的数据是有效的。所以在索引的碎片率非常高的情况下，索引的效率就会非常低，因为其io的使用率也会非常低。
<b>rebuild indexes</b>
解决索引碎片的方法其实很简单，也就是进行一个rebuild indexes的操作，做完这个操作之后统计信息会被更新，相应的执行计划中的缓存信息也会被清空，当相同的语句再过来的时候，sql server就会重新进行执行计划的评估和选择，并获得更好的执行计划。
<b>注意事项</b>
rebuild indexes操作的方式能够很容易地解决索引碎片问题，但是还是存在三个地方需要大家注意。因为做rebuild indexes操作的时候会导致数据日志文件的增长，那么基于sql server日志文件的技术比如database mirroring、log shipping以及alwayson等，这些基于日志的技术都会导致进程变得很慢，因为日志文件会在短时间内出现暴涨的情况，所以这里需要提醒大家注意这个问题，在后面也会分享如何解决这些问题。
<b>如何去做rebuild indexes</b>
我们所需要基于的原则是一定在100%的需要时才去做rebuild indexes，那些使用率比较低的，哪怕是碎片率很高的表也不会太过于关注，比如一些很小的表或者是heap的表，对于很小的表而言，sql server在做执行计划的时候发现表格很小则会走table scan而不是index seek或者index scan的操作。第二个原则是在rebuild indexes的时候一定要去对每一个索引级别进行索引碎片率的检查，而不要盲目对整个表级rebuild index。第三个原则是当发现索引的碎片率处于不同的级别的时候选择的处理方法也是不一样的，如果碎片率在10%以下，那么就不需要去做rebuild indexes操作，如果索引碎片率在10%到30%之间，应该选择做reorganize操作，当索引碎片率大于30%，可以做rebuild indexes操作。这里还请主要，使用sql server的版本，如果是企业版本，请选择online＝on选项，以较小rebuild index对应用程序对影响。
还有一点需要提醒大家的就是在做rebuild indexes操作的时候一定要选择在业务的低峰期，因为rebuild indexes是一个io密集型的操作，所以会非常消耗io。除此之外，当存在database mirror或者log shipping以及alwayson的时候，如何做rebuild indexes才能够使影响最小呢？这里使用的技术是table partition，可以在大表上面建立table partition，然后逐个partition去做rebuild indexes，因为每个partition都会对于数据进行切分，切分之后数据量就会变得更小，这样产生的影响也会变得更小。
<b>三、数据类型隐式转换 (data conversion implicitly)</b>
很多同学不了解数据类型的转换，特别是数据类型的隐式转换。在这里和大家简单分享一下。
<b>什么是数据类型隐式转换</b>
sql server在做数据类型比较的时候一定要确保比较运算符两端的数据类型是一致的，比如等于、on子句、或者大于等于以及小于等于，一定要确保比较运算符两端的数据类型是一致的，这样才能进行比较。如果数据类型不一致的话，sql server会自动地进行数据类型的隐式转换，这个隐式转换对于用户而言是比较隐蔽的，用户可能是毫无感知的，但是数据库系统却在背后做了大量的隐式转换工作，而且这些工作都是比较会消耗系统性能的，从而导致了高cpu的使用率。
<b>数据类型转换原则</b>
sql server数据类型转换的规则其实很简单，就是将数据类型从低优先级转向高优先级，比如char和int数据类型，int的数据类型的优先级要比char高的，那么在做这两者数据类型的比较的时候就需要转换char数据类型到int数据类型上面来，然后再进行比较。
这里需要提醒大家的是一旦sql server对数据类型进行了隐式转换，并且隐式转换发生在正式表的基表上面的时候，是无法进入index seek的，而是会使用性能非常差的index scan，这样就会使得sql server的io使用率大大升高，io使用率的增加导致cpu的使用率升高，这就是隐式数据转换导致高cpu使用率的理论基础。
<b>那么如何避免数据类型的隐式转换呢？</b>
第一个方法就是review表的数据类型设计，因为在反范式理论中有一个方法是在同一个字段表达同一个含义为了避免多表join的时候采用反范式的设计，在多个表中存储相同含有的字段，在这种情况下一定要保证这些字段的数据类型是一致的，在后面进行查询或者是执行on子句进行join的时候，sql server就不需要在后台进行数据类型的隐式转换工作了。
第二个方法就是当where语句里面使用了像“where column = 常量”这种传入参数的时候，一定要确保传入的参数的数据类型和基表中这个字段的数据类型是一致的，这样才不会导致数据类型的隐式转换。其实经常会遇到的问题就是用户传入的参数的数据类型比基表字段的数据类型的优先级更高，这时sql server就需要在后台自动转换基表字段的数据类型，如果基表有一亿条数据，那么sql server就需要对这一亿条数据的列数据类型进行转换并进行比较，这样对于io的消耗会非常大，进而会导致cpu使用率的上升。
第三个方法就是去检查执行计划，在执行计划中可以通过一个convert_implict关键字知道是否做了xml隐式数据类型转换。第四个方法是搜索执行计划的缓存，可以拿到缓存的xml文件，在xml文件中会有隐式数据类型转换的关键字。
<b>四、非sarg查询 (non-sarg query)</b>
<b>原因</b>
non sarg是什么呢？其实是由于即使基表的某些列上建立了索引，sql sever的查询优化器也必须要去扫描所有的行，这样就会导致了non sarg查询。
<b>通常情况</b>
上面讲的可能比较理论，通常情况下，是在where字句中，在数据库基表的字段上使用函数，比如像convert、cast、以及数据类型隐式转换等，对于时间进行操作的函数，比如取时间差datediff、对时间进行加减的dateadd以及取年的year，以及upper、lower大小写转化的函数，对字符串进行操作的rtrim、substring、left以及像like的完全模糊匹配、isnull函数以及用户自定义函数等。
<b>五、参数嗅探 (parameter sniffing)</b>
<b>sql server中的参数嗅探是什么？</b>
参数嗅探其实是一个非常有意思的话题，那什么是参数嗅探呢？归根结底，导致参数嗅探的原因是由于sql server对执行计划的编译和缓存的过程导致的。想要理解这句话就明白sql server是如何执行一条查询语句的，当sql server的服务端接收到一条sql语句之后，首先要进行语法检查，之后进行语义分析，再之后进行编译，选择最优的执行计划路径，并将所得到的结果缓存到执行计划缓存中。
<b>参数嗅探的问题所在</b>
而问题就出在编译的过程中该如何编译这些查询语句上，因为某些查询语句是有参数的，当编译的时候一定是根据当时传入的参数的值编译一个最好的执行计划，但是当随着时间的推移，数据发生了变更就可能导致统计信息发生变化甚至可能发生数据倾斜的情况，如果发生了这样的情况，那么之前缓存的执行计划就可能不是最优的了，因为之前传入的参数可能是另外一个值，对应的统计信息可能就不是最优的解法了，这就是导致参数嗅探的一个原因。
<b>参数嗅探的解决方案</b>
刚才谈到了导致参数嗅探的原因是执行计划的编译和缓存过程，那么如何解决这个问题呢？很简单的一个思路就是：既然之前缓存的执行计划不是最优的，那么就清空这个缓存。这里为大家提供几种方法，但是其中也有不太推荐的方法。
1.第一种就是重启整个操作系统，因为操作系统重启了，内存当然就清空了，那么执行计划的缓存也会被清空，这时候sql server启动起来之后，查询语句提交到sql server服务端，当然会重新编译、使用最新的执行计划，这样可以解决参数嗅探的问题，但是问题在于这样的做法动作太大了，有点像使用大炮打蚊子，所以这样做思路是对的，但是方法却不恰当，所以这一种是不太推荐大家使用的。
2.第二种方法就是重启sql server服务，其实这样也可以解决问题，这个方法会导致sql server短暂停机和不可服务。这样的做法比第一种稍微好一点，但是也不是推荐的方法。
3.第三种方法是使用dbcc freeproccache命令来清空执行计划的缓存，这种方法比第二种方法又稍微好一点，但是这样还是会清空所有执行计划的缓存，但仍旧不是最好的方法，这样有点像是“宁可错杀一千，也不放过一个”的思维模式，因为真正出现问题就是某一个或者某几个执行计划的缓存，如果把所有的执行计划都清空了是可以解决这样的问题，但是也会产生“错杀其他的999人”的问题，所以这也不是最好的解决方法。
4.最好的解决方法就是针对于特定的查询语句或者存储过程去清空特定的执行计划缓存。
5.另外一种是使用query hits option，这将会告诉sql server在执行存储过程或者查询语句时每次都会进行重新编译，而不进行缓存，这也是一种思路。
6.还有一种就是更新统计信息，这个方法的原因是执行计划的编译和最优路径的选择基础数据就是统计信息，那么将统计信息更新之后相应的查询语句的执行计划缓存会被清空，下一次执行的时候会重新进行编译通过最新的统计信息获取最新的执行计划。
7.最后一个方法就是刚才提到的创建缺失索引或者删除不必要的、多余的以及重复的索引。
<b>六、统计信息 (statistics)</b>
<b>什么是sql server中的统计信息</b>
统计信息也是非常重要的，但是在很多时候统计信息往往会被忽略，因为对于用户而言，统计信息其实是躲在幕后的英雄。那么到底什么是统计信息呢？
统计信息实际上为sql server的优化器提供了数据基础，怎么理解呢？其实就是sql server在做最优路径选择的时候是基于统计信息的值进行预估的，也就是sql server是基于统计信息值的分布来选择执行计划的最优路径的。
<b>如何创建统计信息呢？</b>
创建统计信息的一种方法是手动创建，另外一种就是系统自动创建，数据库中有一个选项可以让数据库自动创建，第三种就是在创建索引时系统自动创建。
<b>如何更新统计信息呢？</b>
更新统计信息的第一种方法是使用update statistics，第二种是使用系统的sys.sp_updatestats存储过程，第三种方法可以通过stats_date拿到统计信息最后一次的更新时间。
<b>七、top cpu查询 (top sql)</b>
最后一部分就是top sql，之前讲解了rds sql server高cpu使用率的理论基础和demo，后面的问题就来了：如何知道哪些语句的cpu使用率分别是多少，并且需要按照cpu使用率倒序排列，也就是我们说的top sql。需要按照cpu使用率的倒叙来排列，可以方便地找出比如像排名前十的最消耗cpu的查询语句是什么。那么如何去实现这样的排序呢，其实就是使用上图中的代码。
当然这里涉及了两个比较小的问题，第一个就是如何找出某一个查询语句总的cpu使用率的倒叙排名，也就是比如某个查询语句执行了1000次，这1000次总的cpu消耗是一个维度；这个查询语句每一次查询的时候的总的top sql是第二个维度。将这个业务铺开来看，io的read和write也有相同的逻辑，也就是top io read、top io write以及top duration这些都可以通过相应的方法排列出来找到相应的top sql。
从整个过程来看，上面的分享已经基本上覆盖了sql server高cpu使用率的所有的场景。
<b>q&a</b>
<b>1、zyowe：那个语句是自动生成索引缺失创建语句？</b>
<b>2、dmv 里面 xml 检测是否存在转换？</b>
打开实际的执行计划 ＝> 执行查询后 => 打开执行计划xml ＝> 以下两处会发现数据类型隐式转化。
第一：
第二：
查看执行计划的方法：
第一步：打开实际的执行计划
第二步：执行查询语句
第三步：打开执行计划xml：右键点击执行计划选择：show execution plan xml
第四步：从打开的xml中，查找关键字convert
以上是手动查看的方法，你完全可以写一个xml的解析方法，获取执行计划缓存中的xml，然后查找关键字。
<b>3、david_ho：存储过程执行时间很长，有100分钟，但cpu不算高，怎么破？</b>
<b>by风移：</b>这个问题不是一个高cpu使用率的问题，是一个执行时间过长的问题。这个应该也要非常小心并加以重视，我之前遇到类似的case是，开发人员写了一个死循环在存储过程里面，导致执行时间很长，一致无法退出。解决方法：
方法一：使用profiler trace，这几个事件应该就可以了
这里需要注意的是，记得设置filter：
第二种方法：使用下面的查询，不断执行，看看存储过程执行到哪个语句慢，针对性的调优
<b>4、动态公式计算有什么解决思路?就是一个表达式是动态组合的，要计算‘5*(2-1)’=5字符串计算成结果，不是计算列。</b>
<b>by风移：</b>实在不好意思，还是没太明白这个“动态公式计算”的含义，已经邮件到您163的邮箱，后续我们再沟通。
<b>5、数据库频繁的delete insert 造成死锁， 怎么破？</b>
<b>6、npf：目前guid作主键最大表4千万条，过亿后是否会有性能问题，有没有解？</b>
<b>by风移：</b>这个问题问的非常好。根据我的经验，个人极不推荐使用guid做为主键，理由如下：
第一：guid字段宽度过长，char(36)，导致主键宽带非常大；因此，间接也会导致其他非主键索引空间变大（因为非主键索引要记录主键的值）。数据空间过大，查询使用的io自然就越大，cpu使用率就越高，效率相对就越低。
第二：guid值，每次的值大小是不可预知的。当有新的数据进入表中，如果guid主键是clustered的话，会导致数据插入到某两行数据的中间（比如，之前两行数据主键是a和c，当b数据进来，会导致插入到a和c之间），这样会导致数据移动。
第三：guid值做为主键，极易导致主键的索引碎片，你自己可以按照我们之前分享的方法，查查索引碎片就知道了。
<b>建议的做法：</b>
使用identity属性列，替换guid列做为主键。identity属性列的值是可以预知的，而且下一个主键值，一定是大于前面的主键值，sqlserver的数据行依次往后写就好了，不存在往中间插入的情况。好处是：1.int数据类型，宽度大大减少，以此降低了io开销和cpu开销；2.没有数据移动的开销；3.还可以大大减少索引碎片的概率
<b>7、更新统计信息会影响查询吗？</b>
<b>by风移：</b>会，两个方面，一个好的，一个坏的：
第一：更新的过程中，可能会导致短时间的查询阻塞，不过你可以选择业务低峰期来做
第二：更新完毕后，会带来执行计划评估相对更准确，因此查询效率更高效。
<b>8、怎么优化 like ‘%a%’，没有太明白刚刚说的优化完全模糊匹配</b>
<b>by风移：</b>
方法一：使用fulltext解决like完全模糊匹配的性能问题，参见我的云栖帖子【sql server fulltext解决like字句性能问题】，链接：https://yq.aliyun.com/articles/64764?spm=5176.8091938.0.0.xuzoxl
方法二:从业务层面来优化，比如直播中我们谈论的基于电话号码查询功能的case。
<b>9、tony_yang:cpu过高 其他状态都低是什么问题</b>
<b>by风移：</b>可能遇到cpu瓶颈了？给您思路：参照我们高cpu使用率的排解方法，先优化数据库性能，如果已经将数据库优化了，还是cpu过高，其他状态都低的话，估计cpu有瓶颈了。
<b>10、老师，同个语句在不同时间段执行效率差别很大，是因为数据库性能下降吗？</b>
<b>by风移：</b>如果是相同查询语句，相同数据结果的话1. 可能是数据库有blocking，或者是有其他大io，高cpu消耗的查询语句，影响到你的查询；2.可能是参数嗅探讨论的情况。
如果相同查询，不同数据结果集：这个首先应该怀疑的是数据库倾斜，不同条件值，数据量大小不同，查询性能不相同，是正常的。需要读取的数据量大，效率低，反之者高。
<b>11、为什么更新完统计信息后，数据库还是生成了错误的执行计划，清空计划指南也不行？</b>
<b>by风移：</b>需要清空执行计划缓存。统计信息的更新需要做到相应查询的所有表（有时候是视图，需要找到视图中相应的表）。另一个思路可能需要调优索引设计，missing index？duplicate index？index fragmentation？
<b>12、profiler的自动优化推荐是否可靠？</b>
<b>by风移：</b>不可靠，可以作为参考，需要人为判断。
<b>13、all1019：大表的索引重建什么时候做比较好？</b>
<b>by风移：</b>思路：大表拆小，比如我们说的partition，然后每个partition逐个重建index。时间的选择，肯定是业务低峰期，注意使用企业版的online=on选项，进一步减少对业务的影响。时机选择：< 10% do nothing; 10%~ 30% reorganize; >30% rebuild