Discussion :

[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
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]

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 ?

++

[zinzineti]

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

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 ...

[SQLpro]

[/QUOTE][quote]

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 +

[zinzineti]

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

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]

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]

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]

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/

[dbaffaleuf]

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 :

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

+1.

Par contre je suis un peu sceptique sur la définition de TPD dans MSDN. Une info de Bob Ward du support sur le sujet: "An EX latch prevents writers and readers. For torn-page protection, the engine must actually modify contents of the page because we place a 2-bit signature at every 512 bytes on the page. While we are doing this, we don’t want anyone to write or read the page. If you are using checksum though, we only have to change a specific field in the header. No problem to let someone read the page, but we don’t want someone else to write it." Donc la signature va dans la page elle-même, pas dans l'entête ? A suivre.

[Ptit_Dje]

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...

[Ptit_Dje]

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...

Je sais que je me repete...
Aussi verifier les logs (windows et SQL Serveur).

[zinzineti]

Je sais que je me repete...
Aussi verifier les logs (windows et SQL Serveur).

Ce qui a changé c'est la volumétrie.
Au début la taille des backup étaient autour de 5-6 Go
Et aujourd'hui on est autour de 17 Go .

A+