Is there a more helpful way to detect lock acquire/release stats in SQL Profiler?

Question

Got a few deadlocks happening on our live server, i know, it sucks right.

Anyway, i'm trying to figure out how to see what SQL code is causing the locks (or holding it for too long).

I've added all the Locks events (acquired, deadlock, escalation, released, timeout, etc), but all i see is a bombardment of acquired/released events, with not much additional info to go along with.

I'm pretty sure i know the "scenario" that causes the deadlocks, as we have a trigger on a table after an INSERT/UPDATE/DELETE, and this does a LOT of extra work, sometimes up to 8 seconds worth.

In the bulk of that extra work though, data is pulled from TableA and inserted into a table variable, heaps of work goes on in-memory on that table variable (around 6 secs passes by), then finally an insert into TableB happens.

Question: Would that actually cause a table lock on the entire table, even though i'm only selecting certain rows into a table variable?

I've added "trigger tracing" (insert into table x values ('hi im here'), etc) and i basically know the procedure taking the longest time (and thus must likely to cause the lock?)

But i'm still not sure why the deadlock is happening.

Question: I can see there are 2 Lock Escalations occuring, does that mean row locks have been escalated to table locks?

Question: Can someone give me some tips on how to trace this deadlock down further?

EDIT:

Here is the deadlock graph

EDIT 2:

Here is the code for [UpdatePostsCleanedUriUniqueUri], that may be causing a table lock?

ALTER PROC [dbo].[UpdatePostsCleanedUriUniqueUri]
(
    @PostIds IdentityType READONLY
)
AS
    SET NOCOUNT ON


    -- *************************************************************************
    -- ******************* Create the Cleaned Uri's,  first ********************
    -- *************************************************************************

    ---- "Remove" any existing cleaned uri's, per location
    UPDATE a
    SET a.CleanedUri = NEWID(),
        a.UniqueUri = NEWID()
    FROM [dbo].[Posts] a
        INNER JOIN @PostIds b ON a.PostId = b.Id


    -- ** Now add the cleaned uri.
    UPDATE a
    SET a.CleanedUri = [dbo].[ToFixedLengthToString](
        [dbo].[IsNullOrEmpty](LOWER([dbo].[ToAlphaNumericText]([Subject], '-', 1)), 'unknown'), 60, '')
    FROM [dbo].[Posts] a
        INNER JOIN @PostIds b ON a.PostId = b.Id



    -- *************************************************************************
    -- ******** Now create the Unique Uri from the cleaned one, above **********
    -- *************************************************************************

    -- Now Re-Add these unique uri's.
    ;WITH CTE AS (
        SELECT DISTINCT CleanedUri
        FROM [dbo].[Posts] a
            INNER JOIN @PostIds b ON a.PostId = b.Id
    )

    UPDATE a
    SET a.UniqueUri = Result.UniqueUri
    FROM [dbo].[Posts] a
        INNER JOIN (
            SELECT SubQuery.PostId, 
                CASE SubQuery.RowNumber
                WHEN 1 THEN SubQuery.CleanedUri
                ELSE SubQuery.CleanedUri + '-' + CAST(SubQuery.RowNumber - 1 AS NVARCHAR(20)) END AS UniqueUri 
            FROM (
                SELECT PostId, a.CleanedUri,
                    ROW_NUMBER() OVER (PARTITION BY a.CleanedUri ORDER BY a.CleanedUri) AS RowNumber
                FROM [dbo].[Posts] a
                    INNER JOIN CTE b ON a.CleanedUri = b.CleanedUri
            ) SubQuery
        ) Result ON a.PostId = Result.PostId
    ;

Because it has an INNER JOIN, i would have thought it would only lock the rows that are matched? Or should i be using WITH (ROWLOCK)?

