专家出诊: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 条评论
登录 后参与评论

相关文章

来自专栏向治洪

浅谈SystemClock 和Thead的区别和联系

其实将SystemClock 和Thead直接放在一起是不合适的,我们首先来看下他们所在的api。 public final class SystemClock...

1659
来自专栏idba

性能优化之分页查询

大部分开发和DBA同行都对分页查询非常非常了解,看帖子翻页需要分页查询,搜索商品也需要分页查询。那么问题来了,遇到上千万或者上亿的数据量怎么快速的拉取...

614
来自专栏极客慕白的成长之路

数据库系统概述必背知识点整理

972
来自专栏散尽浮华

事故记录:php-cgi进程过多导致系统资源耗尽

事故现象: 机房一台服务器运行一段时间后,突然发现系统资源即将被耗尽! 1)top命令查看一下系统的cpu ram swap的使用情况 ? 由上图分析,可以看出...

1935
来自专栏数据和云

实战课堂:系统CPU高消耗的SQL筛选和最佳索引优化

在一次客户系统性能优化项目中,经过第一阶段的优化之后,数据库的DB Time和物理读都明显降低,但是我们发现CPU并没有明显降低。

1044
来自专栏数据和云

【云和恩墨大讲堂】Oracle线上嘉年华第二讲

编辑手记:Oracle线上嘉年华,正在持续分享中。本次的主题是系统割接中的SQL解析问题和结合业务的SQL优化改写技巧。 1 嘉宾介绍 小鱼(邓秋爽) 云和恩...

2716
来自专栏搜云库

操作系统和数据库基础

进程与线程的差别 进程是程序的一次执行。线程可以理解为进程中执行的一段程序片段。在一个多任务环境下中下面的概念可以帮助我们理解两者的区别。 进程间是独立的...

17710
来自专栏javathings

线程的 yield 方法有什么用?

单词 yield 有一个中文意思是车辆或行人的让行,线程中的 yield 也是这个意思,表示该线程主动失去 CPU 调度,进入就绪状态,让优先级更高的线程先运行...

611
来自专栏zhangdd.com

linux服务器CPU物理颗数.内核数.线程数查看及关系详解

公司服务器是分几批购买的,所以造成配置方面也不大相同特别是cpu配置方面,一直想弄清楚这些cpu都是什么型号,有几颗物理cpu,每颗cpu有几个核心,没个核心有...

812
来自专栏数据分析

SQL Server 性能优化之——系统化方法提高性能

1. 概述 在比较大的范围内找出能够大幅提高性能的区域,并且专注于分析这个区域,这是最有效的优化SQL Server性能的方式。否则,大量的时间和精力可能被浪费...

4206

扫码关注云+社区