Discussion :

[FMJ]

Bonjour aux valeureux qui ont vaillamment résisté à l'appel des plages !

J'avais précédemment ouvert un fil sur la façon de pouvoir tracer des erreurs à partir du profiler : http://www.developpez.net/forums/d95...icate-key-row/

En fait, le problème applicatif à l'origine de cette demande s'est reproduit et après un examen qui a dû être fait un peu dans l'urgence, j'ai pu affiner son origine.

En fait à l'origine je pensais qu'il s'agissait d'un pb de violation de clé chrono mais il semble que ce soit davantage un processus qui bloque d'autres processus. Lorsque le problème s'est produit, j'ai jeté rapidement un coup
d'oeil dans les volets "Information sur le processus" et "Verrous / ID de processus". Ceux-ci indiquaient clairement qu'un processus était dans
l'état bloqué (et les autres étaient bloqués cf. la copie d'écran suivante).


En regardant la requête à l'origine du blocage, il me semble (j'ai regardé trop vite et n'ai pas fait fait de capture d'écran) que la requête traitée par ce SPID était de type SELECT sur une table que j'appellerai T1. Par contre ce qui est sûr, c'est que les autres SPID bloqués par celui-ci essayaient de faire un update sur cette table T1, sur un même "secteur de données". Alors que d'autres SPID pouvaient continuer à faire des updates sur la même table mais sur un autre "secteur de données".

Lorsque le blocage s'est levé au bout de 10 bonnes minutes (jusqu'à 30minutes les fois précédentes), les autres requêtes en attente ont pu être traitée.

