changing-indexes-and-the-plan-cache.sql

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php


Get WideWorldImporters-Full.bak from Microsoft at:
https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
******************************************************************************/

USE master;
GO

IF DB_ID('WideWorldImporters') IS NOT NULL
BEGIN
    ALTER DATABASE WideWorldImporters SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END
GO
RESTORE DATABASE WideWorldImporters FROM DISK=
	'S:\MSSQL\Backup\WideWorldImporters-Full.bak'  
	WITH REPLACE,
	MOVE 'WWI_Primary' to 'S:\MSSQL\Data\WideWorldImporters.mdf',
	MOVE 'WWI_UserData' to 'S:\MSSQL\Data\WideWorldImporters_UserData.ndf',
	MOVE 'WWI_Log' to 'S:\MSSQL\Data\WideWorldImporters.ldf',
	MOVE 'WWI_InMemory_Data_1' to 'S:\MSSQL\Data\WideWorldImporters_InMemory_Data_1';
GO


USE WideWorldImporters;
GO

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

SELECT SupplierName, PrimaryContact
FROM Website.Suppliers
WHERE SupplierCategoryName = 'Novelty Goods Supplier';
GO 5

SELECT 
    qs.execution_count AS [# executions],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,3))
		END AS [avg cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_logical_reads / execution_count AS numeric(30,3))
	END AS [avg logical reads],
	qs.creation_time,
	qs.plan_generation_num,
    qp.query_plan AS [plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
WHERE
	SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
		((CASE qs.statement_end_offset  
			WHEN -1 THEN DATALENGTH(st.text)  
			ELSE qs.statement_end_offset  
			END
		- qs.statement_start_offset)/2) + 1) LIKE '%WHERE SupplierCategoryName = ''Novelty Goods Supplier''%'
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);
GO

--CREATE INDEX ix_Application_People_PhoneNumber on Application.People (PhoneNumber);
--GO
CREATE INDEX ix_Purchasing_Suppliers_SupplierCategoryID_INCLUDES on Purchasing.Suppliers (SupplierCategoryID) INCLUDE (PrimaryContactPersonID, SupplierName);
GO

SELECT 
    qs.execution_count AS [# executions],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,3))
		END AS [avg cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_logical_reads / execution_count AS numeric(30,3))
	END AS [avg logical reads],
	qs.creation_time,
	qs.plan_generation_num,
    qp.query_plan AS [plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
WHERE
	SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
		((CASE qs.statement_end_offset  
			WHEN -1 THEN DATALENGTH(st.text)  
			ELSE qs.statement_end_offset  
			END
		- qs.statement_start_offset)/2) + 1) LIKE '%WHERE SupplierCategoryName = ''Novelty Goods Supplier''%'
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);
GO


SELECT SupplierName, PrimaryContact
FROM Website.Suppliers
WHERE SupplierCategoryName = 'Novelty Goods Supplier';
GO 4

SELECT 
    qs.execution_count AS [# executions],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,3))
		END AS [avg cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_logical_reads / execution_count AS numeric(30,3))
	END AS [avg logical reads],
	qs.creation_time,
	qs.plan_generation_num,
    qp.query_plan AS [plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
WHERE
	SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
		((CASE qs.statement_end_offset  
			WHEN -1 THEN DATALENGTH(st.text)  
			ELSE qs.statement_end_offset  
			END
		- qs.statement_start_offset)/2) + 1) LIKE '%WHERE SupplierCategoryName = ''Novelty Goods Supplier''%'
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);
GO

--ALTER INDEX ix_Application_People_PhoneNumber on Application.People REBUILD;
--GO
ALTER INDEX ix_Purchasing_Suppliers_SupplierCategoryID_INCLUDES on Purchasing.Suppliers REBUILD;
GO


SELECT 
    qs.execution_count AS [# executions],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,3))
		END AS [avg cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_logical_reads / execution_count AS numeric(30,3))
	END AS [avg logical reads],
	qs.creation_time,
	qs.plan_generation_num,
    qp.query_plan AS [plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
WHERE
	SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
		((CASE qs.statement_end_offset  
			WHEN -1 THEN DATALENGTH(st.text)  
			ELSE qs.statement_end_offset  
			END
		- qs.statement_start_offset)/2) + 1) LIKE '%WHERE SupplierCategoryName = ''Novelty Goods Supplier''%'
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);
GO

SELECT SupplierName, PrimaryContact
FROM Website.Suppliers
WHERE SupplierCategoryName = 'Novelty Goods Supplier';
GO 3

SELECT 
    qs.execution_count AS [# executions],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,3))
		END AS [avg cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_logical_reads / execution_count AS numeric(30,3))
	END AS [avg logical reads],
	qs.creation_time,
	qs.plan_generation_num,
    qp.query_plan AS [plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
WHERE
	SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
		((CASE qs.statement_end_offset  
			WHEN -1 THEN DATALENGTH(st.text)  
			ELSE qs.statement_end_offset  
			END
		- qs.statement_start_offset)/2) + 1) LIKE '%WHERE SupplierCategoryName = ''Novelty Goods Supplier''%'
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);
GO

--DROP INDEX ix_Application_People_PhoneNumber on Application.People;
--GO
DROP INDEX ix_Purchasing_Suppliers_SupplierCategoryID_INCLUDES on Purchasing.Suppliers;
GO


SELECT 
    qs.execution_count AS [# executions],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,3))
		END AS [avg cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_logical_reads / execution_count AS numeric(30,3))
	END AS [avg logical reads],
	qs.creation_time,
	qs.plan_generation_num,
    qp.query_plan AS [plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
WHERE
	SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
		((CASE qs.statement_end_offset  
			WHEN -1 THEN DATALENGTH(st.text)  
			ELSE qs.statement_end_offset  
			END
		- qs.statement_start_offset)/2) + 1) LIKE '%WHERE SupplierCategoryName = ''Novelty Goods Supplier''%'
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);
GO

SELECT SupplierName, PrimaryContact
FROM Website.Suppliers
WHERE SupplierCategoryName = 'Novelty Goods Supplier';
GO 2


SELECT 
    qs.execution_count AS [# executions],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,3))
		END AS [avg cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_logical_reads / execution_count AS numeric(30,3))
	END AS [avg logical reads],
	qs.creation_time,
	qs.plan_generation_num,
    qp.query_plan AS [plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
WHERE
	SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
		((CASE qs.statement_end_offset  
			WHEN -1 THEN DATALENGTH(st.text)  
			ELSE qs.statement_end_offset  
			END
		- qs.statement_start_offset)/2) + 1) LIKE '%WHERE SupplierCategoryName = ''Novelty Goods Supplier''%'
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);
GO