Maintenance Plan - Lock - SQL Server 2008R2

[gouffe]

Bonjour,

Je précise tout d'abord que je ne suis pas DBA, mais par manque de moyen, je m'occupe en grande partie de l'administration de la base de données de notre projet.

J'ai voulu mettre en place un plan de maintenance quotidien sur la base de données, le voici dans l'ordre :
- Check Database Integrity
- Reorganize Index
- Update Statistics
- Back Up Database
- Maintenance Cleanup

Il me semble que c'est plutôt une bonne pratique sur un serveur SQL très sollicité. Ce plan est exécuté à 3h du matin toutes les nuits.

Le problème que je rencontre est que lors de l'exécution de ce plan, les frontaux du site qui requêtent la base empilent les requêtes car la base est apparemment incapable de répondre. J'imagine donc que ce plan de maintenance génère un lock général lors d'une des étapes, et que les différentes procédures stockées ne peuvent plus s'exécuter et attendent de pouvoir reprendre la main.

Autre problème, qui peut ne pas en être un, c'est que le temps d'exécution est en moyenne de 2h30, est-ce normal ? (Le fichier mdf de la base pèse 2,5 Go)

Enfin dernière question, est-il vraiment nécessaire d'exécuter ce type de maintenance tous les jours ?

Merci d'avance pour vos réponses, et n'hésitez pas à me communiquer de bonnes pratiques, voire meilleures que les miennes

Guillaume

[dbaffaleuf]

-Check Database Integrity: une fois par semaine en week end c'est déjà pas mal.
- Reorganize index: voir si les indexes sont vraiment fragmentés, mais sinon une fois par semaine en week end.
- Update Statistics: si les options de stats auto sont activées sur les bases (options auto update statistics, auto create statistics) et qu'aucune table n'est mise à jour en manuel (cf post), pas d'utilité de lancer un update stats tous les jours sur toutes les tables.

[mikedavem]

La vérification d'intégrité peut en effet se planifier en fin de semaine par exemple comme l'explique David.

La maintenance de vos index dépend principalement de votre charge et du type des opérations effectués sur vos tables. En fonction de vos mises à jour (INSERT, UPDATE, DELETE) vous devrez voir votre planification. Il se peut même que vous deviez voir s'il ne faut pas reconstruire vos index dans certains cas (fragmentation > 30% ..à exclure les petites tables).

- Update Statistics: si les options de stats auto sont activées sur les bases (options auto update statistics, auto create statistics) et qu'aucune table n'est mise à jour en manuel (cf post), pas d'utilité de lancer un update stats tous les jours sur toutes les tables

.

Là pas tout à fait d'accord avec David. L'option de mise à jour automatique des tables est moins puissante que de faire une mise à jour des statistiques via un plan de maintenance. L'algorythme employé peut devenir inefficace en fonction du volume de données dans la table (500 lignes + 20% des données globales à atteindre pour déclencher une mise à jour dans le process normal pour les tables > 8MB il me semble). Après vous pouvez remplacer la tâche par défaut de votre plan qui s'attaque à toutes vos statistiques en la remplaçant par l'instance sp_updatestatistics qui à partir de SQL Server 2005 ne met à jour que les statistiques dites obsolètes.

++

[gouffe]

Merci pour vos réponses !

En effet, il me semblait bien que tous les jours, c'était peut être un peu trop. Je vais mettre vos conseils en pratique assez rapidement.

En ce qui concerne les statistiques, nous avons des tables de plus de 40 millions de lignes, je ne sais pas si ça change quelque choses à ce que vous me dites, mais je préfère le préciser

Enfin, il me reste cette dernière interrogation, est-ce normal que les procédures stockées se bloquent pendant une partie du processus ? Car du coup, le site ne répond plus, et c'est assez embêtant pour un site grand public !

Merci encore

[dbaffaleuf]

Envoyé par mikedavem

Là pas tout à fait d'accord avec David. L'option de mise à jour automatique des tables est moins puissante que de faire une mise à jour des statistiques via un plan de maintenance. L'algorythme employé peut devenir inefficace en fonction du volume de données dans la table (500 lignes + 20% des données globales à atteindre pour déclencher une mise à jour dans le process normal pour les tables > 8MB il me semble). Après vous pouvez remplacer la tâche par défaut de votre plan qui s'attaque à toutes vos statistiques en la remplaçant par l'instance sp_updatestatistics qui à partir de SQL Server 2005 ne met à jour que les statistiques dites obsolètes.
++

Tu as raison dans l'absolu, mais là la base ne fait que 2,5Gb. Il faut voir dans quelle proportion l'update stats prend du temps sur les 2h30 de traitements quotidiens, avec toute l'activité IO et le verrouillage que ça suppose. Après je suis d'accord avec toi, ce n'est pas si simple, et tout dépend de la vitesse d'obsolescence des stats dans les tables.