SQL Server provides great enhancement capability features for troubleshooting purposes. Some of the important features are:
SQL Server 提供了强大的增强功能，可用于故障排除。 一些重要的功能是：
Query store
查询存储
Enhancement for extended events
扩展活动的增强
Live query execution plan
实时查询执行计划
Real-Time Analytics
实时分析
In-Memory OLTP (online transaction processing)
内存中OLTP（在线事务处理）
Management studio enhancements like the Compare Showplan
管理工作室增强功能，例如“比较展示计划”
The Compare Showplan feature provides the ability to compare two sets of query execution plans. For example: we’ve had the situation where a user reported that the query is not working fine, while it was working fine for some time before.
比较显示计划功能提供了比较两组查询执行计划的功能。 例如：我们遇到了这样的情况：用户报告查询无法正常工作，而在一段时间之前它仍然可以正常工作。
To elaborate – SQL Server generates the optimized query execution plan based on several factors like number of rows, index, stats, memory, CPU etc. and it might be useful to compare 2 query execution plans to see what actually changed. Prior to SQL Server , it was very difficult to perform this kind of comparison, but now we can use this feature to quickly figure out the changes.
详细地说，SQL Server根据行数，索引，统计信息，内存，CPU等多种因素生成优化的查询执行计划，比较两个查询执行计划以查看实际更改可能很有用。 在SQL Server 之前，执行这种比较非常困难，但是现在我们可以使用此功能快速找出更改。
Some of the potential benefits of the execution plan comparison can be:
执行计划比较的一些潜在好处可能是：
Troubleshooting a production scenario
对生产方案进行故障排除
Comparing execution plans between test and production environment
在测试和生产环境之间比较执行计划
Comparing execution plans between two sets of query like parametric queries
比较两组查询（例如参数查询）之间的执行计划
Query Performance review after doing a server/database upgrade
服务器/数据库升级后的查询性能检查
SQL Server introduces the Compare Showplan feature that allows side-by-side comparison of two execution plans.
SQL Server 引入了Compare Showplan功能，该功能允许并排比较两个执行计划。
如何使用比较显示计划功能 (How to use the Compare Showplan feature)
Let us understand this query execution plan comparison feature in SQL Server with examples.
让我们通过示例了解SQL Server 中的此查询执行计划比较功能。
For the demonstration purpose, I am using a sample database named WideWorldImporters. This is the SQL Server sample database. It can be downloaded from here.
出于演示目的，我使用了一个名为WideWorldImporters的示例数据库。 这是SQL Server 示例数据库。 可以从这里下载。
The sample query is below:
示例查询如下：
/****** Script for SelectTopNRows command from SSMS******/SELECT TOP 1000 [OrderID],[CustomerID],[SalespersonPersonID] ,[PickedByPersonID],[ContactPersonID] ,[BackorderOrderID],[OrderDate],[ExpectedDeliveryDate],[CustomerPurchaseOrderNumber],[IsUndersupplyBackordered],[Comments],[DeliveryInstructions],[InternalComments],[PickingCompletedWhen],[LastEditedBy],[LastEditedWhen]FROM [WideWorldImporters].[Sales].[Orders] where OrderID =1268
I have included the actual execution plan with the query (to add the actual query execution plan click on Query ➜ Include Actual Execution Plan or Shortcut key Ctrl+M)
我已经在查询中包括了实际的执行计划（要添加实际的查询执行计划，请单击查询➜包括实际的执行计划或快捷键Ctrl + M）。
To save this execution plan just right click on Execution plan ➜ Save Execution plan as, and specify the location along with the name of the plan. Extension of the plan will be .sqlplan.
要保存该执行计划，只需右键单击执行计划➜将执行计划另存为，然后指定位置以及计划名称。 该计划的扩展名将是.sqlplan。
In the next step, I’ll run the same query with a different order ID in another query window with its actual execution plan.
下一步，我将在另一个具有实际执行计划的查询窗口中以不同的订单ID运行相同的查询。
To compare the execution plan of both query executions simply right click on Execution plan ➜ Compare Showplan
要比较两个查询执行的执行计划，只需右键单击执行计划➜比较显示计划
It opens a dialog box where we need to specify the location of the previously saved execution plan:
它打开一个对话框，我们需要在其中指定先前保存的执行计划的位置：
We can see both execution plans in the same screen with their properties:
我们可以在同一屏幕上看到两个执行计划及其属性：
By default, the comparison of both execution plans will be shown in top -bottom (horizontal) view. If we want to make it vertical, we’ll just right click on any Execution plan ➜ Toggle Splitter orientation
默认情况下，两个执行计划的比较将在上下（水平）视图中显示。 如果要使其垂直，则只需右键单击任何执行计划➜切换拆分器的方向
Both query execution plans are now adjacent to each other.
这两个查询执行计划现在彼此相邻。
When two Showplans are compared, areas and operators of the plan, that do essentially the same thing, are highlighted in the same colour. This shows the operator that is essentially doing the same kind of operation in both plans.
比较两个Showplan时，实际上执行相同操作的区域和操作员将以相同的颜色突出显示。 这表明操作员在两个计划中实际上都在执行相同的操作。
As we can see in the example above, both execution plans are using clustered index seek operator.
正如我们在上面的示例中看到的那样，两个执行计划都使用聚簇索引查找运算符。
There is also a property dialogue box opened with both execution plans. It shows a lot of information about execution time, estimated number of rows, actual number of rows, logical operations, memory, CPU, parallelism, physical read, logical read, storage, table cardinality, number of executions, etc.
同时打开了两个执行计划的属性对话框。 它显示了大量有关执行时间，估计的行数，实际的行数，逻辑操作，内存，CPU，并行性，物理读取，逻辑读取，存储，表基数，执行次数等信息。
We can compare each execution plan operator in both queries. Suppose we want to see and compare the properties of a clustered index seek; just clicking on it will make the change in the properties section.
我们可以在两个查询中比较每个执行计划运算符。 假设我们要查看和比较聚簇索引查找的属性； 只需单击它即可在属性部分进行更改。
比较已保存的执行计划的执行计划 (Comparing the execution plan of the already saved execution plan)
We can compare the execution plan of the already saved query execution plan as well. To do so, just open the query execution plan in SQL Server Management Studio . Once opened, right click on the execution plan, and click on the Showplan compare.
我们还可以比较已经保存的查询执行计划的执行计划。 为此，只需在SQL Server Management Studio 中打开查询执行计划。打开后，右键单击执行计划，然后单击Showplan比较。
比较不同查询的执行计划 (Comparing the execution plan of different queries)
We can also compare the different queries execution plan with the same Showplan compare. In the below example I am doing a comparison for two sets of queries, that are running Index scan and Index seek operators separately.
我们还可以将不同的查询执行计划与相同的Showplan比较进行比较。 在下面的示例中，我将对分别运行索引扫描和索引查找运算符的两组查询进行比较。
In the below query, I am filtering the records instead of retrieving the complete table:
在下面的查询中，我正在过滤记录，而不是检索完整的表：
Now, we can do the showplan comparison:
现在，我们可以进行显示计划比较：
We can compare both execution plans performing different queries with properties of each operation and then tune our query\workload accordingly. This also helps to design a query in multiple ways and then compare both.
我们可以将执行不同查询的两个执行计划与每个操作的属性进行比较，然后相应地调整我们的query \ workload。 这也有助于以多种方式设计查询，然后将两者进行比较。
比较不同版本\ SQL Server基数之间的执行计划 (Comparing the execution plan between different versions\cardinality of SQL Server)
In my company, we used to upgrade the database to a higher SQL Server version (with better hardware too) but users complained that the query is responding slowly after the upgrade.
在我的公司中，我们曾经将数据库升级到更高SQL Server版本（也具有更好的硬件），但是用户抱怨升级后查询响应缓慢。
We can save the critical, or top resource consuming queries execution plan of the queries before the upgrade and after the upgrade to SQL Server . We can compare the properties of both.
我们可以保存在升级到SQL Server 之前和之后的查询的关键或消耗资源最多的查询的执行计划。我们可以比较两者的属性。
In the below screenshot, I have compared two similar queries but one is running with CardinalityEstimationModelVersion 70 while other is running with CardinalityEstimationModelVersion 130.
在下面的屏幕截图中，我比较了两个类似的查询，但是一个查询正在使用CardinalityEstimationModelVersion 70运行，而另一个查询正在使用CardinalityEstimationModelVersion 130运行。
如何使用Compare Showplan提高查询性能 (How to improve the query performance with the Compare Showplan)
Normally, we are used to test the performance of our query before and after creating any index to see what overall impact that is actually making on the performance of execution plan operators.
通常，我们习惯于在创建任何索引之前和之后测试查询的性能，以了解对执行计划运营商的性能产生的总体影响。
The query that I am using here for the demo is:
我在此处用于演示的查询是：
/****** Script for SelectTopNRows command from SSMS******/SELECTol.[OrderLineID],o.[OrderID],o.ExpectedDeliveryDate ,ol.[StockItemID],ol.[Description],ol.[PickedQuantity],ol.[PickingCompletedWhen],ol.[LastEditedBy],ol.[LastEditedWhen]FROM [WideWorldImporters].[Sales].[OrderLines] OLjoin sales.orders Oon ol.orderid=o.orderidwhere ol.description like 'Tape%'and ol.LastEditedWhen >=getdate()-365and o.ExpectedDeliveryDate >getdate()-180 and o.PickingCompletedWhen >getdate()-180order by OrderLineID
The execution plan for the query is :
该查询的执行计划是：
The execution plan is now showing the index recommendation with overall impact of 72%. Therefore, I created the missing index as per recommendation:
执行计划现在显示索引建议，总体影响为72％。 因此，我根据建议创建了缺失索引：
USE [WideWorldImporters]GOCREATE NONCLUSTERED INDEX [testplan]ON [Sales].[OrderLines] ([Description],[LastEditedWhen])INCLUDE ([OrderLineID],[OrderID],[StockItemID],[PickedQuantity],[PickingCompletedWhen],[LastEditedBy])GO
The execution plan for the same query has been generated and the comparison is as follows:
已生成相同查询的执行计划，比较如下：
After the index creation, query is using the index seek instead of the clustered scan that has a good performance impact in case there is a large number of rows.
创建索引后，如果有大量行，查询将使用索引查找而不是对性能有良好影响的群集扫描。
We can see the overall cost for the clustered index scan is now 8% compared to 88% after creating the recommended index. Furthermore, we can see the impact on using the properties where CPU cost is reduced from 0.307708 to 0.153854
我们可以看到，聚集索引扫描的总成本现在为8％，而创建推荐索引后的总成本为88％。 此外，我们可以看到对使用CPU成本从0.307708降低到0.153854的属性的影响
It also shows the equivalent operator from another execution plan. For example, if we click on index seek operator it automatically selects clustered index scan operator from another execution plan.
它还显示了另一个执行计划中的等效运算符。 例如，如果我们单击索引查找运算符，它将自动从另一个执行计划中选择聚簇索引扫描运算符。
Showplan比较选项 (The Showplan compare options)
When we click on Showplan Compare, along with the execution plan compare, it also opens the Showplan Compare window:
当我们单击Showplan Compare以及执行计划比较时，它还会打开Showplan Compare窗口：
We have seen, in the examples above, that the similar operators are highlighted from both execution plans. We can control this behaviour using the Highlight similar operations option. By default, it is turned on.
在上面的示例中，我们已经看到在两个执行计划中都突出了类似的运算符。 我们可以使用“突出显示类似操作”选项来控制此行为。 默认情况下，它是打开的。
It shows the list of similar operators in the list of a similar area in a compared plan section.
它在比较计划部分的相似区域列表中显示相似运算符的列表。
If we do not want to see the similar operator highlighted, we can simply uncheck the box:
如果我们不想看到类似的运算符突出显示，我们可以简单地取消选中该框：
If we want to highlight operators that are not matching similar segments, we can check the box as shown below:
如果要突出显示与相似句段不匹配的运算符，则可以选中如下所示的框：
结论 (Conclusion)
The Compare Showplan is a useful feature, introduced in SQL Server , which can help in troubleshooting real world scenarios, and in developing queries as well.
Compare Showplan是SQL Server 中引入的一项有用功能，可以帮助解决现实情况，并帮助开发查询。
翻译自: /how-to-compare-query-execution-plans-in-sql-server-/
