Discussion :

[Mothership]

Bonjour,

Cela n'est pas mon premier post sur le sujet, mais je rencontre de nouveaux des problèmes lors de l'exécution des plans de maintenance lourd (rebuild indexes) du weekend.
En effet malgré le fait que ma base est en mode "Bulk logged" lors de ces plans, le remplissage de mes journaux entraine leur saturation. Ces travaux de maintenance durent environ 10H et au bout de 7H00 les TRAN dépassent le seuil des 98% ce qui m'oblige pour éviter à passer la base en mode simple via un seuil défini par une alerte.
Pensez vous que faire une sauvegarde lors de l'exécution du plan de maintenance suffirait ? J'ai cru lire qu'il fallait éviter d'exécuter une sauvegarde pendant ce type d'opérations, mais je me trompe surement.
Quelles solutions voyez vous ?

[dbaffaleuf]

Hello,

Peux-tu nous donner le détail du plan de maintenance ? Est-ce juste une reconstruction des indexes ou il y a autre chose en plus (un shrink au hasard). Merci de donner l'ordre des tâches s'il y en a plusieurs.

Quelle volumétrie est concernée (DATA / LOG) ? Ca paraît énorme 10 heures de rebuild, mais si ça fait suite à un shrink ça peut s'expliquer. Il faut prendre des sauvegardes du journal pendant l'opération de toutes façons. Ce n'est pas déconseillé, c'est juste que la taille des backup log pendant le rebuild sera beaucoup plus importante car elle va embarquer toutes les nouvelles allocations directement dans le fichier de backup.

Si tu es en v2005+, tu peux regarder la colonne log_reuse_wait_desc dans sys.databases pour la base dont le log grossit pour voir ce qui le remplit.

David B.

[SQLpro]

Si vous utilisez les plans de maintenance tout fait de SQL Server, c'est logique. Ils ne sont pas du tout optimisé. En gros ils recalculent tous les index, même si pour 75% d'entre eux c'est parfaitement inutile.

Je vous invite à écrire une procédure optimisée pour ce faire en considérant qu'il faut :
1) reconstruire les index fragmentés à plus de 30%
2) défragmenter les index fragmentés à plus de 10% (et moins de 30%)
3) recalculer les statistiques des autres index

Pour ce faire, lisez les différentes procédures que j'ai écrite :
1) pour les VLDB : http://sqlpro.developpez.com/optimis...ntenanceIndex/
2) pour les autres : http://blog.developpez.com/sqlpro/p8...es-index-et-s/

A +

[Mothership]

Merci pour vos réponses
Concernant tes questions : dbaffaleuf
Peux-tu nous donner le détail du plan de maintenance ?
J'effectue une collecte des statistiques sur les indexes fragmentés sur l'ensemble de ma DB
Pour tout indexes dont la fragmentation est < 10% RAS
Pour tout index ton la fragmentation est 10% < x < 30% alter index reorganize
Pour les indexes dont la fragmentation est > 30% rebuild online
Ces plans ont été inspirés de pas mal de blog visités

Quelle volumétrie est concernée (DATA / LOG) ?
Data : environ 250/300 Go dans la BD
Log : 2 fichiers de 25 Go

Aucun shrink n'est effectué, mais le produit utilisant la base favorise énormément la fragmentation je pense, de plus une épuration est effectuée sur bon nombre de tables les deux jours précédent la reconstruction.
Pour les sauvegardes cela me parait inévitable, ne serait ce que pour ne pas perdre le LSN dans mes sauvegardes des TRAN.

SQLpro : Je vais regarder avec attention ces jobs de reconstruction et les tester sur mes environnements de qualité.

Merci pour vos réponses

[SQLpro]

si vous avez beaucoup de fragmentation, en sus du nombre de mises à jours, cela peut être causé par une mauvaise conception de la base :

• tables trop longues (plus de 20 colonnes)
• types de données inadéquats (par exemple que du VARCHAR)
• clefs primaires mal conçues (par exemple du GUID)
• index cluster mal positionné (par exemple sur du VARCHAR)

Si tel est le cas il faut envisager un refactoring de la base....

Cela dit un JT peut faire jusqu'à 30% de la taille des données, sans que cela paraisse anormal. Le votre est petit en comparaison de ces standards. En sus 2 fichiers pour le JT ne sert à rien. SQL Server n'en utilise jamais qu'un à la fois.

A +

