Discussion :

[Trady]

Bonjour,

Depuis quelques jours, nous avons des montées anormales du CPU à 100% lié à SQL SERVER (version 2012).
La base est opérationnelle depuis plus d'un an, et ce problème est apparu 3 fois ces 4-5 derniers jours.

L'usage CPU retrouve un niveau normal (- de 20%) avec cette commande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DBCC FREEPROCCACHE

Il n'y a pas eu de gros changements sur la base dernièrement et les SP fonctionnaient correctement depuis + d'un an.
Il y a un plan de maintenance en place http://blog.developpez.com/sqlpro/p8...des_index_et_s

Je n'ai aucune idée de l'origine de ce problème !?

Merci d'avance.

[SQLpro]

Avez vous limité la RAM utilisée par SQL Server ?

En effet IL EST IMPÉRATIF de limiter la RAM utilisée par SQL Server à :
RAM physique du serveur MOINS 2 à 8 Go (selon la taille de la RAM du serveur).

Exemple : RAM physique du serveur 64 Go => RAM SQL Server : 60 Go =>

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
EXEC sp_configure 'max server memory', 61440;
GO
RECONFIGURE
GO

Je parierait qu'après cela tout devrait revenir à la normale !

A +

[Trady]

Bonjour,

Merci pour la réponse.

Mais il s'agissait d'une erreur de développement dans notre appli C#.
Donc Sql Server n'y était pour rien, c'est pourquoi j'avais mis la question en "résolu".

Toutefois pour rebondir sur votre réponse, il faut donc appliquer cette limitation bien qu'on soit très "large" en RAM ?

Par exemple, lorsque notre consommation CPU était à 100% pour sql server en raison de l'erreur de codage c#, notre utilisation de RAM n'était que de 8Go sur les 64.

[Trady]

Rectification le problème est réapparu.
De plus, j'ai bien appliqué hier la limitation 'max server memory'.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
DBCC FREEPROCCACHE;
CHECKPOINT
DBCC DROPCLEANBUFFERS;

Cette commande permet le retour a un niveau normal de l'utilisation du CPU par SQL SERVER, voir image :

[Boubou2020]

En principe un administrateur de bases de données ne doit jamais vider les caches SQL Server

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
DBCC FREEPROCCACHE;
CHECKPOINT
DBCC DROPCLEANBUFFERS;

Essayer d’interroger le dmv sys.dm_os_wait_stats

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC

Si la valeur de la colonne wait_percent de CXPACKET (maximum dégrée of parallelism) vaut plus de 5%, des goulots d’étranglement au niveau du parallélisme sont présents.
il faut donc réagir a le désactiver

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max degree of parallelism', 1
GO
RECONFIGURE WITH OVERRIDE
GO

un bon article sur le sujet est présent sur ce lien http://blog.developpez.com/mikedavem...et_performance

[SQLpro]

Effectivement le parallélisme est aussi très important à régler.
Donnez le résultat de :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM sys.configurations WHERE name LIKE '%para%'

Combien de socket avez-vous ?
Combien de cœur par CPU ?
Avez_vous activé l'hyperthreading ?

Renvoyez le contenu de :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT cpu_count, hyperthread_ratio, affinity_type_desc, virtual_machine_type_desc 
FROM sys.dm_os_sys_info

Pour nous aider....

Mais votre stratégie de stockage l'est encore plus...

Quelle est votre configuration au niveau du stockage ?
Disques locaux ou distant (SAN) ?
Si SAN, dédié ou partagé ?
Comment sont organisés les disques physiques (niveau de RAID, disques vus (LUN), capacité...) ?
Donnez aussi le résultat de :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT DB_NAME(mf.database_id), name, type_desc, physical_name, size, max_size, growth, is_percent_growth
FROM   sys.master_files AS mf
       CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id)

Extrait de mon livre "SQL Server 21014" (http://www.amazon.fr/dp/2212135920/) - chapitre 10 : Création des bases et stockage des données
"
Cependant, la gestion des espaces de stockage réserve quelques pièges qu’il est bon de connaître avant
de se lancer dans la conception physique de la base. Ce n’est pas pour rien que certains auteurs baptisent
de silent killer les problèmes d’IO…
"

Avez-vous par hasard, dans le journal d'événement de SQL Server des erreurs régulières du genre :
SQL Server has encountered %d occurrence(s) of I/O requests taking longer than ??? seconds to complete on file [???] in database id ???...
SQL Server a rencontré ??? occurrence(s) de requêtes d'E/S mettant plus de ??? secondes à s'effectuer dans le fichier [??? de la base de données ???....
???

A +