Discussion :

[Vyns1520]

Bonjour,

Mon client se plaint de performances de moins en moins bonnes, je souhaiterais donc effectuer des opérations de maintenance à fréquence régulière, comme par exemple une reconstruction des index ou bien une compression/réduction (SHRINK). Pour information, la base de données concernée est composée d'un seul fichier MDF (actuellement 44 Go avec un espace libre disponible de 10%) et d'un fichier LDF (actuellement 10 Mo avec un espace libre disponible de 92%).

Avez-vous des conseils à me donner à ce sujet ?
Par exemple pour l'ordre des traitements, je dirais qu'il faut d'abord faire un SHRINK puis une reconstruction des index. Est-ce correct ?
De plus, j'ai lu beaucoup de posts qui déconseillaient d'utiliser SHRINK. Pour quelle raison ?

Merci d'avance pour vos réponses.
Vyns

[Donpi]

Bonjour, tout d'abord ce qui suit n'engage que moi.

Selon moi les opération de maintenance, même si elles sont indispensables, dans le cas des baisses de performance sont une fausse bonne idée.
Je regarderais d'abord du côté des statistiques d'indexes manquants ainsi que du coté de l'application pour trouver des pistes.

Le Shrink va libérer l'espace réservé par votre base de données. Mais il risque d'être aussi tôt re-réservé.
Globalement le Shrink est inutile sauf lors d'un traitement particulier qui a fait gonflé l'espace réservé inutilement.

Sinon pour répondre à la question, en fonction de la consommation d’espace disque je dimensionnerais la taille de la base une fois par semaine pour réserver l'espace a consommer dans les jours suivants.
En ensuite une réindexation.

A+

[Vyns1520]

Merci de votre réponse.

En fait, la base de données est utilisée depuis plus d'une dizaine d'années et je pense qu'aucun SHRINK n'a jamais été exécuté.
Nous avons récupéré le projet depuis 8-9 mois suite à un changement d'architecture.
Le serveur précédent était SQL2000 et des opérations de maintenance étaient planifiées (reconstruction des index de mémoire) alors que depuis la migration vers SQL2008R2, aucune opération de maintenance n'a été effectuée et je suppose que c'est pourquoi le client constate des pertes de performances...

Sur une base de tests (copie récente de la base de prod), j'ai procédé aujourd'hui à un SHRINK (une heure de traitement et passage de 44Go à 36Go) + reconstruction des index (6 heures de traitement) et j'ai programmé ces mêmes opérations pour cette nuit. Je vous tiens au courant...

[Invité]

Le shrink c'est le mal absolu : Y a pas mieux pour fragmenter les indexes !
Et si tu veux les recréer juste après, ça revient pas mal à faire une chose et son contraire.

Je te renvoie à cette note de SQLPro : http://www.developpez.net/forums/d14...s/#post7797262
et à son article : http://blog.developpez.com/sqlpro/p5..._fichiers_et_t

[Vyns1520]

OK, merci, cela devient plus clair.

Je pense donc que :
- je vais laisser tomber le SHRINK à fréquence régulière
- je vais quand même faire un SHRINK unitaire pour récupérer 8 Go d'espace disque et on verra comment l'espace libre disponible va évoluer ; je pense qu'il va forcément augmenter car l'application associée fait régulièrement des "DELETE before INSERT", ce qui devrait, sauf erreur, avoir tendance à fragmenter les index...
- je vais en revanche planifier des reconstruction d'index à fréquence régulière

Après avoir lu les différents articles rédigés par SQLPro, je me suis empressé de vérifier comment était configurée la croissance automatique et je me suis aperçu que le fichier MDF était paramétré avec une croissance par tranche de 1Mo !!! Je crois avoir compris que ce n'est vraiment pas l'idéal et j'ai donc modifié cette valeur pour la passer à 100 Mo...
En plus de cela, la réduction automatique "AUTO_SHRINK" était activée !!!! ET apparemment c'est très déconseillé !!!! Je viens donc de la désactiver mais j'ai peur que cela fasse grossir considérablement le fichier MDF ????

