Une requête peut être mise en attente dans le cas où un autre process monopolise déjà la même ressource (RID, Key, Page, etc.). La durée du blocage peut-être plus ou moins pénalisante et la cause très variable : requête à optimiser, indexation non pertinente, niveau d’isolation non adapté, etc.
Dans un article précédent, la procédure stockée sp_whoisactive a été mentionnée. Elle permet à d’identifier les requêtes en cours sur une instance SQL Server, qu’elles soient coûteuses et/ou bloquées et bloquantes.
Voir sp_whoisactive : https://www.concatskills.com/2016/12/10/process-actifs-instance-sql-server/
Problème : Au moment où on nous signale des lenteurs sur une instance SQL Server, il est parfois trop tard pour identifier le processus en cause, autrement dit la tempête est passée. L’historisation des processus bloqués et bloquant est possible via les évènements étendus, XEvent. La finalité est de pouvoir les collecter et les analyser rétroactivement. A partir de SQL Server 2012, les évènements étendus sont pleinement fonctionnels (disponibles dès SQL Server 2008 mais immatures). Le cas échéant, on utilisera le profiler (il est peut-être temps d’envisager une migration pour les versions concernées). Les traces XEvent peuvent être stockées en mémoire et/ou dans des fichiers. Pour un stockage physique, il est primordial de définir une taille MAX par fichier pour éviter de saturer les disques. Le ROLLOVER est activé par défaut à 5 fichiers. La taille des fichiers enfin ne doit pas être trop importante (quelques Mo) pour faciliter le parsing XML Il vaut mieux avoir beaucoup de petits fichiers que peu et volumineux, en sachant que chaque fichier peut être analysé individuellement.
Les traces XEvent sont disponibles dans la section ci-dessous :
Cette étape consiste à modifier le seuil de détection des blocages en seconde au niveau de l’instance. Dans le cas présent tous les blocages ayant une durée minimum de 10 secondes seront remontés dans la trace XEvent. Ce changement de paramètre n’a aucun impact au niveau instance, en revanche plus son seuil est bas et plus la trace chargée d’historiser les blocages sera volumineuse et coûteuse.
1 2 3 4 |
EXEC sys.sp_configure N'blocked process threshold (s)', N'10' GO RECONFIGURE WITH OVERRIDE GO |
Ce paramètre est évoqué dans l’article dédié au Tuning d’instance : https://www.concatskills.com/2016/10/20/tuning-dinstance-sql-server/
Nous allons créer une arborescence de répertoires sur laquelle le compte de service SQL Server aura le droit d’écrire. Le répertoire blocked_process stockera les traces XEvent concernées.
Création de la trace XEvent en spécifiant le nom blocked_process et le chemin D:\XEvent\blocked_process\blocked_process.xel. Notez qu’il y a un rollover de défini sur 5 fichiers par défaut pour une taille limitée à 5 Mo par fichier, les traces n’étant pas supposée saturer les disques.
1 2 3 4 5 6 7 8 9 |
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='blocked_process') DROP EVENT SESSION [blocked_process] ON SERVER GO CREATE EVENT SESSION [blocked_process] ON SERVER ADD EVENT sqlserver.blocked_process_report ADD TARGET package0.event_file(SET filename=N'D:\XEvent\blocked_process\blocked_process.xel',max_file_size=(5)) WITH (STARTUP_STATE=OFF) GO |
Démarrage de la trace XEvent blocked_process
1 2 3 4 |
ALTER EVENT SESSION [blocked_process] ON SERVER STATE = start; GO |
Le détail des processus bloqués et bloquant peut être consulté en parsant le XML des traces XEvent générées et le résultat reste à historiser dans une table au besoin. Il est désormais possible de savoir qui (hostname, application name, etc…) a bloqué qui, à quelle heure, pendant combien de temps avec les requêtes associées aux processus bloqué et bloquant et enfin la ressource concernée.
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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
IF OBJECT_ID('tempdb..#Events') IS NOT NULL DROP TABLE #Events DECLARE @TopCount Int = 100, @SessionName SysName = 'blocked_process', @Target_File VARCHAR(1000), @Target_Dir VARCHAR(1000), @Target_File_WildCard VARCHAR(1000) -- Récupérer le fichier de trace SELECT @Target_File = CAST(t.target_data as XML).value('EventFileTarget[1]/File[1]/@name', 'VARCHAR(256)') FROM sys.dm_xe_session_targets t INNER JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address WHERE s.name = @SessionName AND t.target_name = 'event_file' SET @Target_Dir = LEFT(@Target_File, LEN(@Target_File) - CHARINDEX('\', REVERSE(@Target_File))) SET @Target_File_WildCard = @Target_Dir + '\' + @SessionName + '_*.xel' --SELECT @Target_File_WildCard -- Récupérer TOP n Events en XML SELECT TOP (@TopCount) CAST(event_data AS XML) AS event_data INTO #Events FROM sys.fn_xe_file_target_read_file(@Target_File_WildCard, null, null, null) AS F ORDER BY File_name DESC, file_offset DESC SELECT DATEADD(hour, DATEDIFF(hour, GETUTCDATE(), GETDATE()) , event_data.value('(event/@timestamp)[1]', 'datetime')) AS log_date_time , event_data.value('(event/data[@name="transaction_id"]/value)[1]', 'bigint') AS transaction_id , event_data.value('(event/data[@name="database_name"]/value)[1]', 'VARCHAR(128)') AS database_name , event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000 AS duration_ms , event_data.value('(event/data[@name="resource_owner_type"]/text)[1]', 'varchar(10)') AS resource_owner_type , event_data.value('(event/data[@name="lock_mode"]/text)[1]', 'varchar(10)') AS lock_mode , event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/@waitresource)[1]', 'varchar(max)') AS wait_resource , event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/@isolationlevel)[1]', 'varchar(max)') AS blocked_tran_iso_level , event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/@isolationlevel)[1]', 'varchar(max)') AS blocking_tran_iso_level , event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/@spid)[1]', 'varchar(max)') AS blocked_spid , event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/@clientapp)[1]', 'varchar(max)') AS blocked_clientapp , event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/@loginname)[1]', 'varchar(max)') AS blocked_loginname , event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/process/inputbuf)[1]', 'varchar(max)') AS blocked_statement , event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/@spid)[1]', 'varchar(max)') AS blocking_spid , event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/@clientapp)[1]', 'varchar(max)') AS blocking_clientapp , event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/@loginname)[1]', 'varchar(max)') AS blocking_loginname , event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/process/inputbuf)[1]', 'varchar(max)') AS blocking_statement , event_data FROM #Events |