Utiliser les rapports d'information (SSMSInfoReports) pour juger des index

Par Arian Papillon le jeudi 5 février 2015, 14:24 - Lien permanent

Un petit exemple pour voir comment la nouvelle version des rapports d'information disponibles sur CodePlex peut vous aider à juger rapidement l'état de l'indexation de votre base de données (et c'est à cela que je m'en sers), en mode rapport dans SSMS sans dérouler nombre de scripts !

Pour une bonne vue d'ensemble, les toutes premières questions que l'on va se poser sont :

  • Toutes les tables ont-elles bien une clé primaire ?
  • Toutes les tables ont-elles bien un index cluster ?
  • Ai-je des tables sans aucun index ? Ou ont-elles seulement un index cluster et rien d'autre ?

Tables Report

Les réponses se trouvent dans le rapport "Tables Report", que vous pouvez lancer directement depuis SSMS en cliquant (clic droit) sur la base de données et en ouvrant le rapport personnalisé (fichier RDL).

Le premier tableau nous propose une synthèse : dans l'exemple ci-dessous, nous savons tout de suite que nous avons dans la base examinée 518 tables, dont 29 sans clé primaire, 24 sans aucun index, 93 sans index cluster, 316 sans aucun index nonclustered, 292 sans autre index que la clé primaire clustered, etc... !

Et nous y trouvons aussi quelques autres informations disponibles : combien de tables ont une colonne identity, un index full-text, une colone RowGuid, une colonne text/image, une colonne timestamp, un trigger...
A partir de ce premier tableau, il est possible de lancer le rapport détaillé, filtré, en cliquant sur chacun des hyperliens disponibles. Nous verrons ce rapport détaillé un peu plus bas.

Le deuxième tableau nous donne la liste des tables, que l'on peut trier par schéma, nom, nombre de lignes, taille, type de clé primaire (cluster/noncluster) :

Ici aussi, les liens disponibles nous permettront d'accéder au rapport détaillé sur les index, pour la table sélectionnée, mais aussi au rapport sur les statistiques de colonnes.

Index Report

Le rapport "Index Report" nous apporte un niveau supplémentaire de détail : pour chacune des tables faisant partie de la sélection, il affiche la liste détaillée des index. Il va permettre de répondre rapidement aux questions suivantes (et bien d'autres) : 

  • Mes index cluster ont-ils bien des clés de taille réduite ?
  • Quelle est la taille de chaque index ?
  • Quelles sont les colonnes de l'index et leur taille, les colonnes incluses, les filtres éventuels ?
  • Mes index sont-ils utilisés ?

Le tableau utilise un code couleur :

  • Rouge pour les clés primaires CLUSTERED
  • Rose pour les clés primaires NONCLUSTERED
  • Bleu pour les index CLUSTERED non clé
  • Vert pour les index NONCLUSTERED non clé
  • Beige pour les tables HEAP (sans index cluster)
  • Orange pour les contraintes UNIQUE CLUSTERED
  • Jaune pour les contraintes UNIQUE NONCLUSTERED

Les informations disponibles comprennent :

  • Nombre de lignes, taille de l'index, nombre de colonnes et longueur de la clé, clé primaire ou une contrainte unique, filegroup de stockage
  • Nombre de colonnes de la table et largeur maximale
  • Syntaxe complète de création de l'index ou de la contrainte
  • Utilisation de l'index depuis le dernier démarrage de l'instance, en recherche, scan, lookup, update

Exemple

Dans AdventureWorks, examinons notre table Production.BillOfMaterials :

On note que la clé primaire (mono-colonne sur un int) est NONCLUSTERED, alors que l'index CLUSTERED choisi est multi-colonnes (et unique).
Est-ce une bonne idée ?

Évidemment, la réponse est "ça dépend..." : pourquoi pas si les trois colonnes sont le plus souvent utilisées pour une recherche par plage avec ces critères, sinon on préfèrera mettre l'index cluster sur la clé primaire (et l'index unique devra être nonclustered)...

Bonne analyse de votre indexation...