Pour résumer si je comprends bien, un bon paramétrage de la croissance automatique optimise les temps de réponse des écritures ; alors que la faible fragmentation des indexes améliore les temps de réponse des lectures. C'est bien cela ?

[Donpi]

Pour résumer si je comprends bien, un bon paramétrage de la croissance automatique optimise les temps de réponse des écritures ; alors que la faible fragmentation des indexes améliore les temps de réponse des lectures. C'est bien cela ?

Dans les grandes des lignes oui, mais ça ne suffit généralement pas pour réglé les problèmes de performances.
A+

[mikedavem]

Hello,

Je pense donc que :
- je vais laisser tomber le SHRINK à fréquence régulière

Je pense que c'est une sage décision. Le shrink doit rester une opération exceptionnelle.

- je vais quand même faire un SHRINK unitaire pour récupérer 8 Go d'espace disque et on verra comment l'espace libre disponible va évoluer ; je pense qu'il va forcément augmenter car l'application associée fait régulièrement des "DELETE before INSERT", ce qui devrait, sauf erreur, avoir tendance à fragmenter les index...
je vais en revanche planifier des reconstruction d'index à fréquence régulière

Très probablement. Tu peux par exemple surveiller l'évolution de fragmentation de tes indexes et déduire à quelle fréquence tu dois opérer une maintenance sur ceux-ci.

Après avoir lu les différents articles rédigés par SQLPro, je me suis empressé de vérifier comment était configurée la croissance automatique et je me suis aperçu que le fichier MDF était paramétré avec une croissance par tranche de 1Mo !!! Je crois avoir compris que ce n'est vraiment pas l'idéal et j'ai donc modifié cette valeur pour la passer à 100 Mo...
En plus de cela, la réduction automatique "AUTO_SHRINK" était activée !!!! ET apparemment c'est très déconseillé !!!! Je viens donc de la désactiver mais j'ai peur que cela fasse grossir considérablement le fichier MDF ????

De manière globale il faut configurer une taille d'accroissement de fichier suffisamment grande pour tes besoins mais il est vrai que laisser 1MB reste un non sens. Pour ma part, je fixe une taille d'accroissement en fonction de la taille de la base de données (<512 Mo = 100Mo, < 1G0 = 512 Mo, > 1Go et < 10 Go = 1G0, > 10Go ... ). Dans ce cas il faut également tenir compte du temps de cette opération. Une bonne pratique est d'utiliser Instant File Initialization avec SQL Server pour permettre une allocation d'espace disque très rapide quand cela est possible bien entendu. Si ce n'est pas le cas il faut tenir compte de ce paramètre pour fixer une taille. Certains utilisent la croissance automatique comme soupape de sécurité et étendent manuellement les fichiers de bases de données dans une stratégie de "capacity planning". D'autres, en revanche, l'utilisent de manière automatique et laisse SQL Server gérer la croissance des fichiers. Par expérience, j'ai déjà vu les 2 mais généralement la 2ème méthode est la plus utilisée bien que moins recommandée car elle relève en général une absence totale de surveillance et d'analyse de l'accroissement des fichiers de bases de données.

Un autre aspect important est également de contrôler l'accroissement de la taille du journal. Un accroissement trop faible peut engendrer une fragmentation excessive des VLF dans le journal des transactions ce qui peut avoir un impact sur les performances ... INSERT, UPDATE, DELETE plus longs ... phase de récupération d'une base qui peut prendre du temps lorsque celle-ci redémarre ... Même chose pour le journal des transactions, la croissance automatique doit rester une soupape de sécurité ... et une extension manuelle est à préférer pour maitriser le nombre de VLF que comportera le journal des transactions. En général lorsqu'un fichier journal est trop fragmenté, il est conseillé de le réduire au maximum et de procéder ensuite manuellement à son extension pour maitriser le nombre de VLF qui sera créé :

<64 MB autogrowth = 4 VLF
64MB to 1GB =8 VLF
1GB = 16 VLF

