Discussion :

[vinse51]

Bonjour à tous,

Je sollicite votre aide car je me trouve confronté depuis quelques jours à un gros problème au niveau de ma base de données.

J'ai une bd sql server 2005 avec environs 200 tables allant de quelques milliers d'enregistrements à quelques centaines de milliers d'enregistrements, et qui est très sollicité aux heures de pointes (plusieurs centaines de requêtes à la seconde). La bd est hébergée sur un serveur dédié sous windows server 2003 qui lui est quasiment exclusivement réservé.
Le fichier de données fait actuellement 600 Mo.

Jusqu'à présent je n'avais eu aucun problème mais depuis quelques jours un problème très bizarre arrive et de plus en plus souvent :

D'un coup, plus aucune requête n'est traitée et le processeur tombe à 1 ou 2% alors qu'en temps normal il est au moins à 50 - 60% d'utilisation. Cela peut durer quelques minutes et d'un coup ça repart, mais aujourd'hui ça empire je crois car j'ai du relancer carrément sql server pour résoudre temporairement le problème. Durant cette période plus aucun site ne répond et impossible de traiter une requête (timeout).

J'ai d'abord vérifié que les paquets tcp arrivaient toujours sur le serveur, ce qui est bien le cas.

Ensuite j'ai suivi l'activité des processus et des disques avec le perfmon et là je me suis vite aperçu du problème : sql server fait des accès disques tellement conséquents qu'il bloque tout le serveur. En gros les accès disques sont plus de 300 fois supérieurs à la normale ! Et la courbe des accès disque globaux est exactement la même que la courbe des accès disque du processus sql server.
Après vérification des fichiers en cause grâce au logiciel filemon j'ai vérifié sans surprise que c'est bien le fichier de la base de donnée .mdf qui est la cause de ces accès.

J'ai d'abord essayé de reconstruire tous les index de toutes les tables et j'ai aussi défragmenté le disque et augmenté la mémoire virtuelle mais le problème resurgit encore.


Je ne sais pas si c'est un problème connu ou si quelqu'un a déjà été confronté à ce cas mais toute aide serait la bienvenue !
N'hésitez pas à me conseiller des logiciels ou des logs à vérifier pour identifier le problème...

Merci d'avance.

[elsuket]

Bonsoir,

Si SQL Server fait énormément d'accès disques, c'est que peu de pages de données sont chargées dans le cache.
Ensuite, si d'un coup votre serveur bloque, puis repart aussi sec, c'est parce que vous avez deux requêtes très longues qui se bloquent l'une avec l'autre : pour résoudre ce problème, SQL Server choisit le processus qui a changé le moins de données et le tue : c'est le deadlock.

Vous pouvez dans un premier temps lire ce livre blanc fort instructif. (Identifying long blocks)

Dans le même temps, vous trouverez les procédures en question en utilisant l'événement "deadlock graph" de SQL Server Profiler. En général la détection du deadlock ne prend pas plus de 5s.
Vous trouvez alors les procédures stockées qui sont à l'origine du deadlock, et il vous faudra faire en sorte de :
- n'utiliser, et cela est vrai globalement, que du code ensembliste,
- avoir recours le moins possible aux tables temporaires, aux curseurs et aux variables table
- accéder aux tables dans le même ordre,
- simplifier votre algorithme

Vous allez devoir créer les indexes adéquats (cherchez les prédicats qui prennent le plus de temps dans les plans des requêtes (CTRL+L)) à l'origine du deadlock.

Vous pouvez aussi rechercher de façon plus barbare les indexes manquants :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT ROUND((MIGS.user_seeks + MIGS.user_scans) * MIGS.avg_total_user_cost * avg_user_impact, 0) Avantage,
		MIGS.unique_compiles Comp,
		MIGS.last_user_seek,
		MIGS.last_user_scan,
		REPLACE(REPLACE(MID.Statement, '[', ''), ']', '') 'Database.Schema.Table',
		REPLACE(REPLACE(MID.equality_columns, '[', ''), ']', '') Egalite,
		REPLACE(REPLACE(MID.inequality_columns, '[', ''), ']', '') Inegalite,
		REPLACE(REPLACE(MID.included_columns, '[', ''), ']', '') Incluse
FROM sys.dm_db_missing_index_group_stats MIGS
JOIN sys.dm_db_missing_index_groups MIG ON MIGS.GROUP_HANDLE = MIG.INDEX_GROUP_HANDLE
JOIN sys.dm_db_missing_index_details MID ON MIG.INDEX_HANDLE = MID.INDEX_HANDLE
WHERE CONVERT(CHAR(10), MIGS.LAST_USER_SEEK, 103) = CONVERT(CHAR(10), GETDATE(), 103)
AND DB_NAME(MID.database_id) NOT IN ('master', 'msdb', 'model', 'ReportServer', 'ReportServerTempDB', 'Distribution', 'TempDB')

Vous trouverez ainsi les indexes qui manquent le plus à votre base de données. Créez en priorité ceux dont vous tirerez le plus gros Avantage
Attention, à chaque fois que vous créerez un index sur une table, les statistiques d'indexes manquants seront purgées pour la table en question : si vous devez créer plusieurs indexes sur la même table, veillez à conserver la première exécution de cette requête.
Créez les indexes dans l'ordre que vous propose cette requête.
Toutefois veillez à ordonner les colonnes répertoriées dans la colonne "Egalité" dans l'ordre décroissant de leur sélectivité.

Veillez également à mettre à jour régulièrement les statistiques de colonnes de vos bases de données :

Code :

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

1
2
3
4
EXEC sp_createstats 'indexonly', 'fullscan';
GO
EXEC sp_updatestats;
GO

Enfin défragmentez régulièrement et pendant les heures de faible traffic vos indexes les plus utiles et les plus fragmentés. Servez-vous, pour déterminer ceux qui doivent l'être, des DMVs sys.dm_db_index_usage_stats et sys.dm_db_index_physical_stats.

Après tout cela, ça devrait aller globalement beaucoup mieux

[vinse51]

Bonsoir et merci pour votre réponse rapide et détaillée !

Donc vous pensez que je suis victime d'un deadlock ? Mais si c'est vraiment un deadlock le processeur ne devrait pas se bloquer à 100% plutôt que le contraire (quasiment inactivité du processeur durant toute la durée du problème) ? Et des fois il peut se passer 10 minutes avant que le serveur reprenne.

Ou alors un deadlock sql server a comme conséquence de faire exploser les accès disque en lecture ?

J'ai une remarques qui peut peut-être vous aider : après augmentation de la mémoire virtuelle le problème n'est survenu qu'une fois dans la journée alors qu'avant il commençait à devenir plutôt fréquent. Je pense que dans tous les cas je manque de mémoire car j'ai 1 Go de RAM et le processus sql server en monopolise 700 Mo en moyenne.

Et je n'utilise aucune procédure stockée.


Après exécution de votre requête, j'obtiens 80 lignes avec certains chiffres dans avantage très élevé. Mais j'avoue ne pas être un expert du sql et je ne saisi pas très bien comment interpréter les résultats.
Il faut créer un index avec les colonnes qui se trouvent dans "incluse" c'est bien ça ?

Et que voulez-vous dire par ordonner la colonne "égalité" dans l'ordre décroissant de leur sélectivité ?

Merci encore pour vos réponses.

[Ptit_Dje]

En effet, un manque de mémoire pour SQL Server peut entrainer une congestion du systeme. Au lieu de charger toutes les données necessaires en mémoire, il va écrire sur le disque, puis aller relire et ainsi de suite, ce qui peut devenir très lent pour une grosse query.

D'un autre coté, il ne faut pas oublier de laisser de la mémoire à l'OS, car si votre OS ne fonctionne pas correctement en background, votre serveur DB risque d'en pâtir aussi.

Si vous avez pu constater une réduction de ses problème en augmentant la mémoire virtuelle, pensez à ajouter un peu de RAM dans votre serveur, car 1GB n'est vraiment pas des masses lorsque l'on recommande d'en laisser 512MB à l'OS pour lui pouvoir fonctionner comme il faut en background.

[vinse51]

Bonjour,

Je viens d'explorer la piste du deadlock mais sans succès. J'ai lancé le profiler avec les 3 évènements disponibles pour les deadlocks et le problème vient d'arriver. J'ai bien patienté jusqu'à ce que le serveur recommence à traiter les requêtes (ce qui a pris environ 7-8 minutes cette fois) mais à la reprise aucune trace d'un deadlock dans le profiler

Je ne pense pas que le problème vienne du fait que la bd soit mal optimisée car au pire les requêtes mettent plus de temps à s'exécuter ou le serveur peut se mettre à ramer... Mais là le processus sql se met à faire des milliers d'accès disques d'un coup et la file de travail du disque explose et plus aucune requête n'est traitée. Ce qui est bizarre c'est que cela dure des fois 10 minutes et même plus alors qu'il m'est déjà arrivé de tester des requêtes mal construites qui faisaient ramer le serveur mais dans ce cas là j'avais toujours une erreur de timout au bout de 30 secondes je crois. Or là je n'ai aucun timeout, et aucune erreur dans le ERROR.LOG.

Et après étude détaillée des accès disques pendant le problème, j'ai remarqué que ce ne sont que des accès en lecture et quasiment aucune écriture.

Je ne sais pas trop sur quelle piste me pencher maintenant...

Merci pour vos réponses en tout cas

[elsuket]

Bonjour,

La mémoire, plus il y en a sur un serveur, mieux c'est, et à nous les niveaux.
Toujours avec le profiler, regardez l'événement "Cache hit ratio". Si vous êtes en dessous de 95% vos performances ne sont pas bonnes.

Vous avez exploité la piste du deadlock, et ce visiblement ce n'est pas cela.
Donc ce doit être une requête qui monopolise énormément de ressources.

Voyons ce que vous donne 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
20
21
22
23
24
25
26
SELECT ES.session_id SPID,
		ER.blocking_session_id BlkBy,
		COUNT(*) Threads,
		DB_NAME(ER.database_id) BD,
		UPPER(ER.status) Statut,
		ER.cpu_time / 1000 CPU,
		ER.reads Reads,
		ER.writes Writes,
		UPPER(ER.command) Commande,
		ESQLT.text SQLCommand
FROM sys.dm_exec_sessions ES (nolock)
JOIN sys.dm_exec_connections (nolock) EC ON ES.session_id = EC.session_id
JOIN sys.dm_exec_requests ER (nolock) ON ES.session_id = ER.session_id
JOIN sys.sysprocesses (nolock) SP ON SP.spid = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) ESQLT
WHERE ES.program_name NOT LIKE 'DatabaseMail90%'
AND ES.session_id <> @@SPID
GROUP BY ES.session_id,
		ER.blocking_session_id,
		ER.database_id,
		ER.status,
		ER.cpu_time / 1000,
		ER.reads,
		ER.writes,
		ER.command,
		ESQLT.text

Elle va vous retourner la liste des requêtes en cours dans SQL Server.
Si vous avez un grand nombre (style 5 ou plus) dans la colonne Threads, c'est que SQL Server a décidé de paralléliser le traitement. Cela ne signifie pas forcément que votre requête est lente.
En revanche avec cela, vous allez pouvoir savoir quelle commande SQL est à l'origine de ce blocage (colonne SQLCommand).

Par contre je vous souhaite du courage pour trouver la requête, puisque vous dites ne pas utiliser de procédure stockée. Pourquoi avoir fait ce choix ?

certains chiffres dans avantage très élevé

Bien, mais de quel ordre sont-ils ? 1 000 ? 10 000 ? 1 000 000 ?
Regardez le texte de ma requête : le calcul de l'avantage se fait à partir de la DMV sys.dm_db_missing_index_group_stats : j'additionne les nombre de recherches et d'analyses pour lesquelles l'index recommandé pourrait avoir été utilisé. Ensuite je le multiplie par ce que cela coûte à la machine, et encore par le coût que je pourrais gagner si cet index était présent.
Donc si vous avez de très gros chiffres dans avantage, c'est qu'ils manquent sérieusement.

Il faut créer un index avec les colonnes qui se trouvent dans "incluse" c'est bien ça ? Et que voulez-vous dire par ordonner la colonne "égalité" dans l'ordre décroissant de leur sélectivité ?

Pas tout à fait.

Sélectivité = plus votre colonne à des valeurs différentes, plus elle est sélective.
En comparaison, si une table contient une colonne de type BIT, donc que des 0 et des 1, elle est peu sélective. En revanche si vos tables contiennent des colonnes auto-incrémentées, alors elles sont très sélectives puisque toutes les valeurs de cette colonne sont différentes.
Pour créer vos index :

Code :

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

