Signification de PREEMPTIVE_XE_GETTARGETSTATE et ...

Bonjour à tous,

J'ai des lenteurs sur un serveur, et en utilisant un script de Brent Ozar un peu plus complet, je tombe sur PREEMPTIVE_XE_GETTARGETSTATE qui est en premier.

Voici ce que j'obtiens:

Pièce jointe 343018

Malgré que je regarde ici, je ne comprends pas la signification et si c'est important : https://www.sqlskills.com/help/waits...ettargetstate/

Pour le WRITELOG, il attend pour écrire dans le log, ça vient surtout d'une lenteur du disque? Est-ce qu'un nombre élevé de VLF peut être à l'origine aussi?

Si vous avez une remarque sur les autres attentes dont je dois faire absolument attention, je suis preneur.

Merci beaucoup.

Hello,

PREEMPTIVE_XE_GETTARGETSTATE est un type d'attente que tu peux exclure de ton résultat global. En effet il se produit lorsqu'un thread bascule en mode préemptif pour récupérer l'état d'une cible de sessions d'événements étendues. Le thread est obligé dans ce cas de sortir d'un contexte du process sqlservr.exe pour avoir accès à l'état d'une cible comme par exemple vérifier l'accès à un ficher xel. Donc on peut considérer que ce type d'attente n'est pas vraiment représentatif d'un problème de performance.

Dans ton cas le temps moyen d'attente pour un WRITELOG reste correct (j'ai certains clients qui ont des valeurs bien plus élevées sans pour autant souffrir de problèmes de performance) et ne présage pas de problème lié au stockage vu de loin. Il peut simplement indiquer une importante activité transactionnelle avec beaucoup de transactions courtes concurrentes ou peut être un problème de fragmentation excessif causé par page splits (phénomène qui peut générer une quantité importante dans le journal lors des mises à jour de données).

Moi j'irai plus vite regarder du côté des attentes PAGEIOLATCH_xxx et LCK_xxx dont les temps moyens sont anormalement importants.
Tu peux avoir un problème de manque de mémoire ou un stockage ne répondant pas assez vite (même si pour l'hypothèse du stockage cela n'est à mon avis plus qu'un symptôme que le réel problème). Les LCK_xxx peuvent indiquent que l'acquisition des verrous prend du temps et cela peut être du à un manque d'index (scans au lieu de seek) ce qui pourrait par ailleurs être corréler aux attentes PAGEIOLATCH_xxx. Cela peut être aussi causé par des transactions trop longues avec peut être des requêtes longues à éxecuter (manque d'index? :P) ou un mauvais niveau d'isolation de transactions.

Bref ce ne sont évidement ici que des pistes à explorer et vérifier ;)

++

Salut David,

Ok merci pour les explications, je garde ça précieusement et je regarderai plus tard car j'ai déjà d'autres priorités que ce serveur...

Juste encore une info stp David, ici je remarque qu'SQL Server passe son temps à attendre des Lock, c'est du "uniquement" à un manque d'index? Sur cette DB de 100 GB, on a des blocked process une fois par semaine, qui bloque l'application complètement. L'owner de l'appli tue les sessions et ça repart. Mais je cherche à savoir ce qui fait ces blocked process. Donc j'ai fait un Xevent, que j'ai trouvé sur un article de Nicolas. Mais j'en profite pour voir les WAIT et je vois ça donc:

Pièce jointe 343565

Citation:

---

on a des blocked process une fois par semaine, qui bloque l'application complètement.

---

Le mieux est de regarder du côté de l'événement blocked process report qui te génère un rapport des processus bloqués au où la situation de blocage se produit.

Il faut ensuite exploiter ce rapport car tu as pas mal d'information de ce que côté - requêtes ou procédures stockées impliqués, sessions, ressources concernées, type de locks, niveau d'isolation de transaction etc ...)

Possible que ce soit un index manquant mais il faut le vérifier ..

++

Ok merci, je n'utilise jamais XEvent, donc j'ai du pas mal chercher. Je peux laisser 2 semaines? Je sais que ma question est compliquée, mais est-ce gourmand en terme de consommation?

