Problème de lenteur : Opération "pompier"

**zinzineti** · 05/10/2010, 23h57

Pour une opération "pompier" j'ai un serveur dédié SQL Server 2005 SP2.
OS = W2K3 SERVER R2 (US)
1 Disque C : espace libre 8 Go
1 Disque D : espace libre environ 17 Go
Mémoire = 4Go
Processeurs = (je ne me souviens plus exactement, je vais le confirmer demain matin)
Ce serveur SQL dialogue avec une application de gestion de centre d'appel.
Et depuis quelques jours des lenteurs inacceptables sont constatées par les utilisateurs de l'application... Je passe les détails...[je vous rassure, tout de suite que je n'ai pas participé à l'installation de ce serveur

]

Après un audit rapide je constate que :

1.) Les bases systèmes et les bases utilisateurs sont installées sur le même disque C. Les fichiers .mdf et .ldf sont tous sur le même disque C pour toutes les bases

ce que je pense faire :
-->déplacer les .mdf des bases utilisateurs sur le disque D

2.) Le processus sqlserver.exe utilise un mémoire max d'environ 1,7 Go (visible via le Task manager) et dans le boot.ini l'option /3GB n'est pas activé

ce que je pense faire :
-->Mettre /3GB dans le boot.ini
-->Configurer le max memory server size à 3Go côté SQL SERVER
-->Un redémarrage de l'OS est-il indispensable pour la prise en compte du paramétrage de /3GB dans le boot.ini ?

3.) L'utilisation CPU oscille entre 98% - 100 % (visible via le Task manager)
en exécutant 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
19
SELECT  
 spid 
,hostname 
,[status] 
,blocked 
,open_tran 
,waittime 
,cmd 
,memusage 
,cpu 
,physical_io 
,login_time 
,last_batch 
,loginame 
,DB_NAME(DBID) AS [database] 
,[program_name] 
FROM master.sys.sysprocesses 
WHERE spid > 50 
AND spid <> @@SPID

je constate qu'il y a une centaine de processus non blockés (colonnes blocked =0 et open_tran =0), mais qui sont dormant (colonne status = SLEEPING)
et dont la date de dernière exécution est environ 1 mois (colonne last_batch) sur les bases utilisateurs.Le détails de quelques un de ces processus montre des DELETE tableUtilisateur WHERE date = 'date_ILYA_1mois'

Je me demande :
-->Je me demande ce qu'il faut faire de ces processus dormant !!! dormant mais consomme quand même des ressources (memoire,cpu,io) !!!

4.) trois des principales bases ont l'option AUTO CLOSE à TRUE

ce que je pense faire :
--> les mettre à FALSE

5.) L'option page verify de toutes les bases Utilisateurs sont à TORN_PAGE_DETECTION

ce que je pense faire :
--> les mettre à CHECKSUM

6.) Pour toutes les bases le mode de récupération est à SIMPLE
--> je ne change pas

7.) La reverse engineering (à l'aide de Power AMC) d'une des principales base m'a montré :
--> 13 tables au total pour cette base
--> la principale table de cette base possède 132 colonnes
--> le MPD montre que toutes les tables sont indépendantes : aucune jointure entre les tables !!!

Ce n'est pas le plus urgent actuellemnt mais j'aimerais quand même avoir votre avis sur ce modèle de données

Merci d'avance

**mikedavem** · 06/10/2010, 06h59

Bonjour,

-->déplacer les .mdf des bases utilisateurs sur le disque D

Une bonne chose à faire. Au passage le disque C: est il sur un axe physique différent que le disque D: ?

-->Un redémarrage de l'OS est-il indispensable pour la prise en compte du paramétrage de /3GB dans le boot.ini ?

Oui

L'utilisation CPU oscille entre 98% - 100 % (visible via le Task manager)

Je suppose que cette valeur est plus ou moins constante dans le temps. La il faut voir ce qui fait monter ta CPU ... avec dans un 1er temps les compteurs perfmon adéquates et les DMV si tu veux pousser l'analyse (Je n'ai pas le temps de mettre la liste ce matin

mais je suppose que tu les connais) . Au fait environnemment virtualisé ou pas ?

