摘要
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 database
IF DB_ID('TestDb') IS NULL
CREATE DATABASE TestDb;
GO
USE TestDb
GO
-- create demo table SalesOrder
IF OBJECT_ID('dbo.SalesOrder', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.SalesOrder
END
GO
CREATE 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)
), RoundData
AS(
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.a
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h
), DATA
AS(
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;
GO
EXEC sys.sp_spaceused 'dbo.SalesOrder'
AI 代码解读
初始化了200万条数据，如下：
执行查询
查询用户10057在近一个月内的商品购买情况（为了获取性能对比信息，我打开了Time和I/O统计），建议在执行语句之前打开实际执行计划获取选项。打开实际执行计划，方法是点击SSMS中的下图方框中图标，或者使用快捷键CTRL + M：
执行查询语句：
----=== get User 10057 purchased Items Info
USE [TestDb]
GO
SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT
ItemID, OrderQty, Price
FROM dbo.SalesOrder
WHERE UserID = 10057
AND OrderDate >= DATEADD(MONTH, -1, GETDATE())
AND OrderDate <= GETDATE();
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
AI 代码解读
执行查询语句的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 TestDb
GO
SELECT 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_id
WHERE 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
AI 代码解读
执行后的查询结果如下图所示：
解决问题
无论是通过执行计划查看索引缺失，还是通过三个动态视图获取缺失索引，最终的目的就是解决问题，让我们创建这个缺失的索引：
--=== Create Missing Indexes
USE [TestDb]
GO
CREATE INDEX IX_UserID_OrderDate_@ItemID_@OrderQty_@Price
ON dbo.SalesOrder([UserID],[OrderDate])
INCLUDE ([ItemID], [OrderQty], [Price])
WITH (FILLFACTOR = 85);
GO
AI 代码解读
效率对比
创建了这个缺失索引以后，再次上面执行上面“执行查询”中的查询语句，执行计划和性能消耗对比。
执行计划，已经走到了更加高效的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倍，效果十分明显。
