Discussion :

[StringBuilder]

Bonjour,

J'ai un client qui me sollicite car il a depuis une semaine maintenant des blocages d'une application qu'il ne s'explique pas.

L'application en question est une application Web, donc pise à part quelques traitements particuliers, aucun verrou ne reste actif plus de quelques dixièmes de secondes (genre on n'a pas besoin que Ginette sorte de la fiche article pour pouvoir passer une commande dessus).

Il n'y a eu aucune modification ni de code, ni de paramétrage, ni de process depuis des mois.

C'est la première fois que ce genre de locks semble se produire.

Connaissant l'application en question, je sais qu'il y a, parfois, des traitements qui font des hold lock sur des requêtes de type SELECT, le temps d'effectuer des opérations en rapport avec le jeu de données renvoyé. Ca a déjà causé des problèmes chez un autre client.
=> Je me suis donc naturellement orienté vers ce point. Sans succès. Visiblement il y a de tels traitements chez ce client, mais ces derniers travaillent sur des tables vides, donc leur exécution est instantanée. On voit dans les logs qu'à chaque fois ça se termine immédiatement.

En analysant les logs applicatifs, pas moyen de déterminer un traitement en particulier : deux plantages coups sur coups ont fait ressortir l'utilisation d'un module en particulier sur une fiche client en particulier. Mais depuis, de nouveaux blocages se sont produits et aucune trace ni du client, ni du module suspecté.

Côté SQL Server, on a lancé une requête pour déterminer la liste des traitements bloqués.

Et là je n'arrive pas à m'expliquer le problème.

Vendredi, nous avons analysé une première liste, où la racine d'une chaîne de blocage de plusieurs requête partait d'un simple SELECT. Pas de présente du moindre HINT de verrou dans la requête. Exécutée manuellement après coup, la requête dure quelques centièmes de seconde. Il y a quelques amélioration d'index suggérées par SSMS, mais rien de significatif.
=> Premièrement, comment un SELECT, même pas très rapide, peut-il être à l'origine de blocages d'autres SELECT ?

Ce matin, nouveau blocage, nouveaux fichiers, nouvelle liste de requête bloquées.

Sur 75 lignes il y a 73 SELECT (sans jamais de hint de lock), un "FETCH API_CURSOR" sans détails, et un "INSERT".

Le INSERT n'est à l'origine d'aucun LOCK et travaille sur une table hors application. Aucune autre requête dans la liste de tente d'accéder à cette table.
Le FETCH API_CURSOR n'est à l'origine aucun lock non plus. Est-ce possible d'avoir une idée de la requête correspondant au curseur ?

Sur les 73 SELECT, 67 ne sont bloqués par rien... Comment un SELECT peut-il se retrouvé locké... par rien ?
Le FETCH API_CURSOR est locké par un SELECT.

Je m'attarde sur le premier cas de lock.

Voici la première requête, numéro 121 du fichier :

Code sql :

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

1
2
 
(@P1 bigint)SELECT ID,F7000,F7007,F7046,F7058,F7060 FROM CRM_AU WHERE ID_KM_176=@P1 AND LosKZ=0 AND (((((F7046=1) AND (F7060=0)))) OR (((F7000 IN(11,12,23,24)))) OR (((F7007 IN(8,2,3,4,15,9,5))))) ORDER BY ID_KM_176,ID

Elle travaille exclusivement sur la table CRM_AU.

Elle est bloquée par la requête numéro 387 que voici :

Code sql :

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

1
2
 
(@P1 bigint)SELECT Kontakt,Status,ID,F7159 FROM CRM_MA WHERE ID_KM=@P1 AND LosKZ=0 AND (((((Kontakt=217) AND (Status=0)))) OR (((Kontakt=217))) OR (((Kontakt=217) AND (F7159=1)))) ORDER BY ID_KM,ID

Elle travaille exclusivement sur la table CRM_AU.
Cette requête n'est pas bloquée par une autre.
Il n'y a aucune clé étrangère entre les deux tables, ni directement, ni indirectement.

Première question : même si c'était des modifications de données, comment une requête dans une table peut-être bloquer une requête sur une autre table, du moment que ces deux tables sont indépendantes ?

Dans le fichier, toutes les requête sont marqués "suspended", sauf la 387 qui est "running".
Si je la lance, elle dure quelques centièmes de secondes. Dans le fichier, c'est l'avant dernière à avoir été lancée... Après les autres requêtes qu'elle soit-disant bloque...

Seconde question : comment une requête peut-être être bloquée par une autre requête qui n'est pas encore démarrée ?

Dans le log, j'ai quatre types de wait_type :

LCK_M_S : 7 requêtes
CXPACKET : 66 requêtes
SOS_SCHEDULER_YIELD : 1 requête, la 387 qui est la seule à tourner
ASYNC_NETWORK_IO : 1 requête, c'est l'INSERT, qui vient d'un traitement lancé depuis un autre serveur

Pour CXPACKET, je trouve ça :

1. Producer Consumer Issue under Parallelism, where Consumer threads report CXPACKET wait type while waiting for data from Producer threads. This is something which is non-actionable because this happen because of parallelism.
2. Excessive Parallelism for small Queries which can be control by changing MAXDOP and /or Cost Threshold for Parallelism values. This is something which is actionable that can be control by changing these values.
3. Uneven task distribution between parallel threads. This situation may occur when some of the parallel threads will complete assigned task prior to other tasks. These threads will report CXPACKET wait type until other threads will complete assigned task as well. This is something which generally occurs because of outdated statistics. We can reduce this situation by updating statistics. This is again something which is actionable.

Je n'ai pas vérifié le MAXDOP (pas d'accès au serveur) mais à mon avis il est à sa valeur par défaut. Je doute qu'il y ait plus de 4 ou 6 cœurs sur le serveur : on est sur une "petite" application et une "petite base" (entre 10 et 20 Go).
Pour les statistiques, il y a un plan de maintenance qui les recalcule tous les week-end. Donc lundi 11h je doute qu'elles soient déjà pourries (ou alors le plan de maintenance est totalement bidon).

Pour LCK_M_S, ça semble être le lock classique qui se produit lorsqu'on tente d'accéder à une ligne verrouillée par un verrou quelconque.

Pour SOS_SCHEDULER_YIELD, j'ai l'impression que ça indique que le serveur BDD est soit surchargé, soit qu'il tente d'utiliser des CPU qu'il n'a pas le droit d'utiliser (sur une édition Express par exemple).
Là on doit être sur une standard, et comme je disais, y'a pas 32 CPU... tout au plus 4 ou 6. On doit pas dépasser la limite.

Qu'est-ce qui à votre avis pourrait poser ce genre de comportement ?

Autre question : j'ai remarqué que la croissante automatique de la base était configurée... à 1 Mo ! J'ai donc immédiatement conseillé de corriger ça au plus vite. Cependant, si c'était ça la source du problème on serait plutôt bloqué par des wait relatifs aux IO, non ? Quel genre de wait type se déclenche quand des lignes sont en cours de réorganisation dans base par manque de place dans une page ?

[SQLpro]

La problématique des ressources peut être la cause de blocages sans visibilité de verrous logique. Il faut donc veiller :
1) à n'avoir aucune croissance pendant la production sur les fichiers de données et surtout du JT => les dimensionner très large (pour 3 à 5 ans de données)
2) même topo pour la tempdb surtout pour les fichiers de données.

Si VM, erreur fréquente :
1) mémoire en balooning
2) coeurs flottant
Faire de la réservation sur l'intégralité des ressources pour ces deux paramètres.
Voir le guide de config VMWare / SQL Server (82 pages...)
https://www.vmware.com/content/dam/d...PubCID=2470763

Au niveau instance :
régler le parallélisme =
1) max dop à 1/4 - 1 si plus de 16 coeurs, sinon 1/2 -1
2) cost treshold for parallelims => entre 12 et 100 (12, 25, 50 ou 100 pour petite, moyenne, grosse, très grosse base)
3) optimize for adhoc workload à 1

S'assurer que les derniers SP ou CU ont été passés

A +

[StringBuilder]

Merci SQLpro pour ta réponse complète.

Je vais faire remonter ces points

[StringBuilder]

Bonjour,

Nous avons commencé à appliquer un certain nombre de recommandations et vérifications.

Pour le moment sans succès : l'application à figé 4 fois en une heure en début d'après-midi.

Nous commençons à avoir une piste (cas qui plante systématiquement) sans pour le moment que nous ne comprenions quoi que ce soit : on tente de modifier une donnée, et dans les requête bloquées y'a que des SELECT, aucun UPDATE... Très bizarre...
Je dois continuer à creuser pour identifier tout le cheminement applicatif lors de cette action.

En revanche, j'ai une question : le DBA a modifié la taille des bases et le taux d'accroissement des fichiers.
Cependant, si la base est très fragmentée (185 Go actuellement avec un accroissement à 1 Mo, je vous laisse imaginer le désastre...) comment retrouver une base avec des fichiers sans fragments ?
Ils sont sur une baie de disques SSD. Est-ce que la fragmentation peut avoir un impact réellement perceptible ?

[Waldar]

Nous commençons à avoir une piste (cas qui plante systématiquement) sans pour le moment que nous ne comprenions quoi que ce soit : on tente de modifier une donnée, et dans les requête bloquées y'a que des SELECT, aucun UPDATE... Très bizarre...

Le SELECT pose un verrou avec le mode de transaction par défaut de SQL-Server.
Ouvre une session, fais begin transaction select * from MaTable, ouvre une autre session, même chose, tant que la transaction de la session 1 n'est pas finie la session 2 ne renvoie rien.
La solution "sale mais qui marche" select * from MaTable (nolock);

[SQLpro]

...
La solution "sale mais qui marche" select * from MaTable (nolock);

ou bien passer en verrouillage optimiste... SQL Server est le seul SGBDR à faire les deux !

A +

[Zabriskir]

Le SELECT pose un verrou avec le mode de transaction par défaut de SQL-Server.
Ouvre une session, fais begin transaction select * from MaTable, ouvre une autre session, même chose, tant que la transaction de la session 1 n'est pas finie la session 2 ne renvoie rien.

Hello,
Vous êtes sûr de ce que vous avancez 🤔 ?
Le select pose un shared lock qui ne bloque pas un autre select (en mode d'isolation par défaut).
@+