SQL Reference

By Frederic JOFES and Co.

Étiquette : Deadlock

Give locks and deadlocks by type

-- Donne la liste des locks et deadlock en fonction des types
SELECT [counter_name],"OibTrackTbl","AllocUnit","HoBT","Metadata","Application","RID","Extent","Key","Page","Object","File","Database","_Total"
FROM (SELECT [counter_name],[instance_name],[cntr_value] FROM sys.dm_os_performance_counters WHERE OBJECT_NAME LIKE '%:Locks%') os_pc
PIVOT (AVG([cntr_value]) FOR [instance_name] IN ("OibTrackTbl","AllocUnit","HoBT","Metadata","Application","RID","Extent","Key","Page","Object","File","Database","_Total")) AS Pvt;

Deadlock count since last start SQL Server engine

-- Donne le nombre de deadlock par minute et le nombre total de deadlock depuis le dernier démarrage de SQL Server
select c.counter_name, c.instance_name, (c.cntr_value / DATEDIFF(n,s.sqlserver_start_time,getdate())) as 'NbDeadLock/m',c.cntr_value from sys.dm_os_performance_counters c, sys.dm_os_sys_info s
where c.counter_name = 'Number of Deadlocks/sec' and c.instance_name = '_Total'

Generate a deadlock

It can be usefull to generate a deadlock to analyse if an audit or a DBA job works or what information XEvent returns

Lire la suite

Check deadlocks with system health XEvent

Cette requête permet de lister l’ensemble des deadlocks récupéré depuis l’XEvent « system health » ( a partir de 2008 R2)

Lire la suite

Fièrement propulsé par Fred & Co.