SSMS执行计划对比功能

问题引入

“鸟儿，我一直有个疑问，在很多时候，被客户问道，为什么非常相似的两个查询语句执行计划天壤之别？”。老鸟已经开始在请教菜鸟问题了。
“这种时候，大部分情况是由于查询语句走的执行计划不一样导致的。”菜鸟卖起了关子。
“那如何高效的对比执行计划的差异呢？”老鸟穷追猛问。
“这个嘛，我自有妙招，且听我细细道来”。

问题分析

SSMS图形化展示SQL Server查询语句执行计划已经是非常牛X的功能了，如果SSMS再完善加入执行计划对比功能的话，一定会解决老鸟的痛点，大放异彩。没想到地球人真的没能阻止SSMS吊炸天的步伐。SSMS 2016中，已经具备了执行计划对比功能。

使用方法

以下是SSMS执行计划对比功能的详细介绍。

创建测试环境

创建测试环境包括创建：测试数据库、测试表和插入100万条测试数据。

-- Create testing database
IF DB_ID('ExecPlanCompare') IS NULL
    CREATE DATABASE ExecPlanCompare;
GO

USE ExecPlanCompare
GO
SET NOCOUNT ON

-- create demo table SalesOrder
IF OBJECT_ID('dbo.SalesOrder', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.SalesOrder
END
GO
CREATE TABLE dbo.SalesOrder
(
    OrderID BIGINT IDENTITY(1,1) NOT NULL
    ,AutoID INT NOT NULL
    ,UserID INT NOT NULL
    ,OrderQty INT NOT NULL
    ,Price DECIMAL(8,2) NOT NULL
    ,OrderDate DATETIME NOT NULL,
    CONSTRAINT PK_SalesOrder PRIMARY KEY NONCLUSTERED (OrderID)
);
GO

-- Create Cluster Column Store Index
CREATE CLUSTERED COLUMNSTORE INDEX CCSI_SalesOrder ON dbo.SalesOrder;
;
GO

-- init 1000000 records
;WITH a 
AS (
    SELECT * 
    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a)
), RoundData
AS(
SELECT TOP(1000000)
    AutoIDRound = abs(checksum(newid()))
    ,Price = a.a * b.a * 500
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h
)
INSERT INTO dbo.SalesOrder(AutoID, UserID, OrderQty, Price, OrderDate)
SELECT 
    AutoID = cast(ROUND((14 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 1), 0) as int)
    ,UserID = cast(ROUND((500 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 10000), 0) as int)
    ,OrderQty = cast(ROUND((2 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 1), 0) as int)
    ,Price = cast(Price AS DECIMAL(8,2))
    ,OrderDate = dateadd(day, -cast(ROUND((1099 * (AutoIDRound*1./cast(replace(AutoIDRound, AutoIDRound, '1' + replicate('0', len(AutoIDRound))) as bigint)) + 1), 0) as int) ,'2017-01-10')
FROM RoundData;

参照组执行计划

最好生成参照组实际执行计划，而不是预估的执行计划。

-- get the execution plan for using PK
SELECT 
    ord.AutoID
    ,TotalAmount = ISNULL(SUM(ord.Price * ord.OrderQty), 0.00)
    ,TotalQty = ISNULL(SUM(ord.OrderQty), 0.00)
FROM dbo.SalesOrder AS ord WITH(INDEX = PK_SalesOrder)
GROUP BY ord.AutoID

生成实际执行的方法是：
 打开采集实际执行计划的开关
 执行查询语句获取实际执行计划
 在Execution Plan窗口右键点击，然后选择：Save Execution Plan As ...，选择要保存执行计划的目录和名字。
整个过程如下图展示：

对比组执行计划

同样打开实际执行计划开关，执行以下对比组SQL查询语句，获取实际执行计划。

-- get the execution plan for using CCSI
SELECT 
    ord.AutoID
    ,TotalAmount = ISNULL(SUM(ord.Price * ord.OrderQty), 0.00)
    ,TotalQty = ISNULL(SUM(ord.OrderQty), 0.00)
FROM dbo.SalesOrder AS ord WITH(INDEX = CCSI_SalesOrder)
GROUP BY ord.AutoID

在Execution Plan窗口任意位置，右键点击，选择 Compare Showplan。如下图展示：

浏览到上一小节“参照组执行计划”保存的文件。

执行计划上下对比

接下来是见证奇迹的时刻，看看两个查询语句的执行计划对比效果图：

整个窗口由三部分组成：
 执行计划对比窗口：两个执行计划的详细图形展示。
 执行计划对比选项：对比选项的设置和执行计划语句对比展示。
 属性窗口：详细的执行计划指标对比展示。比如：这里我们就可以很容易对比出使用聚集列存储索引查询CPU和执行时间分别是7和9；而使用主键查询CPU和执行时间是26114和16176。明显前者性能高太多了，如果没有执行计划的对比功能，我们就很难这么形象的对比出他们之间的执行计划差异。

执行计划左右对比

如果你不喜欢上下位置，我们一二三四，换个姿势，左右对比好了。方法非常简单，右键点击执行计划对比窗口任何位置，然后选择”Toggle Splitter Orientation”。

位置交换完毕，细心的你一定发现属性窗口也有了细微的变化。

动作对比

SSMS对比功能做到这里，已经灰常牛逼了，对不对？我们可以使用SSMS的对比功能对比出两个查询语句的执行计划详细差异。还有更牛X的功能呢，我们可以对比每个非常细小的动作差异，是不是更牛逼，等不及要看看了？简单，只需要在执行计划对比窗口中左右（或者上下）子窗口选中要对比的动作即可。比如：左边窗口我们选中Columnstore Index Scan (Clustered)，右边窗口我们选择Index Scan (Nonclustered)。这时，属性窗口中会展示相应被选择动作的详细对比情况，一目了然。

写在最后

由于之前很长一段时间，自己感动身受，在对比两个查询语句执行计划的差异时，非常痛苦与不便，需要耗费非常大的精力和时间。SSMS具备执行计划对比功能以后，解决了这个痛点，妈妈再也不用担心执行计划对比烧脑的事情了。