100% du CPU avec base de données.

Salut,

Un usage à 100% de CPU en continu, reflète bien souvent des problèmes de
plans d'accès (soit les indexes ne sont pas appropriés, soit ta/tes
requête(s) est/sont mal écrite(s)), donc il te suffit d'utiliser le profiler
pour essayer de retrouver ce qui est en cause. Avec les volumétries que tu
évoques sur tes grandes tables, un plan peu adéquat est vite problématique !

Le plus simple est souvent de classer par temps CPU ou nombre de reads, car
un usage CPU intensif veut généralement dire que tu fais des jointures
(souvent nested lopp) sur des fullscan qui tiennent en mémoire et qui font
donc beaucoup d'itérations inutiles sans attente sur IO.
Si tu ajoutes les événements "sp:stmt completed" tu pourra même trouver dans
une proc la requête précise qui consomme beaucoup (parfois ça n'est qu'un
ordre qui est en cause !). Et un "show plan" te permettra d'y voir plus clair
si besoin.

Enfin, vérifies bien que tes statistiques d'index sont à jour car parfois
même avec une bonne indexation et une requête bien écrite, des stats
obsolètes entrainent des plans d'exec erronés avec les conséquences que l'on
connait. Dans ce cas, pense à recompiler la proc en cause après update stats
pour t'assurer d'avoir une pré-compilation en rapport avec tes stats.

Bon courage !

Guillaume.
==================
"Mikado" a écrit :

Bonjour,

> je suppose que cette application est sur le même serveur ?

Bien sûr que non.

> SQL Server est programmé pour prendre toutes les ressources qu'il juge
> nécessaires au détriment de toutes autres applications. La seule exception
> à cette règle est l'OS lui même qui peut demander à SQL Server de lui
> restituer de la mémoire, mais uniquement en cas de stress.

Oui tout à fait. Sauf que dans mon cas SQL Server utilise 100% des CPU
injustement en rapport l'activité en cours. En fait après avec regardé avec
SQL Profiler j'ai trouvé une PS dont le poids est excessif et qui
visiblement n'est pas très optimisée. Je vais déjà modifier la procédure et
voir ensuite ce qui ce passe.

> Soit vous déportez vos applications, soit il vous faudra limiter la
> mémoire utilisée par SQL Server. Si votre serveur (machine) est équipé en
> multi processeur, vous pouvez même limiter le nombre de processeurs
> utilisés par SQL Server.

Tiens d'ailleurs petite question. La machine possède deux procs +
hyperthreading. D'après ce que j'ai pu voir sur un site le hyperthreading
est assez déconseillé avec SQL Server. Est-ce votre constat ?

>
> Encore faudrait-il que vous nous donniez votre configuration :
> description technique complète de la machine (RAM, disque, proc...)
> version des OS
> appli installées
> Volume des bases de données, des disque et taux d'occupation
> etc.
>
> A +

Machine :
BiProc Xeon 3.2Ghz
2Go de RAM
70Go de disque dur en SCSI Raid 5 (utilisé à 21%)

Base de données :
Données : 11323Mo (dont 5452,69Mo disponible)
Journal des transactions : 51,18Mo (dont 44Mo disponible)

Les tables les plus conséquentes font respectivement : 5512, 5469,
31617, 2293254 et 24219374 lignes.
Il y a 14 tables.
Rien d'extraordinaire.

Quelques statistiques sur 10 minutes :

% Temps processeur 91.6%
Connexions utilisateur 26
Nombre de requêtes de lots/s 15
Transactions/seconde 7 pour tempdb, 0 pour les autres
Requêtes de verrous/seconde 97000
Recherche de pages/seconde 197010

Je suis étonné par la recherche de pages/seconde, est-ce normal ? Peut-être
que d'autres infos serait intéressantes ?

Jérôme

Mikado vient de nous annoncer :

Soit vous déportez vos applications, soit il vous faudra limiter la mémoire
utilisée par SQL Server. Si votre serveur (machine) est équipé en multi
processeur, vous pouvez même limiter le nombre de processeurs utilisés par
SQL Server.

