--© 2021 | ByrdNest Consulting
--Microsoft SQL Server Optimizer Doesn't Get the Best Query Plan

SELECT @@VERSION
/*
Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64) 
	Nov  2 2020 18:35:09 
	Copyright (C) 2019 Microsoft Corporation
	Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19041: )
*/

--start with clean copy of AdventureWorks2017
USE [master]
GO
ALTER DATABASE [AdventureWorks2017] 
	SET SINGLE_USER WITH ROLLBACK IMMEDIATE			--kick off all users except me
RESTORE DATABASE [AdventureWorks2017] FROM  DISK = N'E:\SQL_Data2019\Backups\AdventureWorks2017.bak' WITH  FILE = 1,  
	MOVE N'AdventureWorks2017' TO N'D:\SQL_Data2019\Data\AdventureWorks2017.mdf',  
	MOVE N'AdventureWorks2017_log' TO N'D:\SQL_Data2019\Logs\AdventureWorks2017_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 10;
ALTER DATABASE [AdventureWorks2017] SET MULTI_USER	--set back to multi-user
GO
ALTER AUTHORIZATION ON DATABASE::AdventureWorks2017 TO sa;	--give ownership to sa; not me
GO
ALTER DATABASE [AdventureWorks2017] SET COMPATIBILITY_LEVEL = 150	-- make it SS2019 compatible; but these scripts will run any version of SS from 2008
GO

USE AdventureWorks2017
GO
--00:00:03


--Query 1; no applicable indexes
SET STATISTICS IO,TIME ON;
DECLARE @StartDate		DATETIME = '1/1/2012';
DECLARE @EndDate		DATETIME = '1/1/2013';
DECLARE @Status			INT = 5;
DECLARE @TerritoryID	INT = 6;			-- get all territories except Canada
SELECT SalesOrderID, RevisionNumber,TerritoryID, ModifiedDate
	FROM Sales.SalesOrderHeader
	WHERE ModifiedDate	>= @StartDate 
	  AND ModifiedDate	<  @EndDate	
	  AND [Status]		= @Status
	  AND TerritoryID   <> @TerritoryID;	
SET STATISTICS IO,TIME OFF;
GO
--there are no non-clustered indexes based on TerritoryID or Status
--Execution plan asks for missing index 
--		CREATE NONCLUSTERED INDEX [<Name of Missing Index] ON Sales.SalesOrderHeaderBig (Status,TerritoryID,ModifiedDate) 
--			INCLUDE (RevisionNumber) 
--Clustered Index Scan; Total Query Plan Cost = 0.54456 {ExecutionPlan.xml yields 8ms actual cpu time}
--689 logical reads, 0ms cpu time, 3345 rows returned from statistics IO,Time ON
--  if you hover over the clustered index scan you can see that there
--    is a Predicate.  Usually when an index is accessed it is done so
--    by a Seek Predicate and/or Predicate operator.  The Seek Predicate
--    operator can use the b-tree order within the actual index b-tree. 
--    The Predicate operator operates after the b-tree is traversed (if
--    it is) and acts as a filter on the Seek Predicate operator result 
--	  set (like a WHERE clause).  Obviously a Seek Predicate should have 
--    better performance than a Predicate.  In this particular query,
--	  note that the Predicate is in same order as the WHERE clause.


