Reducing SQL Server Deadlocks

One Response to “Reducing SQL Server Deadlocks”

1. 

   Mike Pindrik Reply February 21, 2012 at 4:52 pm

   As mentioned in the main part, one of the improvements would be is to reduce lock time.
   In our shop one of the stored procedures has a complicated query that uses many views and constructs a long XML. The query runs for about 5 minutes and occasionally causes deadlocks elsewhere. We decided to place the data, constructed by views, into temporary tables and to use temporary table instead of views in the complicated query itself. As the result, permanent tables became locked only for about 1-2 minutes, although the whole SP was still running 5 minutes or longer.
   Identifying the length of time of tables being locked is not very straight forward, so we created an SP that runs in a separate Query Analyzer session and monitors another process for tables being locked. Here is the code of this monitoring SP:

   IF OBJECT_ID(N’DBO.MonLocks’, ‘P’) > 0
   BEGIN
   DROP PROCEDURE DBO.MonLocks
   END
   GO

   CREATE PROCEDURE DBO.MonLocks
   (@Process_id as int = NULL,
   @DBName VARCHAR (63) = ‘DailyDB’,
   @IncludeTempDB CHAR (1) = ‘N’)
   AS

   /************************************************************************************
   PRINT @@SPID

   EXEC MonLocks
   @Process_id = 187,
   @DBName = ‘WeeklyDB’

   select * from ##TTot
   order by TimeLocked DESC

   ************************************************************************************/
   BEGIN
   SET NOCOUNT ON

   DECLARE @CpuUsed BIGINT,
   @TickStart DATETIME,
   @TickEnd DATETIME,
   @Msec FLOAT,
   @SQL NVARCHAR(MAX),
   @WaitFor_Delay DATETIME = ’00:00:01′, – 1 second
   – need to count how many time in a row ‘CPU idle’ happenes
   – because in tests ‘CPU idle’ was for mor than 3 seconds,
   – but the monitored process was still running
   @WaitsTotIdleCPU INT = 15,
   @WaitsCntIdleCPU INT

   IF OBJECT_ID(‘TEMPDB..##TCurr’, ‘U’) IS NOT NULL
   DROP TABLE ##TCurr
   IF OBJECT_ID(‘TEMPDB..##TTot’, ‘U’) IS NOT NULL
   DROP TABLE ##TTot

   – create temp tabs
   CREATE TABLE ##TCurr (TabName VARCHAR(511) NOT NULL)

   CREATE TABLE ##TTot (
   TabName VARCHAR(511) NOT NULL,
   TimeLocked FLOAT NULL DEFAULT 0.0,
   LockStart DATETIME NULL,
   LastLockEnded DATETIME NULL)

   – wait for process to start
   WHILE 1=1
   BEGIN
   – save @CpuUsed
   SELECT @CpuUsed = cpu
   FROM master..sysprocesses
   WHERE spid = @Process_id

   – wait for 1 second
   WAITFOR DELAY @WaitFor_Delay

   – if @CpuUsed changed, the processing started
   IF @CpuUsed
   (
   SELECT TOP 1 cpu
   FROM master..sysprocesses
   WHERE spid = @Process_id
   )
   BREAK
   END

   – processing started

   – save CPU start time
   INSERT ##TTot
   SELECT TabName=’CPU’, TimeLocked=0, LockStart = GETDATE(), LastLockEnded = GETDATE()

   – ‘SEED’ the @TickStart
   SET @TickStart = GETDATE()

   – accumulate tables lock times
   WHILE 1=1
   BEGIN
   TRUNCATE TABLE ##TCurr

   – get all currently active perm tables
   SET @SQL = ‘INSERT ##TCurr
   SELECT DISTINCT SO.Name
   FROM sys.dm_tran_locks TL
   JOIN ‘ + @DBName + ‘..SYSOBJECTS SO ON SO.ID =TL.resource_associated_entity_id
   WHERE request_session_id = ‘ + CAST(@Process_id AS VARCHAR)
   EXEC sp_executesql @SQL

   – get all currently active temp tables
   IF @IncludeTempDB = ‘Y’
   BEGIN
   – TEMPDB is causing deadlocks and early termination
   INSERT ##TCurr
   SELECT DISTINCT SO.Name
   FROM sys.dm_tran_locks TL
   JOIN TEMPDB..SYSOBJECTS SO ON SO.ID =TL.resource_associated_entity_id
   WHERE request_session_id = @Process_id
   END

   – insert missing active permanent tables into ##TTot
   INSERT ##TTot (TabName, LockStart)
   SELECT DISTINCT TabName, LockStart = @TickStart
   FROM ##TCurr
   WHERE NOT EXISTS
   (
   SELECT 1 FROM ##TTot WHERE TabName = ##TCurr.TabName
   )

   – save @CpuUsed
   SELECT @CpuUsed = cpu
   FROM master..sysprocesses
   WHERE spid = @Process_id

   – wait for 1 second
   WAITFOR DELAY @WaitFor_Delay

   – if @CpuUsed did not change, the processing stopped
   IF @CpuUsed =
   (
   SELECT TOP 1 cpu
   FROM master..sysprocesses
   WHERE spid = @Process_id
   )
   BEGIN
   – comes here if CPU did not change for the process
   – need to count how many time in a row ‘CPU idle’ happenes
   – because in tests ‘CPU idle’ was for mor than 3 seconds,
   – but the monitored process was still running
   SET @WaitsCntIdleCPU = @WaitsCntIdleCPU + 1
   IF @WaitsCntIdleCPU < @WaitsTotIdleCPU
   BEGIN
   – just keep counting 'CPU idle' occurances
   CONTINUE
   END
   ELSE
   BEGIN
   – process did not get CPU for long enough time,
   – so assume it stopped
   BREAK
   END
   END

   SET @WaitsCntIdleCPU = 0

   SET @TickEnd = GETDATE()
   SET @Msec = DATEDIFF(ms, @TickStart, @TickEnd) / 1000.0
   – keep @TickStart close to @TickEnd, so
   – SUM will match the real difference between end time and start time
   SET @TickStart = GETDATE()

   – update CPU time
   UPDATE ##TTot
   SET TimeLocked = TimeLocked + @Msec,
   LastLockEnded = GETDATE()
   WHERE TabName = 'CPU'

   – update ##TTot for tables that are currently active
   UPDATE ##TTot
   SET TimeLocked = TimeLocked + @Msec,
   LastLockEnded = GETDATE()
   WHERE EXISTS (SELECT 1 FROM ##TCurr WHERE TabName = ##TTot.TabName)

   END

   – update CPU time
   UPDATE ##TTot
   SET LastLockEnded = GETDATE()
   WHERE TabName = 'CPU'

   select * from ##TTot
   order by TimeLocked DESC

   select * from ##TTot
   order by LastLockEnded DESC
   END

Leave a Reply