++

**SQLpro** · 06/10/2010, 10h08

[/QUOTE][quote]

Envoyé par zinzineti

Pour une opération "pompier" j'ai un serveur dédié SQL Server 2005 SP2.
OS = W2K3 SERVER R2 (US)
1 Disque C : espace libre 8 Go
1 Disque D : espace libre environ 17 Go

ATTENTION, si l'espace libre sur les disque est faible, les temps de réponse augmente de manière exponentielle. Il n'est pas conseillé que l'espace libre fasse moins de 10%. (en général on alerte à 30% de libre).

Mémoire = 4Go
Processeurs = (je ne me souviens plus exactement, je vais le confirmer demain matin)
Ce serveur SQL dialogue avec une application de gestion de centre d'appel.
Et depuis quelques jours des lenteurs inacceptables sont constatées par les utilisateurs de l'application... Je passe les détails...[je vous rassure, tout de suite que je n'ai pas participé à l'installation de ce serveur

]

Y a t-il d'autres applications installées, parce que 98 % de CPU me laisse à panser qu'il doit y avoir un antivirus, un firewall, u serveur web, un serveur d'impression et une usine à gaz qui tourne en //

Après un audit rapide je constate que :

1.) Les bases systèmes et les bases utilisateurs sont installées sur le même disque C. Les fichiers .mdf et .ldf sont tous sur le même disque C pour toutes les bases

ce que je pense faire :
-->déplacer les .mdf des bases utilisateurs sur le disque D

Cela n'a d'intérêt que si les disques sont physiquement indépendant. Si ce sont des partitions cela ne sert à rien.

2.) Le processus sqlserver.exe utilise un mémoire max d'environ 1,7 Go (visible via le Task manager) et dans le boot.ini l'option /3GB n'est pas activé

ce que je pense faire :
-->Mettre /3GB dans le boot.ini
-->Configurer le max memory server size à 3Go côté SQL SERVER
-->Un redémarrage de l'OS est-il indispensable pour la prise en compte du paramétrage de /3GB dans le boot.ini ?

OUI

3.) L'utilisation CPU oscille entre 98% - 100 % (visible via le Task manager)
en exécutant 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
19
SELECT  
 spid 
,hostname 
,[status] 
,blocked 
,open_tran 
,waittime 
,cmd 
,memusage 
,cpu 
,physical_io 
,login_time 
,last_batch 
,loginame 
,DB_NAME(DBID) AS [database] 
,[program_name] 
FROM master.sys.sysprocesses 
WHERE spid > 50 
AND spid <> @@SPID

je constate qu'il y a une centaine de processus non blockés (colonnes blocked =0 et open_tran =0), mais qui sont dormant (colonne status = SLEEPING)
et dont la date de dernière exécution est environ 1 mois (colonne last_batch) sur les bases utilisateurs.Le détails de quelques un de ces processus montre des DELETE tableUtilisateur WHERE date = 'date_ILYA_1mois'

Je me demande :
-->Je me demande ce qu'il faut faire de ces processus dormant !!! dormant mais consomme quand même des ressources (memoire,cpu,io) !!!

Voir avec DBCC OPENTRAN quelle est la plus vieille transaction, si très ancienne la killer

4.) trois des principales bases ont l'option AUTO CLOSE à TRUE

ce que je pense faire :
--> les mettre à FALSE

Oh que OUI !

5.) L'option page verify de toutes les bases Utilisateurs sont à TORN_PAGE_DETECTION

ce que je pense faire :
--> les mettre à CHECKSUM

cela va légèrement ralentir

6.) Pour toutes les bases le mode de récupération est à SIMPLE
--> je ne change pas