Je suis désormais conscient que pour bien surveiller sa base et pouvoir anticiper voire résoudre plus rapidement ce type de problème, il convient de mettre en oeuvre :
> optimisation du code (j'ai pas la main dessus)
> optimisation des connexions et notamment du timeout de session (j'ai pas la main dessus)
> Montiteur de performance (en cours)
> Trace (en cours)
> monitoring des verrous et blocage (cf. ci-dessous)

Concernant ce dernier point, ayant peu de connaissance en la matière et donc étant mal préparé à l'analyse de ce type de problème, je me suis replongé ce week-end dans "Optimiser SQL Server" de R. Bruchez + tous les papiers de SQL Server. Une chose dont je m'aperçois, c'est que SQL Server 2005/2008 offre plus de faciliter pour traquer ces problèmes que SQL Server 2000 (version dont nous disposons).Ex: pas de vues sys.dm_..... sous 2000.

Enfin, bref, comme il est difficile de faire une trace des requêtes et de tous les verrous (pb de perf et d'analyse aussi), je suis plus simplement en train de faire une PS qui enregistre toutes les x minutes l'état des processus tout en conservant leur dernière requête.
Je me suis inspiré de la PS sp_MSset_current_activity qui utilise les tables master.dbo.sysprocesses et master.dbo.syslockinfo + de DBCC inputbuffer (pour obtenir la dernière requête lancée par le process).

Pensez-vous que ce monitoring est suffisant afin d'identifier les causes du problème ? Je crains qu'il y ait également une question de "contexte" que ma PS ne monitore pas !


Quelques autres interrogations :

> Question 1 : A piori, la requête bloquante serait donc du type SELECT ... J'ai pu identifier l'utilisateur à l'origine du problème et retester

ce qu'il faisait dans les secondes précédant le blocage mais sans parvenir à reproduire les symptômes du problème !
Et comme la session a par défaut comme niveau d'isolation READ COMMITTED, je vois pas ce que je peux faire de plus restrictif !

Pour rappel, la requête est générée par un progiciel : je n'ai donc pas de regard sur le code.
Comment est-il possible d'occasionner un blocage avec un simple SELECT ?

> Question 2 : j'ai essayé ensuite de faire une blocage à partir d'un UPDATE massif (plusieurs minutes) sur la même table et d'essayer de

faire des SELECT sur le même périmètre avec d'autres SPID.
Ce qui m'étonne, c'est qu'au niveau du volet de "Verrous / ID de processus", je n'arrive pas à obtenir la même icône de blocage que celle obtenue lors du problème (celle avec une point d'exclamation).

Pour les deux process, l'icône utilisée est celle du bas. Donc les deux process se voient bloqués mais le 99 ne se voit pas comme bloquant !

Pourquoi ?

Merci d'avance de votre aide.

[dbaffaleuf]

1) Le mode d'isolation par défaut est READ COMMITTED, le select bloque la mise à jour.
2) Tu ne vois pas 99 comme bloquant probablement parce qu'il fait du parallélisme (99 bloqué par 99), regarde si le type de ressource en attente pour cette session est CXPACKET.

Le phénomène d'attente de ressource fait partie de la gestion de la concurrence d'accès, il arrive en permanence sur un système où plus d'une session utilisateur est en exécution. Ce qui est anormal et donc que tu devrais surveiller, c'est le temps de blocage qui est remonté par la colonne waittime de sysprocesses. (en ms) S'il dépasse une certaine valeur (à toi de la définir), alors il faut tenter de capturer le texte de la requête qui bloque.

David B.

[FMJ]

Salut et merci pour ta réponse.

1. Je viens de faire un test avec :
> Un process qui lance une SELECT avec un produit de convolution bien volumineux qui est précédé d'un "set transaction isolation level read committed"
> juste après je lance un update portant sur le "même périmètre"
Résultat : le update est appliqué (ou en tant cas ne semble pas en attente) alors que le select continue a être traité

Ce que tu dis, c'est effectivement ce que j'ai lu mais dans les faits, je ne parviens pas à l'appliquer (ou du moins à le mettre en évidence)

2. Tu parles du Wait type ?
> SPID 99 (requête bloquante) : PAGEIOLATCH_SH (c'est dû au produit de convolution qui page énormément)
> SPID 92 (requête bloquée) : LCK_M_IS

3. Oui j'étais déjà en train de mettre dans la PS un filtrage sur le wait time pour éviter de voir les SPID non intéressants.

Merci

[dbaffaleuf]

Ce que tu dis, c'est effectivement ce que j'ai lu mais dans les faits, je ne parviens pas à l'appliquer (ou du moins à le mettre en évidence)

C'est difficile à mettre en évidence dans ce sens là parce que c'est une histoire de timing. Tu ne contrôle pas quand le select obtient les verrous sur les lignes et quand il les libère. Il y a aussi la question de l'escalade de verrous dans l'équation. Lorsque SQL Server a besoin de verrouiller une certaine quantité de verrous de même type (entre 5000 et 6000 en général en fonction de la mémoire), il escalade au niveau supérieur (page puis objet tout entier). Donc ton SELECT sur PARPIECE, s'il touche plus de 5000-6000 lignes dans son plan, peut décider d'escalader le verrouillage. Il faudrait voir quels sont les verrous détenus par le SPID 79 dans ton cas. Il existe un event dans Profiler pour détecter quand ça arrive.

David B.

[FMJ]

Salut

En fait quand je dis que je n'arrive pas à le mettre en évidence, ce n'est pas dans le cadre du problème de production mais dans le cas d'un test.

Cas n°1 :
> SPID 50 : Je fais un UPDATE sur une table T1 sur un ensemble de plus d'un million de lignes. Cet UPDATE prend dans les 5 minutes
> SPID 51 : Durant ces 5 minutes, si je fait un SELECT TOP 10 ... la même table mais sur le même périmètre de lignes --> blocage de la part du SPID 50 : normal !
> SPID 52 : Toujours durant ces 5 minutes, si je fait un SELECT TOP 10 ... la même table mais sur un périmètre de lignes différent --> pas blocage de la part du SPID 50 : normal !


Cas n°2 :
> SPID 50 : Toujours sur le même périmètre de cette table, je fais un SELECT très lourd qui représente plusieurs centaines de millions de lignes. Cet UPDATE prend plus de 5 minutes
> SPID 51 : Durant ces 5 minutes, si je fait un UPDATE sur la même table quelques lignes du même --> pas blocage de la part du SPID 50 : pas normal non ???!!!
> SPID 52 : Toujours durant ces 5 minutes, si je fait un UPDATE sur la même table mais sur un périmètre de lignes différent --> pas blocage de la part du SPID 50 : normal !

[FMJ]

Tiens au fait, en passant, comment récupérer dans une table temporaire la sortie de EXEC(DBCC INPUTBUFFER(@var)) ?

Merci

[FMJ]

Bon, c'est tout bê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
DECLARE @spid int, @sql varchar(4000)
 
CREATE TABLE #Inputbuffer(
EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(255) NULL
)
set @spid =66
 
INSERT #Inputbuffer
EXEC('DBCC INPUTBUFFER(66)')
 
EXEC('SELECT '  + @spid + ', * FROM #Inputbuffer')
 
drop table #Inputbuffer
 
GO

mais par contre une chose que je n'arrive pas à faire, c'est quelque chose du style :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE @spid int

CREATE TABLE #Inputbuffer(
spid int,
EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(255) NULL
)
set @spid =66

EXEC('INSERT #Inputbuffer ' + @spid + ', EXEC(''DBCC INPUTBUFFER(' +  @spid + ')'')')
GO

Je ne sais pas ajouter une colonne avec @spid dans le INSERT avant le EXEC

[FMJ]

A propos de DBCC INPUTBUFFER, un papier pas inintéressant qui compare cette commande à la fonction fn_get_sql()
http://phelabaum.com/archive/2010/03...vs-fn-get-sql/

[dbaffaleuf]

Un petit update au sujet de la situation où le même SPID est vu comme bloqué et bloquant. Apparemment de nouveaux diagnostics ont été ajoutés en SP2 de SQL Server 2000 et sysprocesses peut également rapporter des attentes sur des latches aussi bien que sur des locks, ce qui se produit avec le SPID 99 dans ton cas. Lorsque la valeur de waittime n'est pas trop élevée, il ne faut pas en tenir compte.
Ref : http://blogs.msdn.com/b/sqlblog/arch...-possible.aspx