Tiens d'ailleurs petite question. La machine possède deux procs +
hyperthreading. D'après ce que j'ai pu voir sur un site le hyperthreading est
assez déconseillé avec SQL Server. Est-ce votre constat ?

J'ai pratiqué un petit HS officiel avec changement de sujet car ce
point m'interresse aussi

j'ai deux bi-xeon 2.6ghz et je me posait la meme question en fait :)

amicalement
wardead

http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx

--
Avec mes meilleurs voeux 2006
Med Bouchenafa


"Wardead" <Wardead_tagada@ifrance.Com> a écrit dans le message de news:
mn.2b707d61a4042b55.23407@ifrance.Com...

Mikado vient de nous annoncer :

Soit vous déportez vos applications, soit il vous faudra limiter la
mémoire utilisée par SQL Server. Si votre serveur (machine) est équipé
en multi processeur, vous pouvez même limiter le nombre de processeurs
utilisés par SQL Server.

Tiens d'ailleurs petite question. La machine possède deux procs +
hyperthreading. D'après ce que j'ai pu voir sur un site le hyperthreading
est assez déconseillé avec SQL Server. Est-ce votre constat ?

J'ai pratiqué un petit HS officiel avec changement de sujet car ce point
m'interresse aussi

j'ai deux bi-xeon 2.6ghz et je me posait la meme question en fait :)

amicalement
wardead

meilleurs voeux egalements et merci

Wardead

Med Bouchenafa avait soumis l'idée :

http://blogs.msdn.com/slavao/archive/2005/11/12/492119.aspx

--
Avec mes meilleurs voeux 2006
Med Bouchenafa


"Wardead" <Wardead_tagada@ifrance.Com> a écrit dans le message de news:
mn.2b707d61a4042b55.23407@ifrance.Com...

Mikado vient de nous annoncer :

Soit vous déportez vos applications, soit il vous faudra limiter la
mémoire utilisée par SQL Server. Si votre serveur (machine) est équipé en
multi processeur, vous pouvez même limiter le nombre de processeurs
utilisés par SQL Server.

Tiens d'ailleurs petite question. La machine possède deux procs +
hyperthreading. D'après ce que j'ai pu voir sur un site le hyperthreading
est assez déconseillé avec SQL Server. Est-ce votre constat ?

J'ai pratiqué un petit HS officiel avec changement de sujet car ce point
m'interresse aussi

j'ai deux bi-xeon 2.6ghz et je me posait la meme question en fait :)

amicalement
wardead

Mikado a écrit:

Bonjour,

je suppose que cette application est sur le même serveur ?

Bien sûr que non.

SQL Server est programmé pour prendre toutes les ressources qu'il juge
nécessaires au détriment de toutes autres applications. La seule exception
à cette règle est l'OS lui même qui peut demander à SQL Server de lui
restituer de la mémoire, mais uniquement en cas de stress.

Oui tout à fait. Sauf que dans mon cas SQL Server utilise 100% des CPU
injustement en rapport l'activité en cours. En fait après avec regardé avec
SQL Profiler j'ai trouvé une PS dont le poids est excessif et qui
visiblement n'est pas très optimisée. Je vais déjà modifier la procédure et
voir ensuite ce qui ce passe.

Soit vous déportez vos applications, soit il vous faudra limiter la
mémoire utilisée par SQL Server. Si votre serveur (machine) est équipé en
multi processeur, vous pouvez même limiter le nombre de processeurs
utilisés par SQL Server.

Tiens d'ailleurs petite question. La machine possède deux procs +
hyperthreading. D'après ce que j'ai pu voir sur un site le hyperthreading
est assez déconseillé avec SQL Server. Est-ce votre constat ?

Oui et non. L'hyperthreading en théorie peu être plus performant que le vrai
quadri proc. Tout dépend du type d'appli.

évidement d'abord tenter d'optimiser la SP en cause
sinon :
1) restreindre le nombre de proc à 3 pour SQL Server (affinity mask)
2) reduire le degré de parrallélisme de certaines requêtes (les plus grosses) à
2 voir 1
3) voir si le passage au mode fibre (lightweight pooling) apporte un gain.

