USE
	CourseDB;
GO


SET STATISTICS IO ON;
GO


-- Display a summary of the plan cache contents

SELECT
	CacheName		= name ,
	CacheType		= type ,
	CacheSize_KB	= pages_kb ,
	EntriesCount	= entries_count
FROM
	sys.dm_os_memory_cache_counters
WHERE
	type IN (N'CACHESTORE_OBJCP' , N'CACHESTORE_SQLCP' , N'CACHESTORE_PHDR' , N'CACHESTORE_XPROC')
GO


-- Empty the plan cache

DBCC FREEPROCCACHE;
GO


-- Generate some activity

SELECT
	Id ,
	Name ,
	Phone ,
	BirthDate ,
	SourceURL
FROM
	Marketing.Customers
WHERE
	Id = 1234;
GO


SELECT
	OrderId		= OrderProducts.OrderId ,
	ProductName	= Products.Name ,
	TotalPrice	= OrderProducts.Quantity * OrderProducts.UnitPrice
FROM
	Sales.OrderProducts AS OrderProducts
INNER JOIN
	Inventory.Products AS Products
ON
	OrderProducts.ProductId = Products.Id
WHERE
	OrderProducts.Quantity * OrderProducts.UnitPrice > $8000.00
ORDER BY
	TotalPrice DESC;
GO


EXECUTE Sales.usp_GetOrdersByCustomerId
	@CustomerId = 1234;
GO


-- Display a summary of the plan cache contents again

SELECT
	CacheName		= name ,
	CacheType		= type ,
	CacheSize_KB	= pages_kb ,
	EntriesCount	= entries_count
FROM
	sys.dm_os_memory_cache_counters
WHERE
	type IN (N'CACHESTORE_OBJCP' , N'CACHESTORE_SQLCP' , N'CACHESTORE_PHDR' , N'CACHESTORE_XPROC')
GO


-- Display the plan cache contents

SELECT
	ObjectType				= objtype ,
	CacheObjectType			= cacheobjtype ,
	CacheObjectSize_KB		= size_in_bytes / 1024 ,
	UseCount				= usecounts ,
	PlanHandle				= plan_handle
FROM
	sys.dm_exec_cached_plans;
GO


-- Display the plan cache contents including the plan text and the plan itself

SELECT
	PlanText		= QueryTexts.text ,
	ExecutionPlan	= QueryPlans.query_plan ,
	ExecutionCount	= CachedPlans.usecounts ,
	ObjectType		= CachedPlans.objtype ,
	Size_KB			= CachedPlans.size_in_bytes / 1024
FROM
	sys.dm_exec_cached_plans AS CachedPlans
CROSS APPLY
	sys.dm_exec_query_plan (CachedPlans.plan_handle) AS QueryPlans
CROSS APPLY
	sys.dm_exec_sql_text (CachedPlans.plan_handle) AS QueryTexts;
GO


-- Add information about recompilations

SELECT
	PlanText						= QueryTexts.text ,
	ExecutionPlan					= QueryPlans.query_plan ,
	CompilationCount				= BatchStats.CompilationCount ,
	ExecutionsSinceLastCompilation	= BatchStats.ExecutionsSinceLastCompilation ,
	TotalExecutionCount				= CachedPlans.usecounts ,
	ObjectType						= CachedPlans.objtype ,
	Size_KB							= CachedPlans.size_in_bytes / 1024 ,
	LastCompilationDateTime			= BatchStats.LastCompilationDateTime ,
	LastExecutionDateTime			= BatchStats.LastExecutionDateTime
FROM
	sys.dm_exec_cached_plans AS CachedPlans
CROSS APPLY
	sys.dm_exec_query_plan (CachedPlans.plan_handle) AS QueryPlans
CROSS APPLY
	sys.dm_exec_sql_text (CachedPlans.plan_handle) AS QueryTexts
