August 2, 2010 at 4:54 am
Hi ,
we uses a view of two select queries with no.of joins and these two quies retriving data using UNION all.
it clousing deadlock
how i optimize it....i checked indexex are currect.
how i decide it is a poor query.....
------------------this is the view
CREATE view [INMISPR01].[ANH_Cancellation_Report] as
--set dateformat 'dmy'
SELECT RecCustomerInformation.PolicyNo as [Policy No], RecCustomerInformation.CustomerName as [Policy Holder],
RecDataEntry.CCNo as [CC No.], RecDataEntry.RefundAmount, [Billed Date]=cast(RecDataEntry.BilledDt as nvarchar(12)),
[Pol Eff. Date]=cast(RecCustomerInformation.PolicyEffectiveDate as nvarchar(12)),
Premium=isnull(RecCustomerInformation.Premium,0),RecDataEntry.CancelledEmma, [Cancel Date]=
cast(RecDataEntry.cancellationDate as nvarchar(12)),RecDataEntry.EndorsementDate ,
[Refund Date]=cast(RecDataEntry.Refunddate as nvarchar(12)), [Sec Rfnd Amt]=RecDataEntry.SndRefundAmount,
[Refund Date2]=cast(RecDataEntry.SndRefundDate as nvarchar(12)), RecDataEntry.Remarks,[HSBC Date]=
cast(RecDataEntry.HSBCRecDt as nvarchar(12)), RecDataEntry.HSBCBatchNo, RecDataEntry.RefundAmount as Amount,
CAST( RecDataEntry.BilledDt AS nvarchar(12)) as [Transaction Date],CAST( ReccustomerInformation.PolicyPrintDate AS
nvarchar(12)) as [Policy Print Date], DocumentsRecDate, null as VLIDATE
FROM RecDataEntry INNER
JOIN RecCustomerInformation ON RecDataEntry.PolicyNo = RecCustomerInformation.PolicyNo
Inner join RecCallDetails on reccustomerInformation.TxnrecID=ReccallDetails.Txnrecid
where
--RecDataEntry.EndorsementDate between '10/14/2009' and '10/14/2009' and
RecDataEntry.TxnID in ( select max(TxnID)
from RecDataEntry group by PolicyNo)-- and RecDataEntry.DocumentsRecDate is not null
and reccalldetails.TxnID in ( select max(TxnID)
from reccalldetails group by PolicyNo)-- and RecDataEntry.DocumentsRecDate is not null
--Comment By Priya Arora dated 0n 14-10-09
and reccalldetails.policyno not in(select distinct policyno from reccalldetails)
--where vlidate is not null)
--order by RecDataEntry.policyno
--Comment By Priya Arora dated on 14-10-09
union all
SELECT RecCustomerInformation.PolicyNo as [Policy No], RecCustomerInformation.CustomerName as [Policy Holder],
RecDataEntry.CCNo as [CC No.], RecDataEntry.RefundAmount, [Billed Date]=cast(RecDataEntry.BilledDt as nvarchar(12)),
[Pol Eff. Date]=cast(RecCustomerInformation.PolicyEffectiveDate as nvarchar(12)),
Premium=isnull(RecCustomerInformation.Premium,0),RecDataEntry.CancelledEmma, [Cancel Date]=
cast(RecDataEntry.cancellationDate as nvarchar(12)), RecDataEntry.EndorsementDate ,
[Refund Date]=cast(RecDataEntry.Refunddate as nvarchar(12)), [Sec Rfnd Amt]=RecDataEntry.SndRefundAmount,
[Refund Date2]=cast(RecDataEntry.SndRefundDate as nvarchar(12)), RecDataEntry.Remarks,[HSBC Date]=
cast(RecDataEntry.HSBCRecDt as nvarchar(12)), RecDataEntry.HSBCBatchNo, RecDataEntry.RefundAmount as Amount,
CAST( RecDataEntry.BilledDt AS nvarchar(12)) as [Transaction Date],CAST( ReccustomerInformation.PolicyPrintDate AS
nvarchar(12)) as [Policy Print Date], DocumentsRecDate, VLIDATE
FROM RecDataEntry INNER
JOIN RecCustomerInformation ON RecDataEntry.PolicyNo = RecCustomerInformation.PolicyNo
Inner join RecCallDetails on reccustomerInformation.TxnrecID=ReccallDetails.Txnrecid
where
--RecDataEntry.EndorsementDate between '15/05/2007' and '15/05/2007' and
RecDataEntry.TxnID in ( select max(TxnID)
from RecDataEntry group by PolicyNo)-- and RecDataEntry.DocumentsRecDate is not null
and reccalldetails.TxnID in ( select min(TxnID)
from reccalldetails
--where VLIDATE is not Null
group by policyno)-- and RecDataEntry.DocumentsRecDate is not null
--order by RecDataEntry.policyno
Regards,
Shivrudra W
August 2, 2010 at 5:33 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
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
August 3, 2010 at 12:15 am
Hi Gail ...this is the wait graph.
08/03/2010 09:43:13,spid14s,Unknown,waiter id=process291f28e0
08/03/2010 09:43:13,spid14s,Unknown,waiter-list
08/03/2010 09:43:13,spid14s,Unknown,owner id=process2885caa8
08/03/2010 09:43:13,spid14s,Unknown,owner-list
08/03/2010 09:43:13,spid14s,Unknown,exchangeEvent id=Port5412700 WaitType=e_waitPortOpen nodeId=7
08/03/2010 09:43:13,spid14s,Unknown,waiter id=process2885caa8 mode=U requestType=wait
08/03/2010 09:43:13,spid14s,Unknown,waiter-list
08/03/2010 09:43:13,spid14s,Unknown,owner id=process291cfe40 mode=U
08/03/2010 09:43:13,spid14s,Unknown,owner-list
08/03/2010 09:43:13,spid14s,Unknown,pagelock fileid=1 pageid=1918424 dbid=5 objectname=CCMIS.dbo.reccalldetails id=lock73b328c0 mode=U associatedObjectId=72057594771079168
08/03/2010 09:43:13,spid14s,Unknown,waiter id=process291cfe40 mode=U requestType=wait
08/03/2010 09:43:13,spid14s,Unknown,waiter-list
08/03/2010 09:43:13,spid14s,Unknown,owner id=process291f28e0 mode=U
08/03/2010 09:43:13,spid14s,Unknown,owner-list
08/03/2010 09:43:13,spid14s,Unknown,pagelock fileid=1 pageid=1918919 dbid=5 objectname=CCMIS.dbo.reccalldetails id=lock5f04e500 mode=U associatedObjectId=72057594771079168
08/03/2010 09:43:13,spid14s,Unknown,resource-list
08/03/2010 09:43:13,spid14s,Unknown,inputbuf
08/03/2010 09:43:13,spid14s,Unknown,Update RecCallDetails set ParentCallID = TxnID<c/>IntDataUploadTimeStamp=DataUploadTimeStamp<c/>CaseType ='FRESH' where CaseType ='FRESHNew'
08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000009c2f80359a1419d5f8f7c313e625d1c2fef66ad4
08/03/2010 09:43:13,spid14s,Unknown,(@1 varchar(8000)<c/>@2 varchar(8000))UPDATE [RecCallDetails] set [ParentCallID] = [TxnID]<c/>[IntDataUploadTimeStamp] = [DataUploadTimeStamp]<c/>[CaseType] = @1 WHERE [CaseType]=@2
08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000000ab0803977df2a1f8921311b0cbe3338ad02c782
08/03/2010 09:43:13,spid14s,Unknown,executionStack
08/03/2010 09:43:13,spid14s,Unknown,process id=process291f28e0 taskpriority=0 logused=10000 waittime=1092 schedulerid=3 kpid=3844 status=suspended spid=90 sbid=0 ecid=3 priority=0
trancount=0 lastbatchstarted=2010-08-03T09:43:04.760 lastbatchcompleted=2010-08-03T09:43:04.730 clientapp=ICIS System hostname=OISWXR5IPCO037 hostpid=868 isolationlevel=read committed (2) xactid=1075601334 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
08/03/2010 09:43:13,spid14s,Unknown,inputbuf
08/03/2010 09:43:13,spid14s,Unknown,Update RecCallDetails set ParentCallID = TxnID<c/>IntDataUploadTimeStamp=DataUploadTimeStamp<c/>CaseType ='FRESH' where CaseType ='FRESHNew'
08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000009c2f80359a1419d5f8f7c313e625d1c2fef66ad4
08/03/2010 09:43:13,spid14s,Unknown,(@1 varchar(8000)<c/>@2 varchar(8000))UPDATE [RecCallDetails] set [ParentCallID] = [TxnID]<c/>[IntDataUploadTimeStamp] = [DataUploadTimeStamp]<c/>[CaseType] = @1 WHERE [CaseType]=@2
08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000000ab0803977df2a1f8921311b0cbe3338ad02c782
08/03/2010 09:43:13,spid14s,Unknown,executionStack
08/03/2010 09:43:13,spid14s,Unknown,process id=process2885caa8 taskpriority=0 logused=0 waitresource=PAGE: 5:1:1918424 waittime=8757 ownerId=1075601334 transactionname=UPDATE lasttranstarted=2010-08-03T09:43:04.760 XDES=0x2dc50c28 lockMode=U schedulerid=1 kpid=3992 status=suspended spid=90 sbid=0 ecid=4 priority=0 trancount=0 lastbatchstarted=2010-08-03T09:43:04.760 lastbatchcompleted=2010-08-03T09:43:04.730 clientapp=ICIS System hostname=OISWXR5IPCO037 hostpid=868 isolationlevel=read committed (2) xactid=1075601334 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
08/03/2010 09:43:13,spid14s,Unknown,Update RecCallDetails set ParentCallID = TxnID<c/>IntDataUploadTimeStamp=DataUploadTimeStamp<c/>CaseType ='FRESH' where CaseType ='FRESHNew'
08/03/2010 09:43:13,spid14s,Unknown,inputbuf
08/03/2010 09:43:13,spid14s,Unknown,Update RecCallDetails set ParentCallID = TxnID<c/>IntDataUploadTimeStamp=DataUploadTimeStamp<c/>CaseType ='FRESH' where CaseType ='FRESHNew'
08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000009c2f80359a1419d5f8f7c313e625d1c2fef66ad4
08/03/2010 09:43:13,spid14s,Unknown,(@1 varchar(8000)<c/>@2 varchar(8000))UPDATE [RecCallDetails] set [ParentCallID] = [TxnID]<c/>[IntDataUploadTimeStamp] = [DataUploadTimeStamp]<c/>[CaseType] = @1 WHERE [CaseType]=@2
08/03/2010 09:43:13,spid14s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000000ab0803977df2a1f8921311b0cbe3338ad02c782
08/03/2010 09:43:13,spid14s,Unknown,executionStack
08/03/2010 09:43:13,spid14s,Unknown,process id=process291cfe40 taskpriority=0 logused=0 waitresource=PAGE: 5:1:1918919 waittime=8440 ownerId=1075600959 transactionname=UPDATE lasttranstarted=2010-08-03T09:43:02.123 XDES=0x439c69d8 lockMode=U schedulerid=6 kpid=6356 status=suspended spid=122 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2010-08-03T09:43:02.123 lastbatchcompleted=2010-08-03T09:43:02.117 clientapp=ICIS System hostname=OISWXR5IPCO047 hostpid=1068 loginname=INMISPR01 isolationlevel=read committed (2) xactid=1075600959 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
08/03/2010 09:43:13,spid14s,Unknown,process-list
08/03/2010 09:43:13,spid14s,Unknown,deadlock victim=process291cfe40
08/03/2010 09:43:13,spid14s,Unknown,deadlock-list
08/03/2010 09:43:13,spid6s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x439C69D8 Mode: U SPID:122 BatchID:0 ECID:0 TaskProxy:(0x383CA354) Value:0x412202c0 Cost:(0/0)
08/03/2010 09:43:13,spid6s,Unknown,Victim Resource Owner:
08/03/2010 09:43:13,spid6s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
08/03/2010 09:43:13,spid6s,Unknown,ResType:ExchangeId Stype:'AND' SPID:90 BatchID:0 ECID:3 TaskProxy:(0x28F4C708) Value:0x291f28e0 Cost:(0/10000)
08/03/2010 09:43:13,spid6s,Unknown,Port: 0x05412700 Xid Slot: 4<c/> Wait Slot: 5<c/> Task: 0x291F28E0<c/> (Producer)<c/> Exchange Wait Type: e_waitPortOpen<c/> Merging: 0
08/03/2010 09:43:13,spid6s,Unknown,Node:3
08/03/2010 09:43:13,spid6s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
08/03/2010 09:43:13,spid6s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x2DC50C28 Mode: U SPID:90 BatchID:0 ECID:4 TaskProxy:(0x28F4C6D8) Value:0x2928bbc0 Cost:(0/0)
08/03/2010 09:43:13,spid6s,Unknown,Requested by:
08/03/2010 09:43:13,spid6s,Unknown,Input Buf: Language Event: Update RecCallDetails set ParentCallID = TxnID<c/>IntDataUploadTimeStamp=DataUploadTimeStamp<c/>CaseType ='FRESH' where CaseType ='FRESHNew'
08/03/2010 09:43:13,spid6s,Unknown,SPID: 122 ECID: 0 Statement Type: UPDATE Line #: 1
08/03/2010 09:43:13,spid6s,Unknown,Owner:0x4CAD7840 Mode: U Flg:0x40 Ref:0 Life:00000001 SPID:122 ECID:0 XactLockInfo: 0x439C6A00
08/03/2010 09:43:13,spid6s,Unknown,Grant List 2:
08/03/2010 09:43:13,spid6s,Unknown,PAGE: 5:1:1918424 CleanCnt:2 Mode:U Flags: 0x3
08/03/2010 09:43:13,spid6s,Unknown,Node:2
08/03/2010 09:43:13,spid6s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
08/03/2010 09:43:13,spid6s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x439C69D8 Mode: U SPID:122 BatchID:0 ECID:0 TaskProxy:(0x383CA354) Value:0x412202c0 Cost:(0/0)
08/03/2010 09:43:13,spid6s,Unknown,Requested by:
08/03/2010 09:43:13,spid6s,Unknown,Input Buf: No Event:
08/03/2010 09:43:13,spid6s,Unknown,SPID: 90 ECID: 3 Statement Type: UPDATE Line #: 1
08/03/2010 09:43:13,spid6s,Unknown,Owner:0x52F9D4E0 Mode: U Flg:0x40 Ref:0 Life:00000001 SPID:90 ECID:3 XactLockInfo: 0x2AA3BD28
08/03/2010 09:43:13,spid6s,Unknown,Grant List 3:
08/03/2010 09:43:13,spid6s,Unknown,PAGE: 5:1:1918919 CleanCnt:2 Mode:U Flags: 0x3
08/03/2010 09:43:13,spid6s,Unknown,Node:1
08/03/2010 09:43:13,spid6s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
08/03/2010 09:43:13,spid6s,Unknown,Wait-for graph
08/03/2010 09:43:13,spid6s,Unknown,Deadlock encountered .... Printing deadlock information
Regards,
Shivrudra W
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply