October 3, 2012 at 12:41 pm
I have been going through the forums on how to read this graph. I was asked where does a certain query in the graph can be located and have spent a day trying to find where the certain query is located in the database but can find it.
I was trying to find where this query is located:
SELECT TOP (100) PERCENT dbo.vwUserContactList.Domain_ID, dbo.vwUserContactList.DomainName, COUNT(dbo.vwUserContactList.Object_ID)
Any help is appreciated here is the deadlock information
2012-10-01 12:13:48.170 spid6s Deadlock encountered .... Printing deadlock information
2012-10-01 12:13:48.170 spid6s Wait-for graph
2012-10-01 12:13:48.170 spid6s NULL
2012-10-01 12:13:48.170 spid6s Node:1
2012-10-01 12:13:48.170 spid6s PAGE: 5:1:5278728 CleanCnt:2 Mode:IX Flags: 0x3
2012-10-01 12:13:48.170 spid6s Grant List 1:
2012-10-01 12:13:48.170 spid6s Owner:0x000000011DF67F80 Mode: IX Flg:0x40 Ref:0 Life:02000000 SPID:64 ECID:0 XactLockInfo: 0x000000017131E3F0
2012-10-01 12:13:48.170 spid6s SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 141
2012-10-01 12:13:48.170 spid6s Input Buf: RPC Event: Proc [Database Id = 5 Object Id = 2026138659]
2012-10-01 12:13:48.170 spid6s Requested by:
2012-10-01 12:13:48.170 spid6s ResType:LockOwner Stype:'OR'Xdes:0x000000012E242CB0 Mode: S SPID:74 BatchID:0 ECID:20 TaskProxy:(0x000000019B521C50) Value:0xb47cd200 Cost:(0/0)
2012-10-01 12:13:48.170 spid6s NULL
2012-10-01 12:13:48.170 spid6s Node:2
2012-10-01 12:13:48.170 spid6s PAGE: 5:1:1139537 CleanCnt:2 Mode:S Flags: 0x3
2012-10-01 12:13:48.170 spid6s Grant List 1:
2012-10-01 12:13:48.170 spid6s Owner:0x00000000B4E6E240 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:74 ECID:0 XactLockInfo: 0x0000000165F67CB0
2012-10-01 12:13:48.170 spid6s SPID: 74 ECID: 0 Statement Type: SELECT Line #: 1
2012-10-01 12:13:48.170 spid6s Input Buf: Language Event: SELECT TOP (100) PERCENT dbo.vwUserContactList.Domain_ID, dbo.vwUserContactList.DomainName, COUNT(dbo.vwUserContactList.Object_ID) AS AgentCount, SUM(tbOpened.Units) AS UnitsOpened, SUM(tbOpened.Volume) AS VolumeOpened, S
2012-10-01 12:13:48.170 spid6s Requested by:
2012-10-01 12:13:48.170 spid6s ResType:LockOwner Stype:'OR'Xdes:0x000000017131E3B0 Mode: IX SPID:64 BatchID:0 ECID:0 TaskProxy:(0x00000001B8B80538) Value:0x1df6b5c0 Cost:(0/2016)
2012-10-01 12:13:48.170 spid6s NULL
2012-10-01 12:13:48.170 spid6s Node:3
2012-10-01 12:13:48.170 spid6s Port: 0x0000000156697600 Xid Slot: 2, Wait Slot: -1, Task: 0x00000002717782C8, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-10-01 12:13:48.170 spid6s ResType:ExchangeId Stype:'AND' SPID:74 BatchID:0 ECID:15 TaskProxy:(0x000000019B521770) Value:0x717782c8 Cost:(0/10000)
2012-10-01 12:13:48.170 spid6s SPID: 74 ECID: 15 Statement Type: SELECT Line #: 1
2012-10-01 12:13:48.170 spid6s Input Buf: No Event:
2012-10-01 12:13:48.170 spid6s NULL
2012-10-01 12:13:48.170 spid6s Node:4
2012-10-01 12:13:48.170 spid6s Port: 0x0000000156697680 Xid Slot: 3, Wait Slot: -1, Task: 0x000000008F308748, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-10-01 12:13:48.170 spid6s ResType:ExchangeId Stype:'AND' SPID:74 BatchID:0 ECID:17 TaskProxy:(0x000000019B5219B0) Value:0x8f308748 Cost:(0/10000)
2012-10-01 12:13:48.170 spid6s SPID: 74 ECID: 17 Statement Type: SELECT Line #: 1
2012-10-01 12:13:48.170 spid6s Input Buf: No Event:
2012-10-01 12:13:48.170 spid6s NULL
2012-10-01 12:13:48.170 spid6s Node:5
2012-10-01 12:13:48.170 spid6s Port: 0x0000000156697580 Xid Slot: 1, Wait Slot: -1, Task: 0x000000021365EE08, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-10-01 12:13:48.170 spid6s ResType:ExchangeId Stype:'AND' SPID:74 BatchID:0 ECID:16 TaskProxy:(0x000000019B521950) Value:0x1365ee08 Cost:(0/10000)
2012-10-01 12:13:48.170 spid6s SPID: 74 ECID: 16 Statement Type: SELECT Line #: 1
2012-10-01 12:13:48.170 spid6s Input Buf: No Event:
2012-10-01 12:13:48.170 spid6s NULL
2012-10-01 12:13:48.170 spid6s Node:6
2012-10-01 12:13:48.170 spid6s Port: 0x0000000080193200 Xid Slot: 0, Wait Slot: 1, Task: 0x0000000004259708, (Coordinator), Exchange Wait Type: e_waitPortOpen, Merging: 0
2012-10-01 12:13:48.170 spid6s ResType:ExchangeId Stype:'AND' SPID:74 BatchID:0 ECID:0 TaskProxy:(0x00000001B8C4E510) Value:0x4259708 Cost:(0/10000)
2012-10-01 12:13:48.170 spid6s NULL
2012-10-01 12:13:48.170 spid6s Node:7
2012-10-01 12:13:48.170 spid6s Port: 0x0000000156697500 Xid Slot: 0, Wait Slot: -1, Task: 0x000000012191E088, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-10-01 12:13:48.170 spid6s ResType:ExchangeId Stype:'AND' SPID:74 BatchID:0 ECID:14 TaskProxy:(0x000000019B521710) Value:0x2191e088 Cost:(0/10000)
2012-10-01 12:13:48.170 spid6s SPID: 74 ECID: 14 Statement Type: SELECT Line #: 1
2012-10-01 12:13:48.170 spid6s Input Buf: No Event:
2012-10-01 12:13:48.170 spid6s NULL
2012-10-01 12:13:48.170 spid6s Node:8
2012-10-01 12:13:48.170 spid6s Port: 0x0000000156697100 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000271778508, (Consumer), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
2012-10-01 12:13:48.170 spid6s ResType:ExchangeId Stype:'AND' SPID:74 BatchID:0 ECID:15 TaskProxy:(0x000000019B521A70) Value:0x71778508 Cost:(0/10000)
2012-10-01 12:13:48.170 spid6s SPID: 74 ECID: 15 Statement Type: SELECT Line #: 1
2012-10-01 12:13:48.170 spid6s Input Buf: No Event:
2012-10-01 12:13:48.180 spid6s NULL
2012-10-01 12:13:48.180 spid6s Victim Resource Owner:
2012-10-01 12:13:48.180 spid6s ResType:LockOwner Stype:'OR'Xdes:0x000000012E242CB0 Mode: S SPID:74 BatchID:0 ECID:20 TaskProxy:(0x000000019B521C50) Value:0xb47cd200 Cost:(0/0)
2012-10-01 12:13:48.190 spid18s deadlock-list
2012-10-01 12:13:48.190 spid18s deadlock victim=process21365fb88
2012-10-01 12:13:48.190 spid18s process-list
2012-10-01 12:13:48.190 spid18s process id=process21365fb88 taskpriority=0 logused=0 waitresource=PAGE: 5:1:5278728 waittime=3836 ownerId=3726899487 transactionname=SELECT lasttranstarted=2012-10-01T12:13:43.250 XDES=0x12e242cb0 lockMode=S schedulerid=4 kpid=4104 status=suspended spid
2012-10-01 12:13:48.190 spid18s executionStack
2012-10-01 12:13:48.190 spid18s frame procname=adhoc line=1 sqlhandle=0x02000000e65598046e667814baccbd021a3a61b371ebb5e3
2012-10-01 12:13:48.190 spid18s SELECT TOP (100) PERCENT dbo.vwUserContactList.Domain_ID, dbo.vwUserContactList.DomainName, COUNT(dbo.vwUserContactList.Object_ID) AS AgentCount, SUM(tbOpened.Units) AS UnitsOpened, SUM(tbOpened.Volume) AS VolumeOpened, SUM(tbFunded.Units) AS UnitsFun
2012-10-01 12:13:48.190 spid18s inputbuf
2012-10-01 12:13:48.190 spid18s process id=process4258bc8 taskpriority=0 logused=2016 waitresource=PAGE: 5:1:1139537 waittime=4021 ownerId=3726901176 transactionname=UPDATE lasttranstarted=2012-10-01T12:13:44.163 XDES=0x17131e3b0 lockMode=IX schedulerid=2 kpid=3444 status=suspended sp
2012-10-01 12:13:48.190 spid18s executionStack
2012-10-01 12:13:48.190 spid18s frame procname=Brokerview_New.dbo.xgen_tblProcessingFile_Change line=141 stmtstart=7126 stmtend=16496 sqlhandle=0x03000500236cc478a0e1da00d9a000000100000000000000
2012-10-01 12:13:48.190 spid18s UPDATE tblProcessingFile
2012-10-01 12:13:48.190 spid18s SET
2012-10-01 12:13:48.190 spid18s Object_ID = @Object_ID,
2012-10-01 12:13:48.190 spid18s LOSApp_ID = @LOSApp_ID,
2012-10-01 12:13:48.190 spid18s LOSFile_ID = @LOSFile_ID,
2012-10-01 12:13:48.190 spid18s Processor_ID = @Processor_ID,
2012-10-01 12:13:48.190 spid18s LastStatus = @LastStatus,
2012-10-01 12:13:48.190 spid18s LastStatusDate = @LastStatusDate,
2012-10-01 12:13:48.190 spid18s FilePath = @FilePath,
2012-10-01 12:13:48.190 spid18s ProgramName = @ProgramName,
2012-10-01 12:13:48.190 spid18s Rate = @Rate,
2012-10-01 12:13:48.190 spid18s QRate = @QRate,
2012-10-01 12:13:48.190 spid18s RequestedClose = @RequestedClose,
2012-10-01 12:13:48.190 spid18s LOSNote = @LOSNote,
2012-10-01 12:13:48.190 spid18s SubjectAddress = @SubjectAddress,
2012-10-01 12:13:48.190 spid18s SubjectState = @SubjectState,
2012-10-01 12:13:48.190 spid18s Investor = @Investor,
2012-10-01 12:13:48.190 spid18s LenderName = @LenderName,
2012-10-01 12:13:48.190 spid18s CreditReportID = @CreditReportID,
2012-10-01 12:13:48.190 spid18s Prospect_ID = @Prospect_ID,
2012-10-01 12:13:48.190 spid18s Loan_ID = @Loan_ID,
2012-10-01 12:13:48.190 spid18s IsActiveFile = @IsActiveFile,
2012-10-01 12:13:48.190 spid18s Created = @Created,
2012-10-01 12:13:48.190 spid18s Opened = @Opened,
2012-10-01 12:13:48.190 spid18s ToProcessing = @ToProcessing,
2012-10-01 12:13:48.190 spid18s Processing = @Processing,
2012-10-01 12:13:48.190 spid18s Submitted = @Submitted,
2012-10-01 12:13:48.190 spid18s Approved = @Approved,
2012-10-01 12:13:48.190 spid18s ClearToClose = @ClearToClose,
2012-10-01 12:13:48.190 spid18s Documents = @documents,
2012-10-01 12:13:48.190 spid18s DocsSigned = @DocsSigned,
2012-10-01 12:13:48.190 spid18s DocsBack = @DocsBack,
2012-10-01 12:13:48.190 spid18s Funded = @Funded,
2012-10-01 12:13:48.190 spid18s Recorded = @Recorded,
2012-10-01 12:13:48.190 spid18s Purchased = @Purchased,
2012-10-01 12:13:48.190 spid18s Shipped = @Shipped,
2012-10-01 12:13:48.190 spid18s Closed = @Closed,
2012-10-01 12:13:48.190 spid18s Suspended = @Suspended,
2012-10-01 12:13:48.190 spid18s OnHold = @OnHold,
2012-10-01 12:13:48.190 spid18s Denied =
2012-10-01 12:13:48.190 spid18s inputbuf
2012-10-01 12:13:48.190 spid18s Proc [Database Id = 5 Object Id = 2026138659]
2012-10-01 12:13:48.190 spid18s process id=process2717782c8 taskpriority=0 logused=10000 waittime=3829 schedulerid=2 kpid=4972 status=suspended spid=74 sbid=0 ecid=15 priority=0 trancount=0 lastbatchstarted=2012-10-01T12:13:43.250 lastbatchcompleted=2012-10-01T12:13:43.250 clientapp=.
2012-10-01 12:13:48.190 spid18s executionStack
2012-10-01 12:13:48.190 spid18s frame procname=adhoc line=1 sqlhandle=0x02000000e65598046e667814baccbd021a3a61b371ebb5e3
2012-10-01 12:13:48.190 spid18s SELECT TOP (100) PERCENT dbo.vwUserContactList.Domain_ID, dbo.vwUserContactList.DomainName, COUNT(dbo.vwUserContactList.Object_ID) AS AgentCount, SUM(tbOpened.Units) AS UnitsOpened, SUM(tbOpened.Volume) AS VolumeOpened, SUM(tbFunded.Units) AS UnitsFun
2012-10-01 12:13:48.190 spid18s inputbuf
2012-10-01 12:13:48.190 spid18s process id=process8f308748 taskpriority=0 logused=10000 waittime=3829 schedulerid=3 kpid=4400 status=suspended spid=74 sbid=0 ecid=17 priority=0 trancount=0 lastbatchstarted=2012-10-01T12:13:43.250 lastbatchcompleted=2012-10-01T12:13:43.250 clientapp=.N
2012-10-01 12:13:48.190 spid18s executionStack
2012-10-01 12:13:48.190 spid18s frame procname=adhoc line=1 sqlhandle=0x02000000e65598046e667814baccbd021a3a61b371ebb5e3
2012-10-01 12:13:48.190 spid18s SELECT TOP (100) PERCENT dbo.vwUserContactList.Domain_ID, dbo.vwUserContactList.DomainName, COUNT(dbo.vwUserContactList.Object_ID) AS AgentCount, SUM(tbOpened.Units) AS UnitsOpened, SUM(tbOpened.Volume) AS VolumeOpened, SUM(tbFunded.Units) AS UnitsFun
2012-10-01 12:13:48.190 spid18s inputbuf
2012-10-01 12:13:48.190 spid18s process id=process21365ee08 taskpriority=0 logused=10000 waittime=3828 schedulerid=4 kpid=516 status=suspended spid=74 sbid=0 ecid=16 priority=0 trancount=0 lastbatchstarted=2012-10-01T12:13:43.250 lastbatchcompleted=2012-10-01T12:13:43.250 clientapp=.N
2012-10-01 12:13:48.190 spid18s executionStack
2012-10-01 12:13:48.190 spid18s frame procname=adhoc line=1 sqlhandle=0x02000000e65598046e667814baccbd021a3a61b371ebb5e3
2012-10-01 12:13:48.190 spid18s SELECT TOP (100) PERCENT dbo.vwUserContactList.Domain_ID, dbo.vwUserContactList.DomainName, COUNT(dbo.vwUserContactList.Object_ID) AS AgentCount, SUM(tbOpened.Units) AS UnitsOpened, SUM(tbOpened.Volume) AS VolumeOpened, SUM(tbFunded.Units) AS UnitsFun
2012-10-01 12:13:48.190 spid18s inputbuf
2012-10-01 12:13:48.190 spid18s process id=process4259708 taskpriority=0 logused=10000 waittime=4472 schedulerid=2 kpid=4336 status=suspended spid=74 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2012-10-01T12:13:43.250 lastbatchcompleted=2012-10-01T12:13:43.250 clientapp=.Net
2012-10-01 12:13:48.190 spid18s executionStack
2012-10-01 12:13:48.190 spid18s frame procname=adhoc line=1 sqlhandle=0x02000000e65598046e667814baccbd021a3a61b371ebb5e3
2012-10-01 12:13:48.190 spid18s SELECT TOP (100) PERCENT dbo.vwUserContactList.Domain_ID, dbo.vwUserContactList.DomainName, COUNT(dbo.vwUserContactList.Object_ID) AS AgentCount, SUM(tbOpened.Units) AS UnitsOpened, SUM(tbOpened.Volume) AS VolumeOpened, SUM(tbFunded.Units) AS UnitsFun
2012-10-01 12:13:48.190 spid18s inputbuf
2012-10-01 12:13:48.190 spid18s SELECT TOP (100) PERCENT dbo.vwUserContactList.Domain_ID, dbo.vwUserContactList.DomainName, COUNT(dbo.vwUserContactList.Object_ID) AS AgentCount, SUM(tbOpened.Units) AS UnitsOpened, SUM(tbOpened.Volume) AS VolumeOpened, SUM(tbFunded.Units) AS UnitsFun
2012-10-01 12:13:48.190 spid18s process id=process12191e088 taskpriority=0 logused=10000 waittime=3829 schedulerid=1 kpid=3048 status=suspended spid=74 sbid=0 ecid=14 priority=0 trancount=0 lastbatchstarted=2012-10-01T12:13:43.250 lastbatchcompleted=2012-10-01T12:13:43.250 clientapp=.
2012-10-01 12:13:48.190 spid18s executionStack
2012-10-01 12:13:48.190 spid18s frame procname=adhoc line=1 sqlhandle=0x02000000e65598046e667814baccbd021a3a61b371ebb5e3
2012-10-01 12:13:48.190 spid18s SELECT TOP (100) PERCENT dbo.vwUserContactList.Domain_ID, dbo.vwUserContactList.DomainName, COUNT(dbo.vwUserContactList.Object_ID) AS AgentCount, SUM(tbOpened.Units) AS UnitsOpened, SUM(tbOpened.Volume) AS VolumeOpened, SUM(tbFunded.Units) AS UnitsFun
2012-10-01 12:13:48.190 spid18s inputbuf
2012-10-01 12:13:48.190 spid18s process id=process271778508 taskpriority=0 logused=10000 waittime=4218 schedulerid=2 kpid=4696 status=suspended spid=74 sbid=0 ecid=15 priority=0 trancount=0 lastbatchstarted=2012-10-01T12:13:43.250 lastbatchcompleted=2012-10-01T12:13:43.250 clientapp=.
2012-10-01 12:13:48.190 spid18s executionStack
2012-10-01 12:13:48.190 spid18s frame procname=adhoc line=1 sqlhandle=0x02000000e65598046e667814baccbd021a3a61b371ebb5e3
2012-10-01 12:13:48.190 spid18s SELECT TOP (100) PERCENT dbo.vwUserContactList.Domain_ID, dbo.vwUserContactList.DomainName, COUNT(dbo.vwUserContactList.Object_ID) AS AgentCount, SUM(tbOpened.Units) AS UnitsOpened, SUM(tbOpened.Volume) AS VolumeOpened, SUM(tbFunded.Units) AS UnitsFun
2012-10-01 12:13:48.190 spid18s inputbuf
2012-10-01 12:13:48.190 spid18s resource-list
2012-10-01 12:13:48.190 spid18s pagelock fileid=1 pageid=5278728 dbid=5 objectname=Brokerview_New.dbo.tblProcessingFile id=lockb66e6680 mode=IX associatedObjectId=72057594155696128
2012-10-01 12:13:48.190 spid18s owner-list
2012-10-01 12:13:48.190 spid18s owner id=process4258bc8 mode=IX
2012-10-01 12:13:48.190 spid18s waiter-list
2012-10-01 12:13:48.190 spid18s waiter id=process21365fb88 mode=S requestType=wait
2012-10-01 12:13:48.190 spid18s pagelock fileid=1 pageid=1139537 dbid=5 objectname=Brokerview_New.dbo.tblProcessingFile id=lockb61adc00 mode=S associatedObjectId=72057594160414720
2012-10-01 12:13:48.190 spid18s owner-list
2012-10-01 12:13:48.190 spid18s owner id=process4259708 mode=S
2012-10-01 12:13:48.190 spid18s waiter-list
2012-10-01 12:13:48.190 spid18s waiter id=process4258bc8 mode=IX requestType=wait
2012-10-01 12:13:48.190 spid18s exchangeEvent id=Pipe156697600 WaitType=e_waitPipeGetRow nodeId=102
2012-10-01 12:13:48.190 spid18s owner-list
2012-10-01 12:13:48.190 spid18s owner id=process21365fb88
2012-10-01 12:13:48.190 spid18s waiter-list
2012-10-01 12:13:48.190 spid18s waiter id=process2717782c8
2012-10-01 12:13:48.190 spid18s exchangeEvent id=Pipe156697680 WaitType=e_waitPipeGetRow nodeId=102
2012-10-01 12:13:48.190 spid18s owner-list
2012-10-01 12:13:48.190 spid18s owner id=process21365fb88
2012-10-01 12:13:48.190 spid18s waiter-list
2012-10-01 12:13:48.190 spid18s waiter id=process8f308748
2012-10-01 12:13:48.190 spid18s exchangeEvent id=Pipe156697580 WaitType=e_waitPipeGetRow nodeId=102
2012-10-01 12:13:48.190 spid18s owner-list
2012-10-01 12:13:48.190 spid18s owner id=process21365fb88
2012-10-01 12:13:48.190 spid18s waiter-list
2012-10-01 12:13:48.190 spid18s waiter id=process21365ee08
2012-10-01 12:13:48.190 spid18s exchangeEvent id=Port80193200 WaitType=e_waitPortOpen nodeId=4
2012-10-01 12:13:48.190 spid18s owner-list
2012-10-01 12:13:48.190 spid18s owner id=process271778508
2012-10-01 12:13:48.190 spid18s waiter-list
2012-10-01 12:13:48.190 spid18s waiter id=process4259708
2012-10-01 12:13:48.190 spid18s exchangeEvent id=Pipe156697500 WaitType=e_waitPipeGetRow nodeId=102
2012-10-01 12:13:48.190 spid18s owner-list
2012-10-01 12:13:48.190 spid18s owner id=process21365fb88
2012-10-01 12:13:48.190 spid18s waiter-list
2012-10-01 12:13:48.190 spid18s waiter id=process12191e088
2012-10-01 12:13:48.190 spid18s exchangeEvent id=Pipe156697100 WaitType=e_waitPipeGetRow nodeId=100
2012-10-01 12:13:48.190 spid18s owner-list
2012-10-01 12:13:48.190 spid18s owner id=process12191e088
2012-10-01 12:13:48.190 spid18s owner id=process21365ee08
2012-10-01 12:13:48.190 spid18s owner id=process2717782c8
2012-10-01 12:13:48.190 spid18s owner id=process8f308748
2012-10-01 12:13:48.190 spid18s waiter-list
2012-10-01 12:13:48.190 spid18s waiter id=process271778508
October 4, 2012 at 10:33 am
If you are just looking to locate the query on your server, try this search script I wrote about last week
SQLWorks - Searching across all objects
It will search all objects, including the full text of stored procedures, triggers etc to find the string you specify.
Of course if the query you're looking for is run by a user and not compiled on your server this won't help you.
October 4, 2012 at 2:19 pm
D-SQL (10/3/2012)
I have been going through the forums on how to read this graph. I was asked where does a certain query in the graph can be located and have spent a day trying to find where the certain query is located in the database but can find it.
Looks like it's coming in over a linked server:
Input Buf: RPC Event: Proc [Database Id = 5 Object Id = 2026138659]
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 6, 2012 at 6:01 am
Just for the record an RPC event does not necessarily mean it was generated by a call via a Linked Server. All data clients have the option to generate RPC calls. For example when executing a stored procedure from C# using a SqlCommand object w/ CommandType set to StoredProcedure an RPC call is made.
SELECT OBJECT_NAME(2026138659, 5) AS [object_name];
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 6, 2012 at 7:11 am
Firstly, if you want a readable deadlock graph, turn traceflag 1204 off and turn traceflag 1222 on (or read the deadlock graph from the system health extended events session)
The deadlock graph produced by traceflag 1204 is a pain to read and missing important information.
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
October 7, 2012 at 7:20 pm
Thank you
October 8, 2012 at 1:50 pm
Quick question on those flags, I noticed that both are turned on. Turning off the 1204 and leaving shouldnt hurt anything correct?
October 8, 2012 at 2:15 pm
Traceflag 1204 just produces the old-style deadlock graph. If you have 1222 on or are pulling deadlock graphs from the extended events sessions, you probably don't want it turned on at all.
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
October 8, 2012 at 2:18 pm
No, it won't hurt a thing to disable 1204.
Do you know how they were enabled? If not then check the startup options of the instance. If they were enabled using -T then you'll want to remove the entry otherwise it'll be enabled after the next service start.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 10, 2012 at 3:08 am
opc.three (10/6/2012)
Just for the record an RPC event does not necessarily mean it was generated by a call via a Linked Server. All data clients have the option to generate RPC calls. For example when executing a stored procedure from C# using a SqlCommand object w/ CommandType set to StoredProcedure an RPC call is made.
SELECT OBJECT_NAME(2026138659, 5) AS [object_name];
Playing catchup... thanks for the clarification, Orlando. I'd... errr, at best... forgotten that any foreign entity calling a Sproc would fire the RPC.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply