Discussion :

[Invité]

Bonjour,

j'ai actuellement des problèmes de blocage sur une base SQL.
J'ai des erreurs de type : sql server has encountered x occurrence(s) of i/o requests taking longer than 15 seconds to complete...
Ces problèmes se produisent lors du plan de maintenance de la base, je pense lors de la phase de reconstruction d'index.

La base pèse près de 100GB (sans compter les log). Les fichiers sont situés sur une baie de 2*10 disques en RAID6.
Les datas et les logs sont divisés en 7 fichiers comme suis :

data 1 - groupe PRIMARY - Initial Size 98141 MB - Autogrowth BY 1000
data 2 - groupe PRIMARY - Initial Size 4297 MB - Autogrowth BY 100
data 3 - groupe PRIMARY - Initial Size 4395 MB - Autogrowth BY 100
data 4 - groupe PRIMARY - Initial Size 4798 MB - Autogrowth BY 100
data 5 - groupe PRIMARY - Initial Size 4299 MB - Autogrowth BY 100
data 6 - groupe PRIMARY - Initial Size 4491 MB - Autogrowth BY 100
data 7 - groupe PRIMARY - Initial Size 4591 MB - Autogrowth BY 100

Log1 - Initial Size 1 MB - Autogrowth BY 10 percent
Log2 - Initial Size 1000 MB - Autogrowth BY 10 percent
Log3 - Initial Size 1000 MB - Autogrowth BY 10 percent
Log4 - Initial Size 1000 MB - Autogrowth BY 10 percent
Log5 - Initial Size 1000 MB - Autogrowth BY 10 percent
Log6 - Initial Size 31890 MB - Autogrowth BY 10 percent
Log7 - Initial Size 1000 MB - Autogrowth BY 10 percent

Pour moi, il y a clairement un problème dans les tailles des fichiers et j'aimerai uniformiser ça en passant les Initial Size des datas à 15000 MB avec des autogrow à 1000 MB.
D’où mes questions :
- Est-ce que cette action pourrait régler mes problèmes de blocage de la base ?
- A quel moment le redimensionnement va être fait (lors d'un SHRINK DATABASE ??)
- Combien de temps ça va prendre à peu près ?
- La base va t-elle être inaccessible durant ce temps ?

Merci d'avance.

[SQLpro]

D’où mes questions :
- Est-ce que cette action pourrait régler mes problèmes de blocage de la base ?
- A quel moment le redimensionnement va être fait (lors d'un SHRINK DATABASE ??)
- Combien de temps ça va prendre à peu près ?
- La base va t-elle être inaccessible durant ce temps ?

Merci d'avance.

1) En principe oui... mais, voir conseils après !
2) non, lors d'un ALTER DATABASE
3) dépend de la grandeur nouvelle et si vous êtes en "instant file initialisation"
4) non...

Quelques remarques/conseils :
1) SQL Server nécessite un stockage dédié (disque interne au serveur ou SAN dédié). Sans cela ces problèmes peuvent se reproduire, car il devra attendre que d'autres applications finissent leurs manipulations.
2) tous les fichiers d'un même groupe de fichier (ici PRIMARY) doivent impérativement être équilibrés, c'est à dire avoir la même taille. Sinon des problèmes de contention vont se produire
3) pour être réellement efficace, chaque fichier devrait être placé sur un disque mécaniquement indépendant des autres placements de fichiers. De même pour le JT
4) vous devez faire cela aussi pour le journal des transactions
5) il est impératif que JAMAIS vous n'ayez d'opération de croissance des fichiers. En effet ces opérations sont longues et couteuses et peuvent parfois conduire à un time out et par conséquence une annulation de la transaction. Dans le même esprit, le pas d'incrément doit lui aussi être limité. Autrement dit provisionnez le stockage pour 3 à 5 années de production
6) le nombre de fichiers doit correspondre à la limitation du parallélisme effectué au niveau du serveur
7) faites de même pour la base tempdb.

Sinon, voyez mon livre, il consacre un chapitre de 70 pages au stockage...
http://www.amazon.fr/SQL-Server-2014...Server+brouard

A +

[Invité]

Merci pour vos réponses et conseils.
Je pense que la base respecte assez bien vos recommandations à quelques détails prés.

Par exemple la tempdb a des gros problèmes également.
Elle est bien divisé en 7 fichiers également mais ils ont des initial size à 100MB avec un autogrowth à 10MB alors que la taille de la base est de 2,8GB.

Encore une petite question pour le lancement de mes actions.
Je pense augmenter les tailles en mode graphique (dans Database Properties / Files dans SQL server Management), du coup est-ce qu'il faut faire des actions après ?

[hmira]

