Taille transaction log et reconstruction index

[deviljoker]

bonjour,

sur une des bases de prod, j'ai un plan de maintenance qui :
- sauvegarde le journal toutes les heures
- réorganise les index tous les jours à 01H00
- sauvegarde la base à 02h00

le petit soucis c'est que la réorganisation des index fait gonfler mon journal transactionnel et prend plus de 90% du disque du journal
la sauvegarde de journal suivante vide le fichier journal mais sans le retailler...

donc comment peut on gérer cette taille excessive de fichier ?
dbcc shrinkfile en tâche planifiée...?

[ylarvor]

prend plus de 90% du disque du journal

Quel est votre version de sql serveur ?
Quel est la taille du disque de log ?
Quel est la taille de votre base de données ? du log initial ?

Vous n'êtes pas obligé de reconstruire tous les jours, une fois par mois, cela suffit... faites une défragmentation chaque jour.

[vtrone]

Pour faire un SHRINKFILE du log:

Code :

1
2
3
4
5
6
7
8
9
ALTER DATABASE mabase
SET RECOVERY SIMPLE
GO
-- réduit le log à 16 MB
DBCC SHRINKFILE ('mon_fichier_log',16)
GO
ALTER DATABASE mabase
SET RECOVERY FULL
GO

[oadin]

une question par rapport a ce qu'il est écrit ici plus haut (je sens que le problème va se poser bientot chez nous)

Comment je fais un alter database recovery mode simple avant et full après alors que ma base de donnée est en mirroir sql et donc réclame du mode full?

[deviljoker]

Envoyé par ylarvor

Quel est votre version de sql serveur ?
Quel est la taille du disque de log ?
Quel est la taille de votre base de données ? du log initial ?

Vous n'êtes pas obligé de reconstruire tous les jours, une fois par mois, cela suffit... faites une défragmentation chaque jour.

-SQLSERVER 2000
-disque de log 8 Go
-fichier de données 5,2 Go effectifs
-fichier de log initial 1Go et grimpe à 4,6Go suite à la réindexation

puis d'autres bases sur les mêmes disques mains moindres

[ylarvor]

Première solution (originale):

Essayez d'utiliser le script de petit dej chaque jour qui utilise la fonction DBCC INDEX DEFRAG en adaptant MAXFRAG à votre base pour voir si cela à une moindre influence sur votre fichier de log. Cela sera moins efficace qu'une reconstruction mais cela affecte moins les performances de la base.

Essayez d'utiliser le deuxième script fourni une fois par mois en appliquant la deuxième solution juste apres.

Pour en savoir plus : http://blog.developpez.com/index.php...000_-_Rindexer

Deuxième solution :

Code :

Sauvegarde complète.

puis

Code :

BACKUP LOG basededonnees WITH  TRUNCATE_ONLY

puis

Code :

dbcc shrinkfile (basededonnees_log)

[oadin]

Je vais reprendre une question que j'avais posé un peu plus haut mais avec peut être plus d'explication.

Nous avons passé nos serveurs en mirroir ce we, le problème est la taille du journal de transaction le jour du rebuild des index.

Il y a des moyens mal propre qui consiste a faire un backup directement après full et après effacer tout les fichiers de log.

Mais ça me force a avoir 2 fois la taille de ma base de donnée minimum (une fois pour mon backup, une fois pour mon journal des log qui fait la même taille que ma base de donnée ou presque).

