Geek Sync | Performance Tuning with Execution Plans

Using Execution Plans to performance tune SQL Server queries can be a great starting point to help a database application run optimally. Execution Plans have been around for some time now and Microsoft has improved the interface and extra tools with every release.

Join IDERA and Thomas LeBlanc on Wednesday, July 11 at 11 AM CT as he walks through the history of execution plans. The progression will be to Scans and Seeks while dipping into Loops and Lookups. These are the basic skills needed to start using execution plans. We will conclude the session with the new features released with SQL Server 2016 and 2017.

About Thomas: Thomas LeBlanc (Microsoft Data Platform MVP) is a Data Warehouse and Business Intelligence Architect in Baton Rouge, LA and uses his decades of experience in IT to help develop OLAP database for end-to-end dimensional data marts with SSIS, SSAS, Power BI, and Excel. He also assists with OLTP systems designing and tuning normalized databases for high-performing T-SQL. Thomas has been blessed to speak at PASS Summit (2011-17), VSLive (2018), Live! 360 (2015-17), IT/Dev Connections (2015 & 2017) and SQLSaturday events (2011-18).

You can watch this Geek Sync recording here. You can view Thomas' slides here.

You can view Thomas' scripts below.

0-DropIndexes.sql
USE AdvWrk16
GO

/*  Delete DROP indexes */
IF  EXISTS 
	(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseLog]') 
		AND name = N'PK_DatabaseLog')
	ALTER TABLE [dbo].[DatabaseLog] DROP CONSTRAINT [PK_DatabaseLog]
GO


DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
-- Cleanup database

IF  EXISTS 
	(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') 
		AND name = N'IX_SalesOrderHeader_CustomerID_IncludeAcctNumOrderDate')
DROP INDEX [IX_SalesOrderHeader_CustomerID_IncludeAcctNumOrderDate] ON [Sales].[SalesOrderHeader]
GO

IF  EXISTS 
	(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') 
		AND name = N'idxSalesOrderHeader_CustomerID_IncludeAccountNumberOrderDate')
DROP INDEX [idxSalesOrderHeader_CustomerID_IncludeAccountNumberOrderDate] ON [Sales].[SalesOrderHeader]
GO
IF  EXISTS 
	(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') 
		AND name = N'idxSalesOrderHeader_ShipDate_IncludeOrderDate')
DROP INDEX [idxSalesOrderHeader_ShipDate_IncludeOrderDate] ON [Sales].[SalesOrderHeader]
GO
IF  EXISTS 
	(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') 
		AND name = N'idxSalesOrderHeader_ShipDate')
DROP INDEX [idxSalesOrderHeader_ShipDate] ON [Sales].[SalesOrderHeader]
GO
IF  EXISTS 
	(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') 
		AND name = N'idxSalesOrderHeader_ShipDate_IncludeSalesOrderIDOrderDate')
DROP INDEX [idxSalesOrderHeader_ShipDate_IncludeSalesOrderIDOrderDate] ON [Sales].[SalesOrderHeader]
GO
IF  EXISTS 
	(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderHeader]') 
		AND name = N'idxSalesOrderHeader_OrderDate_IncludeShipDate')
DROP INDEX [idxSalesOrderHeader_OrderDate_IncludeShipDate] ON [Sales].[SalesOrderHeader]
GO



IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Production].[bigTransactionHistory]') 
		AND name = N'idxbigTransactionHistory_ProductID_IncludeTransDateActualCost')
DROP INDEX [idxbigTransactionHistory_ProductID_IncludeTransDateActualCost] ON [Production].[bigTransactionHistory]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Production].[bigTransactionHistory]') 
		AND name = N'idxbigTransactionHistory_ProductIDActualCost')
DROP INDEX [idxbigTransactionHistory_ProductIDActualCost] ON [Production].[bigTransactionHistory]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Production].[bigTransactionHistory]') 
		AND name = N'idxbigTransactionHistory_TranDate_IncludeProdIDActCost')
