Hi,
Is it possible to rewrite the following statement in order to avoid multiple scan of the tables:
SELECT
(ACT.ID_ACCES_CLIENT_TYPE * 100000) + 30024 ss_key,
TO_CHAR(( SYSDATE ),'MM/DD/YYYY') date_key,
30024 transtype_key,
ACT.ID_ACCES_CLIENT_TYPE client_acces_d_sskey,
T.ID_MODELE fonct_mobile_d_sskey,
'0' type_mobile_key,
1 MEMBER
FROM ACCES_CLIENT_TYPE ACT, ACCES AC, TYPE_MODELE T
where ACT.FLAG_ACTIF is not null
and NVL(( ACT.DAT_FIN ),( SYSDATE ))> SYSDATE - ((3 + 0)*30)
and AC.ID_ACCES = ACT.ID_ACCES
and AC.FLAG_ACTIF is not null
and T.TAC = AC.TAC_1
AND mod(ACT.ID_ACCES_CLIENT_TYPE, 2) = 1
union all
SELECT
(ACT.ID_ACCES_CLIENT_TYPE * 100000) + 30025 ss_key,
TO_CHAR(( SYSDATE ),'MM/DD/YYYY') date_key,
30025 transtype_key,
ACT.ID_ACCES_CLIENT_TYPE client_acces_d_sskey,
T.ID_MODELE fonct_mobile_d_sskey,
'1' type_mobile_key,
1 MEMBER
FROM ACCES_CLIENT_TYPE ACT, ACCES AC, TYPE_MODELE T
where ACT.FLAG_ACTIF is not null
and NVL(( ACT.DAT_FIN ),( SYSDATE ))> SYSDATE - ((3 + 0)*30)
and AC.ID_ACCES = ACT.ID_ACCES
and AC.FLAG_ACTIF is not null
and T.TAC = AC.TAC_U
AND mod(ACT.ID_ACCES_CLIENT_TYPE, 2) = 1
union all
SELECT
(ACT.ID_ACCES_CLIENT_TYPE * 100000) + 30026 ss_key,
TO_CHAR(( SYSDATE ),'MM/DD/YYYY') date_key,
30026 transtype_key,
ACT.ID_ACCES_CLIENT_TYPE client_acces_d_sskey,
T.ID_MODELE fonct_mobile_d_sskey,
'2' type_mobile_key,
1 MEMBER
FROM ACCES_CLIENT_TYPE ACT, ACCES AC, TYPE_MODELE T
where ACT.FLAG_ACTIF is not null
and NVL(( ACT.DAT_FIN ),( SYSDATE ))> SYSDATE - ((3 + 0)*30)
and AC.ID_ACCES = ACT.ID_ACCES
and AC.FLAG_ACTIF is not null
and T.TAC = AC.TACG_G
AND mod(ACT.ID_ACCES_CLIENT_TYPE, 2) = 1
Thanks for helpAre you assuming that the table scan is not a the optimal access path that your DBMS (whatever it may be) will choose regardless of the number of rows and organization of the tables?|||The query is doing 3 table scan of acces_client_type but maybe this query can be rewrite without union all. But how can i vary in one query type_mobile_key and ss_key ?
Showing posts with label rewriting. Show all posts
Showing posts with label rewriting. Show all posts
Wednesday, March 21, 2012
rewriting Oracle trigger to MSSQL
Hello,
I have this trigger :
CREATE OR REPLACE TRIGGER PLN_TEST
BEFORE INSERT
ON
TEST
REFERENCING
New AS MyNew
Old AS MyOld
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_new_syscode TEST.SYSCODE%TYPE;
BEGIN
if :MyNew.Syscode is null then
PLN_GETNEWID('TEST',v_new_syscode);
commit;
:MyNew.SYSCODE :=v_new_syscode;
end if;
END
;
/
How can I rewrite it to MSSQL
ThanksProviding that your table has a unique record identifier field, the following will do the trick:
create trigger PLN_TEST on TEST for INSERT as
update t set syscode = newid()
from inserted i
inner join TEST t
on i.record_id = t.record_id
where t.syscode is null|||The problem is that i can get my id in oracle in a AUTONOMOUS_TRANSACTION, i think that this can not be done in MSSQL
Is this true.|||The entire UPDATE in proposed trigger is autonomous. Of course, it should be followed by this code to meet the basic programming standards:
if @.@.error != 0 begin
raiserror ('Update failed!', 15, 1)
rollback transaction
end
I have this trigger :
CREATE OR REPLACE TRIGGER PLN_TEST
BEFORE INSERT
ON
TEST
REFERENCING
New AS MyNew
Old AS MyOld
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_new_syscode TEST.SYSCODE%TYPE;
BEGIN
if :MyNew.Syscode is null then
PLN_GETNEWID('TEST',v_new_syscode);
commit;
:MyNew.SYSCODE :=v_new_syscode;
end if;
END
;
/
How can I rewrite it to MSSQL
ThanksProviding that your table has a unique record identifier field, the following will do the trick:
create trigger PLN_TEST on TEST for INSERT as
update t set syscode = newid()
from inserted i
inner join TEST t
on i.record_id = t.record_id
where t.syscode is null|||The problem is that i can get my id in oracle in a AUTONOMOUS_TRANSACTION, i think that this can not be done in MSSQL
Is this true.|||The entire UPDATE in proposed trigger is autonomous. Of course, it should be followed by this code to meet the basic programming standards:
if @.@.error != 0 begin
raiserror ('Update failed!', 15, 1)
rollback transaction
end
Rewriting left joins
Hello,
I am working on a query that has 11 left join statements, some are hitting against reference data that has a small amount of records, whereas others not so small. From a performance standpoint, should I look at rewriting this query, and how would I do so? What is an alternative to left joins; any examples anyone has?
Thanks.
The alternative to a join is a subquery. Google "Join or subquery" for information in which performs better. Loads of different opinions, but it would appear that only testingyourquery inyourenvironment will produce the right answer foryou.|||
bmains:
From a performance standpoint, should I look at rewriting this query
I would say that it depends on whether your query is performing poorly or not. I wouldn't touch it if it isn't broken
Rewriting Insert Statements
Hi Friends,
I have the following set of Insert Statements that calculates sums for various criteria and inserts a row at a time onto my table.
I have a row for every month starting from January with sums for 4 severity levels. So for 12 months that would be 48 Insert Statements and if I want to do this for 4 different types of [EName] that would be 48 * 4 = 192 Insert Statements. Is there a better way to write this. Thanks for your help
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 1)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 2)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 3)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 4)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 1)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 2)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 3)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 4)Maybe something along the lines of
SELECT 'OVERALL' AS [EName], trendmonth AS TrendMonth, SeverityLevel , Sum([Count])
FROM dbo.tbl_Ticket
group by EName, Trendmonth, SeverityLevel|||Works Great!! Thank you very much.
I have the following set of Insert Statements that calculates sums for various criteria and inserts a row at a time onto my table.
I have a row for every month starting from January with sums for 4 severity levels. So for 12 months that would be 48 Insert Statements and if I want to do this for 4 different types of [EName] that would be 48 * 4 = 192 Insert Statements. Is there a better way to write this. Thanks for your help
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 1)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 2)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 3)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'January' and [Severity Level] = 4)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 1 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 1)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 2 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 2)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 3 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 3)
INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 4 , Sum([Count])
FROM dbo.tbl_Ticket
WHERE (TrendMonth LIKE 'February' and [Severity Level] = 4)Maybe something along the lines of
SELECT 'OVERALL' AS [EName], trendmonth AS TrendMonth, SeverityLevel , Sum([Count])
FROM dbo.tbl_Ticket
group by EName, Trendmonth, SeverityLevel|||Works Great!! Thank you very much.
Rewriting a query causing lock escalation
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)
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)
>
>
|||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[vbcol=seagreen]
> wizard to see if it comes up with anything.
> John
>
> "Steph" wrote:
causing[vbcol=seagreen]
deadlock.[vbcol=seagreen]
Does[vbcol=seagreen]
not[vbcol=seagreen]
run[vbcol=seagreen]
them.[vbcol=seagreen]
|||Hi
If you have a significant number of rows look at using a temporary table
rather than a table variable.
John
"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...
> tuning
> causing
> deadlock.
> Does
> not
> run
> them.
>
>
|||Hi
If you are seeing contention on tempdb e.g. lock timeouts on database id 2
check out http://support.microsoft.com/default...b;en-us;328551
John
"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...
> tuning
> causing
> deadlock.
> Does
> not
> run
> them.
>
>
sql
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)
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)
>
>
|||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[vbcol=seagreen]
> wizard to see if it comes up with anything.
> John
>
> "Steph" wrote:
causing[vbcol=seagreen]
deadlock.[vbcol=seagreen]
Does[vbcol=seagreen]
not[vbcol=seagreen]
run[vbcol=seagreen]
them.[vbcol=seagreen]
|||Hi
If you have a significant number of rows look at using a temporary table
rather than a table variable.
John
"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...
> tuning
> causing
> deadlock.
> Does
> not
> run
> them.
>
>
|||Hi
If you are seeing contention on tempdb e.g. lock timeouts on database id 2
check out http://support.microsoft.com/default...b;en-us;328551
John
"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...
> tuning
> causing
> deadlock.
> Does
> not
> run
> them.
>
>
sql
Rewriting a query causing lock escalation
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)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)
>
>|||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)
> >
> >
> >|||Hi
If you have a significant number of rows look at using a temporary table
rather than a table variable.
John
"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)
> > >
> > >
> > >
>
>|||Hi
If you are seeing contention on tempdb e.g. lock timeouts on database id 2
check out http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
John
"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)
> > >
> > >
> > >
>
>
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)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)
>
>|||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)
> >
> >
> >|||Hi
If you have a significant number of rows look at using a temporary table
rather than a table variable.
John
"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)
> > >
> > >
> > >
>
>|||Hi
If you are seeing contention on tempdb e.g. lock timeouts on database id 2
check out http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
John
"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)
> > >
> > >
> > >
>
>
Rewriting a query causing lock escalation
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)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. Do
es
> 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 r
un
> 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)
>
>|||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[vbcol=seagreen]
> wizard to see if it comes up with anything.
> John
>
> "Steph" wrote:
>
causing[vbcol=seagreen]
deadlock.[vbcol=seagreen]
Does[vbcol=seagreen]
not[vbcol=seagreen]
run[vbcol=seagreen]
them.[vbcol=seagreen]|||Hi
If you have a significant number of rows look at using a temporary table
rather than a table variable.
John
"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 variab
le
> 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...
> tuning
> causing
> deadlock.
> Does
> not
> run
> them.
>
>|||Hi
If you are seeing contention on tempdb e.g. lock timeouts on database id 2
check out http://support.microsoft.com/defaul...kb;en-us;328551
John
"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 variab
le
> 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...
> tuning
> causing
> deadlock.
> Does
> not
> run
> them.
>
>
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)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. Do
es
> 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 r
un
> 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)
>
>|||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[vbcol=seagreen]
> wizard to see if it comes up with anything.
> John
>
> "Steph" wrote:
>
causing[vbcol=seagreen]
deadlock.[vbcol=seagreen]
Does[vbcol=seagreen]
not[vbcol=seagreen]
run[vbcol=seagreen]
them.[vbcol=seagreen]|||Hi
If you have a significant number of rows look at using a temporary table
rather than a table variable.
John
"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 variab
le
> 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...
> tuning
> causing
> deadlock.
> Does
> not
> run
> them.
>
>|||Hi
If you are seeing contention on tempdb e.g. lock timeouts on database id 2
check out http://support.microsoft.com/defaul...kb;en-us;328551
John
"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 variab
le
> 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...
> tuning
> causing
> deadlock.
> Does
> not
> run
> them.
>
>
Subscribe to:
Posts (Atom)