Oh, je viens de tomber sur ça : https://blog.dbi-services.com/analyz...cesses-report/ :-)

Je regarderai demain, car j'ai trouvé d'autres scripts sur sqlskills, j'ai aussi fait manuellement.

Citation:

---

Envoyé par janlouk

Oh, je viens de tomber sur ça : https://blog.dbi-services.com/analyz...cesses-report/ :-)

Je regarderai demain, car j'ai trouvé d'autres scripts sur sqlskills, j'ai aussi fait manuellement.

---

L'avantage avec mikedavem (l'auteur de l'article que tu cite), c'est qu'il est français, tu peut donc lui causer en français, y compris dans ce forum, même s'il s'est réfugié en Suisse !!!!!

A +

Démasqué :lol:

J'ai besoin d'être certains car je commence à mélanger.

blocked_process_report, c'est bien dans Extended Events?

Pièce jointe 343795

Et ta SP dans ton article, c'est pour analyser ce rapport plus "visiblement"?

Pourtant quand je lance ceci par exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

USE [_SQLAdmin] GO   DECLARE @return_value int   EXEC @return_value = [dbo].[sp_blocked_process_report_viewer_dbi] @Trace = 'F:\BlockedProcess_XEvent_session_0_131607607077160000.xel'   SELECT 'Return Value' = @return_value   GO

---

J'ai ça comme erreur:

Code:

---

1 2 3 4 5

Msg 19050, Level 16, State 2, Line 4 Trace file name 'F:\BlockedProcess_XEvent_session_0_131607607077160000.xel' is invalid. The statement has been terminated.   (1 row affected)

---

Alors j'explique pourquoi j'ai un doute, entre Xevent et trace via SQL Profiler...

Hier, j'avais déjà vu cette SP que tu as modifié, sur le site de Brent Ozar : https://www.brentozar.com/sql/lockin...in-sql-server/

Et lui, crée une trace qui tourne 5 minutes et la fait lire via la SP et quand je lance ta SP sur la trace, .trc, là je n'ai pas d'erreur.

Pourtant quand tu me parles de "événement blocked process report", c'est bien dans Extended Events comme dans mon printscreen au dessus?

A vrai dire le script que je propose sur mon blog permet d'exploiter les fichiers générés avec une trace profiler (qui peut être côté trace côté serveur). C'est la seule chose dont je suis sûr :-D
Je n'ai jamais essayé avec un fichier généré avec une trace d'événement étendue ;)

++

Et bien je peux te dire que ça ne fonctionne pas :-)

Et tu n'aurais pas quelques choses dans le même genre pour rendre plus lisible les fichiers .xel?

J'ai trouvé ça pour lire mon évènement :

Code:

---

SELECT * FROM sys.fn_xe_file_target_read_file('F:\*.xel', null, null, null);

---

Mais c'est franchement pas "génial" car je ne sais pas si j'ai déjà rencontré un blocked process, mais c'est tellement illisible (pour moi) que je ne sais même pas si je le verrai dans mon rapport...

Moi je peine en voyant ça 8O

Code:

---

