Discussion :

[Lyche]

Bonjour à tous,

je me retrouve dans une situation étrange pour la 2ème fois en un peu plus d'un mois, et j'avoue être un peu inquiété par ce phénomène.

Je m'explique, j'ai un serveur de test pour une application, dessus il y a un plan de maintenance pour les indexes et recalcule de statistiques. Lorsque j'exécute le plan de maintenance, il se trouve que les dimensions de ma base et de mes logs explosent littéralement. En 10 minutes de recalcule Ma base est passée de 32Go à 42Go et mes log de 72 à 102Go..
J'ai déjà eu le problème il y a un peu plus d'un moi ou ma base était passé de 30Go à 120 Go à la fin de la maintenance. Et là, je parle de mon mdb, pas de mes logs. Mon souci, c'est que je n'ai pas assez d'espace disque, il me reste 12Go de libre et j'ai du couper le traitement parce que je courrais droit sur un no disk space.
Avez vous déjà rencontré ce problème, si oui qu'est-ce qui le provoque? et comment l'éviter?


Cordialement,

[SQLpro]

Impossible de dire quoi que ce soit sans avoir :
1) que fais votre maintenance (donnez nous votre code)
2) quel est le mode de récupération
3) quelles sont les transactions, sessions et accès concurrents à ce moment.

A +

[Lyche]

Bonsoir,

et merci pour le temps pris.
Côté plan de maintenance, j'ai utilisé les objets de maintenance de SQL Server, à savoir défragementation (reorganize ou rebuild) des indexes. La croissance à commencé immédiatement, en 10 minutes j'avais 30Go de log en plus et 10Go de base, donc je pense qu'il en était aux rebuild.

La base est en mode de récupération simple, (suite aux traitements de la journée, le fichier de log était à 72Go), il est vidé tous les soirs via les robots de backup du client.
Côté transactions, j'avais coupé le site, donc personne n'y avait accès, cependant, j'avais oublié de couper le "calculateur" qui lance environs 10 requêtes par secondes, de simple select sur des tables vides quand personnes n'utilise le site. Ce sont des tables qui schedulent des calculs elles étaient donc vide à ce moment là.

Je suis chez moi ce soir, si cela ne suffit pas, je posterais des captures d'écran de mon plan de maintenance demain matin + une capture d'écran du moniteur d'activité avec les différentes requêtes qui se produisent une dizaine de fois par seconde.

Cordialement,
Lyche

[SQLpro]

Il n'y a que 2 possibilités pour que le fichier grossisse :
1) une transaction terminée non finalisée => DBCC OPENTRAN pour diagnostic
2) la base est en mode FULL ou BULK LOGGED et aucune sauvegarde de JT n'est faite
3) une transaction non terminée d'un processus qui dure longtemps => DBCC OPENTRAN pour diagnostic

Sans le code de votre maintenance impossible de dire ce qui se passe, c'est pourquoi il faut éviter l'assistant

A +

[Lyche]

Bonjour, désolé pour le temps de réponse, je n'ai pas pu me connecter hier.

Voici le plan de maintenance de la base



Voici un exemple de chaque commande exécutée par le plan de maintenance

Code sql :

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

1
2
3
4
5
6
7
8
 
ALTER INDEX [IX_xxxx] ON [schema].[table] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
 
 
ALTER INDEX [IX_xxxx] ON [schema].[table] REORGANIZE WITH ( LOB_COMPACTION = ON )
 
UPDATE STATISTICS [schema].[table] 
WITH FULLSCAN

Toutes les commandes REBUILD ont le même format, et toutes les commandes REORGANIZE pareil. Les deux fois ou j'ai eu le problème le plan de maintenance n'a pas dépassé la première étape, à savoir le REBUILD.
Pour le côté "obects : Table and views" il n'y a pas de vues indexées dans la base, du coup je n'ai pas ciblé uniquement les tables, je ne pense pas que ça ai un impacte particulier.