On a regardé avec notre consultant sql si il existait un moyen propre de réduire cette taille de log, mais il n'a pu me fournir aucune solution valable.
Si ce n'est défragmenter qu'une partie de mes index (pas tous), ou de faire des backups du log pdt l'opération de reconstruction d'index... (je ne vois pas la différence que j'aurai entre un fichier de 40go et 10 fichiers de 4go ).

Il n'a pas pu me donner une méthode de reconstruction d'index n'utilisant pas le journal de transaction, on utilise un maintenance plan actuellement pour réorganiser notre db le we.

Je me demande si il n'y aurait pas aussi un soucis du coté de nos journal de transaction que je trouve très gros. J'ai du mal a estimer la quantité de donnée encodée/modifiée/supprimée par jour, mais 200mo par heure, notre entreprise est très loin de l'activité de site comme ebay

Nous sommes en sql server 2005 SP2 x64 avec des bases de donnée en compatibilité 8.0 rien de compliqué sur nos tables sauf des accès sql, du mirroring

Merci de votre aide

[deviljoker]

Envoyé par oadin

Je vais reprendre une question que j'avais posé un peu plus haut mais avec peut être plus d'explication.

Nous avons passé nos serveurs en mirroir ce we, le problème est la taille du journal de transaction le jour du rebuild des index.

Il y a des moyens mal propre qui consiste a faire un backup directement après full et après effacer tout les fichiers de log.

Mais ça me force a avoir 2 fois la taille de ma base de donnée minimum (une fois pour mon backup, une fois pour mon journal des log qui fait la même taille que ma base de donnée ou presque).

On a regardé avec notre consultant sql si il existait un moyen propre de réduire cette taille de log, mais il n'a pu me fournir aucune solution valable.
Si ce n'est défragmenter qu'une partie de mes index (pas tous), ou de faire des backups du log pdt l'opération de reconstruction d'index... (je ne vois pas la différence que j'aurai entre un fichier de 40go et 10 fichiers de 4go ).

Il n'a pas pu me donner une méthode de reconstruction d'index n'utilisant pas le journal de transaction, on utilise un maintenance plan actuellement pour réorganiser notre db le we.

Je me demande si il n'y aurait pas aussi un soucis du coté de nos journal de transaction que je trouve très gros. J'ai du mal a estimer la quantité de donnée encodée/modifiée/supprimée par jour, mais 200mo par heure, notre entreprise est très loin de l'activité de site comme ebay

Nous sommes en sql server 2005 SP2 x64 avec des bases de donnée en compatibilité 8.0 rien de compliqué sur nos tables sauf des accès sql, du mirroring

Merci de votre aide

dans mon cas je n'ai pas trouvé d'autres solutions que d'agrandir le disque du journal
la sauvegarde des logs pendant la reconstruction : j'ai testé ça n'a pas marché chez moi... la reconstruction passe apparemment en priorité.
par contre l'idée est loin d'être bête, la sauvegarde des logs prendrait au final autant de place mais ton fichier de log lui même ne grossirait pas de la même manière

d'après ce que j'ai compris, c'est la taille des sauvegardes de log qui te gêne mais 200Mo par heure sur une base de 40Go ça ne me paraît pas énorme...

[SQLpro]

Le passage au mode simple de journalisation impliquerait de casser le mirroring.
Il ne sert à rien de reconstruire tous les index. C'est cela qui sature votre journal.

un backup directement après full et après effacer tout les fichiers de log.

C'est effectivement une bonne solution. Qu'est ce qui vous en empêche ? Vous pouvez faire des sauvegardes sur un disque externe voire une bande directement dans SQL Server.

Il faut s'intéresser à ne reconstruire ou défragmenter que les index qui le nécessite. Ainsi vous irez plus vite et journaliserez moins.

On a regardé avec notre consultant sql si il existait un moyen propre de réduire cette taille de log

Quand à la taille du journal, ce n'est pas un problème. Mieux vaut laisser une grande taille au JT, car sinon vous allez vous payer des opérations de croissance du JT de nouveau, opération hautement perturbante à tout point de vue : transactions anormalement longues, blocage des utilisateurs... et donc performances lamentables !

Votre consultant devrait savoir que réduire la taille d'un JT qui a grossit est une opération contre performante dont l'intérêt est nul. En effet si le JT à grossit il reviendra un jour ou l'autre à cette même taille et par conséquent aura besoin de faire des opérations de croissance de fichier qui à nouveau seront problématiques...

Le seul moyen de réduire drastiquement la journalisation est de ne faire la maintenance des index que pour les index dont le taux de fragmentation logique ou physique de page ou d'extension le mérite et non pas de tout défragmenter en incluant même les index n'ayant pas de fragmentation. Autrement dit évitez le plan par défaut de MS !
J'ai une procédure qui optimise les ré indexation de VLDB. Je peut vous la fournir... (étant en déplacement pour donner une formation d'optimisation, je ne puis vous la communiquer maintenant).

Un JT n'a pas a être gros ou petit. Il doit contenir le nécessaire. Sa taille n'a aucune importance. En général je considère qu'un JT doit être taillé à 25 ou 33 % de la taille de la base à terme. Par exemple si votre base de données doit faire 100 Go dans 3 ans, alors le JT devrait être taillé à 25 ou 33 Go.
De la même manière si vous voulez des performances il vaudrait mieux que votre base ait été créée avec des fichiers de taille fixe. Cela éviterait toute fragmentation physique des fichiers système et En accélérerait les mises à jour (INSERT, UPDATE notamment) de façon importante.

Pour preuve, voici un test qui vous en dira long...

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
-- le répertoire C:\SQLDB\ doit préalablement exister sur votre PC
USE master
GO
 
IF EXISTS(SELECT * FROM sys.DATABASES WHERE name = 'TEST_FICHIER_VAR')
   DROP DATABASE TEST_FICHIER_VAR
GO
 
CREATE DATABASE TEST_FICHIER_VAR
ON PRIMARY ( NAME = DATA,
    FILENAME = 'C:\SQLDB\DATA',
    SIZE = 3MB,
    FILEGROWTH = 1MB)
LOG ON 
   (NAME = JT,
    FILENAME = 'C:\SQLDB\JT',
    SIZE = 1MB,
    FILEGROWTH = 1MB)
GO
USE TEST_FICHIER_VAR
GO
 
CREATE TABLE T (LIGNE VARCHAR(500))
GO
 
DECLARE @T1 DATETIME, @T2 DATETIME, @I INT
SET @T1 = CURRENT_TIMESTAMP
SET @I =1
 
INSERT INTO T SELECT REPLICATE('A', 500);
 
WHILE @I < 100
BEGIN
 
   INSERT INTO T SELECT TOP 3000 * FROM T
 
   SET @I = @I + 1
 
END
 
CHECKPOINT
 
SET @T2 = CURRENT_TIMESTAMP
 
SELECT CAST((@T2 - @T1) AS FLOAT) * 86400.0 AS SECONDE
 
 
USE master
GO
 
IF EXISTS(SELECT * FROM sys.DATABASES WHERE name = 'TEST_FICHIER_VAR')
   DROP DATABASE TEST_FICHIER_VAR
GO
 
CREATE DATABASE TEST_FICHIER_FIX
ON PRIMARY ( NAME = DATA,
    FILENAME = 'C:\SQLDB\DATA2',
    SIZE = 2GB,
    FILEGROWTH = 10%)
LOG ON 
   (NAME = JT,
    FILENAME = 'C:\SQLDB\JT2',
    SIZE = 2GB,
    FILEGROWTH = 10%)
GO
 
USE TEST_FICHIER_FIX
GO
 
 
CREATE TABLE T(LIGNE VARCHAR(500))
GO
 
DECLARE @T1 DATETIME, @T2 DATETIME, @I INT
SET @T1 = CURRENT_TIMESTAMP
SET @I =1
 
INSERT INTO T SELECT REPLICATE('A', 500);
 
WHILE @I < 100
BEGIN
 
   INSERT INTO T SELECT TOP 3000 * FROM T
 
   SET @I = @I + 1
 
END
 
CHECKPOINT
 
SET @T2 = CURRENT_TIMESTAMP
 
SELECT CAST((@T2 - @T1) AS FLOAT) * 86400.0 AS SECONDE
 
USE master;
GO
 
DROP DATABASE TEST_FICHIER_FIX

Votre consultant aurait du attirer votre attention plutôt la dessus.
Testé sur mon portable (2 Go de RAM, Dual Core), c'est édifiant :
Base à fichier variable = 40,14 secondes
Base à fichier fixe = 13,34 secondes

Quand à 200 Mo de JT par jour si l'essentiel est du à la maintenance des index c'est normal. Attention cependant au développement brouillon à base d'utilisation immodérée des curseurs....

De même séparer physiquement les fichiers de JT / data / tempdb / index/ pagefile.sys sur des disques physiques différents est non seulement fortement préconisé, mais améliore singulièrement les temps de réponse...

Enfin, attention au 64 bits, c'est certes séduisant pour gérer un bon niveau de cache mais contre performant pour les lectures/écritures au niveau disque. En effet, les données dans les disques étant 32 bits il y a conversion 32/64 bits à chaque fois, ce qui fait perdre du temps. On a donc coutume de dire que c'est adapté aux bases OLAP (analysis services) mais pas aux bases de données transactionnelles (OLTP) c'est à dire celles utilisant le moteur SQL. Maintenant je ne connais pas votre cas...

A +

[oadin]

Bonjour, merci de votre réponse comme d'habitude clair et précise

Et qui cette fois ci confirme plus ou moins les dire de notre consultant.

Concernant les performances sql server 2005 64bits, je vais m'en inquiéter car nous avons passer notre base de donnée qui est plus de style OLTP pour gagner en performance.
Nous avons eu un gros gain en performance par le fait de passer a 1.7go de mémoire pour le processus Sql server (nous n'avions pas mis /3gb dans le boot.ini ) a 5go donc après revérification de ma part les temps d'attention de type io sont devenus quasi inexistant.

Et nous avons passé notre disque des fichiers de Data en Raid 5 qui c'est vrai reste encore une fois moins performant en écriture, mais nos utilisateurs ne s'en plaignent pas.

Je vais donc continuer a examiner cela au jour par jour en espérant que tout continue a bien marcher.


Pour la réindexation d'une partie des index, nous allons faire des tests ce we, mais je suis persuadé que nous ne gagneront pas bcp en temps car les index les plus fragmenté sont ceux des tables les plus grosses. Je dirais que 60% de la taille de notre db se résume en une dizaine de table maximum sur presque 1.000 et c'est tables sont évidement les plus souvent modifiée.
Nous devons encore mettre en place un système de partitionnement horizontale sur ces tables.
Enfin ce n'est pas au gout du jour

[SQLpro]

Code :

(nous n'avions pas mis /3gb dans le boot.ini  )

Cela ne concerne que le 32 bits... Le 64 bits n'en a pas besoin

Code :

Pour la réindexation d'une partie des index, nous allons faire des tests ce we

Il vaudrait mieux réindexer toutes les nuits.

Code :

Nous devons encore mettre en place un système de partitionnement horizontale sur ces TABLES.

Ce n'est pas la panacé et le remède peut s'avérer pire que le mal. Il faut en effet :
1° QUE LE VOLUME DE LA TABLE SOIT très important (plusieurs centaines de Go).
2) que les partitions soient sur des disques physiques dfférents
3) aligner le partitionnement des index sur le partitionnement des données
Bref cela concerne les VLDB (> 1 To)

Je pense que vos problèmes de performances viennent d'autres points durs. Il serait intéressant de faire un audit. Je pense notamment à la qualité des données, aux clefs, aux contraintes, notamment IR, CHECK... à la modélisation des données. Par exemple combien avez vous de tables dépassant les 20 colonnes ?

A +

[oadin]

Pour le /3gb je parlais de notre ancien serveur en 32bit oui

Pour le partitionnement, nous sommes en train d'y réfléchir, pour moi ça me semble un gros gain sur certaines de nos tables car nous avons des tables "volumineuse" qq go, mais surtout ce sont des tables de production donc on se sert des valeurs des 10 derniers jours avec une fréquence très intense. Par contre on ne se sert des valeurs antérieurs que très occasionnellement (pour des statistiques mais nous devons le faire 1 fois par mois contre des milliers de fois).

La présentation de nos données a assez bien de problème a mon sens, mais les modifications se font lentement, il faut donc tenter de trouver des solutions "temporaires" pour améliorer les performances.

Notre modélisation est incorrect actuellement dans le sens ou, je pense que nos index ne sont pas bon, toujours le même type d'index (alors que justement dans le cas des tables citées plus haut un index cluster serait surement plus interressant).
Des types de donnée mal choisi mais que nous ne pouvons pas modifié faute du langage qui attaque la base de donnée, les valeurs numériques sont stockés sous format de numeric(x,x) et decimal(x,x).
Aucune clé étrangère mise en place pour l'instant (ça fait peur d'imaginer de les mettre en place si ça a été mal géré au dessus ). Les contraintes j'essaye de les améliorer au fur et a mesure (remplacement de null par des valeurs par défaut, et insertion de contraintes).

