Reconstructions d'index qui dégradent les performances

[vinse51]

Bonjour à tous,

J'ai récemment acquis un nouveau serveur très performant pour accueillir ma base de données. Il s'agit d'un SQL Server 2008 r2 Web Edition, avec 8 processeurs, 24 Go de ram et 120 Go en SSD.
Depuis les migrations, les performances des requêtes ont été améliorées par 10 et je suis très satisfait du serveur.

Cependant, quelques jours après la migration j'ai procédé à la reconstruction des différents indexs (non cluster) comme je le faisais environ 1 fois par semaine sur l'ancien serveur. Cela concerne une centaine d'index (la bd compte 300 tables et pèse 40 Go au total).

Mais voila, environ 1 heure après les rebuild, je me suis aperçu que le processeur était bloqué à 100% alors qu'en temps normal il ne dépasse absolument jamais les 20% de moyenne.
Plusieurs requêtes un peu gourmandes donnait des timeout alors qu'en temps normal elles prennent 5s max.

J'ai tenté de relancer le moteur, puis la machine mais rien à faire la base de données était complètement inutilisable, détruite !
J'ai du me contraindre à restaurer une sauvegarde complète effectuée quelques heures avant les rebuild et tout est revenu à la normale.

Mais je voudrais vraiment comprendre ce problème car maintenant je n'ose plus du tout effectuer la moindre reconstruction d'index !

Les seules différences avec l'ancien serveur sont :
- disque dur en SSD
- 24 Go de ram au lieu de 1 Go (limitation de la version Express disparue avec la version Web)

J'ai lu qu'avec les disque SSD il ne fallait plus faire de défragmentation puisqu'il n'y a plus de têtes de lecture et il me semble que la reconstruction d'index est similaire à une défragmentation, mais est-ce que cela pourrait dégrader les performances à ce point ?

Sinon peut être que le fait que la BD utilise désormais plus de 20 Go en ram a à voir quelque chose. Si toutes les reconstructions se font dans la ram alors peut être que tous les plans d’exécutions ne sont plus valides car l'ordre des données a changé ? Mais alors pourquoi en redémarrant le serveur cela ne résoudrait pas le problème ?

Bref je n'ai franchement aucune explication à ce phénomène mais je serai vraiment très curieux d'en comprendre les causes.

Merci d'avance pour vos éclairages.

[SQLpro]

Ceci est parfaitement normal. deux choses à dire :
1) SQL Server est optimisé pour tirer partit du maximum de ressources
2) pendant le reconstruction d'un index la table ne peut pas être utilisée.

1) signifie que TOUS vos processeurs seront utilisés pour reconstruire un index, laissant peu de CPU pour les autres demandes. Utilisez l'option MAXDOP dans le CREATE / REBUILD INDEX pour limiter le nombre de CPU utilisé...

2) il ne faut pas recréer un index aux heures pleines, mais :
- soit le faire aux heures creuses
- soit le faire avec l'option "ONLINE" (disponible uniquement en version Enterpise).

En sus : en 64 bits, vous devez impérativement limiter la RAM utilisée par SQL Server à TOTAL RAM - 2 Go. Sinon SQL pompera la RAM de Windows et vous arriverez à l'instabilité que vous avez décrit (de toute façon les choses seraient rentrées dans l'ordre au final).

Bref, un petit cours d'admin SQL Server ne serait sans doute pas superflu !

A +

[mikedavem]

J'ai lu qu'avec les disque SSD il ne fallait plus faire de défragmentation puisqu'il n'y a plus de têtes de lecture et il me semble que la reconstruction d'index est similaire à une défragmentation, mais est-ce que cela pourrait dégrader les performances à ce point ?

Je rajouterai que ceci est parfaitement faux. La fragmentation des index même sur des disques SSD est néfaste aux performances de vos requêtes pour plusieurs raisons :

- Il faudrait lire ramener beaucoup plus de pages (ou blocs de pages .. extents) pour lire un même volume de données

- Le choix de l'optimiseur peut changer si un index trop fragmenté est présent.

Il ne faut pas confondre fragmentation logique (dans votre cas) et la fragmentation physique sur disque.

++

[vinse51]

Bonjour et merci pour vos réponses !

Par contre je crois que je n'ai pas été bien compris.
Le problème ne s'est pas posé pendant la reconstructions des index mais plusieurs dizaines de minutes plus tard.

Je connais assez bien le fonctionnement des index et je sais qu'il ne faut pas les reconstruire n'importe quand car cela bloque l'accès aux tables.
C'est pourquoi j'ai effectué les rebuild très tôt le matin et tout s'est bien passé.
L'opération a pris quelques minutes et le serveur n'a pas bronché.

C'est seulement plus tard que les performances ce sont dégradées, bien après la fin des requêtes de rebuild.

Du coup est-ce que vos indications tiennent toujours où il s'agit d'autre chose ?

PS: merci pour la remarque sur le total de ram à utiliser je n'y avais pas pensé mais c'est vrai ça parait logique qu'il faut en réserver pour le système je vais modifier la config même si je ne pense pas encore avoir eu de problème à ce niveau là.