je suis obligé de faire une obfuscation des noms de tables puisque l'application est propriétaire et que je ne suis pas de la société d'édition. J'ai généré les scripts à partir des composants

[Invité]

Peux-tu expliquer pourquoi tu fais un rebuild et un Reorganize aussitôt après ?

[Lyche]

Peux-tu expliquer pourquoi tu fais un rebuild et un Reorganize aussitôt après ?

Parce que techniquement le rebuild je ne le fais qu'à 30%+ de fragmentation, et que le reorganize je le fais dans les autres cas. Après, c'est le plan de maintenance qui doit définir. Avant je gérais ça sur une requête qui construisait mes requêtes de rebuild/reorg. J'avoue ne pas avoir fait attention si il passe sur tous les indexes ou non dans les deux cas, ou si il gère la fragmentation pour faire l'un ou l'autre. Je vais faire quelques tests pour voir.

C'est une bonne remarque ^_^

Edit : Bon, en effet, ce système de plan de maintenance n'est pas assez précis, il passe dans les 2 cas sur tous les indexes.. je pense que je vais revenir à mon ancienne forme par des requêtes SQL

[olivtone]

Les UPDATE STATS après les rebuild index ne servent pas a grand chose, car les REBUILD mettent a jour les stats par défaut.
Perso je mets les UPDATE STATS pour les Stats qui ont plus de trois jours ou le nombre de lignes modifiés > 500, schédulé par un job SQL toutes a 8h du matin et a 13h ,et les stats se portent a merveille

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
 
DECLARE @SQL NVARCHAR(max);
SET @SQL = N'';
SELECT @SQL = @SQL + 'UPDATE STATISTICS ' + s.name +'.' + o.name + '(' + st.name +');'
FROM   sys.stats AS st
       LEFT OUTER JOIN sys.indexes AS i
	        ON st.object_id = i.object_id
			   AND st.name = i.name
       JOIN sys.objects AS o
	        ON st.object_id = o.object_id
       JOIN sys.schemas AS s
	        ON o.schema_id = s.schema_id	
       CROSS APPLY sys.dm_db_stats_properties (st.object_id, st.stats_id) AS sp
WHERE  modification_counter > 0
  AND  (last_updated < DATEADD(day, -3, GETDATE())
        OR  rows / NULLIF(modification_counter, 0) > 0.2)
  AND  rows > 500;
EXEC (@SQL);

Question :

as tu regarde le fichier pagefile sur ton serveur qui est fixé , j'ai déjà eu un cas similaire.

J'ai fait aussi de mon coté , un RS qui m'envoie par mail tous les jours la volume trie des tables, donc je sais par coeur, quel table a prit en volumétrie, et la je cherche pourquoi elle a grossie.

Sinon pourquoi pas augmenter la RAM, pour que la volumetrie des données soient stockées en RAM au lieu de le trier sur Disque ?

bon courage

[davidjo_20165]

Les UPDATE STATS après les rebuild index ne servent pas a grand chose, car les REBUILD mettent a jour les stats par défaut

et pour les statistics Hors indexes ?

[olivtone]

ils sont geres par :

job SQL UPDATE STATS qui tournent tous les matins a 8h du matin , 13h et 17h si modification > 500 ou Stats qui ont plus de 3 jours.

[Boubou2020]

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
 
DECLARE @SQL NVARCHAR(max);
SET @SQL = N'';
SELECT @SQL = @SQL + 'UPDATE STATISTICS ' + s.name +'.' + o.name + '(' + st.name +');'
FROM   sys.stats AS st
       LEFT OUTER JOIN sys.indexes AS i
	        ON st.object_id = i.object_id
			   AND st.name = i.name
       JOIN sys.objects AS o
	        ON st.object_id = o.object_id
       JOIN sys.schemas AS s
	        ON o.schema_id = s.schema_id	
       CROSS APPLY sys.dm_db_stats_properties (st.object_id, st.stats_id) AS sp