[mikedavem]

Data : environ 250/300 Go dans la BD
Log : 2 fichiers de 25 Go

Je confirme les propos de SQLPro.
25Go ne me paraît pas énorme pour votre journal des transactions.

Pour comparaison ici j'ai une base qui fait environ 150Go avec 50Go de JT et qui arrive à se remplir à plus de 80% pendant les plans de maintenance ...

++

[Mothership]

Donc 2 fichiers de 50 Go ou 1 fichier de 100Go puisqu'il n'y a pas d'intérêt à les doubler ?

Je test votre plan de maintenance dans la journée, quelques questions me viennent à l'esprit :
Vous ne prévoyez pas le passage en mode BULK LOGGED, est ce un choix ou tout simplement ce paramètre doit être passé s'il y a un besoin ?

Pour ma base, malheureusement elle n'est pas du tout mais pas du tout "carré"
* tables trop longues (plus de 20 colonnes) : check
* types de données inadéquats (par exemple que du VARCHAR) : check
* clefs primaires mal conçues (par exemple du GUID) : ??
* index cluster mal positionné (par exemple sur du VARCHAR) : check

Je n'ai que mes plans de maintenance pour pleurer alors
Je le prends avec le sourire car nous n'avons pas "la main" sur le schéma de nos tables liées à notre application notamment les colonnes et le type de données contenues.

[mikedavem]

Je n'ai que mes plans de maintenance pour pleurer alors

Je suis pratiquement dans la même situation que vous . J'arrive à faire modifier certaines choses mais il faut pousser, pousser, argumenter .... etc ...

1 fichier de 100Go puisqu'il n'y a pas d'intérêt à les doubler ?

Oui car l'écriture dans le journal se fait de manière séquentielle. Il n'y a donc rien à gagner à les doubler. Si vous avez la place pour avoir un seul fichier de cette taille allez-y.

Vous ne prévoyez pas le passage en mode BULK LOGGED, est ce un choix ou tout simplement ce paramètre doit être passé s'il y a un besoin ?

Nous ne passons en mode BULK-LOGGED qu'en cas d'extrême nécessité.
(Pendant une release d'application par exemple). C'est un choix qui a été fait pour permettre une restauration dans le temps à tout moment.

++

[elsuket]

Bonjour,

Pour ma base, malheureusement elle n'est pas du tout mais pas du tout "carré"
* tables trop longues (plus de 20 colonnes) : check
* types de données inadéquats (par exemple que du VARCHAR) : check
* clefs primaires mal conçues (par exemple du GUID) : ??
* index cluster mal positionné (par exemple sur du VARCHAR) : check

Chez moi c'est un peu pareil, et ooch ...
Et comme pour Mothership et Mikedavem, faut pousser, argumenter, ... pareil dans la boîte où j'étais avant ... 'vais finir par mettre à mon compte moi

Par clefs primaires mal conçues sur du type GUID, SQLPro entendait le type uniqueidentifier.
Comme ce type nécessite 16 octets (c'est à dire 2 fois plus qu'un BIGINT, 4 fois plus qu'un INT) et que de surcroît les valeurs de clé de cet index sont répliquées dans tous les index non-cluster de la table, ben ... c'est de la m...

Je ne vous parle pas non plus des jointures où cela double le calcul.
Ajoutons la fragmentation que cela cause, surtout quand la colonne n'a pas le défaut NEWSEQUENTIALID() ...

Mais l'autre DBA avec qui je travaille me soutient mordicus que cela ne cause pas de problèmes de fragmentation.
Bon il a 10 ans d'expérience, donc je suis une bille
ça fait quand même plaisir de voir que je ne suis pas le seul dans cette situation

@++

[zinzineti]

SQLpro : Je vais regarder avec attention ces jobs de reconstruction et les tester sur mes environnements de qualité.

Pour éviter de réinventer la roue (une roue bien faite ), je confirme que
que la procédure de maintenance des index de SQLPro marche très bien.http://blog.developpez.com/sqlpro/p8...es-index-et-s/

A+

[Mothership]

Tout fonctionne correctement effectivement, l'exécution du plan de maintenance effectuant les opérations dans la table d'historique est extrêmement long, en même temps il y a plus de 4000 lignes, environ 3600 ont été traitées.
Cette nuit devrait tout devrait être terminé j'espère pour un passage en production ce weekend par exemple.

Merci pour ces informations !

Bonne journée