1
2
3
4
CREATE INDEX IX_maTable_mesColonnes
ON maTable (listeDesColonnesEgalite+listeDesColonnesInegalite)
INCLUDE (listeDesColonnesIncluses)
WITH (FILLFACTOR = xx)

Doc CREATE INDEX ici

A+

[vinse51]

Bonjour elsuket,

Je pense aussi à la piste d'une requête qui consomme énormément de ressources mais dans ce cas là n'y-a-t-il pas un timeout relativement court (30s ou 60s) car avec certaines requêtes mal construites qui faisaient un cross join de 2 grosses tables j'obtenais rapidement un timeout.

Mais peut être que si la requête a commencée à retourner des lignes alors le timeout n'entre plus en jeu ?

J'ai un souci avec votre requête :

Code :

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

1
2
Msg 102, Niveau 15, État 1, Ligne 15
Syntaxe incorrecte vers '.'.

et la ligne 15 est :

Code :

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

CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) ESQLT

et apparemment il n'aime pas les alias après le (nolock).

Par contre il faudrait que j'exécute cette requête durant le problème c'est bien ça ? Le souci qui va se poser c'est que je crois que je ne peux exécuter aucune requête pendant la durée du problème car je reçois des erreurs de timeout. Je ne peux même pas mettre à jour la moniteur d'activité disponible dans SQL Server Management Studio Express, il me renvoi également un timeout avec impossibilité de se connecter au sgbd !

Au niveau des avantages, j'ai un chiffre à plus de 5 000 000 et plusieurs autres autours des 1 000 000 !
Je commence à comprendre un peu le fonctionnement de tout ça et toutes les différentes techniques d'optimisations possibles et je me rends compte que ma base en a vraiment besoin.

Mais vous pensez que mon problème vient de l'optimisation ? Car une base mal optimisée tourne mal et beaucoup plus lentement mais est-ce que cela peut vraiment amener à un blocage total pendant 10 minutes, avec un comportement bizarre : explosion des lectures disque et inactivité du processeur ?

Merci d'avance.

[elsuket]

Bonsoir,

J'ai un souci avec votre requête :
Code :

Msg 102, Niveau 15, État 1, Ligne 15
Syntaxe incorrecte vers '.'.

Bizarre, je viens de reprendre le code que j'ai mis dans le post, ça fonctionne parfaitement sur mon PC
J'ai même mis l'alias avant et après le (nolock) et ça fonctionne ...

Par contre il faudrait que j'exécute cette requête durant le problème c'est bien ça ?

C'est exact. Si vous ne parvenez pas à vous connecter à la BD, alors laissez SQL Server Management Studio ouvert, avec la requête prête, et dès l'occurence du problème, F5 !

Au niveau des avantages, j'ai un chiffre à plus de 5 000 000 et plusieurs autres autours des 1 000 000 !

