Sql-server – more helpful way to detect lock acquire/release stats in SQL Profiler

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