while working on finding all the queries in the query plan that are related to a particular index I realised that there were too many duplicates of the same queries on the query plan.
Finding what queries in the plan cache use a specific index - Jonathan Kehayias
this populates a temp table with stats about my PK_application index:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
IF OBJECT_ID('TEMPDB..#TheIndex') IS NOT NULL
DROP TABLE #TheIndex;
CREATE TABLE #TheIndex (
[SQL_Text] VARCHAR(max) NULL,
[DatabaseName] VARCHAR(128) NULL,
[SchemaName] VARCHAR(128) NULL,
[TableName] VARCHAR(128) NULL,
[IndexName] VARCHAR(128) NULL,
[IndexKind] VARCHAR(128) NULL,
[plan_handle] VARBINARY(64) NOT NULL,
[query_plan] XML NULL)
-- =============================================================================
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @IndexName AS NVARCHAR(128) = 'PK_application';
-- Make sure the name passed is appropriately quoted
IF (LEFT(@IndexName, 1) <> '[' AND RIGHT(@IndexName, 1) <> ']')
SET @IndexName = QUOTENAME(@IndexName);
--Handle the case where the left or right was quoted manually but not the opposite side
IF LEFT(@IndexName, 1) <> '[' SET @IndexName = '['+@IndexName;
IF RIGHT(@IndexName, 1) <> ']' SET @IndexName = @IndexName + ']';
--Dig into the plan cache and find all plans using this index
;WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
INSERT INTO #TheIndex
SELECT
stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text,
obj.value('(@Database)[1]', 'varchar(128)') AS DatabaseName,
obj.value('(@Schema)[1]', 'varchar(128)') AS SchemaName,
obj.value('(@Table)[1]', 'varchar(128)') AS TableName,
obj.value('(@Index)[1]', 'varchar(128)') AS IndexName,
obj.value('(@IndexKind)[1]', 'varchar(128)') AS IndexKind,
cp.plan_handle,
query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)
OPTION(MAXDOP 1, RECOMPILE);
--check what is in #theindex
select *
from #TheIndex
--add a primary key to the table #theindex
alter table #theIndex
add i int not null identity(1,1) primary key clustered
--checking duplicates by sql_text
select i=max(i),the_count=count(i),sql_text
from #TheIndex
group by sql_text
order by count(i) desc
--checking duplicates by plan handle
select i=max(i),the_count=count(i),plan_handle
from #TheIndex
group by plan_handle
order by count(i) desc
when I run sp_blitzcache I get:
sp_BlitzCache @SortOrder = 'recent compilations'
In this condition I have ruled out setting optimise for ad hoc workloads on
question:
Do I have a case for Forced Parameterization?
what could be the indications - pro and cons?
