Optimisation Index SQL SERVER 2008 R2

[zerjad]

Bonjour à tous,

J'ai des soucis de lenteur dans une de mes applications sous SQL server 2008 R2.

J'ai exécuté la requête suivante pour trouver les index qui manquent :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT  sys.objects.name, CONVERT(int,(avg_total_user_cost * avg_user_impact * 0.01) * (user_seeks + user_scans)) AS Impact
,  'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') 
+ CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END 
+ mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL THEN ''  ELSE 'INCLUDE (' + mid.included_columns + ')' END 
+ ';' AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE     (migs.group_handle IN
(
SELECT     TOP (500) group_handle
FROM          sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC)) 
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1

J’obtiens le résultat suivant pour une de mes tables



La question que je pose est : Est ce que je doit créer les 3 index pour la même table ou le fait de créer le premier diminuera l'impact des 2 autres ?

Par ailleurs, à partir de quel impact peut-on considérer qu'un index est vraiment nécessaire?

La requête me ramène en fait 540 lignes de création d'index avec des impacts allant de 1 à 800.000

Merci d'avance pour vos réponses.

[dbaffaleuf]

par exemple (id,status) include (code)

Mais avant il faudrait regarder la quantité d'updates sur l'index clustered de cette table (sys.dm_db_index_usage_stats, colonne user_updates) pour évaluer le coût de mise à jour.

[zerjad]

Merci David pour la réponse.

Il y a 2 autres index sur la table en question.

La colonne user_updates de la table sys.dm_db_index_usage_stats donne 0 pour les 2 index.

Sinon à partir de quel impact il faut créer les index ?

Est ce que je doit créer les 540 index conseillé par ma première requête ou prendre juste les impacts supérieur à un seuil (1000, 10.000 !!!!) ?

Merci

[dbaffaleuf]

Nan il ne faut pas faire tout ce que les assistants proposent

La pertinence des informations de ces vues dépend de la date de dernier restart de l'instance. Plus de restart est éloigné dans le temps et plus les informations sont pertinentes.

L'impact est un pourcentage donc il ne peut pas dépasser 100. C'est la valeur estimée de réduction du coût moyen des requêtes pour lesquelles l'index est préconisé. Donc un impact proche de 100% indique que le gain est très élevé. Sa valeur brute sera proportionnelle au coût de la requête, c'est la raison pour laquelle on utilise ce coefficient multiplicateur (coût * impact) dans l'order by. Il faut trouver le couple coût * gain le plus élevé, et regarder à chaque fois si des mises à jour ont été effectuées sur la table ou l'index cluster. Préférer ceux pour lesquels on observe le moins de mises à jour, mais encore une fois tout dépend de la date de démarrage !