摘要：在阿里云sql server最佳实践系列在线直播中，阿里云数据库专家汪建明总结了7大问题并结合案例为大家分享了阿里云sql server高cpu使用率问题排查的实践经验。
本期直播回顾链接：http://yq.aliyun/webinar/play/237 更多系列精彩课程直播，尽在 云数据库经典案例和最佳实践专场，等待你的参与！
以下内容均根据演讲视频以及ppt整理而成。
演讲者简介
汪建明（花名：风移），近10年sql server数据库dba经验。曾就职于新蛋中国6年、新蛋美国3年半。现任阿里云数据库专家，负责sql server产品线。
分享议程
本文将按照sql server高cpu使用率问题排查的7个方面进行分享：
缺失索引 (missing indexes)索引碎片 (indexes fragmentation)数据类型转换 (data conversion)非sarg查询 (non-sarg query)参数嗅探 (parameter sniffing)统计信息 (statistics)top cpu查询 (top sql)
一、缺失索引 (missing indexes)
为什么索引缺失会降低sql server的cpu使用率？
真正排查出的高cpu使用率的第一大因素就是missing indexes，那么为什么索引的缺失是sql server cpu使用率的第一大杀手呢？要回答这个问题就需要首先回答什么是索引。索引的结构其实是基表的某一列或者某几列数据的投影，并且这些列的数据是按照升序或者降序排列完毕之后的特殊结构，这个特殊结构使得查询的性能会更加高效，特别是对于经常会使用到的查询语句。既然索引特殊的结构已经排序完成了，那么在进行检索的时候效率就会非常高，可以很快地定位到数据所在的位置，这样就能够大大降低sql server本身的io的消耗，io的消耗降低之后cpu的使用率自然也会下降。
发现缺失索引的方法
如何发现哪些表中又缺失了哪些索引呢？第一种方法就是dta (database tuning advisor)。第二种方法就是执行计划中存在索引缺失的警告，也就是当执行某一条语句的时候，执行计划会报出一个警告提示这里缺少一个索引，这个时候就可以将缺失的索引找出来并创建它。第三种方法就是访问系统的动态视图，大致有sys.dm_db_missing_index_group_stats、sys.dm_db_missing_index_group_stats以及sys.dm_db_missing_index_details这三个视图，具体怎样去使用大家可以查阅帮助文档。
不要盲目地创建缺失的索引
在创建缺失索引时一定不要盲目，一定要确保创建的缺失索引是有效的，这样做的第一个原因是创建索引会导致一定的存储开销，因为索引的数据结构也会占用数据文件空间。第二个原因是dml操作会导致索引的维护成本增加，因为索引的结构是基于表的某列或者某几列组合出来的数据结构，这个数据结构的一致性一定是随着基表的数据变化而变化的，当我们进行delete、insert以及update操作的时候也需要去维护索引的数据结构，因为需要保证索引结构数据与基表数据的一致性，所以就会带来索引维护成本的上升。
这部分的相关demo请参考云栖社区的博客：https://yq.aliyun/articles/72265
二、索引碎片 (indexes fragmentation)
刚才提到了索引缺失会导致cpu使用率的升高，而另外一个问题是：是不是索引创建以后cpu的使用率就一定会降低呢？或者是说在索引不缺失的情况下，cpu的使用率就一定不会上升呢？这两个问题的答案都是否定的。这里涉及的话题就是索引碎片，这里的索引碎片可以理解为索引数据页中的一些空隙，这应该如何理解呢？假如某一个页里面是满的，比如是8k，如果存在25%的空隙，那么真正有效的数据只有75%，举个简单的例子比如某个表格的索引数据有100个页，但是碎片率是25%，所以这100个换页面里面只有75个页面的数据是有效的。所以在索引的碎片率非常高的情况下，索引的效率就会非常低，因为其io的使用率也会非常低。
rebuild indexes
解决索引碎片的方法其实很简单，也就是进行一个rebuild indexes的操作，做完这个操作之后统计信息会被更新，相应的执行计划中的缓存信息也会被清空，当相同的语句再过来的时候，sql server就会重新进行执行计划的评估和选择，并获得更好的执行计划。
注意事项
rebuild indexes操作的方式能够很容易地解决索引碎片问题，但是还是存在三个地方需要大家注意。因为做rebuild indexes操作的时候会导致数据日志文件的增长，那么基于sql server日志文件的技术比如database mirroring、log shipping以及alwayson等，这些基于日志的技术都会导致进程变得很慢，因为日志文件会在短时间内出现暴涨的情况，所以这里需要提醒大家注意这个问题，在后面也会分享如何解决这些问题。
如何去做rebuild indexes
我们所需要基于的原则是一定在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都会对于数据进行切分，切分之后数据量就会变得更小，这样产生的影响也会变得更小。
这部分的相关demo请参考云栖社区的博客：https://yq.aliyun/articles/72348
三、数据类型隐式转换 (data conversion implicitly)
很多同学不了解数据类型的转换，特别是数据类型的隐式转换。在这里和大家简单分享一下。
什么是数据类型隐式转换
sql server在做数据类型比较的时候一定要确保比较运算符两端的数据类型是一致的，比如等于、on子句、或者大于等于以及小于等于，一定要确保比较运算符两端的数据类型是一致的，这样才能进行比较。如果数据类型不一致的话，sql server会自动地进行数据类型的隐式转换，这个隐式转换对于用户而言是比较隐蔽的，用户可能是毫无感知的，但是数据库系统却在背后做了大量的隐式转换工作，而且这些工作都是比较会消耗系统性能的，从而导致了高cpu的使用率。
数据类型转换原则
sql server数据类型转换的规则其实很简单，就是将数据类型从低优先级转向高优先级，比如char和int数据类型，int的数据类型的优先级要比char高的，那么在做这两者数据类型的比较的时候就需要转换char数据类型到int数据类型上面来，然后再进行比较。
这里需要提醒大家的是一旦sql server对数据类型进行了隐式转换，并且隐式转换发生在正式表的基表上面的时候，是无法进入index seek的，而是会使用性能非常差的index scan，这样就会使得sql server的io使用率大大升高，io使用率的增加导致cpu的使用率升高，这就是隐式数据转换导致高cpu使用率的理论基础。
那么如何避免数据类型的隐式转换呢？
第一个方法就是review表的数据类型设计，因为在反范式理论中有一个方法是在同一个字段表达同一个含义为了避免多表join的时候采用反范式的设计，在多个表中存储相同含有的字段，在这种情况下一定要保证这些字段的数据类型是一致的，在后面进行查询或者是执行on子句进行join的时候，sql server就不需要在后台进行数据类型的隐式转换工作了。
第二个方法就是当where语句里面使用了像“where column = 常量”这种传入参数的时候，一定要确保传入的参数的数据类型和基表中这个字段的数据类型是一致的，这样才不会导致数据类型的隐式转换。其实经常会遇到的问题就是用户传入的参数的数据类型比基表字段的数据类型的优先级更高，这时sql server就需要在后台自动转换基表字段的数据类型，如果基表有一亿条数据，那么sql server就需要对这一亿条数据的列数据类型进行转换并进行比较，这样对于io的消耗会非常大，进而会导致cpu使用率的上升。
第三个方法就是去检查执行计划，在执行计划中可以通过一个convert_implict关键字知道是否做了xml隐式数据类型转换。第四个方法是搜索执行计划的缓存，可以拿到缓存的xml文件，在xml文件中会有隐式数据类型转换的关键字。
以上就是由于隐式数据类型转换导致高cpu使用率的场景，这样的场景也是非常多见的，这部分的相关demo请参考云栖社区的博客：https://yq.aliyun/articles/72420
四、非sarg查询 (non-sarg query)
原因
non sarg是什么呢？其实是由于即使基表的某些列上建立了索引，sql sever的查询优化器也必须要去扫描所有的行，这样就会导致了non sarg查询。
通常情况
上面讲的可能比较理论，通常情况下，是在where字句中，在数据库基表的字段上使用函数，比如像convert、cast、以及数据类型隐式转换等，对于时间进行操作的函数，比如取时间差datediff、对时间进行加减的dateadd以及取年的year，以及upper、lower大小写转化的函数，对字符串进行操作的rtrim、substring、left以及像like的完全模糊匹配、isnull函数以及用户自定义函数等。
大家在写sql的where等语句对于基表进行函数运算的时候一定要注意这里面存在一个non sarg查询会导致cpu使用率的上升。这部分的相关demo请参考云栖社区的博客：https://yq.aliyun/articles/72482
五、参数嗅探 (parameter sniffing)
sql server中的参数嗅探是什么？
参数嗅探其实是一个非常有意思的话题，那什么是参数嗅探呢？归根结底，导致参数嗅探的原因是由于sql server对执行计划的编译和缓存的过程导致的。想要理解这句话就明白sql server是如何执行一条查询语句的，当sql server的服务端接收到一条sql语句之后，首先要进行语法检查，之后进行语义分析，再之后进行编译，选择最优的执行计划路径，并将所得到的结果缓存到执行计划缓存中。
参数嗅探的问题所在
而问题就出在编译的过程中该如何编译这些查询语句上，因为某些查询语句是有参数的，当编译的时候一定是根据当时传入的参数的值编译一个最好的执行计划，但是当随着时间的推移，数据发生了变更就可能导致统计信息发生变化甚至可能发生数据倾斜的情况，如果发生了这样的情况，那么之前缓存的执行计划就可能不是最优的了，因为之前传入的参数可能是另外一个值，对应的统计信息可能就不是最优的解法了，这就是导致参数嗅探的一个原因。
参数嗅探的解决方案
刚才谈到了导致参数嗅探的原因是执行计划的编译和缓存过程，那么如何解决这个问题呢？很简单的一个思路就是：既然之前缓存的执行计划不是最优的，那么就清空这个缓存。这里为大家提供几种方法，但是其中也有不太推荐的方法。
1.第一种就是重启整个操作系统，因为操作系统重启了，内存当然就清空了，那么执行计划的缓存也会被清空，这时候sql server启动起来之后，查询语句提交到sql server服务端，当然会重新编译、使用最新的执行计划，这样可以解决参数嗅探的问题，但是问题在于这样的做法动作太大了，有点像使用大炮打蚊子，所以这样做思路是对的，但是方法却不恰当，所以这一种是不太推荐大家使用的。
2.第二种方法就是重启sql server服务，其实这样也可以解决问题，这个方法会导致sql server短暂停机和不可服务。这样的做法比第一种稍微好一点，但是也不是推荐的方法。
3.第三种方法是使用dbcc freeproccache命令来清空执行计划的缓存，这种方法比第二种方法又稍微好一点，但是这样还是会清空所有执行计划的缓存，但仍旧不是最好的方法，这样有点像是“宁可错杀一千，也不放过一个”的思维模式，因为真正出现问题就是某一个或者某几个执行计划的缓存，如果把所有的执行计划都清空了是可以解决这样的问题，但是也会产生“错杀其他的999人”的问题，所以这也不是最好的解决方法。
4.最好的解决方法就是针对于特定的查询语句或者存储过程去清空特定的执行计划缓存。
5.另外一种是使用query hits option，这将会告诉sql server在执行存储过程或者查询语句时每次都会进行重新编译，而不进行缓存，这也是一种思路。
6.还有一种就是更新统计信息，这个方法的原因是执行计划的编译和最优路径的选择基础数据就是统计信息，那么将统计信息更新之后相应的查询语句的执行计划缓存会被清空，下一次执行的时候会重新进行编译通过最新的统计信息获取最新的执行计划。
7.最后一个方法就是刚才提到的创建缺失索引或者删除不必要的、多余的以及重复的索引。
以上这些都是解决参数嗅探的方法，这部分的相关demo请参考�...