<event name="blocked_process_report" package="sqlserver" timestamp="2018-01-18T16:21:05.308Z"><data name="duration"><value>22599000</value></data><data name="database_id"><value>7</value></data><data name="object_id"><value>112771509</value></data><data name="index_id"><value>0</value></data><data name="lock_mode"><value>8</value><text><![CDATA[IX]]></text></data><data name="transaction_id"><value>509802353</value></data><data name="resource_owner_type"><value>0x00000001</value><text><![CDATA[LOCK]]></text></data><data name="blocked_process"><value><blocked-process-report monitorLoop="370510"> <blocked-process> <process id="processaff01dc38" taskpriority="0" logused="0" waitresource="OBJECT: 7:112771509:0 " waittime="22599" ownerId="509802353" transactionname="implicit_transaction" lasttranstarted="2018-01-18T17:20:39.160" XDES="0x9b62643a8" lockMode="IX" schedulerid="2" kpid="508" status="suspended" spid="152" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-01-18T17:20:42.707" lastbatchcompleted="2018-01-18T17:20:42.703" lastattention="2018-01-18T15:37:53.997" clientapp="Microsoft SQL Server JDBC Driver" hostname="V32030357" hostpid="0" loginname="XXXXXXX" isolationlevel="read committed (2)" xactid="509802353" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame line="1" stmtstart="4368" sqlhandle="0x020000004c57bc29e0f7632f2aa2b415a3714c59c625b59f0000000000000000000000000000000000000000"/> <frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"/> </executionStack> <inputbuf> (@P0 varchar(8000),@P1 int,@P2 int,@P3 varchar(8000),@P4 int,@P5 int,@P6 datetime,@P7 varchar(8000),@P8 varchar(8000),@P9 float,@P10 datetime,@P11 varchar(max),@P12 varchar(8000),@P13 float,@P14 varchar(max),@P15 varchar(8000),@P16 int,@P17 datetime,@P18 varchar(max),@P19 varchar(8000),@P20 int,@P21 varchar(max),@P22 float,@P23 float,@P24 bigint,@P25 int,@P26 int,@P27 int,@P28 varchar(8000),@P29 int,@P30 int,@P31 int,@P32 varchar(max),@P33 varchar(max),@P34 varchar(8000),@P35 float,@P36 varchar(max),@P37 varchar(max),@P38 float,@P39 varchar(8000),@P40 varchar(8000),@P41 varchar(8000),@P42 int,@P43 varchar(max),@P44 int,@P45 int,@P46 bigint,@P47 float,@P48 varchar(8000),@P49 int,@P50 varchar(8000),@P51 float,@P52 datetime,@P53 float,@P54 varchar(8000),@P55 varchar(8000),@P56 varchar(8000),@P57 varchar(8000),@P58 int,@P59 varchar(8000),@P60 varchar(8000),@P61 int,@P62 int,@P63 datetime,@P64 varchar(8000),@P65 int,@P66 varchar(max),@P67 datetime,@P68 varchar(8000),@P69 varchar(max),@P70 int,@P71 datetime,@P72 f </inputbuf> </process> </blocked-process> <blocking-process> <process status="suspended" waittime="66" spid="56" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-01-18T17:20:34.980" lastbatchcompleted="2018-01-18T17:20:34.977" lastattention="1900-01-01T00:00:00.977" clientapp="Microsoft SQL Server" hostname="V32030103" hostpid="36984" loginname="XXXXXXX" isolationlevel="read committed (2)" xactid="509801360" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame line="1" sqlhandle="0x02000000ff4c9a22e99f4123d760de7c5acd8856cdc009220000000000000000000000000000000000000000"/> </executionStack> <inputbuf> SELECT dbo.workorder.wonum AS WoWorkorder, dbo.workorder.workorderid AS WoWorkorderId, dbo.workorder.parent AS WoParentWo, dbo.workorder.origrecordid AS WoSr, dbo.workorder.origsr AS WoOriginationRecord, dbo.workorder.pmnum AS WoPmNum, dbo.workorder.jpnum AS WoJpNum, dbo.workorder.worktype AS WoWorkType, dbo.workorder.pluspcustomer AS WoCustomer, dbo.workorder.customerref AS WoCustomerReference, dbo.workorder.pluspcostcenter AS WoCustomerCostCenter, dbo.workorder.customerpo AS WoCustomerPO, dbo.workorder.customerses AS WoCustomerSES, dbo.workorder.filteredcontracts AS WoContract, dbo.workorder.imputation AS WoImputation, dbo.workorder.failurecode AS WoFailureCode, dbo.workorder.problemcode AS WoProblemCode, dbo.workorder.description AS WoDescription, dbo.workorder.description AS WoDescriptionEN, dbo.workorder.nextcomment AS WoNextComment, dbo.work </inputbuf> </process> </blocking-process> </blocked-process-report> </value></data><data name="database_name"><value><![CDATA[XXXXXXX]]></value></data></event>

---

EDIT 14h50

Bon, j'ai trouvé ça, qui me fait un xml pour chaque ligne, c'est pas encore top :-)