CROSS APPLY
	(
		SELECT TOP (1)
			PlanHandle						= QueryStats.plan_handle ,
			CompilationCount				= QueryStats.plan_generation_num ,
			ExecutionsSinceLastCompilation	= QueryStats.execution_count ,
			LastCompilationDateTime			= QueryStats.creation_time ,
			LastExecutionDateTime			= QueryStats.last_execution_time
		FROM
			sys.dm_exec_query_stats AS QueryStats
		WHERE
			QueryStats.plan_handle = CachedPlans.plan_handle
		ORDER BY
			LastExecutionDateTime ASC
	)
	AS
		BatchStats;
GO


-- Let's make it a view...

CREATE VIEW
	dbo.CachedPlans
(
	PlanText ,
	ExecutionPlan ,
	CompilationCount ,
	ExecutionsSinceLastCompilation ,
	TotalExecutionCount ,
	ObjectType ,
	Size_KB ,
	LastCompilationDateTime ,
	LastExecutionDateTime
)
AS

SELECT
	PlanText						= QueryTexts.text ,
	ExecutionPlan					= QueryPlans.query_plan ,
	CompilationCount				= BatchStats.CompilationCount ,
	ExecutionsSinceLastCompilation	= BatchStats.ExecutionsSinceLastCompilation ,
	TotalExecutionCount				= CachedPlans.usecounts ,
	ObjectType						= CachedPlans.objtype ,
	Size_KB							= CachedPlans.size_in_bytes / 1024 ,
	LastCompilationDateTime			= BatchStats.LastCompilationDateTime ,
	LastExecutionDateTime			= BatchStats.LastExecutionDateTime
FROM
	sys.dm_exec_cached_plans AS CachedPlans
CROSS APPLY
	sys.dm_exec_query_plan (CachedPlans.plan_handle) AS QueryPlans
CROSS APPLY
	sys.dm_exec_sql_text (CachedPlans.plan_handle) AS QueryTexts
CROSS APPLY
	(
		SELECT TOP (1)
			PlanHandle						= QueryStats.plan_handle ,
			CompilationCount				= QueryStats.plan_generation_num ,
			ExecutionsSinceLastCompilation	= QueryStats.execution_count ,
			LastCompilationDateTime			= QueryStats.creation_time ,
			LastExecutionDateTime			= QueryStats.last_execution_time
		FROM
			sys.dm_exec_query_stats AS QueryStats
		WHERE
			QueryStats.plan_handle = CachedPlans.plan_handle
		ORDER BY
			LastExecutionDateTime ASC
	)
	AS
		BatchStats;
GO


-- Retrieve the most used plans

SELECT TOP (5)
	PlanText ,
	ExecutionPlan ,
	CompilationCount ,
	ExecutionsSinceLastCompilation ,
	TotalExecutionCount ,
	ObjectType ,
	Size_KB ,
	LastCompilationDateTime ,
	LastExecutionDateTime
FROM
	dbo.CachedPlans
ORDER BY
	TotalExecutionCount DESC;
GO


-- Empty the plan cache

DBCC FREEPROCCACHE;
GO


-- Execute a test query

SELECT
	OrderId		= OrderProducts.OrderId ,
	ProductName	= Products.Name ,
	TotalPrice	= OrderProducts.Quantity * OrderProducts.UnitPrice
FROM
	Sales.OrderProducts AS OrderProducts
INNER JOIN
	Inventory.Products AS Products
ON
	OrderProducts.ProductId = Products.Id
WHERE
	OrderProducts.Quantity * OrderProducts.UnitPrice > $8000.00
ORDER BY
	TotalPrice DESC;
GO


-- Display the cache entry corresponding to the previous query

SELECT
	PlanText ,
	ExecutionPlan ,
	CompilationCount ,
	ExecutionsSinceLastCompilation ,
	TotalExecutionCount ,
	ObjectType ,
	Size_KB ,
	LastCompilationDateTime ,
	LastExecutionDateTime
FROM
	dbo.CachedPlans
WHERE
	PlanText LIKE N'%OrderProducts%'
