SQL server 2016 index et log

Version imprimable

09/10/2024, 16h11
gunnar

SQL server 2016 index et log

Bonjour

J'ai un SQL server 2016 (version 13.0.01742). Ce serveur sert uniquement pour mon ERP divalto.
Il abrite une base de production de 98Go (log 321Mo)
Et une base de test de 84Go (log 1Mo)
Espace disponible sur le disque 114Go

Il fonctionne depuis des années mais les performances se dégradent.
Donc j'ai commencé à fouiller car mon prestataire traine des pieds pour résoudre le soucis.
Il a bien tenté un plan de maintenance des index. Mais aprés 2 échecs, il a abandonné. échec car l'espace disque était full suite à une explosion de la taille du log.

J'aimerais savoir si des solutions existent pour limiter la tailler des logs lors des opérations de maintenance des index.
J'ai cherché sur le net mais les informations sont contradictoires selon les sources.

Je suis actuellement en mode de récupération complet.
quel est le mode de récupération à appliquer avant de faire une réorganisation d'un index ? On reste en Full ? Dans ce cas mes logs vont grossir de maniére incontrôlable ?
Quel est le mode de récupération à appliquer avant de faire un rebuild d'index ? J'ai lu qu'on pouvait mettre en bulk pour un rebuild car c'est une seule opération contrairement à la réoganisation pour laquelle le mode bulk ne change rien.
Il faut que je teste un faisant un seul index pour voir ? contrairement à mon presta qui la joue bourrin et fait tous les index de la base ?
Et question bête un log ça reste combien de temps. Car si mon log grossi, il va rester à cette taille combien de temps ?

Sachant que je voudrais optimiser les index de la table des mouvements par exemple.
C'est la table la plus utilisée et de loin.
Elle contient 14924Mb de données et 29996Mb d'index
Les index sont fragmentés de 0% à 68.02% selon leur utilisation.
L'index F est par exemple fragmenté à 57.91% (125736 pages).

Merci par avance.
09/10/2024, 19h11
AaâÂäÄàAaâÂäÄàAaâÂäÄ

Citation:

Envoyé par gunnar

Et question bête un log ça reste combien de temps. Car si mon log grossi, il va rester à cette taille combien de temps ?

Jusqu'au moment de la sauvegarde du Log !
Il vous faudrait d'abord comprendre le besoin du mode de récupération et la stratégie de sauvegarde qui vient avec.

Mais de ce que je vois, ça serait bien plus simple d'ajouter de l'espace à votre machine, au prix que ça coûte maintenant.

Citation:

Envoyé par gunnar

J'ai un SQL server 2016 (version 13.0.01742).

ATTENTION : support terminé depuis le 13 juil. 2021

Citation:

Envoyé par gunnar

Il fonctionne depuis des années mais les performances se dégradent.
Donc j'ai commencé à fouiller car mon prestataire traine des pieds pour résoudre le soucis.
Il a bien tenté un plan de maintenance des index. Mais après 2 échecs, il a abandonné. échec car l'espace disque était full suite à une explosion de la taille du log.
Je suis actuellement en mode de récupération complet.
J'aimerais savoir si des solutions existent pour limiter la tailler des logs lors des opérations de maintenance des index.
J'ai cherché sur le net mais les informations sont contradictoires selon les sources.

Le journal des transactions en mode FULL enregistre tout, y compris toutes les entrées de tous les index défragmentés ce qui peut devenir un volume important.

Pour palier à ce problème l’idéal est la séquence suivante :
1) sauvegarde FULL de la base
2) journalisation en mode simple de la base
3) défragmentation, reconstruction intelligente
4) création des index manquant selon seuil de gain
5) journalisation en mode full de la base
6) nouvelle sauvegarde FULL de la base

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
52
53
54
55
56
57
58
59
60
61
62
63
USE MA_BASE; --> remplacer MA_BASE par le nom de votre base
GO
 
--> 1) sauvegarde FULL de la base
BACKUP DATABASE MA_BASE TO DISK = 'BACKUP_MA_BASE_BEFORE INDEX.BAK' WITH COMPRESSION; --> remplacer MA_BASE par le nom de votre base
 
--> 2) journalisation en mode simple de la base
ALTER DATABASE MA_BASE SET RECOVERY SIMPLE;
 
--> 3) défragmentation, reconstruction intelligente
DECLARE @SQL NVARCHAR(max) = N''
SELECT @SQL = @SQL + 
       CASE 
          WHEN i.name IS NULL 
             THEN N'ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' REBUILD;'
          WHEN avg_fragmentation_in_percent < 30 
             THEN N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' REORGANIZE;'
             ELSE N'ALTER INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' REBUILD;'
       END + ' BACKUP LOG MA_BASE TO DISK = ''NUL'';' --> remplacer MA_BASE par le nom de votre base
FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , NULL) AS ips
       JOIN sys.objects AS o ON ips.object_id = o.object_id
       JOIN sys.schemas AS s ON s.schema_id = o.schema_id
       JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE  page_count > 64 
       AND avg_fragmentation_in_percent > 10;
       PRINT @SQL
EXEC (@SQL);
GO
 
