June 29, 2010 at 5:15 am
Hi,
please help me in analyzing this deadlock...
what is causing the deadlock(i mean which query/insert statement is causing the deadlock)
i'm not sure whether this is an xml or xdl file.
i got this deadlock info from my colleague...
.....my SSMS/SQL Profiler failed to open saying "file may be corrupted"
There are 2 triggers on REQUIREMENT table:
1. REQ_OnAllPHI is an AFTER INSERT, UPDATE, DELETE trigger
2. REQ_OnInsertUpdate is an FOR INSERT, UPDATE trigger
1st trigger updates PHITracking table
2nd trigger updates MVR_Header table by calling a stored procedure.
--Deadlock info
<?xml version="1.0" encoding="iso-8859-1" ?>
- <deadlock-list>
- <deadlock victim="processba9f2328">
- <process-list>
- <process id="processbaf978" taskpriority="0" logused="44588" waitresource="PAGE: 7:1:442657" waittime="3640" ownerId="390592572" transactionname="implicit_transaction" lasttranstarted="2010-06-25T12:21:41.373" XDES="0x93c50a10" lockMode="S" schedulerid="1" kpid="5212" status="suspended" spid="298" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2010-06-25T12:21:50.073" lastbatchcompleted="2010-06-25T12:21:50.063" clientapp="DTLD" hostname="NLGPNBAPRC04" hostpid="604" loginname="hxload" isolationlevel="read committed (2)" xactid="390592572" currentdb="7" lockTimeout="4294967295" clientoption1="537002016" clientoption2="128026">
- <executionStack>
<frame procname="NEWBFAST.dbo.nba_request_MVR" line="117" stmtstart="8564" stmtend="9040" sqlhandle="0x0300070056ff3136045c75002a9400000100000000000000">if exists ( select * from mvr_headers where mvrhz_state = @state and mvrhz_license_no = @license and datediff(day, isnull(mvrh_sent_dt, mvrh_insert_dt), getdate()) < 180)</frame>
<frame procname="NEWBFAST.dbo.REQ_OnInsertUpdate" line="122" stmtstart="11470" stmtend="11574" sqlhandle="0x0300070027095b73958917018f9d00000000000000000000">exec nba_request_MVR @caseID, 'REQUIREMENTS'</frame>
<frame procname="adhoc" line="1" stmtstart="152" sqlhandle="0x020000009e7edb014f082e4672455d09c8bcd4d7f9ae0669">INSERT INTO [REQUIREMENT]([SATISFY_WAIVED_IND],[PHYSICIAN_NAME],[SATISFIED_DT],[REPEAT_DT],[ORDER_DT],[REQUIREMENT_CODE],[CASE_POL_NO],[REQ_SEQ_NO],[REQUIREMENT_ID]) values(NULL,NULL,NULL,NULL,CONVERT([datetime],@1,0),@2,@3,CONVERT([smallint],@4,0),@5)</frame>
<frame procname="adhoc" line="1" sqlhandle="0x020000008743df207a4c46d3b47f5ab7025ecdd698a181ec">insert into REQUIREMENT (SATISFY_WAIVED_IND, PHYSICIAN_NAME, SATISFIED_DT, REPEAT_DT, ORDER_DT, REQUIREMENT_CODE, CASE_POL_NO, REQ_SEQ_NO, REQUIREMENT_ID) values(NULL, NULL, NULL, NULL, convert(datetime, '06/25/2010'), '#MVR', 'LS022718900', convert(smallint, '6'), 9757027)</frame>
</executionStack>
<inputbuf>insert into REQUIREMENT (SATISFY_WAIVED_IND, PHYSICIAN_NAME, SATISFIED_DT, REPEAT_DT, ORDER_DT, REQUIREMENT_CODE, CASE_POL_NO, REQ_SEQ_NO, REQUIREMENT_ID) values(NULL, NULL, NULL, NULL, convert(datetime, '06/25/2010'), '#MVR', 'LS022718900', convert(smallint, '6'), 9757027)</inputbuf>
</process>
- <process id="processba9f2328" taskpriority="0" logused="38644" waitresource="KEY: 7:72057594043564032 (6701a6ec5e14)" waittime="4812" ownerId="390596343" transactionname="implicit_transaction" lasttranstarted="2010-06-25T12:21:44.750" XDES="0x1271406c0" lockMode="U" schedulerid="2" kpid="3720" status="suspended" spid="441" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2010-06-25T12:21:48.970" lastbatchcompleted="2010-06-25T12:21:48.950" clientapp="DTLD" hostname="NLGPNBAPRC04" hostpid="2192" loginname="hxload" isolationlevel="read committed (2)" xactid="390596343" currentdb="7" lockTimeout="4294967295" clientoption1="673316896" clientoption2="128058">
- <executionStack>
<frame procname="NEWBFAST.dbo.REQ_OnAllPHI" line="47" stmtstart="4646" stmtend="5012" sqlhandle="0x0300070046112615960d4b000b9d00000000000000000000">update PHITracking set status = dbo.PHIStatus(ptID) where ptID in (select dbo.PHITrackingID(case_pol_no, requirement_code) from deleted)</frame>
<frame procname="NEWBFAST.dbo.REQ_OnInsertUpdate" line="439" stmtstart="43912" stmtend="44594" sqlhandle="0x0300070027095b73958917018f9d00000000000000000000">update requirement set orderOn = getdate() where requirement_id in ( select i.requirement_id from inserted as i left join deleted as d on d.requirement_id = i.requirement_id where i.ORDER_DT is not null and d.ORDER_DT is null )</frame>
<frame procname="adhoc" line="1" stmtstart="152" sqlhandle="0x020000009e7edb014f082e4672455d09c8bcd4d7f9ae0669">INSERT INTO [REQUIREMENT]([SATISFY_WAIVED_IND],[PHYSICIAN_NAME],[SATISFIED_DT],[REPEAT_DT],[ORDER_DT],[REQUIREMENT_CODE],[CASE_POL_NO],[REQ_SEQ_NO],[REQUIREMENT_ID]) values(NULL,NULL,NULL,NULL,CONVERT([datetime],@1,0),@2,@3,CONVERT([smallint],@4,0),@5)</frame>
<frame procname="adhoc" line="1" sqlhandle="0x0200000056bf822cab156351234c60abde75a857267d717d">insert into REQUIREMENT (SATISFY_WAIVED_IND, PHYSICIAN_NAME, SATISFIED_DT, REPEAT_DT, ORDER_DT, REQUIREMENT_CODE, CASE_POL_NO, REQ_SEQ_NO, REQUIREMENT_ID) values(NULL, NULL, NULL, NULL, convert(datetime, '06/25/2010'), '?8027', 'LS022719000', convert(smallint, '3'), 9757025)</frame>
</executionStack>
<inputbuf>insert into REQUIREMENT (SATISFY_WAIVED_IND, PHYSICIAN_NAME, SATISFIED_DT, REPEAT_DT, ORDER_DT, REQUIREMENT_CODE, CASE_POL_NO, REQ_SEQ_NO, REQUIREMENT_ID) values(NULL, NULL, NULL, NULL, convert(datetime, '06/25/2010'), '?8027', 'LS022719000', convert(smallint, '3'), 9757025)</inputbuf>
</process>
</process-list>
- <resource-list>
- <pagelock fileid="1" pageid="442657" dbid="7" objectname="NEWBFAST.dbo.MVR_HEADERS" id="lockb73ad000" mode="IX" associatedObjectId="317629419290624">
- <owner-list>
<owner id="processba9f2328" mode="IX" />
</owner-list>
- <waiter-list>
<waiter id="processbaf978" mode="S" requestType="wait" />
</waiter-list>
</pagelock>
- <keylock hobtid="72057594043564032" dbid="7" objectname="NEWBFAST.dbo.PHITracking" indexname="CaseRequirement" id="lock16be7c680" mode="X" associatedObjectId="72057594043564032">
- <owner-list>
<owner id="processbaf978" mode="X" />
</owner-list>
- <waiter-list>
<waiter id="processba9f2328" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
</deadlock-list>
Thanks,
Santhosh
June 29, 2010 at 5:48 am
Can you post the schema of the table, all indexes and the trigger definitions?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2010 at 6:01 am
GilaMonster (6/29/2010)
Can you post the schema of the table, all indexes and the trigger definitions?
Schema of table and indexes & triggers are attached.
Thanks,
Santhosh
June 30, 2010 at 8:41 am
Hi,
any updates please.
i know here cursors have been used extensively but need to know where i should modify.
the deadlock happened when two INSERTs occured on a table.
Thanks,
KB
Thanks,
Santhosh
June 30, 2010 at 12:00 pm
Here is a deadlock analyzing script I created some time ago... just save off your deadlock xml info to a file, and set the filename in the code (in the OpenRowset function) to that location. This might give you some more information that might help you out a bit.
if OBJECT_ID('tempdb..#deadlock') IS NOT NULL DROP TABLE #deadlock
CREATE TABLE #deadlock (TextData varchar(max))
INSERT INTO #deadlock
select * from OpenRowset(BULK N'C:\\Desktop\Deadlock.txt', SINGLE_BLOB) x
-- ^ SET TO YOUR PATH HERE
;with CTE as
(
select -- get the raw trace data, add a rowid and convert deadlock graph to xml
[RowID] = ROW_NUMBER() OVER (ORDER BY (SELECT 0)), -- assign a row number to each deadlock
[DeadlockGraph] = convert(xml, TextData),
*
from #deadlock
)
, CTE2 AS
(
select -- get information about the process
[DeadlockID] = CTE.RowID,
[DeadlockGraph],
[Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
[Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = Deadlock.Process.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end,
[LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
[Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
[ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
[HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
[LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
[TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
[InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)'),
[ProcessID] = Deadlock.Process.value('@id', 'varchar(50)'),
[SPID] = Deadlock.Process.value('@spid','int'),
[SBID] = Deadlock.Process.value('@sbid','int'),
[ECID] = Deadlock.Process.value('@ecid','int')
from CTE
CROSS APPLY CTE.[DeadlockGraph].nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
)
, ObjectLock AS
(
select -- get information about all locks
[DeadlockID] = CTE.RowID,
[ObjectName] = ObjectLock.Process.value('../../@objectname', 'varchar(200)'),
[ProcessID] = ObjectLock.Process.value('@id', 'varchar(200)')
from CTE
CROSS APPLY CTE.[DeadlockGraph].nodes('/deadlock-list/deadlock/resource-list/*/owner-list/owner') as ObjectLock(Process)
)
, KeyLock AS
(
select -- get information about key locks (includes indexes)
[DeadlockID] = CTE.RowID,
[ObjectName] = KeyLock.Process.value('../../@objectname', 'varchar(200)') + '.' +
KeyLock.Process.value('../../@indexname', 'varchar(200)'), -- get the index name also
[ProcessID] = KeyLock.Process.value('@id', 'varchar(200)')
from CTE
CROSS APPLY CTE.[DeadlockGraph].nodes('/deadlock-list/deadlock/resource-list/keylock/owner-list/owner') as KeyLock(Process)
)
select -- now put this all together
CTE2.[DeadlockID],
CTE2.[SPID],
CTE2.[SBID],
CTE2.[ECID],
--[DeadlockTime],
[DeadlockGraph],
CTE2.[ProcessID],
[Victim],
[LockedObject] = coalesce(KeyLock.ObjectName, ObjectLock.ObjectName),
[Procedure],
[LockMode],
[Code],
[ClientApp],
[HostName],
[LoginName],
[TransactionTime],
[InputBuffer]
from CTE2
LEFT OUTER JOIN KeyLock ON KeyLock.ProcessID = CTE2.ProcessID and KeyLock.DeadlockID = CTE2.DeadlockID
LEFT OUTER JOIN ObjectLock ON ObjectLock.ProcessID = CTE2.ProcessID and ObjectLock.DeadlockID = CTE2.DeadlockID
ORDER BY CTE2.DeadlockID DESC
If this information is in a trace file, here is a link to a deadlock analyzing script[/url] that I put up here a year or so ago - it might help you out a bit.
Edit: I see in the deadlock info you supplied is an expression in the where clause of DateDiff() < 180. You will need to change the "<" to "& lt;" (remove the space - added to show properly in the post)) in order for this code to work. When you do this, running the code will show you that you are locking on the NEWBFAST.dbo.MVR_HEADERS table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 30, 2010 at 12:51 pm
KBSK (6/29/2010)
Hi,please help me in analyzing this deadlock...
what is causing the deadlock(i mean which query/insert statement is causing the deadlock)
Is this the information that you're looking for?
process 1:
INPUT BUFFER: insert into REQUIREMENT (SATISFY_WAIVED_IND, PHYSICIAN_NAME, SATISFIED_DT, REPEAT_DT, ORDER_DT, REQUIREMENT_CODE, CASE_POL_NO, REQ_SEQ_NO, REQUIREMENT_ID) values(NULL, NULL, NULL, NULL, convert(datetime, '06/25/2010'), '#MVR', 'LS022718900', convert(smallint, '6'), 9757027)
Code being run: if exists ( select * from mvr_headers where mvrhz_state = @state and mvrhz_license_no = @license and datediff(day, isnull(mvrh_sent_dt, mvrh_insert_dt), getdate()) < 180)
Procedure: NEWBFAST.dbo.nba_request_MVR
Locked object: NEWBFAST.dbo.PHITracking.CaseRequirement
process 2: (victim)
INPUT BUFFER: insert into REQUIREMENT (SATISFY_WAIVED_IND, PHYSICIAN_NAME, SATISFIED_DT, REPEAT_DT, ORDER_DT, REQUIREMENT_CODE, CASE_POL_NO, REQ_SEQ_NO, REQUIREMENT_ID) values(NULL, NULL, NULL, NULL, convert(datetime, '06/25/2010'), '?8027', 'LS022719000', convert(smallint, '3'), 9757025)
Code being run: update PHITracking set status = dbo.PHIStatus(ptID) where ptID in (select dbo.PHITrackingID(case_pol_no, requirement_code) from deleted)
Procedure:NEWBFAST.dbo.REQ_OnAllPHI
Locked object: NEWBFAST.dbo.MVR_HEADERS
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 30, 2010 at 2:50 pm
WayneS (6/30/2010)
KBSK (6/29/2010)
Hi,please help me in analyzing this deadlock...
what is causing the deadlock(i mean which query/insert statement is causing the deadlock)
Is this the information that you're looking for?
process 1:
INPUT BUFFER:
insert into REQUIREMENT (SATISFY_WAIVED_IND, PHYSICIAN_NAME, SATISFIED_DT, REPEAT_DT, ORDER_DT, REQUIREMENT_CODE, CASE_POL_NO, REQ_SEQ_NO, REQUIREMENT_ID) values(NULL, NULL, NULL, NULL, convert(datetime, '06/25/2010'), '#MVR', 'LS022718900', convert(smallint, '6'), 9757027)Code being run:
if exists ( select * from mvr_headers where mvrhz_state = @state and mvrhz_license_no = @license and datediff(day, isnull(mvrh_sent_dt, mvrh_insert_dt), getdate()) < 180)Procedure: NEWBFAST.dbo.nba_request_MVR
Locked object: NEWBFAST.dbo.PHITracking.CaseRequirement
process 2: (victim)
INPUT BUFFER:
insert into REQUIREMENT (SATISFY_WAIVED_IND, PHYSICIAN_NAME, SATISFIED_DT, REPEAT_DT, ORDER_DT, REQUIREMENT_CODE, CASE_POL_NO, REQ_SEQ_NO, REQUIREMENT_ID) values(NULL, NULL, NULL, NULL, convert(datetime, '06/25/2010'), '?8027', 'LS022719000', convert(smallint, '3'), 9757025)Code being run:
update PHITracking set status = dbo.PHIStatus(ptID) where ptID in (select dbo.PHITrackingID(case_pol_no, requirement_code) from deleted)Procedure:NEWBFAST.dbo.REQ_OnAllPHI
Locked object: NEWBFAST.dbo.MVR_HEADERS
yes, i was looking for this information.... here i got it clearly.
In process 1: ------> 1st INSERT to requirement table
procedure: nba_request_MVR -----> this procedure is called from REQ_OnInsertUpdate trigger (1st trigger)
Locked Object: PHITracking.CaseRequirement(index) ------> this table is updated from the REQ_OnAllPHI trigger(2nd trigger)
In process 2: ------> 2nd INSERT to requirement table
procedure: REQ_OnAllPHI -----> this is trying to update PHITracking table (LOCK IS HELD BY 1st INSERT above - in process 1) -------> this is 2nd trigger.
Locked Object: MVR_HEADERS ------> this table is updated from the REQ_OnInsertUpdate trigger(1st trigger)
Final note: Because of trigger firing in reverse order did this deadlock happen or something else ?
Thanks for the reply,
KB
Thanks,
Santhosh
June 30, 2010 at 3:30 pm
Heh... almost 1000 lines of trigger code with cursors and multiple table updates with some pretty nasty joins and you wonder why there are deadlocks occurring. My suggestion would be to get rid of the RBAR and the single column updates and shorten the triggers up for both performance and duration. I know... sorry... I just can't bring myself to rewritting over a 1000 lines of code for free but don't mind giving a little advice on what to change. 😉
--Jeff Moden
June 30, 2010 at 3:57 pm
In looking through your triggers, I see quite a bit of code that can be consolidated, making one update statement instead of ~10. I'll post an example of this shortly; you should be able to take it and continue on with the consolidation from there. As Jeff stated, I won't re-write those huge triggers (well, not for free anyway), but I'll show you how to do it.
I also see several unnecessary cursors. You might want to look at the articles by RBarryYoung[/url] on "15 ways to lose your cursors".
And finally, I see several calls to the user function dbo.ufUserID(NULL). Since it's always passing a NULL, what is the output of this? Will it always return the same thing for NULL? It might be better to do:
declare @ufUserID varchar(20);
set @ufUserID = dbo.ufUserID(NULL);
and then in your code, just reference the variable. This would call the function just once, instead of multiple times.
Let's attack these issues, and see how this helps.
Also, can you run a trace on one (just one) of these inserts, and post that execution plan? I want to ensure that you have proper indexes on all of the other tables that you're selecting from. If you end up scanning one of those, that could end up being the culprit by slowing down the process.
I'll have a follow-up post later tonight (when I get a chance) with some specific guidance.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 30, 2010 at 9:20 pm
First, lets look at consolidating those update statement. As an example, your first trigger has these individual update statements:
-- set or reset submittedBy based on startedOn action
if update(submittedOn)
begin
update PHITracking set submittedBy = null
where submittedOn is null
and submittedby is not null
and ptID in (select ptID from inserted)
update PHITracking set submittedBy = dbo.ufUserID(NULL)
where submittedOn is not null
and submittedby is null
and ptID in (select ptID from inserted)
end
-- set or reset completedBy based on startedOn action
if update(completedOn)
begin
update PHITracking set completedBy = null, assignedTo = null
where completedOn is null
and completedBy is not null
and ptID in (select ptID from inserted)
update PHITracking set completedBy = dbo.ufUserID(NULL), assignedTo = dbo.ufUserID(NULL)
where completedOn is not null
and completedBy is null
and ptID in (select ptID from inserted)
end
-- set or reset cancelledOn based on cancelledBy action
if update(cancelledOn)
begin
update PHITracking set cancelledBy = null
where cancelledOn is null
and cancelledBy is not null
and ptID in (select ptID from inserted)
update PHITracking set cancelledBy = dbo.ufUserID(NULL)
where cancelledOn is not null
and cancelledBy is null
and ptID in (select ptID from inserted)
end
Here, you are running up to 6 update statements. This can be replaced with this code:
declare @ufUserID int;
set @ufUserID = dbo.ufUserID(NULL);
UPDATE p
SET submittedBy = CASE WHEN NOT UPDATE(submittedON) THEN submittedBy
WHEN submittedOn IS NULL AND submittedBy IS NOT NULL THEN NULL
WHEN submittedOn IS NOT NULL and submittedBy IS NULL THEN @ufUserID
END,
completedBy = CASE WHEN NOT UPDATE(completedOn) THEN completedBy
WHEN completedOn IS NULL AND completedBy IS NOT NULL THEN NULL
WHEN completedOn IS NOT NULL and completedBy IS NULL then @ufUserID
END,
assignedTo = CASE WHEN NOT update(completedOn) THEN assignedTo
WHEN completedOn IS NULL AND completedBy IS NOT NULL THEN NULL
WHEN completedOn IS NOT NULL and completedBy IS NULL then @ufUserID
END,
cancelledBy = CASE WHEN NOT update(cancelledOn) THEN cancelledBy
WHEN cancelledOn IS NULL AND cancelledBy IS NOT NULL THEN NULL
WHEN cancelledOn IS NOT NULL and cancelledBy IS NULL then @ufUserID
END
FROM dbo.PHITracking p
JOIN inserted i
ON i.ptID = p.ptID;
So, first notice that instead of all the multiple calls to dbo.ufUserID(NULL), I'm getting that value once and storing it into a variable.
Next, notice how there is just one update statement. Each column to be updated has a case statement that determines what it's value should be. You just need to continue this logic for the remaining update statements in that trigger.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 30, 2010 at 11:26 pm
Jeff Moden (6/30/2010)
... but don't mind giving a little advice on what to change. 😉
The entire thing? 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2010 at 6:46 am
GilaMonster (6/30/2010)
Jeff Moden (6/30/2010)
... but don't mind giving a little advice on what to change. 😉The entire thing? 😉
Heh... z'actly. 😛
--Jeff Moden
July 1, 2010 at 6:52 am
Jeff Moden (7/1/2010)
GilaMonster (6/30/2010)
Jeff Moden (6/30/2010)
... but don't mind giving a little advice on what to change. 😉The entire thing? 😉
Heh... z'actly. 😛
Hi All,
thanks for your valuable suggestions.
i know cursors are bad, but here the situation is that there are many things that are being done inside the loop.
Can all of them be written as set based query?
(I read the article 15ways to lose cursors)
I am eager to have your advice on some of the major flaws in this trigger.
(one of the thing was several update stmts are turned into single update stmt using CASE stmt - by Waynes.....thank you)
Thanks,
KB
Thanks,
Santhosh
July 1, 2010 at 10:11 am
WayneS (6/30/2010)
Also, can you run a trace on one (just one) of these inserts, and post that execution plan? I want to ensure that you have proper indexes on all of the other tables that you're selecting from. If you end up scanning one of those, that could end up being the culprit by slowing down the process.I'll have a follow-up post later tonight (when I get a chance) with some specific guidance.
I just ran the 1st insert statement with Actual Execution plan set to on and I was totally surprised to see a error message:
"The query has exceeded the maximum number of result sets that can be displayed in the Execution Plan pane. Only the first 250 result sets are displayed in the Execution Plan pane."
Attached is execution plan(upto 250 result sets).
Thanks,
KB
Edited: I am sorry, only one result set was saved in the execution plan.
Can I know how to save all 250 sets of execution plan in a single file?
Thanks,
Santhosh
July 1, 2010 at 11:19 am
KBSK (7/1/2010)
I just ran the 1st insert statement with Actual Execution plan set to on and I was totally surprised to see a error message:"The query has exceeded the maximum number of result sets that can be displayed in the Execution Plan pane. Only the first 250 result sets are displayed in the Execution Plan pane."
Attached is execution plan(upto 250 result sets).
Thanks,
KB
Edited: I am sorry, only one result set was saved in the execution plan.
Can I know how to save all 250 sets of execution plan in a single file?
Over 250 queries being executed in the process of that 1 insert? Geez, it's no wonder that you're running into deadlock issues.
I right-click the execution plan, and select "Save Execution Plan As..."
FYI, I was looking at the modified update statement I supplied above. You might need an ELSE condition in each of those CASE statements (set to the field being updated, like the first WHEN does):
declare @ufUserID int;
set @ufUserID = dbo.ufUserID(NULL);
UPDATE p
SET submittedBy = CASE WHEN NOT UPDATE(submittedON) THEN submittedBy
WHEN submittedOn IS NULL AND submittedBy IS NOT NULL THEN NULL
WHEN submittedOn IS NOT NULL and submittedBy IS NULL THEN @ufUserID
ELSE submittedBy
END,
completedBy = CASE WHEN NOT UPDATE(completedOn) THEN completedBy
WHEN completedOn IS NULL AND completedBy IS NOT NULL THEN NULL
WHEN completedOn IS NOT NULL and completedBy IS NULL then @ufUserID
ELSE completedBy
END,
assignedTo = CASE WHEN NOT update(completedOn) THEN assignedTo
WHEN completedOn IS NULL AND completedBy IS NOT NULL THEN NULL
WHEN completedOn IS NOT NULL and completedBy IS NULL then @ufUserID
ELSE assignedTo
END,
cancelledBy = CASE WHEN NOT update(cancelledOn) THEN cancelledBy
WHEN cancelledOn IS NULL AND cancelledBy IS NOT NULL THEN NULL
WHEN cancelledOn IS NOT NULL and cancelledBy IS NULL then @ufUserID
ELSE cancelledBy
END
FROM dbo.PHITracking p
JOIN inserted i
ON i.ptID = p.ptID;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply