FREEPROCACHE - usage CPU 100%

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:

---

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.

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:

---

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 +

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.

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

Code:

---

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 :

Pièce jointe 168396

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

Code:

---

1 2 3

DBCC FREEPROCCACHE; CHECKPOINT DBCC DROPCLEANBUFFERS;

---

Essayer d’interroger le dmv sys.dm_os_wait_stats

Code:

---

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:

---

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

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

Code:

---

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:

---

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:

---

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 +

En effet, le pourcentage est de plus de 5% et le 'max degree of parallelism' est activé (16 cores).

Pour une résolution du problème dans l'immédiat (avant de voir plus en profondeur, audit...) , qu'implique la désactivation (risques) ?

Citation:

---

Envoyé par SQLpro

Code:

---

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

---

Code:

---

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

---

Code:

---

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)

---

---

1 ère :

configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
1538 cost threshold for parallelism 5 0 32767 5 cost threshold for parallelism 1 1
1539 max degree of parallelism 0 0 32767 0 maximum degree of parallelism 1 1


2ème :

cpu_count hyperthread_ratio affinity_type_desc virtual_machine_type_desc
16 16 AUTO NONE

3ème :

Pièce jointe 168445

Serveur dédié :

2.0GHz Intel® Xeon
64GB RAM
2 x 250GB SSD (RAID 1)
2 x 2TB SATA HDD (RAID 1)

Citation:

---

Envoyé par Trady

En effet, le pourcentage est de plus de 5%

---

Passez le à 25

Citation:

---

et le 'max degree of parallelism' est activé (16 cores).

---

passez le à 1 ou 2 dans l'immédiat

Citation:

---

Pour une résolution du problème dans l'immédiat (avant de voir plus en profondeur, audit...) , qu'implique la désactivation (risques) ?

---

De meilleures performances !!!!! Car moins de parallélisme et moins de temps d'attente en rendez-vous de tâche.

A +

Citation:

---

Envoyé par Trady

...
Pièce jointe 168445

---

A la lecture de vos emplacement de fichier, je crains que vous en soyez en version EXPRESS !

Pouvez vous confirmer cela en lançant :

Code:

---

SELECT @@VERSION

---

et

Code:

---

EXEC xp_msver

---

?

Merci

Effectivement votre stratégie de stockage est pourrie... Voici comment je la remanierais au vu de vos info :

--> 1) Remaniement de tempdb :

--> 1.1 - Modifiez l'emplacement des fichiers actuels de tempdb pour les placer sur le SSD

Code:

---

1 2	ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = '???...'); ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = '???...');

---

--> 1.2 - redémarrez le service SQL Server pour prise en compte

--> 1.3 - Supprimmez les anciens fichiers de tempdb (commande système) :

Code:

---

1 2	DEL "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\tempdb.mdf" DEL "C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\templog.ldf"

---

--> 1.4 - Redimensionnez le fichier templog à 1 Go et un pas d'incrément de 50 Mo :

Code:

---

ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = 1 GB, FILEGROWTH = 50 MB);

---

--> 1.5 - Redimensionnez le fichier tempdev à 1 Go et un pas d'incrément de 50 Mo :

Code:

---

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 1 GB, FILEGROWTH = 50 MB);

---

--> 1.6 - Créez 3 autres fichiers de données sur le SSD de 1 Go et un pas d'incrément de 50 Mo :

Code:

---

1 2 3

ALTER DATABASE tempdb ADD FILE (NAME = 'tempdev2', FILENAME = '???...', SIZE = 1 GB, FILEGROWTH = 50 MB); ALTER DATABASE tempdb ADD FILE (NAME = 'tempdev3', FILENAME = '???...', SIZE = 1 GB, FILEGROWTH = 50 MB); ALTER DATABASE tempdb ADD FILE (NAME = 'tempdev4', FILENAME = '???...', SIZE = 1 GB, FILEGROWTH = 50 MB);

---

--> 2) base de production :

--> 2.1 - redimensionnement de votre fichier de journalisation et du pas d'incrément

