Discussion :

[Mont73]

Bonjour,

Je ne comprends pas pourquoi mon code ne marche pas

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
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 ?

[al1_24]

Bonjour,

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 ?

[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

[SQLpro]

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 :

S�lectionner tout - Visualiser dans une fen�tre � part

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 :

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
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 :

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
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 +

[Michel.Priori]

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/