--running missing index above (notice all columns in select query {where clause, select clause, joins if applicable}
--	this is a covering index
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_StatusTerritoryIDModifiedDate 
	ON Sales.SalesOrderHeader ([Status],TerritoryID,ModifiedDate)
	INCLUDE ([RevisionNumber])
GO
--00:00:00


--Query 2 (same as above but with recommended index)
SET STATISTICS IO,TIME ON;
DECLARE @StartDate		DATETIME = '1/1/2012';
DECLARE @EndDate		DATETIME = '1/1/2013';
DECLARE @Status			INT = 5;
DECLARE @TerritoryID	INT = 6;			-- get all territories except Canada
SELECT SalesOrderID, RevisionNumber,TerritoryID, ModifiedDate
	FROM Sales.SalesOrderHeader
	WHERE ModifiedDate	>= @StartDate 
	  AND ModifiedDate	<  @EndDate	
	  AND [Status]		= @Status
	  AND TerritoryID   <> @TerritoryID;	
SET STATISTICS IO,TIME OFF;
GO
--non-clustered index seek:  IX_SalesOrderHeader_StatusTerritoryIDModifiedDate;
--	 seek predicates on Status & TerritoryID; notice Status is first in seek parameters
--total query cost = 0.045771
--87 logical reads                
--cpu = 0ms  (acutal cpu = 3ms from executionplan.xml




--so what happens if we change the NC index and flip-flop the order of TerritoryID and ModifiedDate
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_StatusTerritoryIDModifiedDate;
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_StatusModifiedDateTerritoryID			--switch order of TerritoryID & ModifiedDate columns in index definition
	ON Sales.SalesOrderHeader ([Status], ModifiedDate, TerritoryID)
	INCLUDE ([RevisionNumber])
GO

--now rerun query
--Query 3   -- NC index with Status first, then ModifiedDate, then TerrirotyID in index definition
SET STATISTICS IO,TIME ON;
DECLARE @StartDate		DATETIME = '1/1/2012';
DECLARE @EndDate		DATETIME = '1/1/2013';
DECLARE @Status			INT = 5;
DECLARE @TerritoryID	INT = 6;			-- get all territories except Canada
SELECT SalesOrderID, RevisionNumber,TerritoryID, ModifiedDate
	FROM Sales.SalesOrderHeader
	WHERE ModifiedDate	>= @StartDate 
	  AND ModifiedDate	<  @EndDate	
	  AND [Status]		= @Status
	  AND TerritoryID   <> @TerritoryID;	
SET STATISTICS IO,TIME OFF;
GO
--non-clustered index seek:  IX_SalesOrderHeader_StatusModifiedDateTerritoryID;
--	 seek predicates on Status then ModifiedDate; Predicate on TerritoryID
--total query cost = 0.0200804	
--14 logical reads            
--cpu = 0ms (but from query plan xml = 2ms).


--now let's look at NC index on Status, but TerritoryID is in INCLUDE clause
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_StatusModifiedDateTerritoryID;
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_StatusModifiedDate			--move TerritoryID to INCLUDE clause
	ON Sales.SalesOrderHeader ([Status],ModifiedDate)
	INCLUDE ([RevisionNumber],TerritoryID)
GO

--now rerun query
--Query 4   -- NC index with Status first, then ModifiedDate, then TerritoryID in INCLUDE clause of index definition
SET STATISTICS IO,TIME ON;
DECLARE @StartDate		DATETIME = '1/1/2012';
DECLARE @EndDate		DATETIME = '1/1/2013';
DECLARE @Status			INT = 5;
DECLARE @TerritoryID	INT = 6;			-- get all territories except Canada
SELECT SalesOrderID, RevisionNumber,TerritoryID, ModifiedDate
	FROM Sales.SalesOrderHeader
	WHERE ModifiedDate	>= @StartDate 
	  AND ModifiedDate	<  @EndDate	
	  AND [Status]		= @Status
	  AND TerritoryID   <> @TerritoryID;	
SET STATISTICS IO,TIME OFF;
GO
--non-clustered index seek:  IX_SalesOrderHeader_StatusModifiedDate;
--	 seek predicates on Status then ModifiedDate; Predicate on TerritoryID
--total query cost = 0.0200804	
--14 logical reads            
--cpu = 0ms (but from query plan xml = 2ms).

/***********************************************************************************

Notice that the performance is almost identical for scenarios 3 & 4.  Since TerritoryID
is in the Predicate then it really doesn't matter whether it comes from the index
definition columns or the INCLUDE clause since the Predicate is essentially a result set
scan from the Seek Predicate.  For a very large table though, by having TerritoryID in the 
INCLUDE clause you might get a slightly lower logical read count since TerritoryID would 
only be carried in the leaf level and not the root and intermediate levels of the B-Tree.  

**************************************************************************************/


--Cleanup
DROP INDEX Sales.SalesOrderHeader.IX_SalesOrderHeader_StatusModifiedDate;
GO