...
Sinon, voyez mon livre, il consacre un chapitre de 70 pages au stockage...
http://www.amazon.fr/SQL-Server-2014...Server+brouard

A +

Bonjour SQLpro,

Je viens de commander votre livre "SQL Server 2014" chez amazon.fr. Le livre est mentionné disponible à partir du 21 novembre 2014. C'est donc tout frais, tout chaud ! J'ai vraiment hâte de vous lire ...

Parmi les auteurs du livre, qui figurent sur la couverture, j'ai lu le nom d'un certain Christian Soutou que je connais bien ! En effet, Christian Soutou et moi-même avons eu, à des époques différentes, le même Professeur Pierre BAZEX à l'Université Paul Sabatier Toulouse III.

En ce qui me concerne, mon sujet de doctorat en informatique en Base de données au CNRS à Toulouse, traitait des Contraintes d'intégrité exprimées sous forme de formule en forme normale prénexe conjonctive, Calcul des prédicats, etc. ...

Je tiens sincèrement à vous remercier vous tous, auteurs de ce livre, d'avoir pris l'initiative de rédiger et de publier un livre en français (c'est rare !) sur SQL Server, qui j'en suis persuadé, connaissant les auteurs, sera de bonne facture.

A+

[SQLpro]

Je suis en retard de correction des épreuves, donc il sortira avec 1 mois de retard !

A +

[bvesan]

Bonjour,

La seule conclusion que vous pouvez tirer de l'erreur remontée est que, à un instant particulier, les disques ne sont plus en capacité de répondre correctement à la demande d'entrées/sorties formulée par l'instance SQL Server.
Les opérations de reconstruction peuvent nécessiter de l'espace supplémentaire dans vos fichiers de données, dans vos fichiers de journaux transactionnels, et peuvent très fortement solliciter vos disques:

• Lecture massive de pages de données (balayage des indexes existants)
• Ecriture massive de nouvelles pages de données (création des "nouveaux" indexes)
• Ecriture massive dans les journaux de transactions (si la base est en mode de recouvrement COMPLET)
• Ecriture massive dans la base TempDB (si les reconstructions d'indexes sont effectuées avec l'option ONLINE).

Effectivement, si vos fichiers de données doivent être agrandis pendant cette opération, ils vont ajouter une sollicitation des disques pendant la phase de "zéro initialisation" des pages de données. Comme l'indique SQL Pro, cette phase peut être considérablement allégée en profitant du mécanisme "Instant File Initialization". Cet article vous explique comment en profiter.

Pour alléger la charge sur disque, il serait intéressant de diminuer l'activité sur les journaux de transaction. Lorsqu'une base est en mode de recouvrement SIMPLE ou BULK-LOGGED, la reconstruction d'indexe est une opération dite "minimalement loggée" et sollicite moins les journaux de transactions. Si votre base est en mode de recouvrement COMPLET, il peut être intéressant de la passer en mode BULK-LOGGED juste avant la reconstruction, et la repasser en mode complet juste après (avec bien entendu une sauvegarde des journaux après chaque changement de mode de recouvrement).


Pourquoi votre base est-elle composée d'autant de fichiers, sur le même disque (vu côté Windows) ? Le seul intérêt que je vois pour les fichiers de données est un temps de restauration réduit lors de la perte d'un unique fichier (inutile de restaurer l'intégralité de la base) mais ce type d'incident reste très rare (bien plus rare que la perte totale du disque entier ou que la corruption de quelques blocs). Et j'avoue ne pas en voir pour la multiplication des fichiers du journal transactionnel. Je pense que vous gagneriez en exploitation et potentiellement en performance en réduisant le nombre de fichiers qui composent votre base.

Pour répondre à votre question, c'est DBCC SHRINKFILE qui permettra de réduire la taille du premier fichier de données et d'équilibrer vos données sur les autres fichiers. Cette commande consiste en deux opérations:

• Déplacement des pages de données allouées vers le début du fichier et vers les autres fichiers. Cette opération est très intrusive puisqu'elle bloque l'accès aux pages à déplacer. A n'effectuer que lorsque l'activité en base est très faible.
• Suppression des pages non allouées à la fin du fichier.

Le temps de cette opération est extrêmement difficile à estimer, puisqu'il dépend du temps de réponse de vos disques, du nombre de pages à déplacer, et de l'activité en base. Mais il n'est pas inconcevable qu'elle prenne plusieurs heures. Et elle devra être suivie d'une nouvelle reconstruction des indexes puisque le déplacement des pages peut fortement fragmenter vos objets. Donc à lancer idéalement un weekend (si cela correspond à une plage d'inactivité sur la base), et prévoir jusqu'à une journée pour la durée du traitement (et éviter à tout prix que l'opération s'exécute en même temps que la réorganisation des indexes).


Cordialement.

Benjamin

[dbaffaleuf]

En plus par rapport à ce que dit Benjamin,

Il y a possibilité dans le rapport standard 'Disk Usage' (Clic-droit sur la base -> Reports -> Standard Reports -> Disk Usage) de voir les occurrences d'augmentation des fichiers (Data/log Files Autogrow/Autoshrink Events). Regarder la durée associée à chaque fois.

Regarder si la base n'est pas en autoshrink aussi

Code :

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

1
2
select name, is_auto_shrink_on  from sys.databases ;
GO

[Invité]

Il y a possibilité dans le rapport standard 'Disk Usage' (Clic-droit sur la base -> Reports -> Standard Reports -> Disk Usage) de voir les occurrences d'augmentation des fichiers (Data/log Files Autogrow/Autoshrink Events). Regarder la durée associée à chaque fois.

je ne connaissais pas ces rapports. Effectivement de nombreux auto grow se produisent très régulièrement.

Regarder si la base n'est pas en autoshrink aussi

j'ai vérifié et non elle n'est pas en autoshrink

merci

[Invité]

Donc si je résume un peu mes solutions, je dois :
- uniformiser la taille de mes fichiers de données et de logs pour ma base et la tempdb
- bien réfléchir à la taille de base des fichiers pour ne pas subir d'autogrow
- lancer l'opération avec un SHRINKFILE sur le fichier le plus gros, lorsque la base n'est pas beaucoup utilisée et pas en même temps qu'une réorganisation d'index

Juste quelques dernières questions concernant le plan de maintenance.
Voici les étapes :
- BACKUP DES BASES ET LOGS
- CHECK INTEGRITY
- SHRINK DATABASE
- REORGANIZE INDEX
- REBUILD INDEX
- UPDATE STATISTICS
- CLEAN UP HISTORY

D'après ce que j'ai pu lire, y'a pas mal de chose à ne pas faire la dedans.
Est-ce que ceci suffirai ?
- BACKUP DES BASES ET LOGS
- CHECK INTEGRITY
- REBUILD INDEX
- CLEAN UP HISTORY

Est-ce que faire les BACKUP après le rebuild serai plus judicieux ?

merci

[SQLpro]

La bonne façon est plutôt :

- BACKUP FULL DES BASES
- CHECK INTEGRITY avec profondeur variable
- REORGANIZE ou REBUILD d'INDEX après analyse du taux de fragmentation
- UPDATE STATISTICS pour les statistiques anciennes ou avec nombreuses modifs
- BACKUP LOG DES BASES
- CLEAN UP HISTORY

Or les plans de requête graphique ne permettent pas de mettre en œuvre des analyses fines pour décider quoi faire. C'est du grossier !
C'est à vous de prévoir des travaux plus fin... Bref un vrai travail de DBA !

En sus on pourrait y ajouter :
- vérification du dimensionnement du stockage (espace résiduel sur les fichiers et sur les disques)
- augmentation du dimensionnement du stockage en fonction des réponses à la tâche précédente

A +

[bvesan]

Effectivement, il ne faut jamais systématiser un SHRINK. C'est d'ailleurs peut-être l'une des causes (voire LA cause) de votre problème de performance initial.
Quant aux choix des sauvegardes avant ou après la réindexation, à vous de voir !

• Faire de la sauvegarde une première étape, c'est s'assurer qu'elle sera lancée à un horaire connu et qu'elle sera terminée rapidement, mais qu'elle sauvegardera plus de pages que si elle était lancée après les réorganisations.
• Faire de la sauvegarde la dernière étape, c'est s'assurer qu'elle ne sauvegardera que des objets réorganisés, mais l'horaire de sauvegarde est inconnu puisqu'il dépend du temps de traitement de chacune des étapes précédentes.
  

Si vous laissez votre base en mode de recouvrement complet, il peut être intéressant d'effectuer une seconde sauvegarde des journaux de transactions après les opérations de réorganisation (et n'oubliez pas de choisir des sauvegardes compressées, plus petites et surtout plus rapides).