PS2: merci pour la confirmation sur la fragmentation, je me disais aussi que peu importe la fragmentation physique, la fragmentation des pages reste toujours un problème important et donc la reconstruction d'index nécessaire.

[mikedavem]

Il faudrait mettre des sondes (via perfmon ou DMV) dans ce cas pour savoir ce qui prend autant de temps CPU après rebuild.

Est ce que c'est le processus SQL Server ?
Est ce que l'antivirus n'est pas en cause ?
Est ce le temps CPU utilisateur ou privilégié qui est en cause ?
Est ce une requête en particulier ?

++

[vinse51]

Le problème c'est que ça ne va pas être évident à reproduire malheureusement.

Ce que je peux dire du peu que j'ai pris le temps d'analyser :
- Oui c'est bien uniquement le processus SQL serveur bloqué continuellement à 100%
- Il n'y a pas d'antivirus ni aucun logiciel d'analyse (le serveur est uniquement dédié à SQL serveur et j'utilise IPfilter pour n'autorisuer que l'IP du serveur Web et la mienne.
- Ce n'est pas une requête en particulier mais toutes les "grosses requêtes", c'est à dire celles qui en temps normal lisent de quelques centaines à quelques milliers de pages (mais sur ce serveur c'est tout de même instantané)
- Par contre d'autres requêtes passent encore comme de simple select sur clé primaire
- même en attendant plusieurs dizaines de minutes rien ne s'arrange, la BD est complètement détruite et inutilisable sans restauration antérieure
- Et dernière observation surement la plus importante : je suis presque sûr que le nombre de pages lues par requêtes a littéralement explosé : par exemple une requête qui en temps normal lit 400 pages en lisait peut être 150000 (le tout en 5min au lieu de 1s)

Tout ceci me fait penser à un problème de statistiques qui ne seraient plus à jour ou bien aux plans d’exécutions qui deviennent fous, mais alors pourquoi en redémarrant le serveur (et même la machine) cela ne rentrerait pas dans l'ordre ?

[SQLpro]

Quelle est la version de la base et celle du serveur ?

A +

[vinse51]

Il s'agit de Sql Server 2008 r2 Web Edition qui tourne sur Windows Server 2008 R2 Core Web Edition 64bits sans aucun rôle installé.
La machine est entièrement dédié au processus SQL Server.

Par contre je ne sais pas si c'est important mais la BD a d'abord tournée sur 2005 Express puis 2008 r2 Express avant ce serveur.

[iberserk]

Question bête:
après 'reconstruction' de vos indexes, avez vous vérifié qu'ils étaient toujours présent (et actif)?

[iberserk]

A propos la web edition ne gère que 4 processeurs....

[vinse51]

Non malheureusement je n'ai pas effectué cette vérification.
J'avoue que j'étais tellement choqué par le problème que je n'ai vraiment pas pris le temps d'investir en profondeur.

Plus je tourne autour et plus il me semble qu'effectivement après les reconstructions les index n'étaient tout simplement plus utilisés.

Je ne pense pas qu'ils aient pu être supprimés par SQL Server sans que je lui en donne l'instruction.

Par contre pour une raison inconnue il semble que l'optimiseur ai décidé que les indexs reconstruits n'étaient plus utilisables !
Je soupçonne que cela a à voir avec les statistique puisque c'est là dessus que se base l'optimiseur.

Mais comment une reconstruction d'index pourrait entrainer l'invalidation de ceux-ci ?

Edit : oui quand je dit 8 processeurs il s'agit de 2 processeurs physique de 4 cœurs chacun.

[elsuket]

Bonjour,

Je ne pense pas qu'ils aient pu être supprimés par SQL Server sans que je lui en donne l'instruction.

C'est exact : seule l'instruction DROP INDEX unIndex ON uneTableOuUneVueIndexee supprime un index.
Est-ce que vous utilisez un plan de maintenance, ou bien une procédure stockée "maison" ?

Envoyé par iberserk

après 'reconstruction' de vos indexes, avez vous vérifié qu'ils étaient toujours présent (et actif)?

Cela mérite un coup d’œil
Utilisez pour ce faire la première requête de mon billet en ajoutant I.is_disabled dans le SELECT

Par contre pour une raison inconnue il semble que l'optimiseur ai décidé que les indexs reconstruits n'étaient plus utilisables !
Je soupçonne que cela a à voir avec les statistique puisque c'est là dessus que se base l'optimiseur.

Mais comment une reconstruction d'index pourrait entrainer l'invalidation de ceux-ci ?

Lors de la reconstruction d'un index, la statistique sous-jacente est ré-échantillonnée (jamais dans le cas d'une réorganisation) à 100% (équivalent FULLSCAN), et je me souviens avoir eu un problème avec cela, résolu par un échantillonnage par défaut (UPDATE STATISTICS monIndex (sans FULLSCAN)).

Je pense que c'est dû au fait que le nombre d'étapes des statistiques est trop faible (maximum 200) dans certains cas pour permettre à SQL Server d'évaluer à peu près correctement et dans tous les cas la cardinalité d'un prédicat ...
Mais encore une fois, c'était un cas isolé, ce qui ne semble pas être votre cas ...

Vous devez peut-être envisager de capturer le plan d'une requête avant que la reconstruction soit faite, puis après, et évaluer les différences ...

@++