A +

Encore faudrait-il que vous nous donniez votre configuration :
description technique complète de la machine (RAM, disque, proc...)
version des OS
appli installées
Volume des bases de données, des disque et taux d'occupation
etc.

A +

Machine :
BiProc Xeon 3.2Ghz
2Go de RAM
70Go de disque dur en SCSI Raid 5 (utilisé à 21%)

Base de données :
Données : 11323Mo (dont 5452,69Mo disponible)
Journal des transactions : 51,18Mo (dont 44Mo disponible)

Les tables les plus conséquentes font respectivement : 5512, 5469,
31617, 2293254 et 24219374 lignes.
Il y a 14 tables.
Rien d'extraordinaire.

Quelques statistiques sur 10 minutes :

% Temps processeur 91.6%
Connexions utilisateur 26
Nombre de requêtes de lots/s 15
Transactions/seconde 7 pour tempdb, 0 pour les autres
Requêtes de verrous/seconde 97000
Recherche de pages/seconde 197010

Je suis étonné par la recherche de pages/seconde, est-ce normal ? Peut-être
que d'autres infos serait intéressantes ?

Jérôme

--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Salut,

Les cas où un hypethreading est plus rapide qu'un SMP à nombre de
processeurs logiques équivalents (ex : 2 CPU en HT contre 4 CPU physiques)
sont extrèmement rarissimes, car cela serait uniquement dû au partage du
cache L1 et L2 sans être géné par les attentes entre les deux processeurs
logiques d'un CPU au niveau de certains steps partagés du pipeline. Bref,
surtout quand il y a plus d'un CPU hyperthreadé, c'est d'une improbabilité
quasi totale.
Par contre, le fait d'activer l'hyperthread ou ne pas l'activer sur une
machine donnée est en effet sujet à discussions. En pratique, il semble que
tant que l'activité CPU n'est pas trop élevée et que l'on n'a pas trop de
concurrence sur les processus, l'HT est plutôt bénéfique (ça donne de la
marge), mais par contre, en situation de saturation CPU c'est logiquement
handicapant voire aggravant.
Il faut donc savoir si l'activité de la machine est maitrisée (stable ?).

Ensuite, sur un système en saturation CPU, réduire le nombre de processeurs
utilisés par SQLServer peut être intéressant en cas de multi-instances pour
cloisonner ou pour donner un peu de disponibilité à la machine car Windows
aime modérément les plafonds soutenus à 100% de CPU (certaines actions dont
le debogage de la situation deviennent pénibles !). Mais par contre, cela ne
fera que pénaliser les performances théoriques de l'instance qui perdra
logiquement en puissance de traitement. Donc dans le cas présent, je ne suis
pas sûr que ce soit une solution utile.

Pour la réduction du degré de parallélisme, cela revient souvent, si l'on
cherche à améliorer les perfs (et non pas seulement à réduire la consommation
CPU) à agir sur le plan d'éxecution, donc on revient sur les considérations
index/ecriture de la proc/stats.

Quand au passage au mode fibre, par définition il est lié au nombre de
clients étant donné qu'il existe pour alléger le surcoût exponentiel du
"process context switching" et des passages trop nombreux entre mode noyau et
mode utilisateur, donc dans le cas présent (30 clients) je doute de son
intérêt. En plus, il faut bien se rappeler que vu son mode de fonctionnement,
l'usage du mode fibre est sujet à de grandes précautions :
http://msdn.microsoft.com/library/en-us/dnsqldev/html/sqldev_02152005.asp

Voilà mon opinion !
"Just my 2 cents ..."

Guillaume.

"Fred BROUARD" a écrit :

Oui et non. L'hyperthreading en théorie peu être plus performant que le vrai
quadri proc. Tout dépend du type d'appli.

évidement d'abord tenter d'optimiser la SP en cause
sinon :
1) restreindre le nombre de proc à 3 pour SQL Server (affinity mask)
2) reduire le degré de parrallélisme de certaines requêtes (les plus grosses) à
2 voir 1
3) voir si le passage au mode fibre (lightweight pooling) apporte un gain.

A +