Code:

---

ALTER DATABASE Database_2 MODIFY FILE (NAME = 'Database_2_log', SIZE = 64 GB, FILEGROWTH = 50 MB);

---

--> 2.2 - ajout de 3 nouveaux fichiers à la base :

Code:

---

1 2 3

ALTER DATABASE Database_2 ADD FILE (NAME = 'Database_2_1', FILENAME = '???...', SIZE = 16 GB, FILEGROWTH = 50 MB) TO FILEGROUP [PRIMARY]; ALTER DATABASE Database_2 ADD FILE (NAME = 'Database_2_2', FILENAME = '???...', SIZE = 16 GB, FILEGROWTH = 50 MB) TO FILEGROUP [PRIMARY]; ALTER DATABASE Database_2 ADD FILE (NAME = 'Database_2_3', FILENAME = '???...', SIZE = 16 GB, FILEGROWTH = 50 MB) TO FILEGROUP [PRIMARY];

---

--> 2.3 - Migration des données dans les nouveaux fichiers :

Code:

---

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

USE Database_2; DECLARE @SQL NVARCHAR(max); SET @SQL = N''; WITH T AS ( SELECT N'ALTER INDEX [' + i.name + N'] ON [' + s.name + N'].[' + o.name + N'] REBUILD;' AS COMMANDE FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.object_id = o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id WHERE index_id > 0 AND o."type" = 'U' UNION ALL SELECT N'ALTER TABLE [' + s.name + N'].[' + o.name + N'] REBUILD;' FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.object_id = o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id WHERE index_id = 0 AND o."type" = 'U' ) SELECT @SQL = @SQL + COMMANDE FROM T; EXEC (@SQL);

---

--> 2.4 - Redimensionement du fichier actuel de données de la base de prod :

Code:

---

1 2	USE Database_2; DBCC SHRINKFILE ('Database_2', 16384);

---

--> 2.5 - Redimensionement du pas d'incrément de ce fichier :

Code:

---

ALTER DATABASE Database_2 MODIFY FILE (NAME = 'Database_2', FILEGROWTH = 50 MB);

---

Après, vous pourrez faire un MAXDOP à 4 :

Code:

---

1 2 3 4 5 6 7 8

EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'max degree of parallelism', 4; GO RECONFIGURE; GO

---

À faire aux heures creuses !
Tout cela devrait améliorer notablement votre instance !!!!

Mais dernière question : vu la grosseur de votre fichier de transaction : FAITES VOUS RÉGULIÈREMENT des sauvegardes du journal de transaction ?



A +

Vous ne m'avez toujours pas répondu à :

Citation:

---

Envoyé par SQLpro

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

---

Non il n'y a pas ces messages dans le journal d'évènement.

La version de sql server n'est pas EXPRESS :
Microsoft SQL Server 2012 - 11.0.2218.0 (X64)
Jun 12 2012 13:05:25
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


Pièce jointe 168490

Nous faisons un BACKUP LOG toutes les 15mn (BACKUP FULL journalier).

Merci beaucoup pour vos infos sur la stratégie de stockage.

A+

Citation:

---

Envoyé par SQLpro

--> 1) Remaniement de tempdb :

--> 1.1 - Modifiez l'emplacement des fichiers actuels de tempdb pour les placer sur le SSD

---

Le disque C: est le disque SSD.

Je viens de voir à ce sujet cette observation concernant les SSD :
un mauvais choix concernant celles sur lequelles s'effectuent un certains nombres d'ecriture limitant ainsi la durée de vie de ces disques

Citation:

---

Envoyé par Trady

Le disque C: est le disque SSD.

Je viens de voir à ce sujet cette observation concernant les SSD :
un mauvais choix concernant celles sur lequelles s'effectuent un certains nombres d'ecriture limitant ainsi la durée de vie de ces disques

---

Alors il faudrait plutôt mettre les journaux sur le disque SSD, tant pour la base tempdb que pour la base de production.

Il faut voir aussi l'indexation.

A +