Pour défragmenter un indexe, il est possible de le reconstruire ou de le réorganiser. Systématiser la reconstruction n'est pas forcément judicieux sur une base de 100Go (opération trop coûteuse en temps et en ressources, pour un gain non significatif lorsque la fragmentation n'est pas élevée). Idéalement, il faut mettre en place un mécanisme de défragmentation qui tient compte du niveau de fragmentation de chaque indexe (je ne suis pas un grand fan de la publicité dans les forums, mais vous pourrez trouver des informations sur les enjeux de la réorganisation ainsi q'une procédure de réorganisation sélective dans cet article). Ce traitement pourrait remplacer la tâche REBUILD INDEXES du plan de maintenance, vous gagneriez ainsi (beaucoup) de temps sur la durée de vos traitements de maintenance.

Ce que j'ai compris de la part des personnes qui ont conçu cette base (et qui ne sont plus là maintenant). Les bases de données sont stockées sur un réseau SAN en RAID6.
Du coup sur les 10 disques de la baie, 7 sont utilisées réellement. En théorie il est donc possible d'écrire et de lire dans les 7 fichiers en même temps.

Oubliez cette affirmation ! Chacun de vos fichier est étalé sur l'ensemble des disques de vos disques, et une lecture ou écriture fait travailler les disques en parallèles. Pour SQL Server, ces fichiers sont positionnés sur le même disque physique (la même LUN), il ne va donc pas (dans la très grande majorité des cas) solliciter les différents fichiers en parallèle. Si les fichiers étaient répartis sur des disques différents (comprendre "vus comme des disques différents par Windows", c-à-d sur des LUNs différentes), la parallélisation pourrait se faire. Donc j'insiste sur le fait que réduire le nombre de fichiers de données de votre base peut simplifier l'exploitation de la base, sans conséquence néfaste sur les performances.

[Invité]

Effectivement, si vos fichiers de données doivent être agrandis pendant cette opération, ils vont ajouter une sollicitation des disques pendant la phase de "zéro initialisation" des pages de données. Comme l'indique SQL Pro, cette phase peut être considérablement allégée en profitant du mécanisme "Instant File Initialization". Cet article vous explique comment en profiter.

Je vais regarder ça.

Pour alléger la charge sur disque, il serait intéressant de diminuer l'activité sur les journaux de transaction. Lorsqu'une base est en mode de recouvrement SIMPLE ou BULK-LOGGED, la reconstruction d'indexe est une opération dite "minimalement loggée" et sollicite moins les journaux de transactions. Si votre base est en mode de recouvrement COMPLET, il peut être intéressant de la passer en mode BULK-LOGGED juste avant la reconstruction, et la repasser en mode complet juste après (avec bien entendu une sauvegarde des journaux après chaque changement de mode de recouvrement).

Effectivement, elle est bien en mode complet.

Pourquoi votre base est-elle composée d'autant de fichiers, sur le même disque (vu côté Windows) ? Le seul intérêt que je vois pour les fichiers de données est un temps de restauration réduit lors de la perte d'un unique fichier (inutile de restaurer l'intégralité de la base) mais ce type d'incident reste très rare (bien plus rare que la perte totale du disque entier ou que la corruption de quelques blocs). Et j'avoue ne pas en voir pour la multiplication des fichiers du journal transactionnel. Je pense que vous gagneriez en exploitation et potentiellement en performance en réduisant le nombre de fichiers qui composent votre base.

Ce que j'ai compris de la part des personnes qui ont conçu cette base (et qui ne sont plus là maintenant). Les bases de données sont stockées sur un réseau SAN en RAID6.
Du coup sur les 10 disques de la baie, 7 sont utilisées réellement. En théorie il est donc possible d'écrire et de lire dans les 7 fichiers en même temps.

Pour répondre à votre question, c'est DBCC SHRINKFILE qui permettra de réduire la taille du premier fichier de données et d'équilibrer vos données sur les autres fichiers. Cette commande consiste en deux opérations:

• Déplacement des pages de données allouées vers le début du fichier et vers les autres fichiers. Cette opération est très intrusive puisqu'elle bloque l'accès aux pages à déplacer. A n'effectuer que lorsque l'activité en base est très faible.
• Suppression des pages non allouées à la fin du fichier.

Le temps de cette opération est extrêmement difficile à estimer, puisqu'il dépend du temps de réponse de vos disques, du nombre de pages à déplacer, et de l'activité en base. Mais il n'est pas inconcevable qu'elle prenne plusieurs heures. Et elle devra être suivie d'une nouvelle reconstruction des indexes puisque le déplacement des pages peut fortement fragmenter vos objets. Donc à lancer idéalement un weekend (si cela correspond à une plage d'inactivité sur la base), et prévoir jusqu'à une journée pour la durée du traitement (et éviter à tout prix que l'opération s'exécute en même temps que la réorganisation des indexes).

La seules période d'inactivité(ou presque) de la base est la nuit entre 2h et 6h-7h du matin. C'est d’ailleurs à ce moment la que le plan de maintenance est lancée.
Il ne faut idéalement pas que le site web qui utilise ces bases soient dégradés hors de cette périodes.

Merci pour votre réponse.