Discussion :

[Donpi]

Bonjour,

J'ai un comportement étrange aujourd'hui.

J'avais un serveur 4 cores avec 16Go de ram qui utilisais en moyenne 20% de temps CPU.
Aujourd'hui je l'ai passé à 16 cores et 32Go de ram et la machine est toujours utilisée a 20%-30% de temps CPU.

Le task manager donne bien que c'est SQL server qui utilise 30% de CPU mais il n'y a aucune requête qui tourne.

Qqun à une idées de ce qu'il fait ?

A+

[taibag]

Hello,

Peut-être que ce lien va t'aider : http://mssqlwiki.com/2012/10/04/trou...igh-cpu-usage/

[SQLpro]

SQL Server s'auto tune en permanence et l'Agent SQL se scrute pour les travaux à réaliser...
20% est parfaitement normal et votre serveur est à l'aise.
Dans un SGBDR qui fonctionne correctement, le CPU attend 75 à 80 % de son temps les flux d'IO.
Ce qui serait inquiétant c'est que le CPU commence à être utilisé à 50 ou pire 75%... Cela voudrait dire que l'on a pas fait de l'ensembliste mais de l'itératif => curseurs, boucle WHILE...
Vous vous attendiez à ce qu'il baisse ?
Dans ce cas il fallait au niveau du hardware accélérer les flux d'IO en y mettant de la carte Fusion IO, des disques SSD, un serveur plus rapide dans ses bus...etc !

A +

[Donpi]

Je me dis que si 4 CPU à 20% d'utilisation suffisent à saturer le bus, 16 processeur devraient le saturer à 5%.

[SQLpro]

Et non !!!

A +

[mikedavem]

Le task manager donne bien que c'est SQL server qui utilise 30% de CPU mais il n'y a aucune requête qui tourne.

Tu parles de quel temps CPU?

- % User Time
- % Kernel Time

Quelle est la part pour chaque type?

++

[Donpi]

Il s'agit de ce que donne le task manager, je n'ai pas plus de détail ( ou je ne sais pas ou le trouvé )

[elsuket]

Bonjour,

Vous pouvez vous aider de la requête que j'ai publié ici pour savoir ce qui s'exécute dans SQL Server.
L'outil Moniteur d'Activité peut également vous aider.

@++

[Donpi]

Oui, j'avais déjà utilisé cette requête

Mais elle ne retourne rien de pertinent dans mon cas, maximum deux ou 3 requêtes en simultané.