Code:

---

1 2 3 4 5 6 7 8 9

SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name, event_data.query('(event/data[@name="blocked_process"]/value/blocked-process-report)[1]') as [blocked_process_report] FROM ( SELECT CAST(event_data AS XML) AS event_data FROM sys.fn_xe_file_target_read_file('F:\BlockedProcess_XEvent_session_0_131607607077160000*.xel', NULL, NULL, NULL) ) AS sub; GO

---

C'est donc un peu plus clair, mais moi j'ai du mal à savoir si c'est un process blocké ou pas. Ou alors ce rapport, ne produit qu'une ligne quand il y a un blocked process?

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 27

<blocked-process-report monitorLoop="370510"> <blocked-process> <process id="processaff01dc38" taskpriority="0" logused="0" waitresource="OBJECT: 7:112771509:0 " waittime="22599" ownerId="509802353" transactionname="implicit_transaction" lasttranstarted="2018-01-18T17:20:39.160" XDES="0x9b62643a8" lockMode="IX" schedulerid="2" kpid="508" status="suspended" spid="152" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-01-18T17:20:42.707" lastbatchcompleted="2018-01-18T17:20:42.703" lastattention="2018-01-18T15:37:53.997" clientapp="Microsoft SQL Server JDBC Driver" hostname="V32030357" hostpid="0" loginname="max75pro" isolationlevel="read committed (2)" xactid="509802353" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame line="1" stmtstart="4368" sqlhandle="0x020000004c57bc29e0f7632f2aa2b415a3714c59c625b59f0000000000000000000000000000000000000000" /> <frame line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" /> </executionStack> <inputbuf> (@P0 varchar(8000),@P1 int,@P2 int,@P3 varchar(8000),@P4 int,@P5 int,@P6 datetime,@P7 varchar(8000),@P8 varchar(8000),@P9 float,@P10 datetime,@P11 varchar(max),@P12 varchar(8000),@P13 float,@P14 varchar(max),@P15 varchar(8000),@P16 int,@P17 datetime,@P18 varchar(max),@P19 varchar(8000),@P20 int,@P21 varchar(max),@P22 float,@P23 float,@P24 bigint,@P25 int,@P26 int,@P27 int,@P28 varchar(8000),@P29 int,@P30 int,@P31 int,@P32 varchar(max),@P33 varchar(max),@P34 varchar(8000),@P35 float,@P36 varchar(max),@P37 varchar(max),@P38 float,@P39 varchar(8000),@P40 varchar(8000),@P41 varchar(8000),@P42 int,@P43 varchar(max),@P44 int,@P45 int,@P46 bigint,@P47 float,@P48 varchar(8000),@P49 int,@P50 varchar(8000),@P51 float,@P52 datetime,@P53 float,@P54 varchar(8000),@P55 varchar(8000),@P56 varchar(8000),@P57 varchar(8000),@P58 int,@P59 varchar(8000),@P60 varchar(8000),@P61 int,@P62 int,@P63 datetime,@P64 varchar(8000),@P65 int,@P66 varchar(max),@P67 datetime,@P68 varchar(8000),@P69 varchar(max),@P70 int,@P71 datetime,@P72 f </inputbuf> </process> </blocked-process> <blocking-process> <process status="suspended" waittime="66" spid="56" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2018-01-18T17:20:34.980" lastbatchcompleted="2018-01-18T17:20:34.977" lastattention="1900-01-01T00:00:00.977" clientapp="Microsoft SQL Server" hostname="V32030103" hostpid="36984" loginname="max75pro" isolationlevel="read committed (2)" xactid="509801360" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame line="1" sqlhandle="0x02000000ff4c9a22e99f4123d760de7c5acd8856cdc009220000000000000000000000000000000000000000" /> </executionStack> <inputbuf> SELECT dbo.workorder.wonum AS WoWorkorder, dbo.workorder.workorderid AS WoWorkorderId, dbo.workorder.parent AS WoParentWo, dbo.workorder.origrecordid AS WoSr, dbo.workorder.origsr AS WoOriginationRecord, dbo.workorder.pmnum AS WoPmNum, dbo.workorder.jpnum AS WoJpNum, dbo.workorder.worktype AS WoWorkType, dbo.workorder.pluspcustomer AS WoCustomer, dbo.workorder.customerref AS WoCustomerReference, dbo.workorder.pluspcostcenter AS WoCustomerCostCenter, dbo.workorder.customerpo AS WoCustomerPO, dbo.workorder.customerses AS WoCustomerSES, dbo.workorder.filteredcontracts AS WoContract, dbo.workorder.imputation AS WoImputation, dbo.workorder.failurecode AS WoFailureCode, dbo.workorder.problemcode AS WoProblemCode, dbo.workorder.description AS WoDescription, dbo.workorder.description AS WoDescriptionEN, dbo.workorder.nextcomment AS WoNextComment, dbo.work </inputbuf> </process> </blocking-process> </blocked-process-report>

---

Citation:

---

Envoyé par janlouk

Et bien je peux te dire que ça ne fonctionne pas :-)