--> 4) création des index manquant selon seuil de gain
DECLARE @SQL NVARCHAR(max) = N'';
WITH 
T AS
(
SELECT d.*, 
       ROW_NUMBER() OVER (ORDER BY avg_user_impact * avg_total_user_cost DESC) AS INDICE,
       COUNT(*) OVER() AS NBR
FROM   sys.dm_db_missing_index_details AS d
       JOIN sys.dm_db_missing_index_groups AS g
          ON d.index_handle = g.index_handle
       JOIN sys.dm_db_missing_index_group_stats AS s
          ON g.index_group_handle = s.group_handle
WHERE  d.database_id = DB_ID()
)
SELECT @SQL = @SQL 
       + N'CREATE INDEX X_gunnar_' + N'_' + CAST(NEWID() AS VARCHAR(38))
       + N'_' + CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) 
       + N' ON ' + statement 
       + ' (' + COALESCE(T.equality_columns + ', ' + T.inequality_columns, 
                         T.equality_columns,         T.inequality_columns) + ')' 
       + CASE WHEN included_columns IS NULL THEN N''
              ELSE ' INCLUDE (' + included_columns + ')' END + ';' 
       + N' BACKUP LOG MA_BASE TO DISK = ''NUL'';' --> remplacer MA_BASE par le nom de votre base
FROM   T 
WHERE  INDICE <= NBR / 2;
EXEC (@SQL);
 
 
--> 5) journalisation en mode full de la base
ALTER DATABASE MA_BASE SET RECOVERY FULL; --> remplacer MA_BASE par le nom de votre base
 
--> 6) nouvelle sauvegarde FULL de la base
BACKUP DATABASE MA_BASE TO DISK = 'BACKUP_MA_BASE AFTER INDEX.BAK' WITH COMPRESSION; --> remplacer MA_BASE par le nom de votre base

A +

10/10/2024, 10h05
Michel.Priori

Bonjour,

Citation:

Envoyé par gunnar

Il abrite une base de production de 98Go (log 321Mo)
Et une base de test de 84Go (log 1Mo)
Espace disponible sur le disque 114Go

Où est le problème ?
Il y a plus de place libre que la taille de la plus grosse des bases.

Citation:

Envoyé par gunnar

Il fonctionne depuis des années mais les performances se dégradent.
[...]
Sachant que je voudrais optimiser les index de la table des mouvements par exemple.
C'est la table la plus utilisée et de loin.
Elle contient 14924Mb de données et 29996Mb d'index

Grosso modo vous avez une table dont le taux d'occupation des index est 200% de celle de la table.
Le nom de la table (mouvements) laisse à penser que vous êtes en mode de consommation des données de type OLTP ; donc pas OLAP.

Il faudrait voir si la dégradation des perfs est sur la lecture ou l'écriture.
Voir les articles de Frédéric sur la recherche des index redondants.
10/10/2024, 17h38
gunnar

Citation:

Envoyé par AaâÂäÄàAaâÂäÄàAaâÂäÄ

Jusqu'au moment de la sauvegarde du Log !
Il vous faudrait d'abord comprendre le besoin du mode de récupération et la stratégie de sauvegarde qui vient avec.

Mais de ce que je vois, ça serait bien plus simple d'ajouter de l'espace à votre machine, au prix que ça coûte maintenant.

Bonjour

Merci de la réponse.
Bon il faut que je creuse le mode de fonctionnement des logs.

L'extension de place n'est malheureusement pas une solution car je compte remplacer le serveur dans environ 6 Mois.
10/10/2024, 17h44
gunnar

Citation:

Envoyé par SQLpro

ATTENTION : support terminé depuis le 13 juil. 2021

Pour palier à ce problème l’idéal est la séquence suivante :
1) sauvegarde FULL de la base
2) journalisation en mode simple de la base
3) défragmentation, reconstruction intelligente
4) création des index manquant selon seuil de gain
5) journalisation en mode full de la base
6) nouvelle sauvegarde FULL de la base

A +

Merci pour la réponse détaillée.
Par contre la fin du support étendu est en 14 juil. 2026. Je suppose que ça doit changer quelque chose. Le sql server est associé à l'ERP. Ma licence est associée à l'ERP. Je n'ai d'ailleurs pas le droit de l'utiliser pour autre chose.
J'espère que le prestataire ERP sait ce qu'il fait !

Pour le reste, je vais tester. En commençant par des index précis sur des tables de taille moyenne.
Je ferais un retour.
10/10/2024, 20h02
SQLpro

Citation:

Envoyé par gunnar

Merci pour la réponse détaillée.
Par contre la fin du support étendu est en 14 juil. 2026. Je suppose que ça doit changer quelque chose. Le sql server est associé à l'ERP. Ma licence est associée à l'ERP. Je n'ai d'ailleurs pas le droit de l'utiliser pour autre chose.
J'espère que le prestataire ERP sait ce qu'il fait !

He bien non... Quand vous achetez une voiture vous avez 2 ans de garantie constructeur gratuite et vous pouvez contracter un support pour 3 années supplémentaire payant auprès du constructeur. Cela ne change rien que vous en soyez propriétaire ou locataire. C'est pour cela que les loueurs de voiture renouvellent leur bagnoles fréquemment...

Dans les contrats il y a emboitement. Lorsque votre prestataire à choisit SQL Server il a signé la licence. Pour obtenir le support étendu il faut payer. Je doute fortement vu les prix que votre éditeur à payé le support étendu...
Néanmoins en cas de problème vous pouvez l'attaquer... Bonjour le coût et la durée devant les tribunaux...

Il vous faut utiliser une version plus récente... Vous pouvez utiliser SQL Server 2022 tout en restant dans le mode de rétrocompatibilité 2016... SQL Server est le seul SGBDR à permettre de faire fonctionner des bases dans toutes les versions anciennes depuis la 2008 dans une instance plus moderne....

A +