摘要

CPU高使用率往往会导致SQL Server服务响应缓慢，查询超时，甚至服务挂起僵死，可以说CPU高使用率是数据库这种后台进程服务的第一大杀手。本系列文章之一的“索引缺失”就是CPU高使用率的最常见的原因之一。

问题引入

“鸟啊，我们平时在服务阿里云RDS SQL Server客户的过程中，遇到最多的一个问题就是，客户反馈RDS SQL Server数据库CPU使用率很高（有时超过90%，甚至到100%），导致查询缓慢甚至超时，这类问题要如何解决啊？”。老鸟已经被类似的问题烦透了。

“鸟哥，关于CPU高使用率高问题，原因各式各样，不是一两句话能够说得清楚的。”，菜鸟开始卖关子了：“那，要不这样吧，我写一个专题系列文章来分析各种场景，以解决RDS SQL Server CPU高使用率的问题吧。”。

场景分析

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

解决方法

在这里，我们将这个例子详细分解为五个小步骤：

 测试环境：搭建简单的测试环境。

 执行查询：创建缺失索引前后用于做性能对比的查询语句

 缺失索引：查找缺失索引的方法

 解决问题：创建缺失的索引

 效率对比：创建缺失索引前后的性能对比

测试环境

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

-- Create testing databaseIF DB_ID('TestDb') IS NULL    CREATE DATABASE TestDb;GOUSE TestDbGO-- create demo table SalesOrderIF OBJECT_ID('dbo.SalesOrder', 'U') IS NOT NULLBEGIN    DROP TABLE dbo.SalesOrderENDGOCREATE TABLE dbo.SalesOrder(    RowID INT IDENTITY(1,1) NOT NULL    , OrderID UNIQUEIDENTIFIER NOT NULL    , ItemID INT NOT NULL    , UserID INT NOT NULL    , OrderQty INT NOT NULL    , Price DECIMAL(8,2) NOT NULL    , OrderDate DATETIME NOT NULL         CONSTRAINT DF_OrderDate DEFAULT(GETDATE())    , LastUpdateTime DATETIME NULL    , OrderComment NVARCHAR(100) NULL    , CONSTRAINT PK_SalesOrder PRIMARY KEY(        OrderID    ));-- data init for 2 M records.;WITH a AS (    SELECT *     FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a)), RoundDataAS(SELECT TOP(2000000)    OrderID = NEWID()    ,ItemIDRound = abs(checksum(newid()))    ,Price = a.a * b.a * 10    ,OrderQty = a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.aFROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h), DATAAS(SELECT     OrderID    ,ItemID = cast(ROUND((1300 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 101), 0) as int)    ,UserID = cast(ROUND((500 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 10000), 0) as int)    ,OrderQty    ,Price = cast(Price AS DECIMAL(8,2))    ,OrderDate = dateadd(day, -cast(ROUND((50 * (ItemIDRound*1./cast(replace(ItemIDRound, ItemIDRound, '1' + replicate('0', len(ItemIDRound))) as bigint)) + 1), 0) as int) ,GETDATE())FROM RoundData)INSERT INTO dbo.SalesOrder(OrderID, ItemID, UserID, OrderQty, Price, OrderDate, LastUpdateTime, OrderComment)SELECT     OrderID    , ItemID    , UserID    , OrderQty    , Price    , OrderDate    , LastUpdateTime = OrderDate    , OrderComment = N'User ' + CAST(UserID AS NVARCHAR(8)) + N' purchased item ' + CAST(ItemID AS NVARCHAR(8))FROM DATA;GOEXEC sys.sp_spaceused 'dbo.SalesOrder'

初始化了200万条数据，如下：

执行查询

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

执行查询语句：

----=== get User 10057 purchased Items InfoUSE [TestDb]GOSET STATISTICS TIME ONSET STATISTICS IO ONSELECT     ItemID, OrderQty, PriceFROM dbo.SalesOrderWHERE UserID = 10057    AND OrderDate >= DATEADD(MONTH, -1, GETDATE())    AND OrderDate <= GETDATE();SET STATISTICS TIME OFFSET STATISTICS IO OFF

执行查询语句的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

利用三个系统动态视图来查找缺失的索引，方法如下：

USE TestDbGOSELECT TOP 100     c.equality_columns    , c.inequality_columns    , c.included_columns    , create_Index = 'CREATE INDEX IX_' + REPLACE(REPLACE(REPLACE(equality_columns, '[', ''), ']',''), ',', '')        + '_' + REPLACE(REPLACE(REPLACE(inequality_columns, '[', ''), ']',''), ',', '')         + '_' +REPLACE(REPLACE(REPLACE(included_columns, '[', '@'), ']',''), ', ', '_')  + char(10)        + 'ON ' + SCHEMA_NAME(tb.schema_id) + '.' + object_name(tb.object_id)         + '('            + case                 when c.equality_columns is not null and c.inequality_columns is not null                     then c.equality_columns + ',' + c.inequality_columns                when c.equality_columns is not null and c.inequality_columns is null                     then c.equality_columns                when c.inequality_columns is not null                     then c.inequality_columns            end        + ')' + char(10)        + case             when c.included_columns is not null             then 'INCLUDE (' + c.included_columns + ')'            else ''        end + char(10)        + N'WITH (FILLFACTOR = 85);'FROM sys.dm_db_missing_index_group_stats a    INNER JOIN sys.dm_db_missing_index_groups b        ON a.group_handle = b.index_group_handle    INNER JOIN sys.dm_db_missing_index_details c        ON c.index_handle = b.index_handle    INNER JOIN sys.tables as tb        ON c.object_id = tb.object_idWHERE db_name(database_id) = db_name()    AND equality_columns is not null    AND tb.object_id = object_id('dbo.SalesOrder', 'U')ORDER BY a.avg_total_user_cost * a.avg_user_impact * (a.user_seeks + a.user_scans) DESC

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

解决问题

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

--=== Create Missing IndexesUSE [TestDb]GOCREATE INDEX IX_UserID_OrderDate_@ItemID_@OrderQty_@PriceON dbo.SalesOrder([UserID],[OrderDate])INCLUDE ([ItemID], [OrderQty], [Price])WITH (FILLFACTOR = 85);GO

效率对比

创建了这个缺失索引以后，再次上面执行上面“执行查询”中的查询语句，执行计划和性能消耗对比。

执行计划，已经走到了更加高效的Index Seek上来了，如下图所示：

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

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

总结

这篇文章从理论结合实际，介绍了CPU高使用率的解决方法系列文章之一，缺失索引。从最终的测试结果来看，创建索引后，对于特定查询性能在CPU使用率、时间消耗和I/O读取三个方面都有很大提升，尤其是I/O读取操作提高了256.3倍，平均的性能提升达到了95.92倍，效果十分明显。