SQL Server最佳实践：高CPU使用率排查 sql server数据结构sqlJNIc
程序员文章站
2024-02-05 08:13:58
...
阅读原文请点击：http://click.aliyun.com/m/23240/
摘要： 在阿里云SQL Server最佳实践系列在线直播中，阿里云数据库专家汪建明总结了7大问题并结合案例为大家分享了阿里云SQL Server高CPU使用率问题排查的实践经验。
摘要：在阿里云SQL Server最佳实践系列在线直播中，阿里云数据库专家汪建明总结了7大问题并结合案例为大家分享了阿里云SQL Server高CPU使用率问题排查的实践经验。
本期直播回顾链接：http://yq.aliyun.com/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)
690026b766004839d0f2897aaeae6d2bc5722295
为什么索引缺失会降低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.com/articles/72265
二、索引碎片 (Indexes Fragmentation)
刚才提到了索引缺失会导致CPU使用率的升高，而另外一个问题是：是不是索引创建以后CPU的使用率就一定会降低呢？或者是说在索引不缺失的情况下，CPU的使用率就一定不会上升呢？这两个问题的答案都是否定的。这里涉及的话题就是索引碎片，这里的索引碎片可以理解为索引数据页中的一些空隙，这应该如何理解呢？假如某一个页里面是满的，比如是8K，如果存在25%的空隙，那么真正有效的数据只有75%，举个简单的例子比如某个表格的索引数据有100个页，但是碎片率是25%，所以这100个换页面里面只有75个页面的数据是有效的。所以在索引的碎片率非常高的情况下，索引的效率就会非常低，因为其IO的使用率也会非常低。
a7179ee988ad7b6d00315f273c1ad4f40c1c3dcd
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.com/articles/72348
三、数据类型隐式转换 (Data Conversion Implicitly)
很多同学不了解数据类型的转换，特别是数据类型的隐式转换。在这里和大家简单分享一下。
阅读原文请点击：http://click.aliyun.com/m/23240/
摘要： 在阿里云SQL Server最佳实践系列在线直播中，阿里云数据库专家汪建明总结了7大问题并结合案例为大家分享了阿里云SQL Server高CPU使用率问题排查的实践经验。
摘要：在阿里云SQL Server最佳实践系列在线直播中，阿里云数据库专家汪建明总结了7大问题并结合案例为大家分享了阿里云SQL Server高CPU使用率问题排查的实践经验。
本期直播回顾链接：http://yq.aliyun.com/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)
690026b766004839d0f2897aaeae6d2bc5722295
为什么索引缺失会降低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.com/articles/72265
二、索引碎片 (Indexes Fragmentation)
刚才提到了索引缺失会导致CPU使用率的升高，而另外一个问题是：是不是索引创建以后CPU的使用率就一定会降低呢？或者是说在索引不缺失的情况下，CPU的使用率就一定不会上升呢？这两个问题的答案都是否定的。这里涉及的话题就是索引碎片，这里的索引碎片可以理解为索引数据页中的一些空隙，这应该如何理解呢？假如某一个页里面是满的，比如是8K，如果存在25%的空隙，那么真正有效的数据只有75%，举个简单的例子比如某个表格的索引数据有100个页，但是碎片率是25%，所以这100个换页面里面只有75个页面的数据是有效的。所以在索引的碎片率非常高的情况下，索引的效率就会非常低，因为其IO的使用率也会非常低。
a7179ee988ad7b6d00315f273c1ad4f40c1c3dcd
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.com/articles/72348
三、数据类型隐式转换 (Data Conversion Implicitly)
很多同学不了解数据类型的转换，特别是数据类型的隐式转换。在这里和大家简单分享一下。
阅读原文请点击：http://click.aliyun.com/m/23240/