WHERE  modification_counter > 0
  AND  (last_updated < DATEADD(day, -3, GETDATE())
        OR  rows / NULLIF(modification_counter, 0) > 0.2)
  AND  rows > 500;
EXEC (@SQL);

Tu est sur que votre script est Fonctionnelle ?

il n y a ni l'option with fullscan ni même l'option d'échantillonnage with sample

[hmira]

Les UPDATE STATS après les rebuild index ne servent pas a grand chose, car les REBUILD mettent a jour les stats par défaut.
Perso je mets les UPDATE STATS pour les Stats qui ont plus de trois jours ou le nombre de lignes modifiés > 500, schédulé par un job SQL toutes a 8h du matin et a 13h ,et les stats se portent a merveille

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
 
DECLARE @SQL NVARCHAR(max);
SET @SQL = N'';
SELECT @SQL = @SQL + 'UPDATE STATISTICS ' + s.name +'.' + o.name + '(' + st.name +');'
FROM   sys.stats AS st
       LEFT OUTER JOIN sys.indexes AS i
	        ON st.object_id = i.object_id
			   AND st.name = i.name
       JOIN sys.objects AS o
	        ON st.object_id = o.object_id
       JOIN sys.schemas AS s
	        ON o.schema_id = s.schema_id	
       CROSS APPLY sys.dm_db_stats_properties (st.object_id, st.stats_id) AS sp
WHERE  modification_counter > 0
  AND  (last_updated < DATEADD(day, -3, GETDATE())
        OR  rows / NULLIF(modification_counter, 0) > 0.2)
  AND  rows > 500;
EXEC (@SQL);

Tu est sur que votre script est Fonctionnelle ?
il n y a ni l'option with fullscan ni même l'option d'échantillonnage with sample

Bonjour,
Le script d'olivtone est très intéressant, et sur ce point il est correct.
Lorsque ni l'option "SAMPLE" ni l'option "FULLSCAN" n'est précisée, l'optimiseur de requête calcul la taille d'échantillon par défaut, et ce , selon un algorithme prédéterminé. En gros :
- Si la taille de la table est inférieure ou égale à 8Mo, il effectue une analyse complète (FULLSCAN)
- Si la taille de table est strictement supérieure à 8Mo, il suit son propre algorithme, en ce sens que plus le nombre de lignes augmente, plus il réduit le taux d'échantillonnage. Le rapport entre le nombre de lignes de la table et le taux d'échantillonnage n'est pas du tout linéaire.

En revanche j'ai constaté un autre petit problème dans le script de olivtone !
Sauf erreur de ma part, il y a une inversion dans une expression

Je pense qu'il faut remplacer

Code SQL :

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

rows / NULLIF(modification_counter, 0) > 0.2

par

Code SQL :

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

modification_counter / NULLIF(rows, 0) > 0.2

Qui signifierait plus de 20% des lignes ont été modifiées.
et la phrase d'olivtone devrait, sauf erreur de ma part, être reformulée comme suit :

"Perso je mets les UPDATE STATS pour les Stats où :
( (datés de plus de trois jours)
OU (le pourcentage des lignes modifiées > 20%)
)
ET (ayant plus de 500 lignes)

A+

[julien94320]

Hello,

comme déjà vu ensemble ça recommence !

As tu essayé de creer ton propre plan de maintenance via des procédures stockées / fonction que tu viens executer dans l'agent SQL ?

As tu suivis le conseil de SQLPRO en regardant si tu n'avais pas de transaction ouverte au moment de l'opération ?

A+

Julien

[Lyche]

Hello,

non je suis sur un autre projet en ce moment (d'où mon temps de réponse un peu long)
Le serveur sur lequel j'ai eu les soucis va être déménagé et tout sera à reconfigurer. Je mettrais en place, dans les bonnes pratiques, ce nouveau serveur.

Pour les transactions, je ferais les tests à ce moment là puisque j'aurais des tests de montées en charge à faire. Je regarderais à ce moment là la liste des transactions.

Cordialement,