Indexation de base de données

Version imprimable

Bonjour,

Je ne comprends pas pourquoi mon code ne marche pas

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
    DECLARE @TableName NVARCHAR(128), @IndexName NVARCHAR(128), @SQL NVARCHAR(MAX)
 
    DECLARE IndexCursor CURSOR FOR
    SELECT OBJECT_NAME(IPS.object_id) AS TableName, I.name AS IndexName, IPS.index_id, IPS.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') IPS
    JOIN sys.indexes I ON IPS.object_id = I.object_id AND IPS.index_id = I.index_id
    WHERE I.type_desc <> 'HEAP' AND avg_fragmentation_in_percent > 10
    ORDER BY avg_fragmentation_in_percent DESC;
 
    OPEN IndexCursor
    FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @SQL,
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = CASE 
            WHEN avg_fragmentation_in_percent > 30 THEN 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REBUILD;'
            ELSE 'ALTER INDEX [' + @IndexName + '] ON [' + @TableName + '] REORGANIZE;'
        END
        PRINT @SQL
        EXEC sp_executesql @SQL
        FETCH NEXT FROM IndexCursor INTO @TableName, @IndexName, @SQL
    END
 
    CLOSE IndexCursor
    DEALLOCATE IndexCursor

Pouvez-vous m'aider ?

05/04/2025, 10h52
al1_24
Bonjour,

Citation:

Envoyé par Mont73

mon code ne marche pas

Qu'entends-tu par là ?
Où rencontres-tu un problème ?
- Une erreur d'exécution ?
  Quel est le message associé ?
- Un résultat erroné ?
  Quel est le résultat obtenu ? Le résultat attendu ?
  En quoi le résultat obtenu ne correspond-il pas à tes attentes ?
06/04/2025, 09h13
Mont73

Il me dit que le champ testé dans case when n’existe pas alors qu'il est dans ma requête.

J'attent du script qu'il reconstruise mes indexes ou qu'il les reorganise suivant le pourcentage de fragment

Il manque la variable pour recueillir cette information... C'est là votre erreur !
De plus vous ne mettez pas le schéma SQL en préfixe de la table.... Cela partira en erreur si un index de table système est visé par votre script, car les tables systèmes sont dans le schéma "sys".

Aussi il est préférable de faire des jointures plutôt que d'appeler des fonctions comme OBJECT_NAME
ensuite il est préférable d'ouvrir un curseur avec les options suivantes :

Code:

LOCAL FORWARD_ONLY STATIC READ_ONLY

Enfin vous n'utilisez pas la colonne index_id. Elle est donc inutile dans votre SELECT.
Conseil :

utilisez "sysname" qui est le domaine réservé pour les identifiants SQL à la place de NVARCHAR(128)...
terminez vos instructions par le point-virgule ";"
utilisez QUOTENAME pour éviter les effets de bord des identifiants qui ne respecte pas la norme SQL
faite précer vos chaines de caractères de la lettre "N" majuscule pour le type NCHAR/NVARCHAR
utilisez directement EXEC("ma requête string") plutôt que la procédure sp_executesql pour des requêtes aussi simples...

Script rectifié :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
DECLARE @TableName sysname, 
        @TABLE_SCHEMA sysname,
        @IndexName sysname, 
        @FRAGMENTATION FLOAT,
        @SQL NVARCHAR(MAX);
 
DECLARE IndexCursor CURSOR 
   LOCAL FORWARD_ONLY STATIC READ_ONLY
   FOR   SELECT o.name AS TableName, 
                s.name AS TableSchema,
                i.name AS IndexName, 
                IPS.avg_fragmentation_in_percent
         FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') 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  i.type_desc <> 'HEAP' AND avg_fragmentation_in_percent > 10
         ORDER  BY avg_fragmentation_in_percent DESC;
 
OPEN IndexCursor;
 
FETCH NEXT FROM IndexCursor 
   INTO @TableName, @TABLE_SCHEMA, @IndexName, @FRAGMENTATION;
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
   SET @SQL = N'ALTER INDEX ' + QUOTENAME(@IndexName) 
            + N' ON ' + QUOTENAME (@TABLE_SCHEMA) + N'.' + QUOTENAME(@TableName) 
            + CASE 
                 WHEN @FRAGMENTATION > 30 
                    THEN N' REBUILD;'
                 ELSE N' REORGANIZE;'
              END;
 
    PRINT @SQL;
 
    EXEC (@SQL);
 
    FETCH NEXT FROM IndexCursor 
       INTO @TableName, @TABLE_SCHEMA, @IndexName, @FRAGMENTATION;
END
 
CLOSE IndexCursor;
 
DEALLOCATE IndexCursor;

Enfin il n'est pas souhaitable de défragmenter de très petits index (moins de 64 pages par exemple) et il est en revanche intéressant de reconstruire les tables en HEAP...

Dans ce cas :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
DECLARE @TableName sysname, 
        @TABLE_SCHEMA sysname,
        @IndexName sysname, 
        @FRAGMENTATION FLOAT,
        @HEAP bit,
        @SQL NVARCHAR(MAX);
 
DECLARE IndexCursor CURSOR 
   LOCAL FORWARD_ONLY STATIC READ_ONLY
   FOR   SELECT QUOTENAME(o.name) AS TableName, 
                QUOTENAME(s.name) AS TableSchema,
                QUOTENAME(i.name) AS IndexName, 
                IPS.avg_fragmentation_in_percent,
                CASE WHEN i.type_desc = 'HEAP' THEN 1 ELSE 0 END AS IS_HEAP
         FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') 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 > 10
                AND page_count > 64
         ORDER  BY avg_fragmentation_in_percent DESC;
 
OPEN IndexCursor;
 
FETCH NEXT FROM IndexCursor 
   INTO @TableName, @TABLE_SCHEMA, @IndexName, @FRAGMENTATION, @HEAP;
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
   SET @SQL = CASE
                 WHEN @HEAP = 1
                    THEN N'ALTER TABLE ' + @TABLE_SCHEMA + N'.' + @TableName + ' REBUILD;'
                 WHEN @FRAGMENTATION > 30 
                    THEN  N' REBUILD;'
                 ELSE N'ALTER INDEX ' + @IndexName 
                         + N' ON ' + @TABLE_SCHEMA + N'.' + @TableName + N' REORGANIZE;'
              END;
 
    PRINT @SQL;
 
    EXEC (@SQL);
 
    FETCH NEXT FROM IndexCursor 
       INTO @TableName, @TABLE_SCHEMA, @IndexName, @FRAGMENTATION, @HEAP;
 
END
 
CLOSE IndexCursor;
 
DEALLOCATE IndexCursor;

A +

09/04/2025, 12h09
Michel.Priori

Citation:

Envoyé par Mont73

J'attent du script qu'il reconstruise mes indexes ou qu'il les reorganise suivant le pourcentage de fragment

C'est toujours interressant de savoir faire les choses par soi même ^^

Cependant, au delà de l'étape d'apprentissage, il est utile d'utiliser du code maintenu.
Et pour le besoin explicité, je vous conseille d'aller voir celui de OLA : https://ola.hallengren.com/