7.) La reverse engineering (à l'aide de Power AMC) d'une des principales base m'a montré :
--> 13 tables au total pour cette base
--> la principale table de cette base possède 132 colonnes
--> le MPD montre que toutes les tables sont indépendantes : aucune jointure entre les tables !!!

Ce n'est pas le plus urgent actuellemnt mais j'aimerais quand même avoir votre avis sur ce modèle de données

TOTALEMENT a chier. Lisez les papier sur l'optimisation que j'ai fait. Dans 70% des cas c'est le modèle qui est la cause des mauvaises performances. A l'évidence vus êtes dans le cas le plus tragique : utilisation d'un SGBDR pour gérer des "fichiers COBOL" !

Merci d'avance

A +

**Ptit_Dje** · 06/10/2010, 13h12

Envoyé par zinzineti

Et depuis quelques jours des lenteurs inacceptables sont constatées par les utilisateurs de l'application... Je

Je rajouterai d'essayer d'identifier precisement quand les problemes de lenteur sont apparus et ce qu'il s'est passe sur le serveur a ce moment la.
Car si tout etait stable avant et que les perfs etaient acceptables, il ne faut peut etre pas se lancer dans de grand travaux de re-amenagement bien qu'ils seraient preferables.
Au client de decider...

**zinzineti** · 06/10/2010, 13h16

Envoyé par SQLpro

Y a t-il d'autres applications installées, parce que 98 % de CPU me laisse à panser qu'il doit y avoir un antivirus, un firewall, u serveur web, un serveur d'impression et une usine à gaz qui tourne en //

Bien vu

il y a des choses qui sont installées sur la même machine ! :
--> IIS est installé
--> ce IIS héberge des rapports (Reporting Services )
--> Adobe Acrobat
--> le FULL text est installé mais n'est pas utilisé !
--> L'Analysis service installé mais pas utilisé
--> SQL Server Browser est aussi installé
Ce que j'ai fait
-->Arrêter ces services qui ne sont pas utiles

Envoyé par SQLpro

Cela n'a d'intérêt que si les disques sont physiquement indépendant. Si ce sont des partitions cela ne sert à rien.

Dans le gestionnaire de disque, j'ai constaté qu'il existe UN SEUL disque physique. C et D sont des partitions logiques d'UN et UN SEUL DISQUE.
Ce que j'ai fait
--> J'ai laissé les data à leur place (sur la partition C).

A propos du processeur voici la config

Processeur(s): 8 processeur(s) installé(s).
[01]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[02]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[03]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[04]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[05]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[06]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[07]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[08]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz

Après les opérations et redémarrage du serveur :
1.) la mémoire sqlsrv.exe tourne autour 2,8 Go. Preuve que le /3GB à fonctionner.
2.) Le CPU oscille entre 80 % et 98%

Une légère amélioration des temps de réponse est constatée côté applicatif .

3.) J'ai commencé par regarder les requêtes les plus consommatrices de CPU et les requêtes les plus exécutées sur l'instance et là :
--> des requêtes paramétrées du style WHERE id LIKE @p ( le paramètre @p est un entier)
--> des requêtes kilométriques avec des SELECT imbriqués et des WHERE valeur NOT IN (...) avec des séries de NOT LIKE ....

Malheureusement on peut pas toucher à ces requêtes parce qu'elles sont natives à l'application cliente

--> J'ai proposé au propriétaire de la solution une purge de la plus grosse table qui contient actuellement plus de 5 millions de lignes (pour à peine 2 mois de mise en production de la solution !!!)

En tout cas merci pour votre aide

**kagemaru** · 06/10/2010, 13h28

une remarque :

5.) L'option page verify de toutes les bases Utilisateurs sont à TORN_PAGE_DETECTION

ce que je pense faire :
--> les mettre à CHECKSUM
cela va légèrement ralentir

Je ne suis pas sûr que le CHECKSUM va ralentir car il semble qu'il ait moins de travail à faire que le TORN PAGE :

When CHECKSUM is specified, the Database Engine calculates a checksum over the contents of the whole page and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value stored in the page header.

When TORN_PAGE_DETECTION is specified, a specific 2-bit pattern for each 512-byte sector in the 8-kilobyte (KB) database page is saved and stored in the database page header when the page is written to disk. When the page is read from disk, the torn bits stored in the page header are compared to the actual page sector information.

