Discussion :

[olivtone]

Bonjour a Tous

Je recherche une query permettant de tracer dans le plan cache :

- les RID lookup (avec le plan d'execution + la requete associée)
- les Keylookup (avec le plan d'execution + la requete associée)
- plans d'executions les plus utilisés sur ma BDD (+ la requete associée)
- les plans d'executions liés a une table (+ la requete associée)

Avez vous ca en stock ?

merci a vous

[SQLpro]

Quick et super dirty :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT * 
FROM   sys.dm_exec_cached_plans AS cp
       CROSS APPLY sys.dm_exec_query_plan(plan_handle)
       CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE  CAST(query_plan AS NVARCHAR(max)) LIKE '%RID lookup%' COLLATE French_CI_AI
   OR  CAST(query_plan AS NVARCHAR(max)) LIKE '%Keylookup%' COLLATE French_CI_AI
ORDER BY usecounts desc

A +

[elsuket]

Bonjour,

Pour ce qui est du numbre d'exécution de chaque plan, vous pouvez vous en remettre à la colonne sys.dm_exec_query_stats.execution_count.
Combiné à sys.dm_exec_query_plan, vous obtiendrez le plan, mais attention, c'est coûteux

Pour les keylookup, c'est du costaud. J'ai une requête qui le fait, mais pas de système de production sous la main pour vérifier qu'elle est correcte.
Pour les RID lookup, je n'ai pas mais je vais chercher. Pour les plans liés à une table en particulier, ce doit être assez facile.

I'll be back

@++

[olivtone]

merci a vous

j'attends ton retour elkuset

[elsuket]

Voici pour les key lookup :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 
WITH
	XMLNAMESPACES (	DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
	, HANDLE AS
	(
		SELECT		QS.plan_handle
				, TP.query_plan
		FROM		(
					SELECT	DISTINCT plan_handle
					FROM	sys.dm_exec_query_stats
				) AS QS
		OUTER APPLY	sys.dm_exec_query_plan(QS.plan_handle) AS TP
	)
SELECT		Q.n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text
		, S.i.value('(@PhysicalOp)[1]', 'VARCHAR(128)') AS PhysicalOp
		, S.i.value('(./IndexScan/Object/@Database)[1]', 'VARCHAR(128)') AS DatabaseName
		, S.i.value('(./IndexScan/Object/@Schema)[1]', 'VARCHAR(128)') AS SchemaName
		, S.i.value('(./IndexScan/Object/@Table)[1]', 'VARCHAR(128)') AS TableName
		, S.i.value('(./IndexScan/Object/@Index)[1]', 'VARCHAR(128)') AS IndexName
		, STUFF
		(
			(
				SELECT	DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
				FROM	S.i.nodes('./OutputList/ColumnReference') AS t(cg)
				FOR	XML PATH('')
			),1,2,''
		) AS output_columns
		, STUFF
		(
			(
				SELECT	DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)')
				FROM	S.i.nodes('./IndexScan/SeekPredicates/SeekPredicateNew//ColumnReference') AS t(cg)
				FOR	XML PATH('')
			),1,2,''
		) AS seek_columns
		, S.i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)') AS Predicate
		, P.usecounts
		, PH.query_plan
FROM		HANDLE AS PH
INNER JOIN	sys.dm_exec_cached_plans AS P
			ON PH.plan_handle = P.plan_handle
CROSS APPLY	PH.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS Q(n)
CROSS APPLY	Q.n.nodes('.//RelOp[IndexScan[@Lookup="1"] and IndexScan/Object[@Schema!="[sys]"]]') AS S(i)
OPTION		(RECOMPILE, MAXDOP 1);

Je n'ai pas encore eu le temps de chercher pour les RID lookup ...

@++