Discussion :

[zoltix]

Bonjour,
J’ai une base de données avec beaucoup de redondance d’index. Alors j’essaye de les mutualiser et de faire la chasse à la redondance. Avant les effacer je regarde dans dm_db_index_usage_stats pour analyser leurs utilisations.

Je suis tombé sur une Clef primaire qui est doublé par un simple index lorsque je vais voir sur dm_db_index_usage_stats.

table	user_seeks	user_scans	object_id	name	index_id	type	type_desc
DCOBJECT	2,36E+08	57652	1,62E+09	PDCOB_PK	1	1	CLUSTERED
DCOBJECT	4761879	12331	1,62E+09	UDCOB_PK	2	2	NONCLUSTERED

Je constate que mes 2 index sont utilisés. Je me demande ce que je dois faire(effacer ou ?) et demander votre avis ?

Merci d'avance

[Boubou2020]

je te conseil de ne plus faire la suppression surtout pour index cluster

[zoltix]

Pour un index cluster je me doute qu'il ne faut pas le supprimer mais pour le simple index je me pose des questions pourquoi il serait utilisé par sql car moins performant que clustered et si je l'efface quelle serait les impacts sur les performances pour update insert select ?

Merci

[Boubou2020]

Pour un index cluster je me doute qu'il ne faut pas le supprimer mais pour le simple index je me pose des questions pourquoi il serait utilisé par sql car moins performant que clustered et si je l'efface quelle serait les impacts sur les performances pour update insert select ?

Merci

Dans tout les cas je te conseil de ne plus supprimer vos index
Ton index noncluster peuvent être utilisé dans une clause WHERE en recherche ou dans une opération sélective (index couvrant)
ou dans une opération GROUP BY (regroupement) ou une opération ORDER BY (tri)
Un article de SQLPro décrit quoi indexer
http://www.sqlspot.com/INDEXATION-un...l-exemple.html

[zoltix]

Dans tout les cas je te conseil de ne plus supprimer vos index
Ton index noncluster peuvent être utilisé dans une clause WHERE en recherche ou dans une opération sélective (index couvrant)
ou dans une opération GROUP BY (regroupement) ou une opération ORDER BY (tri)
Un article de SQLPro décrit quoi indexer
http://www.sqlspot.com/INDEXATION-un...l-exemple.html

Merci de ta réponse mais deux index sont pareils sauf un qui est cluster et pas l'autre.

Je vais profiler pour essayer de comprendre le choix du plan d’exécution.

Code :

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

1
2
3
4
ALTER TABLE [dbo].[DCOBJECT] ADD  CONSTRAINT [PDCOB_PK] PRIMARY KEY CLUSTERED 
(
	[OB_ID] ASC
)

Code :

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

1
2
3
4
CREATE UNIQUE NONCLUSTERED INDEX [UDCOB_PK] ON [dbo].[DCOBJECT]
(
	[OB_ID] ASC
)

[elsuket]

Bonjour,

La différence est simplement que l'index qui supporte la contrainte d'unicité contient deux fois les valeurs de la colonne qui supporte l'index cluster : une fois pour l'indexation des valeurs de la colonne, et une fois parce que tout index non-cluster référence les valeurs de la clé de l'index cluster de la table (s'il y en a un), de façon à pouvoir retrouver la ligne correspondante et supporter les opérations de bookmark lookup, entre autres.

Un index non-cluster ne pointe donc pas sur les pages de données de la table.
L'index cluster est un doublon de la table, et pointe directement sur les pages de données de la table.
Du fait de cette différence, il découle une différence de taille (c'est-à-dire de nombre de pages) qui font que si l'on soumet une requête qui ne nécessite aucune autre colonne que celle de la clé primaire de cette table, alors l'index qui supporte la contrainte de clé unique va être utilisé. Si la requête contient d'autres colonnes de la même table, alors l'index cluster (qui ici supporte la contrainte de clé primaire) sera utilisé.

Pour voir à quelle fréquence l'index est utilisé, vous pouvez vous référer aux colonnes last_user_seek, last_user_scan et last_user_lookup de la DMV sys.dm_db_index_usage_stats.
Ces trois colonnes vous renseigneront sur la façon dont ces deux index sont utilisés.

En tout état de cause, un index non-cluster pénalisant légèrement les écritures, vous pouvez considérer la suppression de l'index non-cluster (qui supporte la contrainte d'unicité, déjà vérifiée par la contrainte de clé primaire).

@++

[zoltix]

Ok, un grand merci. Je viens de comprendre quand vous avez parlé de la contrainte. Il y a une contrainte d'unicité et l'index. En fait, je confondais les deux.
Lorsque j’essaie d'enlever l’index je reçois un message d'erreur car le champ de l'index est utilisé dans FK.
Donc pour régler mon problème je dois faire un drop de toute le FK, effacer l'index puis recréer les FK qui vont pointer directement sur PK CLUSTERED.
C’est bien ca ?

Code :

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

1
2
Msg 3723, Level 16, State 6, Line 5
An explicit DROP INDEX is not allowed on index 'dbo.DCOBJECT.UDCOB_PK'. It is being used for FOREIGN KEY constraint enforcement.

[elsuket]

Par défaut :

• Une contrainte de clé primaire est supportée par un index cluster
• Une contrainte d'unicité est supportée par un index non-cluster

Mais on peut tout à fait spécifier :

• NONCLUSTERED dans la clause spécifiant la contrainte de clé primaire
• CLUSTERED dans la clause spécifiant la contrainte d'unicité

Lorsque j’essaie d'enlever l’index je reçois un message d'erreur car le champ de l'index est utilisé dans FK.
Donc pour régler mon problème je dois faire un drop de toute le FK, effacer l'index puis recréer les FK qui vont pointer directement sur PK CLUSTERED.
C’est bien ca ?

Là encore, attention : dans une base de données relationnelle SQL, il n'y a pas de champs, mais des colonnes.
La différence peut vous paraître minime, mais vous pouvez lire ce billet.

Je vous conseillerai plutôt de supprimer la contrainte d'unicité, qui supprimera aussi l'index sous-jacent.
Comme je le disais, une contrainte de clé primaire assure aussi l'unicité des tuples des colonnes qui la constituent.

@++