专家出诊:SQL Server 高CPU系列之索引诊断

作者题记:CPU高使用率往往会导致SQL Server服务响应缓慢,查询超时,甚至服务挂起僵死,可以说CPU高使用率是数据库这种后台进程服务的第一大杀手。引发CPU过高的原因有很多,今天主要从索引的角度进行分析。

引发CPU过高的最常见的两类索引问题是索引缺失和索引碎片。首先我们来分析索引缺失。

一、索引缺失

场景分析

关系型数据库(RDBMS)系统中,索引缺失最为常见会导致I/O读取很高,进而导致CPU使用率很高。这是因为当查询优化器在执行计划评估过程中,发现没有合适的索引可以使用时,不得不选择走全表扫描(Table Scan)或者近似于全表扫描的操作(Clustered Index Scan)来获取所需要的数据。这种大面积的数据扫面会导致I/O子系统读取操作频繁,SQL Server需要读取大量的数据并加载到内存中,这些操作最后都会使得CPU使用率飙高。这种场景中,解决CPU高使用率的问题,其实就变成了解决索引缺失的问题。我们可以从下面的例子中来看看如何发现和解决索引缺失的问题。

解决方法

在这里,我们将这个例子详细分解为五个小步骤:  测试环境:搭建简单的测试环境。  执行查询:创建缺失索引前后用于做性能对比的查询语句  缺失索引:查找缺失索引的方法  解决问题:创建缺失的索引  效率对比:创建缺失索引前后的性能对比

测试环境

创建测试环境包括:创建测试数据库、测试表对象和初始化200万条记录。

Create testing database

create demo table SalesOrder

data init for 2 M records.

初始化了200万条数据,如下:

执行查询

查询用户10057在近一个月内的商品购买情况(为了获取性能对比信息,我打开了Time和I/O统计),建议在执行语句之前打开实际执行计划获取选项。打开实际执行计划,方法是点击SSMS中的下图方框中图标,或者使用快捷键CTRL + M:

执行查询语句:

执行查询语句的I/O,CPU和时间消耗,其中,逻辑I/O读取消耗32295,CPU消耗451 ms,执行时间消耗648 ms,如下图展示:

执行计划走Clustered Index Scan(性能消耗几乎于Table Scan相近),索引缺失警告信息,如下图绿色字体,右键点击,然后选择Missing Indexes Details...可以打开缺失索引的详细信息:

除了上面事例讲解的执行计划查看缺失索引的方法以外,我们还可以使用三个重要的系统动态视图来查看缺失索引(每个视图具体含义,请自行查看帮助文档):  sys.dm_db_missing_index_group_stats  sys.dm_db_missing_index_groups  sys.dm_db_missing_index_details 利用三个系统动态视图来查找缺失的索引,方法如下:

执行后的查询结果如下图所示:

解决问题

无论是通过执行计划查看索引缺失,还是通过三个动态视图获取缺失索引,最终的目的就是解决问题,让我们创建这个缺失的索引:

效率对比

创建了这个缺失索引以后,再次上面执行上面“执行查询”中的查询语句,执行计划和性能消耗对比。 执行计划,已经走到了更加高效的Index Seek上来了,如下图所示:

I/O读逻辑取消耗为126、CPU消耗为16 ms和执行时间消耗为198 ms,截图如下:

创建索引后,执行时间消耗,CPU消耗,I/O读取消耗,分别提高了3.27倍,28.19倍和256.3倍,平均性能提高了95.92倍。对比情况做图如下:

从最终的测试结果来看,创建索引后,对于特定查询性能在CPU使用率、时间消耗和I/O读取三个方面都有很大提升,尤其是I/O读取操作提高了256.3倍,平均的性能提升达到了95.92倍,效果十分明显。

二、索引碎片

解决索引碎片问题是解决SQL Server服务响应缓慢,查询超时的又一利器

索引碎片是什么

索引碎片既指索引文件页中的空白空间;又指被Page Split的索引页;还指索引失序的数据页。前面两种我们称之为索引内部碎片,后面一种我们叫着索引外部碎片。

前面是干瘪瘪的理论描述,下面举一个关于电话簿实例就比较好理解了:假如电话簿按照城市名称、姓名和电话号码组合排序的方式来存储所有人员的联系电话。

以下几种情况都需要更新电话簿:  当一个人更换电话号码的时候或者改名字的时候(西方国家女子结婚后,会修改姓氏为老公的姓,比如:奥巴马的老婆叫米歇尔-奥巴马):需要更新操作  当一个人从一个城市搬迁到另一个城市的时候:需要删除原来的记录,在相应的城市插入新记录  当有新人办理了电话业务的时候:需要在相应城市插入记录  当作废电话号码的时候:需要删除对应条目