cf. http://msdn.microsoft.com/en-us/library/ms190249.aspx

**zinzineti** · 06/10/2010, 13h30

Envoyé par mikedavem

Bonjour,
Une bonne chose à faire. Au passage le disque C: est il sur un axe physique différent que le disque D: ?

Au fait il y a un SEUL disque physique. donc j'ai rebroussé chemin pour ce qui concerne le déplacement des data vers la partition logique D

Envoyé par mikedavem

Je suppose que cette valeur est plus ou moins constante dans le temps. La il faut voir ce qui fait monter ta CPU ... avec dans un 1er temps les compteurs perfmon adéquates et les DMV si tu veux pousser l'analyse (Je n'ai pas le temps de mettre la liste ce matin

mais je suppose que tu les connais) . Au fait environnemment virtualisé ou pas ?

L'environnement n'est pas virtualisé. c'est un serveur physique avec une architecture 32 bits.
L'utilisation du processeur oscille entre 80 % et 98%

Une lègère amélioration est observée côté applicatif ...

**zinzineti** · 06/10/2010, 13h33

Envoyé par kagemaru

une remarque :
Je ne suis pas sûr que le CHECKSUM va ralentir car il semble qu'il ait moins de travail à faire que le TORN PAGE :

Cette modif peut être faite à chaud ou pas ?

Merci d'avance

**kagemaru** · 06/10/2010, 14h06

qq liens utiles, je crois qu'il dit à peu près tout à propos du page_verify :

http://blog.capdata.fr/index.php/mod...puis-sql-2000/
http://blog.capdata.fr/index.php/myt...-que-checksum/

**zinzineti** · 06/10/2010, 14h25

Ok je garde donc le CHECKSUM

A+

**mikedavem** · 06/10/2010, 14h27

La mise à jour peut être fait à chaud pour répondre à ta question.
Seuls les pages lues et écrites par la suite pourront bénéficier de ce mode de vérification.

NB : Les articles de David sont très intéressants sur le sujet

++

**zinzineti** · 06/10/2010, 14h56

Envoyé par mikedavem

La mise à jour peut être fait à chaud pour répondre à ta question.
Seuls les pages lues et écrites par la suite pourront bénéficier de ce mode de vérification.

NB : Les articles de David sont très intéressants sur le sujet

++

Oui j'ai lu rapidement les conclusions de l'article

**SQLpro** · 06/10/2010, 15h54

Voir si l'hyper thrading est activé et le désactiver

des requêtes paramétrées du style WHERE id LIKE @p ( le paramètre @p est un entier)

Là ça tue car il fait une conversion à la volée de la colonne ID en CHAR pour faire le LIKE imbécile !

des requêtes kilométriques avec des SELECT imbriqués et des WHERE valeur NOT IN (...) avec des séries de NOT LIKE ....

Là ça tue doublement puisque le NOT n'est que très rarement optimisable (non sargeable)

Il faut mettre en cause l'éditeur pour son incompétence. Tu peut pas savoir le nombre d'éditeur pour lesquels mes audit ont eut pour effet de mettre le nez dans leurs caca et obligé de rectifier la sauce. C'est lent, mais ça arrive !

--> J'ai proposé au propriétaire de la solution une purge de la plus grosse table qui contient actuellement plus de 5 millions de lignes (pour à peine 2 mois de mise en production de la solution !!!)

Au moins.

Voit aussi si la base est en SET PARAMETERIZATION FORCED, si non met le.

Créé aussi le maximum d'index cela rectifiera un peu le modèle.

A +

**zinzineti** · 06/10/2010, 16h39

Envoyé par SQLpro

Voir si l'hyper thrading est activé et le désactiver

Aïiiiii, là il faut aller dans le bios ==> redémarrage de la machine
Je vais voir avec eux

Envoyé par SQLpro

Au moins.
Voit aussi si la base est en SET PARAMETERIZATION FORCED, si non met le.