Attention cependant car le fichier journal ne peut bénéficier de la fonctionnalité Instant File Initialization .. Une extension de fichier en fonction de sa taille peut plus ou moins prendre du temps en fonction de l'activité IO existante sur les disques et les performances du sous-système disque lui-même . Bref ... la gestion de croissance des fichiers de bases de données est une part importante d'administration pour les DBA

++

[Vyns1520]

Alors le résultat du traitement de cette nuit est le suivant :
- le SHRINK a duré 2 minutes 30 secondes alors que le traitement précédent avait duré une heure ; c'est normal, il n'y avait quasiment plus rien à réduire...
- la reconstruction des index a duré 6 heures (idem traitement précédent)
J'ai vérifié et les index sont désormais très peu fragmentés ; en revanche, la taille de mon fichier MDF est passé à 54 Go !!!!
Je suppose que c'est à cause de la désactivation de la fonctionnalité AUTO_SHRINK ?

J'ai fait un nouveau SHRINK manuel pour que mon fichier MDF repasse à 36 Go mais les indexes sont à nouveau fragmentés !!!
C'est ce que disait le commentaire de "7gyY9w1ZY6ySRgPeaefZ"...
Bref, c'est le serpent qui se mord la queue !!!

[mikedavem]

J'ai vérifié et les index sont désormais très peu fragmentés ; en revanche, la taille de mon fichier MDF est passé à 54 Go !!!!

C'est possible en effet. Lorsque tu reconstruits un index celui-ci l'ancienne version de l'index n'est pas supprimé tant que la nouvelle version n'est pas construite. Il se peut donc qu'il te faille un peu d'espace supplémentaire pendant la reconstruction. Par curiosité quel est le résultat de cette requête :

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
select top 10
	o.name as table_name,
	i.name as index_name,
	sum(p.rows) as total_rows,
	sum(a.total_pages) as total_pages,
	sum(a.used_pages) as used_pages,
	sum(a.total_pages) / 128 as total_size_mb,
	sum(a.used_pages) / 128 as used_size_mb
from sys.partitions as p
	join sys.allocation_units as a
		on p.hobt_id = a.container_id
	join sys.objects as o
		on o.object_id = p.object_id
	join sys.indexes as i	
		on i.object_id = p.object_id
			and i.index_id = p.index_id
group by o.name, i.name
order by total_pages desc

++

[SQLpro]

Pas beaucoup de temps à vous répondre, mais :
1) dimensionnez correctement vos fichiers (data et JT) avec taille actuelle x 1,5 (au minimum). Donc si taille actuelle données = 36 Go, passez à un fichier de 54 Go idem pour JT !
2) défragmentez les index fragments à plus de 10 par REORGANIZE et à plus de 30% par REBUILD chaque nuit
3) pour tous les index non cluster et même pour ceux cluster qui ne sont pas créé sur une unique colonne autoincrémentée, mettez un FILLFACTOR de 85 % lors du REBUILD
4) recalculez toutes les statistiques vieilles de plus de 48 heures chaque nuit
5) placez votre base en AUTO_SCHRINK = FALSE et AUTO_CREATE_STATISTICS = ON et AUTO_UPDATE_STATISTICS = ON
6) dimensionnez votre base tempdb avec des fichiers de données x 1,5 de la même manière que votre base de prod
7) rajoutez à votre base tempdb un second fichier de données de même taille que le premier (après avoir exécuté 6)
8) augmentez le seuil de parallélisme entre 12 ou 25 (sp_configure 'coast threshold for parallelism')
9) limitez le parallélisme à 2 threads (sp_configure 'max degree for parallelism')
10) limitez la RAM utilisée par SQL Server à RAM serveur - 4 Go (sp_configure 'max server memory')

Avec tout cela vous devriez faire un minimum de x 4 sur le global (voir nettement plus) !

Pour aller plus loin, il faudrit investiguer......

A +



Après

[Vyns1520]

Bonjour et merci pour vos réponses,

Depuis le temps, le plan de maintenance a été créé.
La base de données a désormais bien les caractéristiques suivantes : AUTO_SCHRINK = FALSE et AUTO_CREATE_STATISTICS = ON et AUTO_UPDATE_STATISTICS = ON.

