-------------------------------

-- HOW LOGICAL ARE MY READS? --

-------------------------------

-------------------------------

---------- Ami Levin ----------

-------------------------------

 

USE AdventureWorks;

GO

 

-- Clear the data and plan cache

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE;

GO

 

SELECT C.CustomerID, SOH.SalesOrderID, SOH.OrderDate

FROM   Sales.Customer C

       INNER JOIN

       Sales.SalesOrderHeader SOH

       ON SOH.CustomerID = C.CustomerID

WHERE  C.TerritoryID = 1 AND C.CustomerType = N'S';

GO

 

-- Add the index that was suggested by the query optimizer

CREATE NONCLUSTERED INDEX [IDX1] 

       ON [Sales].[Customer] ([TerritoryID],[CustomerType]) 

       INCLUDE ([CustomerID]);

GO

 

-- Clear the data and plan cache

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE;

GO

 

SELECT C.CustomerID, SOH.SalesOrderID, SOH.OrderDate

FROM   Sales.Customer C

       INNER JOIN

       Sales.SalesOrderHeader SOH

       ON SOH.CustomerID = C.CustomerID

WHERE  C.TerritoryID = 1 AND C.CustomerType = N'S';

GO

 

-- Add the index that was suggested by the 'ami optimizer'

CREATE NONCLUSTERED INDEX [IDX2] 

       ON [Sales].[SalesOrderHeader] ([CustomerID]) 

       INCLUDE ([SalesOrderID], [OrderDate]);

GO

 

-- Clear the data and plan cache

DBCC DROPCLEANBUFFERS;

DBCC FREEPROCCACHE;

GO

 

SELECT C.CustomerID, SOH.SalesOrderID, SOH.OrderDate

FROM   Sales.Customer C

       INNER JOIN

       Sales.SalesOrderHeader SOH

       ON SOH.CustomerID = C.CustomerID

WHERE  C.TerritoryID = 1 AND C.CustomerType = N'S';

GO

 

-- CLEANUP

DROP INDEX [IDX2] ON [Sales].[SalesOrderHeader];

DROP INDEX [IDX1] ON [Sales].[Customer];

GO

 

-- Check the number of pages of the tables

EXEC sp_spaceused 'Sales.Customer'

EXEC sp_spaceused 'Sales.SalesOrderHeader'