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

相关文章

来自专栏数据和云

举一反三-分区裁剪作用的“新”发现

作者介绍 ? 赵勇 云和恩墨北区技术工程师 专注于SQL审核和优化相关工作。曾经服务的客户涉及金融保险、电信运营商、政府、生产制造等行业。 分区裁剪的定义 分区...

28810
来自专栏mini188

缓存遇到的数据过滤与分页问题

遇到的问题 1、最初阶段 系统中做了一个监控功能,用于记录所有的请求数据,数据插入频繁,量非常大,比如一天1000万条。考虑到数据插入的效率,就使用内存KV缓存...

4385
来自专栏L宝宝聊IT

索引、视图、存储过程和触发器的应用

1688
来自专栏漫漫全栈路

Oracle数据库项目——MC服务器管理系统

数据库设计内容,分为:系统功能说明,数据库分析与设计,数据库实现。本数据库系统提供的功能用于项目MC-Servers-Web,详情内容参见MC-Service...

7374
来自专栏逸鹏说道

SQL Server 执行计划缓存

概述 了解执行计划对数据库性能分析很重要,其中涉及到了语句性能分析与存储,这也是写这篇文章的目的,在了解执行计划之前先要了解一些基础知识,所以文章前面会讲一些...

4009
来自专栏数据之美

MySQL 死锁与日志二三事

最近线上 MySQL 接连发生了几起数据异常,都是在凌晨爆发,由于业务场景属于典型的数据仓库型应用,白天压力较小无法复现。甚至有些异常还比较诡异,最后 root...

4146
来自专栏Java帮帮-微信公众号-技术文章全总结

Java开发Struts2案例代码

一、struts2综合案例 1、建立一个JavaWeb应用 ? 2、拷贝一下内容到您应用中:美工MM给你准备好的 ? 3、搭建Struts2的开发环境 a、拷贝...

3576
来自专栏杨建荣的学习笔记

索引列顺序导致的性能问题

今天和大家分享一个很有意思的例子,关于索引列的顺序导致的性能问题。 发现数据库的性能比较差,CPU消耗很高,抓了一个awr,发现瓶颈在sql上,top 1的sq...

3795
来自专栏Java技术交流群809340374

MySQL有哪些存储引擎,各自的优缺点,应用场景

经常面试都会问到MYSQL有哪些存储引擎,以及各自的优缺点。今天主要分享常见的存储引擎:MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB...

2333
来自专栏Java面试通关手册

MySQL常见的两种存储引擎:MyISAM与InnoDB的爱恨情仇

Java面试通关手册(Java学习指南,欢迎Star,会一直完善下去,欢迎建议和指导):https://github.com/Snailclimb/Java_G...

2387

扫码关注云+社区

领取腾讯云代金券