Ouch Les colonnes MIGS.last_user_seek et MIGS.last_user_scan sont-elles à la date d'aujourd'hui ? (ou d'il y a quelques secondes ?) Si c'est le cas, allez-y, mais veillez à créer vos indexes à un moment de faible affluence, surtout si l'instance SQL Server de votre serveur n'est pas l'édition Enterprise (SELECT @@VERSION)

Mais vous pensez que mon problème vient de l'optimisation ?

Peut-être pas seulement. Je tente de vous donner les outils pour trouver d'où vient votre problème, mais l'administration de bases de données n'a pas de méthode absolue pour venir à bout de tous les problèmes. Ce que l'on a fait en optimisation sur un projet ne marchera peut-être pas sur l'autre ...

cela peut vraiment amener à un blocage total pendant 10 minutes

Cela m'est déjà arrivé

un comportement bizarre : explosion des lectures disque et inactivité du processeur ?

Si SQL Server a besoin de lire des pages sur votre disque dur, il n'a que très peu besoin du processeur.

Mais avant de faire tout ce que je vous ai donné, avez-vous :

- lu le livre blanc que je vous ai proposé ?
- vérifié qu'à ce moment là votre espace disque n'est pas quasi-nul ?
- Comment est configuré le grossissement de votre log de transactions ? Quelle est la méthode de récupération de votre base de données ?
- Avez-vous des transactions demeurées ouvertes ? (DBCC OPENTRAN)

Pour voir rapidement l'état de vos bases de données :

Code :

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

1
2
3
4
5
6
SELECT name,
		user_access_desc,
		state_desc,
		recovery_model_desc,
		log_reuse_wait_desc
FROM sys.databases

Pour voir la taille de vos logs de transaction:

Code :

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

DBCC SQLPERF(logspace)

A+

[vinse51]

Bonjour,

J'ai réussi à exécuter votre requête durant le problème. Elle m'a renvoyée une dizaine de requêtes en cours d'exécution dont certaines suspendues, mais après exécutions de ces requêtes une à une aucune ne semble poser de problèmes particuliers.

Après avoir lu le document sur les problèmes de performances que vous m'avez conseillé, j'ai rapidement conclu que j'avais de gros soucis au niveau de la mémoire : memory pressure comme ils disent dans le document.

Comme le problème devenait vraiment critique pour les utilisateurs j'ai dû prendre rapidement une décision et j'ai donc décidé d'opter pour un nouveau serveur, aux capacités environ 4 fois supérieures au serveur initial (4 Go de RAM et 2 processeurs notamment).

Depuis la migration le souci n'est pas réapparu et j'ai laissé passer le week end car c'est le dimanche ou je fais les pics de charges et tout s'est bien passé.


Au vu de tout ça je pense que mon problème n'a pas de cause précise, comme une mauvaise requête ou un deadlock, mais que c'est plus un problème général dû à un manque de mémoire qui amène parfois à une congestion du serveur.
Enfin je n'en suis pas sûr du tout mais c'est la seule explication que je trouve sachant que sur le nouveau serveur je n'ai aucun problème...

Je vais maintenant pouvoir me pencher tranquillement sur l'optimisation de la base qui en a grandement besoin dans tous les cas.

Merci encore pour votre aide et vos conseils.

[Philippe Robert]

De combien de mémoire disposais-tu sur l'ancien serveur ?

[elsuket]

Bonsoir,

De combien de mémoire disposais-tu sur l'ancien serveur ?

Oui, question très intéressante, qu'on sache combien il faut au minimum.

Je ne sais plus si c'est dans ce livre blanc que l'auteur parle de passer par toutes les étapes de ce document avant de penser à acheter de la RAM / changer de serveur.
En tout cas pour vous, il a fallu le faire

Merci encore pour votre aide et vos conseils.

Avec plaisir, et n'hésitez pas à revenir pour votre optimisation

[SQLpro]

un comportement bizarre : explosion des lectures disque et inactivité du processeur ?

Ce symptôme, combiné à un comportement erratique, me conduit à vous dire que l'une des causes les plus probable de cet état de fait est d'avoir laisser des tailles de fichiers minimale avec croissance automatique à la base de données.

Lisez l'article que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro?ti..._fichiers_et_t

Code :

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

...c'est plus un problème général dû à un manque de mémoire qui amène parfois à une congestion du serveur.

Donc optimisation à revoir de fond en comble !
Sur l'optimisation en général, lisez les papiers que j'ai écrit :

A) philosophie :
http://sqlpro.developpez.com/optimisation/

B) indexation :
http://sqlpro.developpez.com/optimisation/indexation/

C) optimisation des requêtes par récriture
http://sqlpro.developpez.com/optimisation/mediane/

D) quoi indexer :
http://sqlpro.developpez.com/cours/quoi-indexer/
( a l'état d'ébauche )

A +

[SQLpro]

un comportement bizarre : explosion des lectures disque et inactivité du processeur ?

Ce symptôme, combiné à un comportement erratique, me conduit à vous dire que l'une des causes les plus probable de cet état de fait est d'avoir laisser des tailles de fichiers minimale avec croissance automatique à la base de données.

Lisez l'article que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro?ti..._fichiers_et_t

...c'est plus un problème général dû à un manque de mémoire qui amène parfois à une congestion du serveur.

Donc optimisation à revoir de fond en comble !
Sur l'optimisation en général, lisez les papiers que j'ai écrit :

A) philosophie :
http://sqlpro.developpez.com/optimisation/

B) indexation :
http://sqlpro.developpez.com/optimisation/indexation/

C) optimisation des requêtes par récriture
http://sqlpro.developpez.com/optimisation/mediane/

D) quoi indexer :
http://sqlpro.developpez.com/cours/quoi-indexer/
( a l'état d'ébauche )

A +