würfel cube sql 3d

 

Après notre article « Avez-vous pensez à consolider vos bases de données ? »,  nous vous proposons aujourd’hui de comprendre comment augmenter les performances des bases de données DBA. Plusieurs méthodes sont disponibles et dans cet article nous allons nous attarder sur la gestion des index.

Qu’est-ce que un index SQL?

 Les index permettent au moteur de base de données de récupérer plus rapidement les données dans la base. Ainsi, une gestion de ces index est vitale afin d’optimiser les performances et d’avoir une application avec de meilleur temps de réponse.

Avec des index pertinents, nous allons donc diminuer le temps d’accès aux données et diminuer la charge CPU qui était nécessaire à la récupération d’un ensemble de données. Mais en contrepartie, les index consomment de l’espace disque et ont besoin d’être maintenus au travers de plan de maintenance.

De par leur nature, les index permettent d’améliorer les lectures, les updates et les suppressions. En effet, ils nécessitent une lecture des données avant de la mettre à jour ou de la supprimer. Mais en revanche, pour des tables ayant un taux d’écriture plus important, les index deviennent nuisibles, car davantage de données ont besoins d’être écrites.

Comment faire pour avoir des index pertinents ?

 Il faut partir du principe que les bases sont surtout utilisées pour de la consultation et non de l’insertion, pour les raisons citées ci-dessus. Le processus se décompose en 2 étapes :

  • L’analyse des index déjà existante : Pour nettoyer les index qui ne servent pas ou peu et pouvoir ainsi récupérer de l’espace disque et alléger la maintenance.
  • L’analyse de requêtes exécutées sur la base de données : Pour en déduire les index pertinents et utiles à l’optimisation des performances.

Comment diagnostiquer les indexes problématiques ?

  • La requête suivante permet de lister les index jamais utilisés sur une base :

Declare @dbid2 int
Select @dbid2 = db_id()
Select objectname=object_name(i.object_id)
, indexname=i.name, i.index_id, ‘DROP INDEX ‘ + i.name + ‘ ON ‘+ object_name(i.object_id) as ‘delete query’
from sys.indexes i, sys.objects o
where objectproperty(o.object_id,’IsUserTable’) = 1
and i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id
and i.index_id=s.index_id
and database_id = @dbid2 )
and o.object_id = i.object_id
and object_name(i.object_id) not like ‘MSp%’
and object_name(i.object_id) not like ‘sys%’
order by objectname,i.index_id,indexname asc

 

  • La requête suivante permet de lister les index qui sont rarement utilisés en fonction des différentes requêtes utilisateurs :

declare @dbid int
select @dbid = db_id(‘Nom de la base’)
select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id
, user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s,
sys.indexes i
where database_id = @dbid and objectproperty(s.object_id,’IsUserTable’) = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by (user_seeks + user_scans + user_lookups + user_updates) asc

 

  • Les index retournés par la première requête, sont les index qui n’ont jamais été utilisés. Ils n’ont donc en théorie pas de raison d’être car jugés inutiles.
  • Les index retournés par la deuxième requête sont triés avec la liste des index les moins utilisés en haut. Cela permet donc d’identifier les index qui peuvent être améliorés ou supprimés.

Il n’est pas conseillé de garder un index qui consomme de l’espace disque s’il n’est jamais utilisé.

Commenter déterminer les index pouvant être créés, pour améliorer les performances ?

SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
‘CREATE INDEX [missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle)
+ ‘_’ + LEFT (PARSENAME(mid.statement, 1), 32) + ‘]’
+ ‘ ON ‘ + mid.statement
+ ‘ (‘ + ISNULL (mid.equality_columns,”)
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END
+ ISNULL (mid.inequality_columns, ”)
+ ‘)’
+ ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement,
–migs.*, mid.[object_id],
mid.database_id
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Les index retournés sont ceux “conseillés” par SQL serveur d’après les requêtes les plus consommatrices, enregistrées sur l’instance par ce même serveur (basé sur la dmv dm_db_missing_index_groups).

Il faut faire attention à ne pas créer tous les index proposés par Microsoft, mais de s’en inspirer. Il est donc possible que les index diminuent les performances des instances SQL, dans le cas d’une base axée principalement en écriture par exemple.

Pour conclure :

Les index permettent d’augmenter significativement les performances, mais il faut faire attention, car un mauvais choix peut entraîner l’effet inverse. Des outils sont présents pour nous aider dans la tâche. D’autres requêtes de diagnostiques existent probablement, l’important étant de savoir interpréter les résultats obtenus. Pour une analyse encore plus poussée il est possible d’utiliser l’outil « Database Engine Tuning Advisor », plus complexe à prendre en main.