Compare Plans with SSMS 2016+

Microsoft has been making great strides with their database management platform lately. One really great improvement in their management tool, SQL Server Management Studio, is the “Compare Plans” feature. This allows visually comparing plans to see how they vary.

As an example, take the following two queries, which SQL Server auto-parameterizes. Since both parameter values vary widely, SQL Server creates two different plans for what are, at first glance, the same query. Using the “Compare Plans” feature allows us to pretty quickly understand the details about why SQL Server has different plans for them:

SELECT *
FROM dbo.OnDiskNumbers odn
WHERE odn.Number > 9999;
SELECT *
FROM dbo.OnDiskNumbers odn
WHERE odn.Number > 9999999;

The actual execution plans for the two queries are:

compare plans - #1
compare plans - #2

The dbo.OnDiskNumbers table is a typical “numbers” table:

IF OBJECT_ID('dbo.OnDiskNumbers') IS NOT NULL
DROP TABLE dbo.OnDiskNumbers;

CREATE TABLE dbo.OnDiskNumbers
(
	Number int NOT NULL
		CONSTRAINT PK_OnDiskNumbers
		PRIMARY KEY CLUSTERED
		WITH (
			  DATA_COMPRESSION = PAGE
			, ALLOW_ROW_LOCKS = ON
			, ALLOW_PAGE_LOCKS = ON
			, FILLFACTOR = 100
			, PAD_INDEX = OFF
		)
)
ON [PRIMARY];

CREATE NONCLUSTERED COLUMNSTORE INDEX ixcs_OnDiskNumbers
ON dbo.OnDiskNumbers(Number)
WITH (
        DATA_COMPRESSION = COLUMNSTORE
	, COMPRESSION_DELAY = 1 MINUTE
     )
ON [PRIMARY];

;WITH num AS (
	SELECT *
	FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))n(val)
)
INSERT INTO dbo.OnDiskNumbers (Number)
SELECT rn = ROW_NUMBER() OVER (ORDER BY n1.val)
FROM num n1
	CROSS JOIN num n2
	CROSS JOIN num n3
	CROSS JOIN num n4
	CROSS JOIN num n5
	CROSS JOIN num n6
	CROSS JOIN num n7;

The table has 10,000,000 rows. I’ve create a non-clustered columnstore index on the table, which I’ll talk about in a future post. I’ve included it here because it provides a succinct difference in the two plans.

To compare the plans visually, side-by-side, you need to save the first plan by right-clicking on the plan window, clicking “Save Execution Plan As…”, and specifying a filename. Next, right-click on the plan window, and choose “Compare Showplan”:

At this point, select the saved Execution Plan file you saved above, which will result in the Execution Plan window switching to the comparison view, which includes both graphical execution plans:

If you have the “Properties” window open, you can compare details about the plans, such as Actual Number of Rows, Memory Grants, etc:

These details allow us to notice the vastly different number of rows that are returned by each query, and shows how the access plans for each differ in a way that quickly pinpoints the differences.