AND
	PlanText NOT LIKE N'%dm_exec_cached_plans%';
GO


-- Execute the test query again

SELECT
	OrderId		= OrderProducts.OrderId ,
	ProductName	= Products.Name ,
	TotalPrice	= OrderProducts.Quantity * OrderProducts.UnitPrice
FROM
	Sales.OrderProducts AS OrderProducts
INNER JOIN
	Inventory.Products AS Products
ON
	OrderProducts.ProductId = Products.Id
WHERE
	OrderProducts.Quantity * OrderProducts.UnitPrice > $8000.00
ORDER BY
	TotalPrice DESC;
GO


-- Display the cache entry corresponding to the previous query

SELECT
	PlanText ,
	ExecutionPlan ,
	CompilationCount ,
	ExecutionsSinceLastCompilation ,
	TotalExecutionCount ,
	ObjectType ,
	Size_KB ,
	LastCompilationDateTime ,
	LastExecutionDateTime
FROM
	dbo.CachedPlans
WHERE
	PlanText LIKE N'%OrderProducts%'
AND
	PlanText NOT LIKE N'%dm_exec_cached_plans%';
GO


-- Execute the test query again, this time with an extra space character

SELECT
	OrderId		= OrderProducts.OrderId ,
	ProductName	= Products.Name ,
	TotalPrice	= OrderProducts.Quantity * OrderProducts.UnitPrice
FROM
	Sales.OrderProducts AS OrderProducts
INNER JOIN
	Inventory.Products AS Products
ON
	OrderProducts.ProductId = Products.Id
WHERE
	OrderProducts.Quantity * OrderProducts.UnitPrice > $8000.00
ORDER BY
	TotalPrice DESC ;
GO


-- Display the cache entry corresponding to the previous query

SELECT
	PlanText ,
	ExecutionPlan ,
	CompilationCount ,
	ExecutionsSinceLastCompilation ,
	TotalExecutionCount ,
	ObjectType ,
	Size_KB ,
	LastCompilationDateTime ,
	LastExecutionDateTime
FROM
	dbo.CachedPlans
WHERE
	PlanText LIKE N'%OrderProducts%'
AND
	PlanText NOT LIKE N'%dm_exec_cached_plans%';
GO


-- Add an index to one of the underlying tables

CREATE NONCLUSTERED INDEX
	ix_OrderProoduucts_nc_nu_OrderId
ON
	Sales.OrderProducts (OrderId ASC);
GO


-- Display the cache entry corresponding to the previous query
-- At this point, there is no change to the plan in cache, because the query hans't been executed again yet

SELECT
	PlanText ,
	ExecutionPlan ,
	CompilationCount ,
	ExecutionsSinceLastCompilation ,
	TotalExecutionCount ,
	ObjectType ,
	Size_KB ,
	LastCompilationDateTime ,
	LastExecutionDateTime
FROM
	dbo.CachedPlans
WHERE
	PlanText LIKE N'%OrderProducts%'
AND
	PlanText NOT LIKE N'%dm_exec_cached_plans%';
GO


-- Execute the test query again

SELECT
	OrderId		= OrderProducts.OrderId ,
	ProductName	= Products.Name ,
	TotalPrice	= OrderProducts.Quantity * OrderProducts.UnitPrice
FROM
	Sales.OrderProducts AS OrderProducts
INNER JOIN
	Inventory.Products AS Products
ON
	OrderProducts.ProductId = Products.Id
WHERE
	OrderProducts.Quantity * OrderProducts.UnitPrice > $8000.00
ORDER BY
	TotalPrice DESC;
GO


-- Display the cache entry corresponding to the previous query

SELECT
	PlanText ,
	ExecutionPlan ,
	CompilationCount ,
	ExecutionsSinceLastCompilation ,
	TotalExecutionCount ,
	ObjectType ,
	Size_KB ,
	LastCompilationDateTime ,
	LastExecutionDateTime
FROM
	dbo.CachedPlans
WHERE
	PlanText LIKE N'%OrderProducts%'