Pour toutes les bases ont SET PARAMETERIZATION = SIMPLE
Et je viens de faire la modif SET PARAMETERIZATION = FORCED mais jusque là aucun changement : l'utilisation CPU oscille toujours entre 80% et 98%.

Je vais voir aussi pour les index

A+

**mikedavem** · 06/10/2010, 16h54

As tu regardé également de l'option de serveur max degree of parallelism ?
Celle-ci doit avoir la valeur 1 pour un environnement de type OLTP.

Une autre piste également est de voir si tu n'as pas des compilations excessives ? (SQL Statistics : Compilations / s et Recompilations / s)

++

**zinzineti** · 06/10/2010, 17h24

Envoyé par mikedavem

As tu regardé également de l'option de serveur max degree of parallelism ?
Celle-ci doit avoir la valeur 1 pour un environnement de type OLTP.

Une autre piste également est de voir si tu n'as pas des compilations excessives ? (SQL Statistics : Compilations / s et Recompilations / s)

++

Voici la config actuelle : run_value = 0

Min max config_value run_value
max degree of parallelism 0 64 0 0

Le BOL dit :

Vous pouvez utiliser l'option max degree of parallelism pour limiter le nombre de processeurs à utiliser lors de l'exécution des plans parallèles. Avec la valeur par défaut 0, tous les processeurs disponibles sont utilisés

http://msdn.microsoft.com/fr-fr/libr...v=SQL.90).aspx

Et voici ce que j'ai au niveau processeur

Processeur(s): 8 processeur(s) installé(s).
[01]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[02]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[03]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[04]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[05]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[06]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[07]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz
[08]: x86 Family 6 Model 26 Stepping 5 GenuineIntel ~2261 MHz

Pourquoi dois-je le positionner à 1 alors que j'ai 2 processeurs physique quadri-core ?

merci de m'éclairer

**mikedavem** · 06/10/2010, 17h26

http://blog.developpez.com/mikedavem...ance/#more7215

++

**zinzineti** · 06/10/2010, 17h41

Envoyé par mikedavem

http://blog.developpez.com/mikedavem...ance/#more7215

++

Ok j'ai configuré à 1 mais je pour le moment rien n'a changé !

C'est trop tôt pour observer le changement ?

**mikedavem** · 06/10/2010, 17h56

A voir au fur et à mesure mais ceci n'est valable que si tu observes des temps d'attentes importantes CXPACKET sur ton serveur.

Mais revenons à quelque chose de plus simple ?
Est ce le processus Sqlserv.exe qui prend l'ensemble des ressources CPU ?
D'autres processus ne sont pas en cause ? Tu peux le voir facilement avec perfmon. Est ce le temps processeur utilisateur qui est majoritaire ou le temps processeur kernel ?

Est ce que tu n'aurais pas un antivirus sur ton serveur ? Est ce qu'il ne scannerait pas tes fichiers ?

++

**zinzineti** · 06/10/2010, 22h56

Envoyé par mikedavem

Mais revenons à quelque chose de plus simple ?
Est ce le processus Sqlserv.exe qui prend l'ensemble des ressources CPU ?

Oui c'est le processus Sqlserv.exe qui prend les 80%-98% de ressource (je le vois via le Task manager)

Envoyé par mikedavem

D'autres processus ne sont pas en cause ? Tu peux le voir facilement avec perfmon. Est ce le temps processeur utilisateur qui est majoritaire ou le temps processeur kernel ?

Je n'ai pas lancé perfmon pour collecter les compteurs de perf.
Dans mon cas voici les compteurs que je pense collecter dans un premier temps :

Côté OS
----------------
--> Processor %Privileged Time
--> Processor %Processor Time
--> System %Processor Queue Lenght

Côté Instance SQL SERVER
----------------
--> Process (sqlservr) %Privileged Time
--> Process (sqlservr) %Processor Time

Envoyé par mikedavem

Est ce que tu n'aurais pas un antivirus sur ton serveur ?
Est ce qu'il ne scannerait pas tes fichiers ?

L'antivirus est bien désactivé.

A+