Discussion :

[Christophe Charron]

Bonjour,

j'ai créé tout un ensemble d'index, suivant les préconisations fournies par la jolie requête trouvée ici

http://blog.sqlauthority.com/2011/01...ript-download/

Je souhaiterais pouvoir auditer la pertinence de certains de ces index. Par exemple retrouver les requêtes utilisant cet index, leur nombre, leur fréquence d'exécution. Etc.

Est-ce possible? Et si oui comment ? Peut-être aussi pour les supprimer si finalement ils sont inutiles car créé pour répondre à une problématique fugace ...

D'avance, merci pour vos lumières.

[elsuket]

Bonjour,

J'espère que vous avez bien suivi le conseil en tout début d'article.

En se limitant aux index relationnels (donc pas les index spatiaux, columnstore, fulltext, et pas les index sur les tables optimisées en mémoire) :

• Pour auditer l'utilisation logique des index en nombre de type d'accès (scan, seek, lookup), vous pouvez vous en remettre à la DMV sys.dm_db_index_usage_stats.
• Pour en auditer l'utilisation physique, la DMV sys.dm_db_index_operational_stats vous renseignera.

Pour auditer l'utilisation des index au niveau des requêtes, vous pouvez dépouiller les plans de requête présents dans le cache :

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
DECLARE @index_name sysname = 'myIndex';
 
WITH
	XMLNAMESPACES  (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
	, CTE AS
	(
		SELECT		QP.query_plan
				, CP.usecounts
				, P.n.query('.') AS sql_stmt
		FROM		sys.dm_exec_cached_plans AS CP
		OUTER APPLY	sys.dm_exec_query_plan(CP.plan_handle) AS QP   
		CROSS APPLY	QP.query_plan.nodes('//StmtSimple') AS P(n)
		WHERE		QP.query_plan.exist('//Object[@Index = sql:variable("@index_name")]') = 1
	)
SELECT		C.sql_stmt.value('StmtSimple[1]/@StatementText', 'varchar(max)') AS sql_text
		, T.tbl.value('@Database','sysname') AS database_name
		, T.tbl.value('@Schema','sysname') AS schema_name
		, T.tbl.value('@Table','sysname') AS table_name
		, T.tbl.value('@Index','sysname') AS index_name
		,C.query_plan
FROM		CTE AS C
CROSS APPLY	C.sql_stmt.nodes('//Object') AS T(tbl)
WHERE		T.tbl.exist('//Object[@Index = sql:variable("@index_name")]') = 1

@++

[Christophe Charron]

Bonjour,

Merci pour votre réponse.
Je m'en suis en effet tenu aux index relationnels.
Toutefois, si la requête que vous proposez ne renvoie pas de résultat, dois-je comprendre que l'index en question n'est pas utilisé ?

Encore merci pour vos éclairages.

[elsuket]

Bonjour,

Si vous avez bien changé la chaîne myIndex par l'index pour lequel vous recherchez les plans, cela montre qu'aucun plan de requête en cache a utilisé l'index.
Mais il est tout à fait possible qu'une requête de reporting qui passe à 2h du matin utilise cet index.
Comme une telle requête n'est utilisée qu'une fois, elle a été évincée du cache de requêtes, et vous ne la trouvez donc pas.

Mais si en plus la vue sys.dm_db_index_usage_stats vous retourne NULL ou zéro pour cette index sur les trois colonnes user_seeks, user_scans et users_lookup, et que l'instance n'a pas été récemment redémarrée, il est effectivement inutile. S'il y a des valeurs supérieures à zéro, vous pouvez vous aider des colonnes last_user_seek, last_user_scan et last_user_lookup pour savoir la dernière fois que cet index a été accédé.

@++

[Boubou2020]

Autre que le redémarrage de l'instance
Est ce que l'opération rebuild index initialise le compteur user_seeK, user_scan et user_lookup pour ce dernier?

[mikedavem]

Autre que le redémarrage de l'instance
Est ce que l'opération rebuild index initialise le compteur user_seeK, user_scan et user_lookup pour ce dernier?

Oui