USE [AdventureWorksDW2012];
GO


DBCC FREEPROCCACHE;
GO

-- Retrieved from cache?
SELECT     p.ProductLine,
           SUM(f.SalesAmount) AS TotalSalesAmount
FROM [dbo].[FactInternetSales] AS f
INNER JOIN [dbo].[DimProduct] AS p ON
     f.ProductKey = p.ProductKey
GROUP BY p.ProductLine
ORDER BY p.ProductLine;
GO

-- Retrieved from cache?
EXEC sp_recompile 'FactInternetSales';

SELECT     p.ProductLine,
           SUM(f.SalesAmount) AS TotalSalesAmount
FROM [dbo].[FactInternetSales] AS f
INNER JOIN [dbo].[DimProduct] AS p ON
     f.ProductKey = p.ProductKey
GROUP BY p.ProductLine
ORDER BY p.ProductLine;
GO

-- Retrieved from Cache?
SELECT     p.ProductLine,
           SUM(f.SalesAmount) AS TotalSalesAmount
FROM [dbo].[FactInternetSales] AS f
INNER JOIN [dbo].[DimProduct] AS p ON
     f.ProductKey = p.ProductKey
GROUP BY p.ProductLine
ORDER BY p.ProductLine
OPTION (RECOMPILE);

-- Retrieved from cache?
EXEC sp_configure 'show advanced options',1;
RECONFIGURE;
 
EXEC sp_configure 'optimize for ad hoc workloads',1;
RECONFIGURE;

-- First time? Second time?
SELECT     p.ProductLine,
SUM(f.SalesAmount) AS TotalSalesAmount
FROM [dbo].[FactInternetSales] AS f
INNER JOIN [dbo].[DimProduct] AS p ON
f.ProductKey = p.ProductKey
GROUP BY p.ProductLine
ORDER BY p.ProductLine;
GO

-- Cleanup
EXEC sp_configure 'optimize for ad hoc workloads',0; 
RECONFIGURE;
 
EXEC sp_configure 'show advanced options',0; 
RECONFIGURE;