Pour les tables j'en ai 80 (soit 10%) avec un nombre de colonne > 20 dont une vingtaine avec plus de 90 colonnes.
Je remarque d'ailleurs que ce sont les tables qui posent le plus souvent problème. Mais je ne vois pas trop l'impact ou plutot comment corriger cela doucement (si ce n'est proposer de réécrire)

[SQLpro]

Pour le partitionnement, nous sommes en train d'y réfléchir, pour moi ça me semble un gros gain sur certaines de nos tables car nous avons des tables "volumineuse" qq go, mais surtout ce sont des tables de production donc on se sert des valeurs des 10 derniers jours avec une fréquence très intense. Par contre on ne se sert des valeurs antérieurs que très occasionnellement (pour des statistiques mais nous devons le faire 1 fois par mois contre des milliers de fois).

Le partitionnement ne vous aidera en aucune manière à obtenir des gains de performance dans le cas indiqué si vos tables on été structurée correctement. Il peut en revanche devenir contre performant dans le cas précis que vous évoquez. En effet le partitionnement n'a d'intérêt que pour les mise à jours de données (INSERT, UPDATE, DELETE) et a condition que ces partitions soient uniformes au regard de la fréquence des mises à jour. Autrement dit il doit y avoir autant d'UPDATE/DELETE/INSERT dans chaque partition !

Notre modélisation est incorrect actuellement dans le sens ou, je pense que nos index ne sont pas bon, toujours le même type d'index (alors que justement dans le cas des tables citées plus haut un index cluster serait surement plus interressant).

LMe manque d'indexation est à tout niveau TRES TRES TRES contre performant. Mais il faut veiller à placer les bons index. C'est à dire ceux qui sont réellement efficace. Peu importe leur nombre (se limiter à 3 index par table est par exemple une bétise...).
En cette matière toutes les tables devrait avoir un index cluster, mais il convient de bien le choisir car il eut apporter des dégâts.
Le plus important est de savoir quels sont les types de données sous jacents aux clefs (primaire, unique étragnères).
En particulier TOUTES LES CLEFS ETRANGERES DOIVENT ETRE INDEXEES ! Ce que ne font aucun SGBDR par défaut.

Des types de donnée mal choisi ...

Je pense en particulier à l'usage immodéré des VARCHAR.... ou pire des NATIONAL CHAR/VARCHAR...

Aucune clé étrangère mise en place pour l'instant (ça fait peur d'imaginer de les mettre en place si ça a été mal géré au dessus ). Les contraintes j'essaye de les améliorer au fur et a mesure (remplacement de null par des valeurs par défaut, et insertion de contraintes).