这些操作带来的后果是:更新操作可能导致失序(out of order);删除操作导致空白条目(empty space);插入操作导致分页(page split)。结果就是最终形成电话簿(类似于索引)的碎片外部碎片和内部碎片。

索引碎片的危害

清楚了索引是什么的问题,我们来看看索引碎片的危害。

假如,一本完整存放(没有任何碎片)的电话簿刚好1000页,而由于前面讲的种种操作,导致了10%的碎片,那么最终我们需要1100页来存放。我们每一本书将浪费100页的纸张来印刷,也将浪费掉每个人10%的查询和阅读时间。放在SQL Server索引碎片的角度,原理是相通的:由于SQL Server读取数据的最小单位是数据页,而不是单条记录,所以,相同的查询语句需要SQL Server读取更多的磁盘宽度,加之索引碎片会浪费更多的内存资源来存放读取到的数据。因此,碎片化程度越高意味着更高的内存使用浪费和更低的查询性能。微软建议索引碎片率在5%到30%之间,做索引重组;碎片率超过30%,做索引重建工作。

解决方法

我们从以下几个方面来描述解决方法:  模拟产生索引碎片  获取索引碎片信息  重建索引  前后对比

模拟产生索引碎片

我假设需要变更100万条数据记录,这些变更包括UPDATE、DELETE和INSERT操作。在前一篇文章创建的表dbo.SalesOrder基础上,我们使用如下方法变更数据:

获取索引碎片信息

我们可以使用系统函数sys.dm_db_index_physical_stats来获取索引碎片信息。查询索引碎片的方法如下:

查询结果展示如下图所示:

重建索引

找准了解决问题的方向,处理起来就变得轻车熟路了,从查询结果我发现主键碎片率达到了92.2%,已经是非常之高了。重建索引的方法:

再次执行索引碎片查询,结果如下:

前后对比

重整索引碎片以后,主键碎片率从92.2%降低到0.1%;索引空间、数据空间和总空间大小分别减少了4.3%、52.8%和72.4%,平均空间减少达43.17%。

注意事项

在产品环境中重建索引需要十分小心,原因是:  重建索引会消耗大量的系统I/O读写资源。  重建索引会导致查询进程的死锁或者锁等待,尤其是非企业版SQL Server(企业版可以使用ONLINE选项来最大限度规避这个问题)。  重建索引会导致数据库日志文件暴涨,而因此会给Database Mirroring、Log Shipping和Backup带来压力。 所以,请选择业务低谷期进行索引碎片重整的操作。

总结

这篇文章从索引碎片是什么,有哪些危害,如何解决碎片问题和需要注意的事项等方面,详细探讨了导致高CPU使用率的又一常见原因--索引碎片。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-07-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

深入并行:从数据倾斜到布隆过滤深度理解Oracle的并行

陈焕生 Oracle Real-World Performance Group 成员,senior performance engineer,专注于 OLTP...

2819
来自专栏数据库

单机数据库优化

数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表。...

1837
来自专栏程序猿DD

优雅处理你的Java异常

来源:https://my.oschina.net/c5ms/blog/1827907

922
来自专栏Linyb极客之路

Java面试中常问的数据库方面问题

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的

753
来自专栏数据和云

MySQL row格式的两个问题

作者简介: ? 刘伟 云和恩墨开源解决方案事业部首席架构师 多年一线互联网企业DBA经历,对MySQL、NoSQL,PostgreSQL等各类开源数据库均有涉猎...

3396
来自专栏Java技术栈

2018年不能错过的 14 个 Java 库!

2171
来自专栏北京马哥教育

你所不知道的linux匿名管道知识

豌豆贴心提醒,本文阅读时间5分钟 相信很多在linux平台工作的童鞋, 都很熟悉管道符 '|', 通过它, 我们能够很灵活的将几种不同的命令协同起来完成一件任...

2655
来自专栏JAVA高级架构

SQL性能优化梳理

前言 本文主要针对的是关系型数据数据库MySql。键值类数据库可以参考最简大数据Redis。先简单梳理下Mysql的基本概念,然后分创建时和查询时这两个阶段的优...

2717
来自专栏你不就像风一样

Hibernate各种主键生成策略与配置详解

主键由外部程序负责生成,在 save() 之前必须指定一个。Hibernate不负责维护主键生成。与Hibernate和底层数据库都无关,可以跨数据库。在存储对...

742
来自专栏微信公众号:Java团长

Java面试中常问的数据库方面问题

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接,是有序的

743

扫描关注云+社区