"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:441B4D04-2EF2-4A7F-B131-05B04A63A63A@microsoft.com...
> Hi Steph
>
> How many rows are in Records with E_ID IN (0, @E_ID), you may want to move
> them into a temporary table. Also/alternatively check the query execution
> plan to see if adding index will help, you may want to try the Index
tuning
> wizard to see if it comes up with anything.
>
> John
>
>
> "Steph" wrote:
>
> > All,
> >
> > I've identified a query that is causing deadlocks in our database. The
> > SELECT portion of this query seems to be causing lock escalation -
causing
> > the whole RECORDS table to lock - and causing other processes to
deadlock.
> >
> > I'd like to somehow rewrite this query to decrease the lock escalation.
Does
> > anyone have any ideas as to how I can do this? This query is part of a
> > stored procedure that gets called via an insert trigger on a different
> > table. All fields are integers. 5 variables are inputs to this query -
> > @P_TID, @P_SID, @T_TID, @T_SID, @E_ID.
> >
> > I was thinking of breaking up the query into smaller versions ... but
not
> > sure where to start.
> >
> > I should add that we added option (maxdop 1) to force this query to only
run
> > on one thread - it seems to reduce the deadlocks - but not eliminate
them.
> >
> > Thanks in advance ... SS
> >
> > ------------------------------------------------
> >
> > option (maxdop 1)
> >
> > INSERT INTO RECORDS
> > (
> > P_TID
> > ,P_SID
> > ,T_TID
> > ,T_SID
> > ,E_ID
> > ,PCOUNT
> > )
> > SELECT
> > p1.P_TID
> > ,p1.P_SID
> > ,p2.T_TID
> > ,p2.T_SID
> > ,@E_ID
> > ,p1.PCOUNT*p2.PCOUNT
> > FROM
> > (RECORDS p1 WITH (ROWLOCK)
> > INNER JOIN RECORDS p2 WITH (ROWLOCK)
> > ON p1.T_TID=@P_TID
> > AND p1.T_SID=@P_SID
> > AND p2.P_TID=@T_TID
> > AND p2.P_SID=@T_SID
> > ) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
> > ON p3.P_TID=p1.P_TID
> > AND p3.P_SID=p1.P_SID
> > AND p3.T_TID=p2.T_TID
> > AND p3.T_SID=p2.T_SID
> > AND p3.E_ID=@E_ID
> > WHERE
> > p1.E_ID IN (0, @E_ID)
> > AND p2.E_ID IN (0, @E_ID)
> > AND p3.E_ID IS NULL
> > option (maxdop 1)
> >
> >
> >

"Steph" wrote:

> John - thanks for the suggestion. I did look at the execution plans - and
> saw where some bottlenecks could be. The Index Tuning Wizard suggested
> putting an index on a column that either has the values 0, 1, 2 - so not
> sure how useful the index would be ...
>
> I'm considering doing the select - putting the results into a table variable
> then inserting the values from the table variable. Don't want to add any
> temp tables now - don't want to add more locks on tempdb.
>
> Thanks for suggestions ...
> "John Bell" <jbellnewsposts@hotmail.com> wrote in message
> news:441B4D04-2EF2-4A7F-B131-05B04A63A63A@microsoft.com...
> > Hi Steph
> >
> > How many rows are in Records with E_ID IN (0, @E_ID), you may want to move
> > them into a temporary table. Also/alternatively check the query execution
> > plan to see if adding index will help, you may want to try the Index
> tuning
> > wizard to see if it comes up with anything.
> >
> > John
> >
> >
> > "Steph" wrote:
> >
> > > All,
> > >
> > > I've identified a query that is causing deadlocks in our database. The
> > > SELECT portion of this query seems to be causing lock escalation -
> causing
> > > the whole RECORDS table to lock - and causing other processes to
> deadlock.
> > >
> > > I'd like to somehow rewrite this query to decrease the lock escalation.
> Does
> > > anyone have any ideas as to how I can do this? This query is part of a
> > > stored procedure that gets called via an insert trigger on a different
> > > table. All fields are integers. 5 variables are inputs to this query -
> > > @P_TID, @P_SID, @T_TID, @T_SID, @E_ID.
> > >
> > > I was thinking of breaking up the query into smaller versions ... but
> not
> > > sure where to start.
> > >
> > > I should add that we added option (maxdop 1) to force this query to only
> run
> > > on one thread - it seems to reduce the deadlocks - but not eliminate
> them.
> > >
> > > Thanks in advance ... SS
> > >
> > > ------------------------------------------------
> > >
> > > option (maxdop 1)
> > >
> > > INSERT INTO RECORDS
> > > (
> > > P_TID
> > > ,P_SID
> > > ,T_TID
> > > ,T_SID
> > > ,E_ID
> > > ,PCOUNT
> > > )
> > > SELECT
> > > p1.P_TID
> > > ,p1.P_SID
> > > ,p2.T_TID
> > > ,p2.T_SID
> > > ,@E_ID
> > > ,p1.PCOUNT*p2.PCOUNT
> > > FROM
> > > (RECORDS p1 WITH (ROWLOCK)
> > > INNER JOIN RECORDS p2 WITH (ROWLOCK)
> > > ON p1.T_TID=@P_TID
> > > AND p1.T_SID=@P_SID
> > > AND p2.P_TID=@T_TID
> > > AND p2.P_SID=@T_SID
> > > ) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
> > > ON p3.P_TID=p1.P_TID
> > > AND p3.P_SID=p1.P_SID
> > > AND p3.T_TID=p2.T_TID
> > > AND p3.T_SID=p2.T_SID
> > > AND p3.E_ID=@E_ID
> > > WHERE
> > > p1.E_ID IN (0, @E_ID)
> > > AND p2.E_ID IN (0, @E_ID)
> > > AND p3.E_ID IS NULL
> > > option (maxdop 1)
> > >
> > >
> > >
>
>