Il est important de placer les FK. C'est sans grand risque.
Le remplacement de NULL est une mauvaise chose. Il ne faut JAMAIS céder à la tentation de ne pas avoir de NULL. La recherche d'une colonne NULL ne pose pas de problème particulier, tandis que son rétablissement oblige à écrire des requêtes qui la plupart du temps ne peuvent pas être optimisées...
Les contraintes aident le moteur SQL à faire de bons plans de requêtes.

Pour les tables j'en ai 80 (soit 10%) avec un nombre de colonne > 20 dont une vingtaine avec plus de 90 colonnes.
Je remarque d'ailleurs que ce sont les tables qui posent le plus souvent problème. Mais je ne vois pas trop l'impact ou plutot comment corriger cela doucement (si ce n'est proposer de réécrire)

Et voila votre problème majeur : une mauvaise modélisation de la base de données. Vous pourrez toujours partitionner et rajouter autant d'index que vous le souhaitez, c'est cautère sur jambe de bois.
Le respect des formes normales est un gage d'extrême performances. relisez les article que j'ai écrit pour SQL Server magazine sur l'optimisation.
http://sqlpro.developpez.com/optimisation/
Il y a des techniques pour faire en sorte que votre problème se résolve...

Je serais heureux de vous aider... Ou êtes vous ?

A +