EDIT 3:

    USE [XWing]
    GO

    /****** Object:  Table [dbo].[Posts]    Script Date: 02/17/2012 13:29:05 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[Posts](
        [PostId] [int] IDENTITY(1,1) NOT NULL,
        [Subject] [nvarchar](300) NULL,
        [CleanedUri] [nvarchar](70) NOT NULL,
        [UniqueUri] [nvarchar](70) NOT NULL,
        [Content] [nvarchar](max) NULL,
        [Source] [nvarchar](50) NULL,
        [LocationTypeId] [tinyint] NOT NULL,
        [CreatedOn] [smalldatetime] NOT NULL,
        [ModifiedOn] [smalldatetime] NOT NULL,
        [IsEditorsChoice] [bit] NOT NULL,
        [IsVisible] [bit] NOT NULL,
        [UserId] [int] NOT NULL,
        [LatLongPoint] [geography] NULL,
        [UserLastIpAddress] [varchar](15) NULL,
        [OldPostId] [int] NULL,
        [OldUniqueUri] [nvarchar](250) NULL,
        [ThemeId] [int] NOT NULL,
     CONSTRAINT [PK_Posts] PRIMARY KEY CLUSTERED 
    (
        [PostId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    ALTER TABLE [dbo].[Posts]  WITH CHECK ADD  CONSTRAINT [FK_Posts_Themes] FOREIGN KEY([ThemeId])
    REFERENCES [dbo].[Themes] ([ThemeId])
    GO

    ALTER TABLE [dbo].[Posts] CHECK CONSTRAINT [FK_Posts_Themes]
    GO

    ALTER TABLE [dbo].[Posts]  WITH CHECK ADD  CONSTRAINT [FK_Posts_Users] FOREIGN KEY([UserId])
    REFERENCES [dbo].[Users] ([UserId])
    GO

    ALTER TABLE [dbo].[Posts] CHECK CONSTRAINT [FK_Posts_Users]
    GO

    ALTER TABLE [dbo].[Posts] ADD  CONSTRAINT [DF_Posts_IsEditorsChoice]  DEFAULT ((0)) FOR [IsEditorsChoice]
    GO

    ALTER TABLE [dbo].[Posts] ADD  CONSTRAINT [DF_Posts_ThemeId]  DEFAULT ((1)) FOR [ThemeId]
    GO

USE [XWing]
GO

/****** Object:  Index [IX_Posts_IsEditorsChoice_Include_PostId_LocationTypeId]    Script Date: 02/17/2012 13:31:59 ******/
CREATE NONCLUSTERED INDEX [IX_Posts_IsEditorsChoice_Include_PostId_LocationTypeId] ON [dbo].[Posts] 
(
    [IsEditorsChoice] ASC
)
INCLUDE ( [PostId],
[LocationTypeId]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

USE [XWing]
GO

/****** Object:  Index [IX_Posts_IsVisible]    Script Date: 02/17/2012 13:32:02 ******/
CREATE NONCLUSTERED INDEX [IX_Posts_IsVisible] ON [dbo].[Posts] 
(
    [IsVisible] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

USE [XWing]
GO

/****** Object:  Index [IX_Posts_LocationTypeId]    Script Date: 02/17/2012 13:32:07 ******/
CREATE NONCLUSTERED INDEX [IX_Posts_LocationTypeId] ON [dbo].[Posts] 
(
    [LocationTypeId] ASC
)
INCLUDE ( [PostId]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

USE [XWing]
GO

/****** Object:  Index [IX_Posts_OldPostId]    Script Date: 02/17/2012 13:32:12 ******/
CREATE NONCLUSTERED INDEX [IX_Posts_OldPostId] ON [dbo].[Posts] 
(
    [OldPostId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

USE [XWing]
GO

/****** Object:  Index [IX_Posts_UserId]    Script Date: 02/17/2012 13:32:17 ******/
CREATE NONCLUSTERED INDEX [IX_Posts_UserId] ON [dbo].[Posts] 
(
    [UserId] ASC
)
INCLUDE ( [PostId]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

USE [XWing]
GO

/****** Object:  Index [SX_Posts_Location]    Script Date: 02/17/2012 13:32:22 ******/
CREATE SPATIAL INDEX [SX_Posts_Location] ON [dbo].[Posts] 
(
    [LatLongPoint]
)USING  GEOGRAPHY_GRID 
WITH (
GRIDS =(LEVEL_1 = LOW,LEVEL_2 = LOW,LEVEL_3 = MEDIUM,LEVEL_4 = HIGH), 
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

USE [XWing]
GO

/****** Object:  Index [UIX_Posts_UniqueUri]    Script Date: 02/17/2012 13:32:41 ******/
CREATE UNIQUE NONCLUSTERED INDEX [UIX_Posts_UniqueUri] ON [dbo].[Posts] 
(
    [UniqueUri] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

EDIT 4

Here is the execution plan for UpdatePostsCleanedUriUniqueUri after adding the following index for CleanedUri:

CREATE NONCLUSTERED INDEX [IX_Posts_CleanedUri_Include_PostId] ON [dbo].[Posts] 
(
    [CleanedUri] ASC
)
INCLUDE ( [PostId]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Answer 1

Would that actually cause a table lock on the entire table, even though i'm only selecting certain rows into a table variable?

Lock escalation. If your 'select' is not ... selective, if it has to scan large portions of a table then the engine may escalate to table locks. See Lock Escalation (Database Engine).

I can see there are 2 Lock Escalations occurring, does that mean row locks have been escalated to table locks?

Are they lock escalation events, or just lock escalation attempts? If they succeeded then the transaction has locked the entire table.

Can someone give me some tips on how to trace this deadlock down further?

Approaching this from the individual locks POV is not very productive. The typical investigation focuses on capturing and analyzing the deadlock graph. See Analyzing Deadlocks with SQL Server Profiler. Capture the deadlock graph and post it here and maybe we can help. Upload the deadlock graph XML, not the picture of the deadlock, see How to: Save Deadlock Graphs (SQL Server Profiler).

Deadlock graph:

Spid 58 has page 7:1:11066 in IX mode and is blocking spid 61 that wants it in S mode. Spid 61 has page 7:1:1932345 in S mode and is blocking spid 58 who has an IU mode lock on it but wants it convert it to IX. Things are complicated by the addition of parallelism and a long list of 'me too' waiters, but the fundamental issue can be reduced to the one I described above.

This is a typical pattern of lack of indexes. You have an updater, XWingNew.dbo.UpdatePostsCleanedUriUniqueUri which is issuing an update that likely scans the entire XWingNew.dbo.Posts table. At the same time you have several readers likely scanning the entire table too in some linq generated query that was truncated in the XML: SELECT [UnionAll3].[C2] AS [C1], [UnionAll3].[C3] AS [C2], [UnionAll3].[C4] AS [C3], [UnionAll3].[C5] AS [C4],... . Because both the update and the select issue table scans they are pretty much guaranteed to deadlock when the update attempts to convert the IU page locks it acquired in the scan phase to IX locks needed to update the qualified rows.

You have two alternatives to pursue right now:

• fix your application. The queries are clearly not tuned and there was no thought given to the indexing strategies. You will not only fix the deadlock problem, but your app will also be much faster.
• the easy cop out: enable read committed snapshot. This will not fix the application flaws, but will hide them and eliminate the deadlocks.