0

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

enter image description here

--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

enter image description here

when I run sp_blitzcache I get:

sp_BlitzCache @SortOrder = 'recent compilations'

enter image description here

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?

  • 1
    By Forced Parameterization, do you only mean the database setting, or do you consider plan guides that force parameterization to be part of this? – Forrest Aug 7 at 17:49
  • good question, I was considering the database setting only – Marcello Miorelli Aug 7 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.