Sinon le moniteur de ressources m'indique un taux élevé de compilations par rapport au nombre de requêtes.
J'ai trouver à quelque art que le ratio de compilation devait être inférieure à 20%.
Hors chez mois je suis au mieux à 80% voir même au dessus de 100% dans certains cas (d’ailleurs je ne vois pas comment c'est possible d’avoir plus de compiles que de requêtes)

[mikedavem]

Il s'agit de ce que donne le task manager, je n'ai pas plus de détail ( ou je ne sais pas ou le trouvé )

Sur les graphes CPU > clic droit > Show Kernel Times

++

[elsuket]

Bonjour,

Effectivement 80%+ de compilations ou re-compilations, c'est très très élevé.
Même 20%, c'est à dire une requête sur cinq, c'est très haut, à moins que votre charge de travail soit un grand nombre d'INSERT non paramétrés.

Pour en avoir le cœur net, voyez le résultat de la requête suivante.
Elle vous donnera le nombre d'entrées dans la cache de requêtes pour celles qui en ont plus d'une.
S'il y a de très nombreuses entrées pour l'une d'entre elles, copiez la valeur de la colonne sample_sql_handle de cette requête, et passez la en paramètre aux fonctions commentées en fin de lot : ceci vous donnera la requête et son plan, s'il est encore dans le cache.

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
;WITH
	CTE AS
	(
		SELECT		QS.query_plan_hash
				, QS.query_hash
				, COUNT(*) AS entries_count
				, SUM(QS.execution_count) AS execution_count
				, MIN(QS.sql_handle) AS sample_sql_handle
				, MIN(QS.plan_handle) AS sample_plan_handle
				, SUM(QS.total_worker_time) AS total_CPU_time
				, SUM(QS.total_logical_reads) AS total_logical_reads
				, SUM(CAST(P.size_in_bytes AS bigint)) / 1024 / 1024 AS total_size_MB
		FROM		sys.dm_exec_query_stats AS QS
		INNER JOIN	sys.dm_exec_cached_plans AS P
					ON QS.plan_handle = P.plan_handle
		GROUP BY	query_plan_hash, query_hash HAVING COUNT(*) > 1
	)
SELECT		Q.entries_count
		, Q.execution_count
		, Q.total_logical_reads
		, Q.total_CPU_time
		, Q.sample_plan_handle
		, ROW_NUMBER() OVER(ORDER BY Q.entries_count DESC) AS n
		, Q.query_plan_hash
		, Q.query_hash
		, QP.objectid
		, DB_NAME(QT.dbid) AS database_name
		, Q.total_size_MB
FROM		CTE AS Q
CROSS APPLY	sys.dm_exec_query_plan(Q.sample_plan_handle) AS QP
WHERE		QT.text NOT LIKE '%sp?_replmonitorrefreshagentdata%' ESCAPE '?'
AND		QT.text NOT LIKE '%sp?_replmonitorhelppublisherhelper%' ESCAPE '?'
AND		(
			(
				QP.dbid > 4
				AND QP.dbid <> 32767
			)
			OR QP.dbid IS NULL
		)
ORDER BY	Q.entries_count
 
--SELECT * FROM sys.dm_exec_query_plan(0x06000700000FDA1B4081CADC020000000000000000000000)
--SELECT * FROM sys.dm_exec_sql_text(0x06000700000FDA1B4081CADC020000000000000000000000)

Je vous conseille également de lire ce papier.

@++

[StringBuilder]

A noter aussi certains comportements pour les machines "vertes" : quand l'activité CPU n'est pas intense, le CPU est downclocké. Par conséquent, si 4 fois plus de coeurs qui tournent 4 fois plus lentement, alors toujours même occupation CPU.

A noter aussi un comportement que j'ai remarqué un jour sur une VM : idem. Faible charge, CPU virtuel presque saturé (moins de 2% sur la machine hôte). Montée en charge. Toujours même occupation sur la VM, mais augmentation sur la machine hôte.

A l'heure de la virtualisation sur des machines vertes... bah faut pas chercher

[mikedavem]

Très bonne remarque StringBuilder et c'est le comportement par défaut de VSphere (Power settings: balanced). Il me semble que Donpi est dans ce cas ... à préciser

Dans ce cas il faudra voir ce qui se passe au niveau ESX ou si les VMTools sont installés sur le serveur voir les compteurs de l'object VM Processor :

•% Processor Time
•Effective VM Speed in MHz
•Host processor speed in MHz
•Limit in MHz
•Reservation in MHz
•Shares


Personnellement je suis asse dubitatif sur un problème CPU engendrés par des compilations / recompilations dans ce cas car Donpi disait que très peu de requêtes tournaient sur ce serveur (si je ne me suis pas trompé)

Mais elle ne retourne rien de pertinent dans mon cas, maximum deux ou 3 requêtes en simultané.

++

[SQLpro]

Avez_vous penser à faire en sorte que l'économiseur d'énergie soit sur OFF ? Tant au niveau Windows qu'au niveau BIOS ???

A +

[Donpi]

Bonjour,

Désolé pour cette réponse tardive ( j'étais en arrêt de travail depuis début juillet )

Merci pour ces réponses :

@ elsuket : La requête que vous avez posté ne fonctionne pas car QT n'est pas déclaré
@ mikedavem : Effectivement, on utilise VSpher, je vais de ce pas contrôler le "Power settings"
@ SqlPro : Au niveau soft c'est effectivement désactivé. Au niveau hardware je n'ai pas access à la machine donc je dois me contenter de ce que dit le technicien.

Encore merci, A+

[SQLpro]

Voici la requête d'elsuket, rectifiée :

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
WITH
	CTE AS
	(
		SELECT		QS.query_plan_hash
				, QS.query_hash
				, QS.sql_handle
				, COUNT(*) AS entries_count
				, SUM(QS.execution_count) AS execution_count
				, MIN(QS.sql_handle) AS sample_sql_handle
				, MIN(QS.plan_handle) AS sample_plan_handle
				, SUM(QS.total_worker_time) AS total_CPU_time
				, SUM(QS.total_logical_reads) AS total_logical_reads
				, SUM(CAST(P.size_in_bytes AS bigint)) / 1024 / 1024 AS total_size_MB
		FROM		sys.dm_exec_query_stats AS QS
		INNER JOIN	sys.dm_exec_cached_plans AS P
					ON QS.plan_handle = P.plan_handle
		GROUP BY	query_plan_hash, query_hash, sql_handle
		HAVING COUNT(*) > 1
	)
SELECT		Q.entries_count
		, Q.execution_count
		, Q.total_logical_reads
		, Q.total_CPU_time
		, Q.sample_plan_handle
		, ROW_NUMBER() OVER(ORDER BY Q.entries_count DESC) AS n
		, Q.query_plan_hash
		, Q.query_hash
		, QP.objectid
		, DB_NAME(QT.dbid) AS database_name
		, Q.total_size_MB
FROM		CTE AS Q
CROSS APPLY	sys.dm_exec_query_plan(Q.sample_plan_handle) AS QP
CROSS APPLY	sys.dm_exec_sql_text(Q.sql_handle) AS QT
WHERE		QT.text NOT LIKE '%sp?_replmonitorrefreshagentdata%' ESCAPE '?'
AND		   QT.text NOT LIKE '%sp?_replmonitorhelppublisherhelper%' ESCAPE '?'
AND		(
			(
				QP.dbid > 4
				AND QP.dbid <> 32767
			)
			OR QP.dbid IS NULL
		)
ORDER BY	Q.entries_count;

A +