Hello,
Il faut voir quelles sont les attentes sur ces traitements lorsqu'ils durent plus que la moyenne habituelle.
Deux façons:
- La brute de fonderie: réinitialiser les statistiques d'attente avant le traitement, et faire une capture après le traitement pour voir quels sont les postes d'attente prédominants. Avantage: facile à mettre en place, inconvénient: la mesure est instance-wide,
c'est à dire qu'elle ne concernera pas que le traitement en question mais tout ce qui s'est passé dans l'intervalle.
- La subtile: créer une session extended events en traçant sqlos.wait_info et en filtrant sur le numéro de session. Avantage: on n'a que les postes d'attente du traitement en question. Inconvénient: pas simple à mettre en place.
Déjà je pense que tu peux dégrossir avec la première méthode. Si on voit du LCK_ partout on saura d'où ça vient:
Réinitialiser les stats d'attente:
dbcc sqlperf('sys.dm_os_wait_stats','clear');
GO
Collecter les attentes:
WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
'BROKER_RECEIVE_WAITFOR', 'ONDEMAND_TASK_QUEUE', 'DBMIRROR_EVENTS_QUEUE',
'DBMIRRORING_CMD', 'BROKER_TRANSMITTER', 'SQLTRACE_WAIT_ENTRIES',
'SLEEP_BPOOL_FLUSH', 'SQLTRACE_LOCK','HADR_FILESTREAM_IOMGR_IOCOMPLETION','DIRTY_PAGE_POLL','SP_SERVER_DIAGNOSTICS_SLEEP')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold
GO
Autre chose, tu peux fixer une valeur de 'blocked process threshold' disons à 10 ou 15 secondes, et créer une session Profiler ou extended event pour tracer lorsqu'un blocage de plus de 10 ou 15 secondes se produit (sous Profiler: Errors and Warnings ->
Blocked Process Report).
exec sp_configure 'blocked process threshold',15;
GO
RECONFIGURE
GO
cf http://www.sqlservercentral.com/articles/Blocking/64474/
David B.