J'ai utilisé la tâche "Reconstruire l'index" pour toutes les tables et cela prend environ 30 minutes --> Le temps de traitement est correct mais pouvez-vous svp expliquer quel est exactement le but de faire une réorganisation sur les index feu fragmentés et pas une reconstruction systématique de tous les index ?
En revanche, la tâche "Mettre à jour les statistiques" dure plus de 6 heures. --> Cette étape est beaucoup trop longue ; est-ce vraiment nécessaire sachant que la base est en AUTO_UPDATE_STATISTICS = ON ?

Enfin, par rapport à vos conseils :

défragmentez les index fragments à plus de 10 par REORGANIZE et à plus de 30% par REBUILD

recalculez toutes les statistiques vieilles de plus de 48 heures

Auriez-vous svp déjà réalisé un script SQL que vous pouvez partager ?

Merci.

[mikedavem]

J'ai utilisé la tâche "Reconstruire l'index" pour toutes les tables et cela prend environ 30 minutes --> Le temps de traitement est correct mais pouvez-vous svp expliquer quel est exactement le but de faire une réorganisation sur les index feu fragmentés et pas une reconstruction systématique de tous les index ?

Le but est de minimiser les opérations de maintenance à effectuer sur les indexes car celles-ci peuvent être bloquantes et consommatrices en ressources.
Pour les petites bases ou à taille moyenne reconstruire systématiquement tous les indexes n'a pas forcément un gros impact (il faut modérer ici avec le type d'activité sur la base bien entendu).

Il existe de nombreuses raisons pour commencer à optimiser une maintenance des indexes. Parmi celles que je rencontre le plus : dépassement de la fenêtre de maintenance permise, verrouillage trop agressif de la base de données et qui pénalise l'activité business, impact important sur le journal des transactions en mode full ... Dans ce cas l'idée est de limiter l'étendue d'action de la maintenance en ne s'occupant que des indexes par rapport à leur taux de fragmentation notamment et leur taille. Il faut savoir que réorganiser un index est beaucoup moins verrouillant qu'une reconstruction.

En revanche, la tâche "Mettre à jour les statistiques" dure plus de 6 heures. --> Cette étape est beaucoup trop longue ; est-ce vraiment nécessaire sachant que la base est en AUTO_UPDATE_STATISTICS = ON ?

La mise à jour automatique est un bon début mais ce processus n'est malheureusement pas parfait. En effet le seuil de déclenchement de mise à jour s'effectue à 20% lignes total + 500, ce qui signifie que plus tes tables deviennent importantes moins la mise à jour des statistiques se fera rapidement. Tu peux donc te retrouver avec des statistiques obsolètes et des "skewed data distributions" au bout d'un certain temps ce qui peut conduire à des plans d'exécutions peu performants dans certains cas. De plus la reconstruction des indexes ne permet la mise à jour des statistiques que pour les colonnes d'index concernés. Les statistiques de colonnes non concernés par un index ne sont pas concernés ici. Dans ce cas il faut mettre en place une routine manuelle de mise à jour des statistiques avec une stratégie adéquate à ton environnement. SQLPro te propose une stratégie basée sur l'âge des statistiques. D'autres stratégies plus fines existent également et qui consistent à identifier les indexes posant problème lorsque les statistiques ne sont pas mises à jour régulièrement. Si ces indexes sont reconstruits on ne fait rien. En revanche si ces derniers sont simplement réorganisés alors on met à jour les statistiques. Tu peux toujours utiliser la procédure stockée sp_updatstats pour mettre à jour tes statistiques mais celle-ci est plutôt agressive dans sa manière de procéder car elle se base sur une modification du ind_rowmodctr <> 0. En d'autres termes, il suffit d'un seul changement pour l'index concerné soit mise à jour.

Tu peux procéder de manière empirique ici et procéder à une solution de mise à jour globale et ensuite paufiner ta routine si cela s'avère nécessaire.

++