SQL Server - Les indexes et le préfixe _WA_sys_

Voici quelques informations collectées sur le Net concernant la définition des indexes dans SQL Serveur (version 7).
Récemment, lors d'une révision des tables de notre base de données en SQL 7 (et oui, il en existe toujours), en utilisant la commande sql  "sp_help TableName" j'ai découvert une grande quantité d'indexes préfixés avec "_WA_sys_".
Nous n'avions manifestement pas crées ces indexes... et la question s'est alors posée de savoir pourquoi il étaient là.

Je sais aujourd'hui que l'existence de ces indexes "_WA_sys_" indique une mauvaise optimisation de la base de donnée. Une mauvaise indexation que SQL server a tenté de palier au mieux.
La suite de cet article rassemble le matériel glâné ci et là sur Internet.

Note:

Cet article de fond concerne principalement SQL Serveur 7. Depuis SQL Serveur 2005 bénéficie d'outils avancés facilitant l'optimisation des indexes. Il n'en reste pas moins que les informations contenues dans cet article restent pertinentes à plus d'un title (en autre l'existence des indexes _wa_sys_).

Interroger la table SysIndexes

Pour commencer, une requête sql permettant d'obtenir une  liste des indexes présent sur une table.
Cette section est accompagné d'une description en anglais provenant d'une page anonyme sur le net.
L'information n'en est pas moins intéressante.


;  In the following SQL Statement, indID = 0 is excluded because it correspond to the heap.
;               indID=255 is excluded because it exists if table contains a Text or similar column

  SELECT name as IndexName

  FROM SysIndexes idx

  WHERE idx.id in (

    SELECT id from SysObjects tbl

    WHERE  tbl.type='U' and tbl.Name = 'tblTMcVarInstance' )

    and indID not in ( 255, 0 )

Each table and index has a row in sysindexes uniquely identified by the combination of the object identifier (id) column and the index identifier (indid) column. The allocation of pages to table and index is managed by a chain of IAM pages. The column sysindexes.FirstIAM points to first IAM page in the chain of IAM pages managing the space allocated to the table or index.
Each table has a set of rows in sysindexes:

• A heap has a row in sysindexes with indid = 0.
• The FirstIAM column points to the IAM chain for the collection of data pages for the table. The server uses the IAM pages to find the pages in the data page collection because they are not linked together.
• A clustered index has a row in sysindexes with indid = 1.
  The root column points to the top of the clustered index b-tree. The server uses the index B-tree to find the data pages.
• Each nonclustered index created for the table has a row in sysindexes.
  The values for indid in the rows for each nonclustered index range from 2 to 251. The root column points to the top of the nonclustered index B-tree.
• Each table that has at least one text, ntext, or image column also has a row in sysindexes with indid = 255.
  The column FirstIAM points to the chain of IAM pages that manage the text, ntext, and image pages.

Qu'est-ce qu'un index _WA_sys_?
Lorsqu'un index est créé à l'aide d'une requête SQL, l'utilisateur est prié de mentionner un nom pour celui-ci.
Dans notre cas, l'exploration de SysIndexes peut révéler des indexes complémentaires préfixés avec" _WA_sys_". D'ou viennent-ils?

Réponse:
SQL Server creates statistics when it can not find an adequate index to perform a particular query. It makes this determination based on an algorithm that takes into account frequently used queries. This prevents the engine from creating statistics every time it doesn't find an index, but still allows it to self-optimize when it determines a need. Remember, the presence of statistics that SQL Server builds is an indication of inadequate indexing. Instead of trying to copy stats around, you should be asking yourself why does SQL Server even have to create those stats in the first place? It is creating those stats because you have not created sufficient indexes for the query patterns your users are placing against the server? Stats are not transferred and are transitory things. Your performance problems are solved by creating the indexes you need, instead of relying on SQL Server to finally determine that you need them, and then creating stats to fill the void your lack of indexes have left.

Les entrées _WA_sys_ apparaissant pour les tables indiquent une mauvaise indexation de ces dernières.

Détecter les tables mal indexées
Suite de la précédente section... puisque dans ce cas  le préfix "_WA_sys_" est automatiquement ajouté, une simple requête SQL sur la table sysIndexes révèlera tous les indexes créés par le processus de mise à jour statistic.

select tbl.Name as TableName, idx.indID, idx.name as IndexName, tbl.id as ObjectID

from sysindexes idx

left join sysobjects tbl on idx.id = tbl.id

where idx.name like '_WA_%' and tbl.type='U'

Les indexes "_WA_sys_", copie de base de donnée et PERFORMANCES!Lors du backup d'une DB, les indexes _WA_sys_ n'y sont pas inclus.
Par conséquent, à la restauration, il ne seront pas restaurés (ou recrée).
La présence d'indexes "_WA_sys_" indique une indexation inadéquat dans la base de donnée... résultant forcement en une faible performance... qui est cependant corrigé par le processus d' "update statistic" et "query optimizer" (d'ou la création "sauvage" d'index _WA_sys_).

Mais attention! Puisque les indexes _WA_sys_ ne font pas parties du procéssus backup/restore, les performances des bases de données mal optimisée seront forcement mauvaises (voir catastrophique) après restoration.
Tant que le processus d'optimisation ne re-créera pas les indexes _WA_sys_ , la base de donnée "fonctionnera sur une seule jambe".