Database Administrator always makes an effort to tune SQL Server query performance. The first step in tuning query performance is to analyze the execution plan of a query. Upon some conditions, SQL Server Query Optimizer can create different execution plans. At this point, I would like to add some notes about SQL Server Query Optimizer. SQL Server Query Optimizer is a cost-based optimizer that analyzes execution plans and decides the optimal execution plan for a query. The significant keyword for the SQL Server Query Optimizer is an optimal execution plan which is not necessarily the best execution plan. That’s why, if SQL Server Query Optimizer tries to find out the best execution plan for every query, it takes extra time and it causes damage to SQL Server Engine performance. In SQL Server 2016, Microsoft added a new ability to SQL Server Management Studio, called Compare Showplan. This feature allows us to compare two different execution plans. At the same time, we can use this option offline which means that we don’t need to connect the SQL Server instance. Imagine that you write a query and this query performs well in the TEST environment but in PROD (production environment), it performs very poorly. To handle this issue, we need to compare execution plans. Before this feature, we used to open two SQL Server Management Studios and bring execution plans side by side but this method was very inconvenient.
How to compare two execution plans?
In this demonstration, we will use the AdventureWorks database and compare two execution plans which have different Cardinality Estimation Model Version and detect this difference with Compare Showplan.
At first, we will open a new query window in SQL Server Management Studio and click Include Actual Execution Plan and then execute the following query.
SELECT
soh.[SalesPersonID]
,p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' + p.[LastName] AS [FullName]
,e.[JobTitle]
,st.[Name] AS [SalesTerritory]
,soh.[SubTotal]
,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]
FROM [Sales].[SalesPerson] sp
INNER JOIN [Sales].[SalesOrderHeader] soh
ON sp.[BusinessEntityID] = soh.[SalesPersonID]
INNER JOIN [Sales].[SalesTerritory] st
ON sp.[TerritoryID] = st.[TerritoryID]
INNER JOIN [HumanResources].[Employee] e
ON soh.[SalesPersonID] = e.[BusinessEntityID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = sp.[BusinessEntityID]
In this step, we will save our first execution plan. Right-click anywhere in the execution plan and click Save Execution Plan As and save execution plan as ExecutionPlan_CE140.sqlplan.
Now we will open a new query tab in SQL Server Management Studio and execute the below query. In this query, we will add the FORCE_LEGACY_CARDINALITY_ESTIMATION query hint at the end of the query which forces to use older Cardinality Estimation Model Version.
The task of Cardinality Estimation is to predict how many rows our query will return.
SELECT
soh.[SalesPersonID]
,p.[FirstName] + ' ' + COALESCE(p.[MiddleName], '') + ' ' + p.[LastName] AS [FullName]
,e.[JobTitle]
,st.[Name] AS [SalesTerritory]
,soh.[SubTotal]
,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]
FROM [Sales].[SalesPerson] sp
INNER JOIN [Sales].[SalesOrderHeader] soh
ON sp.[BusinessEntityID] = soh.[SalesPersonID]
INNER JOIN [Sales].[SalesTerritory] st
ON sp.[TerritoryID] = st.[TerritoryID]
INNER JOIN [HumanResources].[Employee] e
ON soh.[SalesPersonID] = e.[BusinessEntityID]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = sp.[BusinessEntityID]
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
We will click Compare Showplan and select previous execution plan which was saved as ExecutionPlan_CE140.sqlplan.
The following image illustrates the first screen of the SQL Server execution compare plan and pink color highlighted areas define similar operations.
If we click any operator in the below or above execution plan screen, SQL Server Management Studio highlights other similar operators. On the right side of the panel, you can find properties and comparison details of properties.
In this step, we will change the ShowPlan Analysis options and will highlight the not matching operator. In the bottom of the screen, we can see the Showplan Analysis panel. If we clear Highlight similar operations and select Highlight operators not matching similar segments, SQL Server Management Studio highlights unmatched operator. After that, click Select operators in the below and above execution plan in the panel. SQL Server Management Studio compares properties of the selected operators and puts inequality signs to the non-identical values.
If we analyze this screen in more detail, the first thing is the Cardinality Estimation Model Version difference. The first query version is 70 and second one is 140. This difference affects Estimated Number of Rows. The main reason that causes the different Estimated Number of Rows is a different version of Cardinality Estimation. Thus, the Cardinality Estimation version directly affects the query estimated metrics. For this query comparison, we can conclude that the query which Cardinality Estimation version is 140 performs better because the estimated number of rows is close to the Actual Number of Rows. This case can be clarified from the below table.
[table id=50 /]
If we want to see execution plans side by side on the same screen, we can click Toggle Splitter Orientation.
Now we will make another demonstration. We will look at the below query and compare execution plans before and after index creation.
When we look at the below query execution plan, it recommends creating a non-clustered index.
SELECT [CarrierTrackingNumber] FROM [Sales].[SalesOrderDetail] WHERE [SalesOrderDetailID]=12
We will apply the recommended index and re-execute the same query.
CREATE NONCLUSTERED INDEX Index_NC ON [Sales].[SalesOrderDetail] ([SalesOrderDetailID]) GO SELECT [CarrierTrackingNumber] FROM [Sales].[SalesOrderDetail] WHERE [SalesOrderDetailID]=12
In this last step, we will compare the execution plans.
In the above image, we can obtain several pieces of information about execution plans. But the major difference is the Logical Operation field. One of them is Index Seek and another one is Index Scan and this operation differentiation leads to dissimilar estimated and actual metric values. Last of all Index Seek operator performs better than Index Scan operator.
Conclusions
As we mentioned in the article, the Compare Showplan feature offers some benefits to Database Developer or Administrator. Some of these can be counted as:
- Simple to compare two execution plans difference.
- Simple to detect query performance problems in different SQL Server Versions.
- Simple to detect query performance problems in different environments.
- Simply clarifies execution plan changes before and after the index creation.