DROP INDEX [idxbigTransactionHistory_TranDate_IncludeProdIDActCost] ON [Production].[bigTransactionHistory]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Production].[bigTransactionHistory]') 
		AND name = N'idxbigTransactionHistory_ActualCost_IncludeProductID')
DROP INDEX [idxbigTransactionHistory_ActualCost_IncludeProductID] ON [Production].[bigTransactionHistory]
GO


IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]') AND name = N'idxDeleteAfteruse')
DROP INDEX [idxDeleteAfteruse] ON [Sales].[SalesOrderDetail]
GO


IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Production].[mediumTransactionHistory]') 
	AND name = N'idxmediumTransactionHistory_ProductID_IncludeTransDateActualCost')
DROP INDEX idxmediumTransactionHistory_ProductID_IncludeTransDateActualCost ON [Production].[mediumTransactionHistory]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Production].[mediumTransactionHistory]') 
	AND name = N'idxmediumTransactionHistory_ActualCode_Include_ProductID')
DROP INDEX idxmediumTransactionHistory_ActualCode_Include_ProductID ON [Production].[mediumTransactionHistory]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Production].[mediumTransactionHistory]') 
	AND name = N'idxmediumTransactionHistory_ProductIDActualCost')
DROP INDEX [idxmediumTransactionHistory_ProductIDActualCost] ON [Production].[mediumTransactionHistory]
GO

USE AdventureWorksDW2016
GO


IF  EXISTS 
	(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[dimCustomer]') 
		AND name = N'idxDimcustomerDelete')
DROP INDEX [idxDimcustomerDelete] ON [dbo].[dimCustomer]
GO

1-ShowPlan_Text.sql
USE AdvWrk16
GO

SET SHOWPLAN_TEXT ON
GO
-- Select all columns
SELECT [DatabaseLogID]      ,[PostTime]      ,[DatabaseUser]      ,[Event]
      ,[Schema]      ,[Object]      ,[TSQL]      ,[XmlEvent]
  FROM [dbo].[DatabaseLog]
GO
SELECT [SalesOrderID]      ,[RevisionNumber]      ,[OrderDate]      ,[DueDate]      ,[ShipDate]
      ,[Status]      ,[SubTotal]      ,[TaxAmt]      ,[Freight]      ,[TotalDue]
  FROM [Sales].[SalesOrderHeader]
  WHERE SubTotal < 100

SET SHOWPLAN_TEXT OFF
GO


-- Display Exstimated Execution Plan
SELECT TOP 100 *
	FROM Sales.SalesOrderHeader


-- Include Execution Plan
-- run and look at messages

SELECT TOP 100 *
	FROM Sales.SalesOrderHeader

-- show difference in total versus IO cost
SELECT TOP 100 *
	FROM Sales.SalesOrderHeader
SELECT *
	FROM Sales.SalesOrderHeader