---

Peux-tu être plus précis et nous dire qu'est qui ne marche pas dans la procédure de mikedavem ?
Quel message d'erreur as-tu eu etc. ?

La procédure de mikedavem permet de consulter une trace préalablement générée par sp_trace_create. Elle n'est pas du tout basée sur les événements étendus.

Pour utiliser cette procédure, tu dois donc au préalable générer un fichier de trace avec la procédure système "sp_trace_create" etc.. tout en interceptant l'événement "137 Blocked Process" et en sélectionnant au moins les colonnes 1 et 12 :
"1 - TextData (Valeur de texte dépendant de la classe d'événements capturée dans la trace)"
"12 - SPID (ID de processus serveur affecté par SQL Server au processus associé au client"

La procédure de mikedavem est vraiment très pratique, elle indique très clairement les liens entre les processus, ceux qui bloquent et ceux qui sont bloqués. La procédure fournit toutes les informations nécessaires et suffisantes pour comprendre l'origine des problèmes de blocages.

Par ailleurs, si tu as à gérer des Serveurs en version 2008 R2, et il y en a encore pas mal ! oublis les événements étendus pour diagnostiquer les "Blocked Process", cela ne fonctionne pas (ça marche qu'à partir de SQL Server 2012 et suivants..). Pour les Serveurs 2008 R2, le seul moyen que connaisse, pour analyser les Blocked Process et de mettre en oeuvre le système des traces tel que décrit ci-dessus et d'utiliser ensuite la procédure de mikedavem (https://blog.dbi-services.com/analyz...cesses-report/) pour visionner la trace des Blocked Process. Merci mikedavem ;)

A+

Citation:

---

Envoyé par hmira

Peux-tu être plus précis et nous dire qu'est qui ne marche pas dans la procédure de mikedavem ?
Quel message d'erreur as-tu eu etc. ?

La procédure de mikedavem permet de consulter une trace préalablement générée par sp_trace_create. Elle n'est pas du tout basée sur les événements étendus.

---

Bonjour Hamid,

Oui oui je sais maintenant que c'est pour un fichier .trc, je lui confirmais.

Citation:

---

Pour utiliser cette procédure, tu dois donc au préalable générer un fichier de trace avec la procédure système "sp_trace_create" etc.. tout en interceptant l'événement "137 Blocked Process" et en sélectionnant au moins les colonnes 1 et 12 :
"1 - TextData (Valeur de texte dépendant de la classe d'événements capturée dans la trace)"
"12 - SPID (ID de processus serveur affecté par SQL Server au processus associé au client"

La procédure de mikedavem est vraiment très pratique, elle indique très clairement les liens entre les processus, ceux qui bloquent et ceux qui sont bloqués. La procédure fournit toutes les informations nécessaires et suffisantes pour comprendre l'origine des problèmes de blocages.

Par ailleurs, si tu as à gérer des Serveurs en version 2008 R2, et il y en a encore pas mal ! oublis les événements étendus pour diagnostiquer les "Blocked Process", cela ne fonctionne pas (ça marche qu'à partir de SQL Server 2012 et suivants..). Pour les Serveurs 2008 R2, le seul moyen que connaisse, pour analyser les Blocked Process et de mettre en oeuvre le système des traces tel que décrit ci-dessus et d'utiliser ensuite la procédure de mikedavem (https://blog.dbi-services.com/analyz...cesses-report/) pour visionner la trace des Blocked Process. Merci mikedavem ;)

---

Merci, j'ignorais que ça ne fonctionnait pas sur 2008 R2, mais je suis en 2012. Merci.

Citation:

---

Envoyé par janlouk

Bonjour Hamid,
Oui oui je sais maintenant que c'est pour un fichier .trc, je lui confirmais.
Merci, j'ignorais que ça ne fonctionnait pas sur 2008 R2, mais je suis en 2012. Merci.

---

Bonjour,

Oui tout à fait, mais juste pour que ce soit clair, je voudrais préciser que la technique des fichiers de trace, adossée à la procédure de mikedavem n'est pas limitée à SQL server 2008 R2. Elle fonctionne très bien sous toutes les versions SQL server (SQL Server 2008 R2, 2012, 2014 etc...)

Maintenant, si tu optes pour la technique des événements étendus pour diagnostiquer les "Blocked Process", tu ne pourras l'utiliser qu'à partir de la version SQL Server 2012.

Personnellement j'utilise, selon mon humeur du jour, l'une ou l'autre des deux techniques, pour analyser les "Blocked Process" : la technique des fichiers de traces ou la technique des événements étendus, avec la contrainte ou limitation bien sûr que j'ai évoquée, c.à.d. sous SQL Server 2008 R2 je n'ai pas trop le choix et je dois utiliser la technique des fichiers de traces.

A+

Merci Hamid pour les précisions.

Une trace peut parfois fortement diminuer les performances d'un serveur. Est-ce que dans le cas, de tracer uniquement les blocked process, as-tu remarqué une diminution des performances? C'est un serveur ultra sollicité dans mon cas, et je ne peux me permettre cela.

Citation:

---

Envoyé par janlouk

Merci Hamid pour les précisions.

Une trace peut parfois fortement diminuer les performances d'un serveur. Est-ce que dans le cas, de tracer uniquement les blocked process, as-tu remarqué une diminution des performances? C'est un serveur ultra sollicité dans mon cas, et je ne peux me permettre cela.

---

Bonjour,

Oui, tout à fait, une trace, d'une manière générale, peut un avoir un impact négatif sur le performances. Mais dans le cas présent, l'étendu de la trace est très circonscrit.

Comme la trace est basée sur le système de fichiers et capture uniquement l'événement "Blocked Process" l'impact est très minime, généralement les fichiers de trace des 'événement "Blocked Process" contiennent très peux de lignes, voire même ils sont vides.

En outre, j'utilise la technique de fichiers de trace uniquement lorsque je soupçonne des problèmes de blocage ou lorsqu'on m'alerte sur des ralentissements anormaux, etc.., et ce sur une période limitée comme par exemple pendant 2h ou 4h. La trace n'est jamais active de manière permanente sur les serveurs de production.

Après, attention, cela dépend aussi du contexte du Serveur et des applications, et de ce que tu définis pour l'option du Serveur "blocked process threshold (s)".
Un modèle de données mal ficelé, des applications mal optimisées adossées à une très faible valeur pour le seuil "blocked process threshold (s)" peut générer beaucoup trop d'événements "Blocked Process" et finir par avoir un impact négatif sur les performances du Serveur. Mais ce scénario heureusement reste très rare.

Un autre point à prendre en considération est la méthode ou technique utilisée. Il est plutôt recommandé d'utiliser les événements étendus en lieu et place du système des traces classiques. En effet, les événements étendus ont été justement conçus pour minimiser l'impact sur les performances du Serveur.

A+