Discussion :

[jesocr]

Bonjour à tous
J'ai une petite question et j'espère que vous pourrez m'aider

Je m'occupe des serveurs SQL de ma boite et je remarque au niveau de la fragmentation des index, des index fortement fragmenté mais dont le nom est NULL

Schema Table Index avg_fragmentation_in_percent page_count
SCHELA TABLE NULL 99,7014925373134 1995

Je souhaitai savoir à quoi cela correspond, car lorsque je vais sur la table correspondante, tous mes index sont OK

Merci d'avance de votre aide
Axel

[Michel.Priori]

bonjour,

Quelle est la requête qui a généré la réponse ?
Je suspecte la présence de mots clé particuliers

[jesocr]

Celle de Microsoft

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
   ON ips.object_id = i.object_id
  AND ips.index_id = i.index_id
ORDER BY page_count DESC;

[Michel.Priori]

re,

par hasard, est-ce que la colonne index_type n'indiquerait pas 'HEAP' ?

[jesocr]

Si effectivement et si je ne me trompe pas, ça veut dire que la table n'a pas de clé primaire.

Donc je m'interroge du pourquoi de l'intégration de ce résultat dans la fragmentation des index le fait qu'il n y ai pas de clé primaire.

[Michel.Priori]

Si effectivement et si je ne me trompe pas, ça veut dire que la table n'a pas de clé primaire.

Confusion : le fait que la table soit un HEAP signifie que qu'elle n'a pas d'index cluster ; des index non cluster peuvent exister (la PK peut être non cluster)

Donc je m'interroge du pourquoi de l'intégration de ce résultat dans la fragmentation des index le fait qu'il n y ai pas de clé primaire.

C'est vrai que le nom de la vue porte à confusion
Dans le même temps avoir 2 vues, une pour les index et l'autre pour les HEAP, s'eut été un peu lourd.
Comment aurais tu nommé cette vue ?

[SQLpro]

De manière à simplifier le travail des administrateurs de bases de données (DBA) Microsoft a simplifié la vision des objets physiques stockant de données à l'index qui peut être :

• une table en "tas" (heap) c'est à dire dépourvue d'index clustered
• un index clustered, c'est à dire que l'index est "emplacé" dans la table
• un index externe à la table (non clustered, XML, spatial...)

Une simplification inverse aurait pu être faire en considérant que tout objet de stockage est une table donc certaines sont des index !

C'est d toute façon plus simple à exploiter que deux objets distincts qui serait les index d'un côté et les tables en heap dans une autre vue...

C'est aussi pourquoi lorsque l'on fait la jointure entre tables et index, les tables en HEAP n'ont pas de nom d'index... Et c'est aussi pourquoi pour défragmenter une table en HEAP one nepeut pas faire une commande ALTER INDEX, mais on doit passer par la commande ALTER TABLE ... REBUILD.

Exemple de script...

• défragmenter une base avec les seuils suivants :
• -- 15 % de fragmentation au moins
• -- 100 pages au moins dans l'objet (index ou table)
• -- si moins de 30 % de fragmentation d'index alors REORGANIZE sinon REBUILD
• -- LIMTES : tables non partitionnées, index relationnels

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE @SQL NVARCHAR(max) = N'';
SELECT @SQL = @SQL + 
       CASE
          WHEN i.name IS NULL --> c'est une table !
             THEN N'ALTER TABLE ' 
                  + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' REBUILD;'
          WHEN avg_fragmentation_in_percent < 30  --> on réorganize
             THEN N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' 
                  + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' REORGANIZE;'
          ELSE    N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' 
                  + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' REBUILD;'
       END
FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS ips
       JOIN sys.indexes AS i ON ips.object_id = i.object_id 
                                AND ips.index_id = i.index_id
       JOIN sys.objects AS o ON ips.object_id = o.object_id
       JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE  avg_fragmentation_in_percent > 15
       AND page_count > 100;
EXEC (@SQL);

A +

Et pour de plus amples information, mon livre "SQL Server 2014", partie Admin en accès gratuit :
https://st1.eyrolles.com/97822121359..._FILIGRANE.pdf

[jesocr]

Confusion : le fait que la table soit un HEAP signifie que qu'elle n'a pas d'index cluster ; des index non cluster peuvent exister (la PK peut être non cluster)


C'est vrai que le nom de la vue porte à confusion
Dans le même temps avoir 2 vues, une pour les index et l'autre pour les HEAP, s'eut été un peu lourd.
Comment aurais tu nommé cette vue ?

Et bien déjà il fallait que je me renseigne bien sur les tables HEAP et pourquoi Microsoft l'intègre dans son résultat.
J'ai trouvé cette documentation
https://www.red-gate.com/simple-talk...fragmentation/

Qu'en pensez vous?

Je pensais qu'une PK était cluster forcément, merci tu m'as appris un truc.

Je suis sur des tables qui, après vérification, sont surtout parcouru en select et un peu d'update et d'insert.
J'ai bien envi de demander aux devs de ma boite de travailler ça, cela ne me semble pas une bonne pratique d'avoir des tables HEAP fortement sollicité en lecture

[Michel.Priori]

Et bien déjà il fallait que je me renseigne bien sur les tables HEAP et pourquoi Microsoft l'intègre dans son résultat.
J'ai trouvé cette documentation
https://www.red-gate.com/simple-talk...fragmentation/

Qu'en pensez vous?

Le premier reflexe devrait de toujours lire la documentation de l'éditeur... https://learn.microsoft.com/en-us/sq...l-server-ver16
Ensuite, vient le temps des lectures annexes.
Ceci permet d'exclure assez rapidement les bêtises qu'on trouve parfois en navigant sur la toile ^^

[jesocr]

Merci en lisant la page Microsoft, j'en ressors que de ne pas utiliser d'index cluster n'est pas une bonne chose, sauf dans de rare cas.
En tous les cas merci, j'ai des réponses
Bonne journée