-- Logical and Physical IO calls (per Data Page 8k data page
SET STATISTICS IO ON
GO

SELECT TOP 100 *
	FROM Sales.SalesOrderHeader
SELECT *
	FROM Sales.SalesOrderHeader



-- Time in CPU and Actual time to execute
SET STATISTICS TIME ON
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT TOP 100 *
	FROM Sales.SalesOrderHeader

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SELECT TOP 100 *
	FROM Sales.SalesOrderHeader
SELECT *
	FROM Sales.SalesOrderHeader


	-- run twice

2-TableScan_CIScan_NCScan.sql
USE AdvWrk16
GO
-- use where clause
SET STATISTICS IO ON
GO

DBCC FREEPROCCACHE
GO


-- Drop Primary key Cluster and nonlcustered if exists

/****** Object:  Index [idxDatabaseLog_DatabaseUser]    Script Date: 10/02/2010 11:10:08 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseLog]') AND name = N'idxDatabaseLog_DatabaseUser')
DROP INDEX [idxDatabaseLog_DatabaseUser] ON [dbo].[DatabaseLog] WITH ( ONLINE = OFF )
GO
/****** Object:  Index [PK_DatabaseLog]    Script Date: 10/02/2010 11:01:57 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseLog]') AND name = N'PK_DatabaseLog_DatabaseLogID')
ALTER TABLE [dbo].[DatabaseLog] DROP CONSTRAINT [PK_DatabaseLog_DatabaseLogID]
GO
/****** Object:  Index [idxDatabaseLog_DatabaseUserScehmaObject]    Script Date: 10/02/2010 11:10:37 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseLog]') AND name = N'idxDatabaseLog_DatabaseUserScehmaObject')
DROP INDEX [idxDatabaseLog_DatabaseUserScehmaObject] ON [dbo].[DatabaseLog] WITH ( ONLINE = OFF )
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseLog]') 
	AND name = N'idxDatabaseLog_DatabaseUser_CoverColumns')
DROP INDEX [idxDatabaseLog_DatabaseUser_CoverColumns] ON [dbo].[DatabaseLog] WITH ( ONLINE = OFF )
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Person].[Address]') 
	AND name = N'idxPersonAddress_City')
DROP INDEX [idxPersonAddress_City] ON [Person].[Address] WITH ( ONLINE = OFF )
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[HumanResources].[Employee]') 
	AND name = N'idxEmployeeTitleContactID')
DROP INDEX [idxEmployeeTitleContactID] ON [HumanResources].[Employee] WITH ( ONLINE = OFF )
GO





-- 1 display estimated plan
-- 2 - Graphical plan, then flow

-- Select all columns
SELECT [DatabaseLogID]      ,[PostTime]      ,[DatabaseUser]      ,[Event]
      ,[Schema]      ,[Object]      ,[TSQL]      ,[XmlEvent]
  FROM [dbo].[DatabaseLog]



  -- demo table scan with no where
-- then with where

SELECT [DatabaseLogID]      ,[PostTime]      ,[DatabaseUser]      ,[Event]
      ,[Schema]      ,[Object]      ,[TSQL]      ,[XmlEvent]
  FROM [dbo].[DatabaseLog]
WHERE [DatabaseUser] = N'sys'


-- add primary key clusters

ALTER TABLE [dbo].[DatabaseLog] 
	ADD  CONSTRAINT [PK_DatabaseLog_DatabaseLogID] 
		PRIMARY KEY CLUSTERED 
		([DatabaseLogID] ASC) 

-- Select all columns
SELECT [DatabaseLogID]      ,[PostTime]      ,[DatabaseUser]      ,[Event]
      ,[Schema]      ,[Object]      ,[TSQL]      ,[XmlEvent]
  FROM [dbo].[DatabaseLog]

  -- Cost 0.061

  SELECT [DatabaseLogID]      ,[PostTime]      ,[DatabaseUser]      ,[Event]
      ,[Schema]      ,[Object]      ,[TSQL]      ,[XmlEvent]
  FROM [dbo].[DatabaseLog]
WHERE [DatabaseUser] = N'sys'


-- NonClustered Index Scan
  CREATE NONCLUSTERED INDEX [idxDatabaseLog_DatabaseUserScehmaObject] 
	ON [dbo].[DatabaseLog]
(	[DatabaseUser], [Schema], [Object] )

-- what is the cost
 SELECT [DatabaseLogID], [DatabaseUser], [Schema], [Object]     
  FROM [dbo].[DatabaseLog]
  WHERE [DatabaseUser] = N'dbo'

 SELECT [DatabaseLogID], [DatabaseUser], [Schema], [Object]     
  FROM [dbo].[DatabaseLog]
 WHERE [DatabaseUser] = N'sys'



   -- pause and look at the properties
			-- Actual versus estimate
			-- Object, Object List and Seek Predicate
			-- 

-- see COUNT for SYS versus DBO
SELECT [DatabaseUser], COUNT(1)
	FROM [dbo].[DatabaseLog]
	GROUP BY [DatabaseUser]

-- like
 SELECT [DatabaseLogID], [DatabaseUser], [Schema], [Object]     
  FROM [dbo].[DatabaseLog]
 WHERE [DatabaseUser] LIKE '%sys'

 SELECT [DatabaseLogID], [DatabaseUser], [Schema], [Object]     
  FROM [dbo].[DatabaseLog]
 WHERE [DatabaseUser] LIKE 'sy%'


-- test 3 column query
SELECT [DatabaseUser], [Schema], [Object]
  FROM [dbo].[DatabaseLog]

-- Why a index seek even though DatabaseLogID not in index (create statement)
SELECT  [Schema], [Object]
  FROM [dbo].[DatabaseLog]
WHERE [DatabaseUser] = 'sys'

SELECT [DatabaseLogID], [Schema], [Object]
  FROM [dbo].[DatabaseLog]
WHERE [DatabaseUser] = 'sys'

/*
ALTER TABLE [dbo].[DatabaseLog] 
	ADD  CONSTRAINT [PK_DatabaseLog_DatabaseLogID] 
		PRIMARY KEY CLUSTERED 
		([DatabaseLogID] ASC) 

  CREATE NONCLUSTERED INDEX [idxDatabaseLog_DatabaseUserScehmaObject] 
	ON [dbo].[DatabaseLog]
(	[DatabaseUser], [Schema], [Object] )

*/


DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 

-- See Index Scan 
	-- Then create covering index
SELECT [DatabaseLogID], [PostTime], [DatabaseUser], [Event], [Schema], [Object]      
  FROM [dbo].[DatabaseLog]
WHERE [DatabaseUser] = 'sys'


-- look at index with no include columns
CREATE NONCLUSTERED INDEX [idxDatabaseLog_DatabaseUser_CoverColumns] 
	ON [dbo].[DatabaseLog]
(	[DatabaseUser] ASC )
INCLUDE ([PostTime], [Event], [Schema], [Object])


-- Remove Key Lookup with Covering Index
SELECT [DatabaseLogID], [PostTime], [DatabaseUser], [Event], [Schema], [Object]      
  FROM [dbo].[DatabaseLog]
WHERE [DatabaseUser] = 'sys'




-- Remove Cluster index and show RID lookup
ALTER TABLE [dbo].[DatabaseLog] DROP CONSTRAINT [PK_DatabaseLog_DatabaseLogID]

-- test query plan - AND LOB lookups
SELECT [DatabaseLogID], [PostTime], [Schema], [Object]      ,[TSQL]
  FROM [dbo].[DatabaseLog]
WHERE [DatabaseUser] = 'sys'


3-loops.sql
USE AdvWrk16
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

-- Nested Loop
-- Simple Nested Loop
-- Seek on cust CustomerID, seek on soh CustomerID
-- No clustered index seek becuase clustered index column(s) is in the non-clustered index
SELECT cust.CustomerID, soh.SalesOrderID
	FROM Sales.Customer cust
		INNER JOIN Sales.SalesOrderHeader soh
			ON soh.CustomerID = cust.CustomerID
	WHERE cust.CustomerID = 11091


-- Add additional soh column needs lookup
	-- Added an Index Seek with Key Lookup
SELECT cust.CustomerID, soh.SalesOrderID, soh.AccountNumber, soh.OrderDate
	FROM Sales.Customer cust
		INNER JOIN Sales.SalesOrderHeader soh
			ON soh.CustomerID = cust.CustomerID
	WHERE cust.CustomerID = 11091

/* - remove the second nested loop with covering index
DROP INDEX [IX_SalesOrderHeader_CustomerID_IncludeAcctNumOrderDate] ON [Sales].[SalesOrderHeader]
CREATE NONCLUSTERED INDEX [IX_SalesOrderHeader_CustomerID_IncludeAcctNumOrderDate] 
    ON [Sales].[SalesOrderHeader]
    ( [CustomerID] ASC )
	INCLUDE (AccountNumber, OrderDate)
*/

-- Merge Join
SELECT  P.Name, total_qty = SUM(I.Quantity)
	FROM Production.Product P
		JOIN Production.ProductInventory I  ON  I.ProductID = P.ProductID
	GROUP BY P.Name
 --OPTION  (RECOMPILE);

-- Add some columns
SELECT  P.Name, I.LocationID, total_qty = SUM(I.Quantity)
	FROM Production.Product P
		JOIN Production.ProductInventory I  ON  I.ProductID = P.ProductID
	GROUP BY P.Name, I.LocationID


-- More than 2 tables joined together
-- HASH Match
-- Identify the most costly  operations
SELECT e.JobTitle, a.[City], 
		P.[LastName] + ', ' + P.[FirstName] AS EmployeeName
	FROM [HumanResources].[Employee] e
		INNER JOIN [Person].[BusinessEntity] be ON be.BusinessEntityID = e.BusinessEntityID
			INNER JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = be.BusinessEntityID
				INNER JOIN [Person].[Address] a ON [a].[AddressID] = [bea].[AddressID]
		INNER JOIN [Person].[Person] P ON P.BusinessEntityID = e.BusinessEntityID

-- example of HASH with Products, one equals the other IN
-- HASH
SELECT p.ProductID, sod.SalesOrderID, sod.OrderQty
	FROM Production.Product p
		INNER JOIN Sales.SalesOrderDetail sod
			ON sod.ProductID = p.ProductID
	-- were clause changes to nested
	WHERE p.ProductID = 870
	--OPTION  (RECOMPILE);

-- Add a second product
-- Distribution of statistics gives optimizer an idea and what to do
SELECT p.ProductID, sod.SalesOrderID, sod.OrderQty
	FROM Production.Product p
		INNER JOIN Sales.SalesOrderDetail sod
			ON sod.ProductID = p.ProductID
	WHERE p.ProductID IN ( 898, 871)
OPTION  (RECOMPILE);

	SELECT p.ProductID, sod.SalesOrderID, sod.OrderQty
	FROM Production.Product p
		INNER JOIN Sales.SalesOrderDetail sod
			ON sod.ProductID = p.ProductID
	WHERE 1=1
	  AND (p.ProductID = 898
	  OR p.ProductID = 871)
OPTION  (RECOMPILE);


-- smaller set
SELECT p.ProductID, sod.SalesOrderID, sod.OrderQty
	FROM Production.Product p
		INNER JOIN Sales.SalesOrderDetail sod
			ON sod.ProductID = p.ProductID
	WHERE p.ProductID IN ( 898, 890)
OPTION  (RECOMPILE);

4-InsertUpdate.sql
-- Insert that has to update indexes
USE AdvWrk16
GO

/****** Object:  Index [idxDatabaseLog_DatabaseUser]    Script Date: 10/02/2010 11:10:08 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseLog]') AND name = N'idxDatabaseLog_DatabaseUser')
DROP INDEX [idxDatabaseLog_DatabaseUser] ON [dbo].[DatabaseLog] WITH ( ONLINE = OFF )
GO
/****** Object:  Index [PK_DatabaseLog]    Script Date: 10/02/2010 11:01:57 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseLog]') AND name = N'PK_DatabaseLog_DatabaseLogID')
ALTER TABLE [dbo].[DatabaseLog] DROP CONSTRAINT [PK_DatabaseLog_DatabaseLogID]
GO
/****** Object:  Index [idxDatabaseLog_DatabaseUserScehmaObject]    Script Date: 10/02/2010 11:10:37 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseLog]') AND name = N'idxDatabaseLog_DatabaseUserScehmaObject')
DROP INDEX [idxDatabaseLog_DatabaseUserScehmaObject] ON [dbo].[DatabaseLog] WITH ( ONLINE = OFF )
GO
/****** Object:  Index [idxDatabaseLog_DatabaseUserScehmaObject]    Script Date: 10/02/2010 11:10:37 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[DatabaseLog]') 
	AND name = N'idxDatabaseLog_DatabaseUser_CoverColumns')
DROP INDEX [idxDatabaseLog_DatabaseUser_CoverColumns] ON [dbo].[DatabaseLog] WITH ( ONLINE = OFF )
GO



SET STATISTICS IO ON
SET STATISTICS TIME ON
GO

INSERT INTO [dbo].[DatabaseLog]
	([PostTime], [DatabaseUser], [Event] ,[Schema] ,[Object]
      ,[TSQL], [XmlEvent])
 VALUES (GETDATE(), 'dbo', 'CRATE STATISTICS', 'Sales', 'SalesOrderHeader', 'EXECUTE...', '')
GO


-- Table 'DatabaseLog'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Cost 0.0103

-- add back indexes
-- add indexes
ALTER TABLE [dbo].[DatabaseLog] 
	ADD  CONSTRAINT [PK_DatabaseLog] 
		PRIMARY KEY CLUSTERED 
		([DatabaseLogID] ASC) 
GO
CREATE NONCLUSTERED INDEX [idxDatabaseLog_DatabaseUserScehmaObject] 
	ON [dbo].[DatabaseLog]
(	[DatabaseUser], [Schema], [Object] )
GO
CREATE NONCLUSTERED INDEX [idxDatabaseLog_DatabaseUser] 
	ON [dbo].[DatabaseLog]
(	[DatabaseUser] ASC )
GO
CREATE NONCLUSTERED INDEX [idxDatabaseLog_DatabaseUser_CoverColumns] 
	ON [dbo].[DatabaseLog]
(	[DatabaseUser] ASC )
INCLUDE ([Schema], [Object])
GO


INSERT INTO [dbo].[DatabaseLog]
	([PostTime], [DatabaseUser], [Event] ,[Schema] ,[Object]
      ,[TSQL], [XmlEvent])
 VALUES (GETDATE(), 'dbo', 'CRATE STATISTICS', 'Sales', 'SalesOrderHeader', 'EXECUTE...', '')
GO

-- Table 'DatabaseLog'. Scan count 0, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- cost 0.04



-- Update statement
BEGIN TRANSACTION

UPDATE [dbo].[DatabaseLog]
	SET [DatabaseUser] = 'dbo'
	WHERE [DatabaseUser] = 'sys'
	
-- ROLLBACK TRANSACTION

4a-ScalerSortsAggregated.sql
USE Adv14
GO

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO


-- Scaler Compute LName + FName
-- CTE
;
WITH EmpAdd (  Title, EmployeeID, AddressID, City )
 AS
	( SELECT e.JobTitle, e.BusinessEntityID AS EmployeeID, a.AddressID, a.City
		FROM [HumanResources].[Employee] e
			INNER JOIN [Person].[BusinessEntity] be ON be.BusinessEntityID = e.BusinessEntityID
				INNER JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = be.BusinessEntityID
					INNER JOIN [Person].[Address] a ON [a].[AddressID] = [bea].[AddressID] )
SELECT e.[Title], e.[City],
		P.[LastName] + ', ' + P.[FirstName] AS EmployeeName
	FROM EmpAdd e
			INNER JOIN [Person].[Person] P ON P.BusinessEntityID = e.EmployeeID
-- Add Order by to show SORT
 ORDER BY City



-- another simple sort
SELECT ProductID, Shelf, Bin, Quantity
FROM [Production].[ProductInventory]
ORDER BY [Shelf]

-- Stream Aggregate
SELECT  P.Name, total_qty = SUM(I.Quantity), Avg_qty = AVG(I.Quantity)
	FROM Production.Product P
		JOIN Production.ProductInventory I  ON  I.ProductID = P.ProductID
	GROUP BY P.Name


5-Parallelism.sql
USE AdvWrk16
GO

EXEC sys.sp_configure N'cost threshold for parallelism', N'5'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'2'
GO
RECONFIGURE WITH OVERRIDE
GO


DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS 

;
With CTETerritory
As
(   Select cr.Name as CountryName, CustomerID, 
                Sum(TotalDue) As TotalAmt
   From Sales.SalesOrderHeader soh 
	  inner join Sales.SalesTerritory ter on soh.TerritoryID=ter.TerritoryID
   inner join Person.CountryRegion cr  on cr.CountryRegionCode=ter.CountryRegionCode
   Group By cr.Name, CustomerID
)
Select *, Rank() Over (Order by TotalAmt DESC) as OverallRank,
Rank() Over
     (Partition By CountryName Order By TotalAmt DESC,
            CustomerID DESC) As NationalRank
From CTETerritory
   OPTION (MAXDOP 1) 

EXEC sys.sp_configure N'cost threshold for parallelism', N'7'
GO
EXEC sys.sp_configure N'max degree of parallelism', N'4'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'cost threshold for parallelism', N'5'
GO
RECONFIGURE WITH OVERRIDE
GO

6-LiveQuery.sql
use AdventureWorksDW2016
go

DBCC DropCleanBuffers
go


use AdventureWorksDW2016
go
SELECT [FactSalesSummary].[SalesOrderNumber] AS [FactSalesSummarySalesOrderNumber0_0],[FactSalesSummary].[ProductKey] AS [FactSalesSummaryProductKey0_1],[FactSalesSummary].[PromotionKey] AS [FactSalesSummaryPromotionKey0_2],[FactSalesSummary].[SalesTerritoryKey] AS [FactSalesSummarySalesTerritoryKey0_3],[FactSalesSummary].[SalesChannel] AS [FactSalesSummarySalesChannel0_4],[FactSalesSummary].[CurrencyKey] AS [FactSalesSummaryCurrencyKey0_5],[FactSalesSummary].[OrderDateKey] AS [FactSalesSummaryOrderDateKey0_6],[FactSalesSummary].[ShipDateKey] AS [FactSalesSummaryShipDateKey0_7],[FactSalesSummary].[DueDateKey] AS [FactSalesSummaryDueDateKey0_8]
  FROM (		
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, NULL AS CustomerKey, EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      'Reseller' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactResellerSales 
UNION
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, NULL AS ResellerKey, CustomerKey, NULL AS EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      'Internet' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactInternetSales ) AS [FactSalesSummary] 
ORDER BY [FactSalesSummary].[SalesOrderNumber]ASC, SalesTerritoryKey

GO

-- really bad

use AdvWrk16
go

SELECT * FROM Production.TransactionHistory th
INNER JOIN Production.TransactionHistoryArchive tha ON th.Quantity = tha.Quantity

7-QueryStore.sql
USE AdventureWorksDW2016
GO

/*
USE [master]
GO
ALTER DATABASE [Adv14DW] 
	SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
GO
USE [master]
GO
ALTER DATABASE [AdvQS14DW] SET QUERY_STORE = OFF
GO
*/
/****** Object:  Index [nc_CS_FactIntersale]    Script Date: 12/4/2016 12:24:26 PM ******/

DROP INDEX IF EXISTS [nc_CS_FactIntersale] ON [dbo].[FactInternetSales]
GO

/****** Object:  Index [nc_CS_FactIntersale]    Script Date: 12/4/2016 12:24:26 PM ******/
CREATE NONCLUSTERED COLUMNSTORE INDEX [nc_CS_FactIntersale] ON [dbo].[FactInternetSales]
(
	[ProductKey],	[OrderDateKey],	[DueDateKey],	[ShipDateKey],	[CustomerKey],	[PromotionKey],	[CurrencyKey],
	[SalesTerritoryKey],	[SalesOrderNumber],	[SalesOrderLineNumber],	[RevisionNumber],	[OrderQuantity],	[UnitPrice],
	[ExtendedAmount],	[UnitPriceDiscountPct],	[DiscountAmount],	[ProductStandardCost],	[TotalProductCost],	[SalesAmount],
	[TaxAmt],	[Freight],	[CarrierTrackingNumber],	[CustomerPONumber],	[OrderDate],	[DueDate],	[ShipDate]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]
GO

SELECT [FactSalesSummary].[SalesOrderNumber] AS [FactSalesSummarySalesOrderNumber0_0],[FactSalesSummary].[ProductKey] AS [FactSalesSummaryProductKey0_1],[FactSalesSummary].[PromotionKey] AS [FactSalesSummaryPromotionKey0_2],[FactSalesSummary].[SalesTerritoryKey] AS [FactSalesSummarySalesTerritoryKey0_3],[FactSalesSummary].[SalesChannel] AS [FactSalesSummarySalesChannel0_4],[FactSalesSummary].[CurrencyKey] AS [FactSalesSummaryCurrencyKey0_5],[FactSalesSummary].[OrderDateKey] AS [FactSalesSummaryOrderDateKey0_6],[FactSalesSummary].[ShipDateKey] AS [FactSalesSummaryShipDateKey0_7],[FactSalesSummary].[DueDateKey] AS [FactSalesSummaryDueDateKey0_8]
  FROM (		
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, NULL AS CustomerKey, EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      'Reseller' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactResellerSales 
UNION
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, NULL AS ResellerKey, CustomerKey, NULL AS EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      'Internet' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactInternetSales ) AS [FactSalesSummary] 
ORDER BY [FactSalesSummary].[SalesOrderNumber]ASC, SalesTerritoryKey



--- compare plans after drop
DROP INDEX IF EXISTS [nc_CS_FactIntersale] ON [dbo].[FactInternetSales]
GO


SELECT [FactSalesSummary].[SalesOrderNumber] AS [FactSalesSummarySalesOrderNumber0_0],[FactSalesSummary].[ProductKey] AS [FactSalesSummaryProductKey0_1],[FactSalesSummary].[PromotionKey] AS [FactSalesSummaryPromotionKey0_2],[FactSalesSummary].[SalesTerritoryKey] AS [FactSalesSummarySalesTerritoryKey0_3],[FactSalesSummary].[SalesChannel] AS [FactSalesSummarySalesChannel0_4],[FactSalesSummary].[CurrencyKey] AS [FactSalesSummaryCurrencyKey0_5],[FactSalesSummary].[OrderDateKey] AS [FactSalesSummaryOrderDateKey0_6],[FactSalesSummary].[ShipDateKey] AS [FactSalesSummaryShipDateKey0_7],[FactSalesSummary].[DueDateKey] AS [FactSalesSummaryDueDateKey0_8]
  FROM (		
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, NULL AS CustomerKey, EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      'Reseller' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactResellerSales 
UNION
SELECT     ProductKey, OrderDateKey, DueDateKey, ShipDateKey, NULL AS ResellerKey, CustomerKey, NULL AS EmployeeKey, PromotionKey, CurrencyKey, 
                      SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
                      DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, 
                      'Internet' AS SalesChannel, CONVERT(CHAR(10), SalesOrderNumber) + 'Line ' + CONVERT(CHAR(4), SalesOrderLineNumber) AS SalesOrderDesc
FROM         FactInternetSales ) AS [FactSalesSummary] 
ORDER BY [FactSalesSummary].[SalesOrderNumber]ASC, SalesTerritoryKey

ExecPlansBasics.ssms_suo

ExecPlansBasics.ssmssln

ExecPlansBasics.ssmssqlproj