INDEX de type HEAP [Résolu]

[Liloye]

Bonjour à tous,

J'ai un projet qui se plain de pb de performances...

J'ai passé une petite requete, afin de verifier la fragmentation des index et la effectivement forte fragmentation sur pas mal d'index...
Surtout sur des index de "TYPE HEAP" qui ne s'appuient sur aucune colonne !!?

Une table de type HEAP je vois bien, mais un index ??...
J'ai donc une théorie et je souhaiterai voir si ca vous parait logique...

Je me suis rendue compte qu'aucune table ne possède de clé primaire (aucune contrainte d'intégrité en fait), hors il existe bien des colonnes correspondant dans chaque table à un "ID"...

Ne serait ce donc pas l'optimiseur SQL qui effectuerai une une recherche sur une colonne ID et comme il n'y a aucun index en crée un de type HEAP !!??

Vous en pensait quoi ?

[Ptit_Dje]

Bonjour,

"Toute table est un index" ordonne de maniere aleatoire pour les HEAPs, ou selon une/des colonne(s) defines pour un clustered index.

[Liloye]

Pour moi une table HEAP est une table non ordonné

Donc si je suis ton raisonnement, j'ai un index non ordonné...
Sauf qu'il n'est pas physique car pas de colonne lié à cet index (juste la table) et qu'effectivement il n'existe pas dans la definition de mes index...

Alors !?

La requete que j'utilise est :

Code :

1
2
3
4
5
6
7
SELECT OBJECT_NAME(a.object_id) AS table_name, 
b.name AS index_name, 
index_type_desc AS index_type, 
avg_fragmentation_in_percent AS fragmentation 
FROM sys.dm_db_index_physical_stats ( db_id('nom_base'),NULL, NULL, NULL, 'DETAILED')a          
INNER JOIN sys.indexes b ON a.object_id = b.object_id 
AND a.index_id = b.index_id

[mikedavem]

Une table HEAP par définition est une table qui ne possède pas d'index cluster.

Une table qui ne possède pas d'index cluster aura un indexid = 0

Code :

1
2
3
4
5
6
7
8
9
SELECT 
 OBJECT_NAME(a.object_id) AS table_name, 
 b.name AS index_name, 
 index_type_desc AS index_type, 
 avg_fragmentation_in_percent AS fragmentation 
FROM sys.dm_db_index_physical_stats (db_id('nom_base'),NULL, NULL, NULL, 'DETAILED') a 
INNER JOIN sys.indexes b ON a.object_id = b.object_id 
AND a.index_id = b.index_id 
WHERE b.index_id = 0;

Cela ne sert pas à grand chose de regarder la fragmentation d'une table heap. Votre problème de performance peut venir du fait que vous manquiez d'indexes stratégiques sur vos tables. Sans index pas de recherche possible mais uniquement des scans de vos tables.

++

[Liloye]

Sur votre dernière remarque je suis d'accord !

Pas de clé primaire ! pas d'index ! c'est quand même quelque chose !!!

En revanche, il est vrai que je me suis arretée sur ces fameux index, car effectivement ils étaient très fragmentés.... Et j'essayais de trouver une explication à ces remontés... Explications qui n'a donc aucun interet c'est bien ca ?

[elsuket]

Bonjour,

Une table peut ne pas avoir d'index cluster et avoir des index non-cluster.
Un index se fragmente dès lors que les valeurs des colonnes sous-jacentes sont fortement modifiées (et encore ce n'est pas toujours le cas).

La fragmentation peut aussi être due au type de données des colonnes qui constituent la clé, surtout dans le cas où ce ne sont pas des entiers (typiquement une chaîne de caractère ou des valeurs de type uniqueidentifier).

Pas de clé primaire ! pas d'index ! c'est quand même quelque chose !!!

Attention : lors de l'ajout d'une clé primaire, par défaut, un index cluster est créé sur la table.
Mais rien n'empêche d'avoir l'index cluster sur une autre colonne, ou pour une contrainte d'unicité.

Explications qui n'a donc aucun interet c'est bien ca ?

Un index très fragmenté est pénalisant :
- Pour les DELETE lors de la recherche de lignes vérifiant le prédicat / filtre
- Pour les UPDATE, pour la même raison
- Pour les SELECT puisqu'on lit plus de pages que nécessaire
- Peut-être pas pour les INSERT

@++

[mikedavem]

En revanche, il est vrai que je me suis arretée sur ces fameux index, car effectivement ils étaient très fragmentés.... Et j'essayais de trouver une explication à ces remontés... Explications qui n'a donc aucun interet c'est bien ca ?

Il n'est pas rare d'avoir des tables HEAP fragmentés. Cependant lors d'un scan d'une table HEAP la fragmentation ne joue pas car celles-ci sont lues dans l'ordre dictée par la page IAM de l'objet associé. Cependant vous êtes obligés de lire toute la table alors que seules certaines données vous intéressent.

Dans le cas d'un index (cluster et non cluster) c'est le contraire. La lecture de l'index peut fortement être affecté par la fragmentation mais ce dernier vous permet de ne lire que la portion de données qui vous intéresse.

Le fait de ne pas avoir de clé primaire sur vos tables suppose fortement que la modélisation de votre base ne doit pas être dans les règles d'une part et cela peut affecter vos performances dans le cadre des jointures entre tables (si vous en avez) d'autre part.

++

[SQLpro]

SQL Server considère que tout ce qui stocke de l'information s’appelle un index....
Vous trouverez donc dans la vue sys.indexes, :
1) des tables (index HEAP)
2) des tables avec index CLUTERED
3) des index non CLUSTERED
Effectivement une table (index HEAP) peut être fortement fragmentée, mais cela ne pose généralement pas de problème majeur, car ce sera toujours un scan, puisque une table n'est pas un vrai index !
Il vaudrait mieux que toutes vos tables soient pourvues d'un index CLUSTERED, et en principe via une PK.
Si cela n'est pas possible vous pouvez défragmenter une table en procédant en 2 temps :
1) créer un index clustered
2) le détruire
Au passage tous les index sur cette table seront impactés.

A +

[Liloye]

La fragmentation et les différents index là j'était OK...

Ce qui m'a pertubé c'était bien les resultats de ces fameux index HEAP, donc là aussi c'est Ok et je vous en remercie....

Je ferais une remarque au projet pour le manque de contrainte d'intégrité (inexistant) et ferais passer un plan de maintenance plus régulièrement pour défrag les index existants...

Merci encore et bonne journée à tous !