摘要

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'

初始化了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

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

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

解决问题

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

--=== 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

效率对比

创建了这个缺失索引以后，再次上面执行上面“执行查询”中的查询语句，执行计划和性能消耗对比。
执行计划，已经走到了更加高效的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倍，效果十分明显。