AND
	PlanText NOT LIKE N'%dm_exec_cached_plans%';
GO


-- Update the statistics on the "Sales.OrderProducts" table

UPDATE STATISTICS
	Sales.OrderProducts;
GO


-- Display the cache entry corresponding to the previous query
-- At this point, there is no change to the plan in cache, because the query hans't been executed again yet

SELECT
	PlanText ,
	ExecutionPlan ,
	CompilationCount ,
	ExecutionsSinceLastCompilation ,
	TotalExecutionCount ,
	ObjectType ,
	Size_KB ,
	LastCompilationDateTime ,
	LastExecutionDateTime
FROM
	dbo.CachedPlans
WHERE
	PlanText LIKE N'%OrderProducts%'
AND
	PlanText NOT LIKE N'%dm_exec_cached_plans%';
GO


-- Execute the test query again

SELECT
	OrderId		= OrderProducts.OrderId ,
	ProductName	= Products.Name ,
	TotalPrice	= OrderProducts.Quantity * OrderProducts.UnitPrice
FROM
	Sales.OrderProducts AS OrderProducts
INNER JOIN
	Inventory.Products AS Products
ON
	OrderProducts.ProductId = Products.Id
WHERE
	OrderProducts.Quantity * OrderProducts.UnitPrice > $8000.00
ORDER BY
	TotalPrice DESC;
GO


-- Display the cache entry corresponding to the previous query
-- The plan was not recompiled, because there wasn't any change to the data in the table

SELECT
	PlanText ,
	ExecutionPlan ,
	CompilationCount ,
	ExecutionsSinceLastCompilation ,
	TotalExecutionCount ,
	ObjectType ,
	Size_KB ,
	LastCompilationDateTime ,
	LastExecutionDateTime
FROM
	dbo.CachedPlans
WHERE
	PlanText LIKE N'%OrderProducts%'
AND
	PlanText NOT LIKE N'%dm_exec_cached_plans%';
GO


-- Insert a new row into the "Sales.OrderProducts" table

INSERT INTO
	Sales.OrderProducts WITH (TABLOCK)
(
	OrderId ,
	ProductId ,
	Quantity ,
	UnitPrice
)
SELECT TOP (1)
	OrderId		= Orders.Id ,
	ProductId	= ABS (CHECKSUM (NEWID ())) % 100000 + 1 ,
	Quantity	= ABS (CHECKSUM (NEWID ())) % 10 + 1 ,
	UnitPrice	= CAST ((ABS (CHECKSUM (NEWID ())) % 100000 / 100.0) AS MONEY)
FROM
	Sales.Orders AS Orders
CROSS JOIN
	(
		VALUES (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) , (9) , (10)
	)
	AS
		Numbers (Number)
ORDER BY
	NEWID () ASC;
GO


-- Update the statistics on the "Sales.OrderProducts" table again

UPDATE STATISTICS
	Sales.OrderProducts;
GO


-- Execute the test query again

SELECT
	OrderId		= OrderProducts.OrderId ,
	ProductName	= Products.Name ,
	TotalPrice	= OrderProducts.Quantity * OrderProducts.UnitPrice
FROM
	Sales.OrderProducts AS OrderProducts
INNER JOIN
	Inventory.Products AS Products
ON
	OrderProducts.ProductId = Products.Id
WHERE
	OrderProducts.Quantity * OrderProducts.UnitPrice > $8000.00
ORDER BY
	TotalPrice DESC;
GO


-- Display the cache entry corresponding to the previous query

SELECT
	PlanText ,
	ExecutionPlan ,
	CompilationCount ,
	ExecutionsSinceLastCompilation ,
	TotalExecutionCount ,
	ObjectType ,
	Size_KB ,
	LastCompilationDateTime ,
	LastExecutionDateTime
FROM
	dbo.CachedPlans
WHERE
	PlanText LIKE N'%OrderProducts%'
AND
	PlanText NOT LIKE N'%dm_exec_cached_plans%';
GO
