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+