September 12, 2016 at 10:43 pm
HI All,
After applying SQL Server 2012 Sp3 patch, SSISDB is encountering multiple deadlocks and jobs are failing. A particular update statement is causing the deadlock. After reading the link https://braingrabber.wordpress.com/2016/04/29/200/ we were able to control the deadlock for few hours but again the same deadlock started reappearing. The update statement:
UPDATE [internal].[execution_parameter_values]
SET [runtime_override] = 1, [value_set] = 1, [base_data_type] = @parameter_type, [parameter_value] = @parameter_value
WHERE [execution_parameter_id] = @execution_parameter_id
Same jobs were running prior to SP3 patching. IS this known issue? How to solve the deadlock?
Cheers,
Got an idea..share it !!
DBA_Vishal
September 13, 2016 at 1:38 am
Please share your update as this is critical on PROD
Cheers,
Got an idea..share it !!
DBA_Vishal
September 13, 2016 at 2:06 am
Did you update your statistics after applying the service pack?
John
September 13, 2016 at 2:45 am
Did you log a connect item for this?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 13, 2016 at 2:52 am
Koen, I'm not sure we've enough evidence yet to prove this is a bug. It could just be that a different execution plan was compiled after the restart. That's why I suggested updating the statistics.
John
September 13, 2016 at 3:10 am
John Mitchell-245523 (9/13/2016)
Koen, I'm not sure we've enough evidence yet to prove this is a bug. It could just be that a different execution plan was compiled after the restart. That's why I suggested updating the statistics.John
Since SSISDB is a database where you supposedly don't have to manage anything (it's an internal database), such an update statistics script should be part of the SP.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 13, 2016 at 3:13 am
Yes after doing index rebuild and update stats deadlock get control for few hours and than once again we started to see and job get fail.
Is this a BUG and do we have any workaround as I am under pressure to rollback SP3
DEADLOCK - info
Node:1
2016-09-12 08:04:27.66 spid5s KEY: 6:72057594043564032 (70ff976f502b) CleanCnt:2 Mode:RangeS-S Flags: 0x1
2016-09-12 08:04:27.66 spid5s Grant List 0:
2016-09-12 08:04:27.66 spid5s Owner:0x0000000524A499C0 Mode: RangeS-S Flg:0x40 Ref:0 Life:02000000 SPID:98 ECID:0 XactLockInfo: 0x0000000A564AD6E8
2016-09-12 08:04:27.66 spid5s SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 226
2016-09-12 08:04:27.66 spid5s Input Buf: RPC Event: Proc [Database Id = 6 Object Id = 2102871154]
2016-09-12 08:04:27.66 spid5s Grant List 2:
2016-09-12 08:04:27.66 spid5s Requested by:
2016-09-12 08:04:27.66 spid5s ResType:LockOwner Stype:'OR'Xdes:0x0000000A5F99D078 Mode: X SPID:97 BatchID:0 ECID:0 TaskProxy:(0x00000002249D8608) Value:0x54cd2c80 Cost:(0/0)
2016-09-12 08:04:27.66 spid5s
2016-09-12 08:04:27.66 spid5s Node:2
2016-09-12 08:04:27.66 spid5s KEY: 6:72057594043564032 (4e5bbf856621) CleanCnt:3 Mode:RangeS-S Flags: 0x1
2016-09-12 08:04:27.66 spid5s Grant List 0:
2016-09-12 08:04:27.66 spid5s Grant List 2:
2016-09-12 08:04:27.66 spid5s Owner:0x000000052CC9F9C0 Mode: RangeS-S Flg:0x40 Ref:0 Life:02000000 SPID:97 ECID:0 XactLockInfo: 0x0000000A5F99D0B8
2016-09-12 08:04:27.66 spid5s SPID: 97 ECID: 0 Statement Type: UPDATE Line #: 226
2016-09-12 08:04:27.66 spid5s Input Buf: RPC Event: Proc [Database Id = 6 Object Id = 2102871154]
2016-09-12 08:04:27.66 spid5s Requested by:
2016-09-12 08:04:27.66 spid5s ResType:LockOwner Stype:'OR'Xdes:0x0000000A564AD6A8 Mode: X SPID:98 BatchID:0 ECID:0 TaskProxy:(0x0000000224D9C608) Value:0x27b80b40 Cost:(0/0)
2016-09-12 08:04:27.66 spid5s
2016-09-12 08:04:27.66 spid5s Victim Resource Owner:
2016-09-12 08:04:27.66 spid5s ResType:LockOwner Stype:'OR'Xdes:0x0000000A5F99D078 Mode: X SPID:97 BatchID:0 ECID:0 TaskProxy:(0x00000002249D8608) Value:0x54cd2c80 Cost:(0/0)
2016-09-12 08:04:27.66 spid20s deadlock-list
2016-09-12 08:04:27.66 spid20s deadlock victim=process504dc38
2016-09-12 08:04:27.66 spid20s process-list
2016-09-12 08:04:27.66 spid20s process id=process504dc38 taskpriority=0 logused=0 waitresource=KEY: 6:72057594043564032 (70ff976f502b) waittime=4080 ownerId=1582853 transactionname=user_transaction lasttranstarted=2016-09-12T08:04:23.580 XDES=0xa5f99d078 lockMode=X schedulerid=19 kpid=14980 status=suspended spid=97 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-09-12T08:04:23.580 lastbatchcompleted=2016-09-12T08:04:23.573 lastattention=1900-01-01T00:00:00.573 clientapp=.Net SqlClient Data Provider hostname=BAETLDB1 hostpid=1208 loginname=S-1-9-3-1490647564-1313287797-1311009419-513328694 isolationlevel=serializable (4) xactid=1582853 currentdb=6 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128024
2016-09-12 08:04:27.66 spid20s executionStack
2016-09-12 08:04:27.66 spid20s frame procname=SSISDB.catalog.set_execution_parameter_value line=226 stmtstart=20462 stmtend=21236 sqlhandle=0x030006007244577de9ea0a007fa6000001000000000000000000000000000000000000000000000000000000
2016-09-12 08:04:27.66 spid20s UPDATE [internal].[execution_parameter_values]
2016-09-12 08:04:27.66 spid20s SET [runtime_override] = 1,
2016-09-12 08:04:27.66 spid20s [value_set] = 1,
2016-09-12 08:04:27.66 spid20s [base_data_type] = @parameter_type,
2016-09-12 08:04:27.66 spid20s [parameter_value] = @parameter_value
2016-09-12 08:04:27.66 spid20s WHERE [execution_parameter_id] = @execution_parameter_id
2016-09-12 08:04:27.66 spid20s inputbuf
2016-09-12 08:04:27.66 spid20s Proc [Database Id = 6 Object Id = 2102871154]
2016-09-12 08:04:27.66 spid20s process id=process3039868 taskpriority=0 logused=0 waitresource=KEY: 6:72057594043564032 (4e5bbf856621) waittime=4081 ownerId=1582805 transactionname=user_transaction lasttranstarted=2016-09-12T08:04:23.580 XDES=0xa564ad6a8 lockMode=X schedulerid=5 kpid=15452 status=suspended spid=98 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-09-12T08:04:23.580 lastbatchcompleted=2016-09-12T08:04:23.580 lastattention=1900-01-01T00:00:00.580 clientapp=.Net SqlClient Data Provider hostname=BAETLDB1 hostpid=19192 loginname=S-1-9-3-1490647564-1313287797-1311009419-513328694 isolationlevel=serializable (4) xactid=1582805 currentdb=6 lockTimeout=4294967295 clientoption1=538968096 clientoption2=128024
2016-09-12 08:04:27.66 spid20s executionStack
2016-09-12 08:04:27.66 spid20s frame procname=SSISDB.catalog.set_execution_parameter_value line=226 stmtstart=20462 stmtend=21236 sqlhandle=0x030006007244577de9ea0a007fa6000001000000000000000000000000000000000000000000000000000000
2016-09-12 08:04:27.66 spid20s UPDATE [internal].[execution_parameter_values]
2016-09-12 08:04:27.66 spid20s SET [runtime_override] = 1,
2016-09-12 08:04:27.66 spid20s [value_set] = 1,
2016-09-12 08:04:27.66 spid20s [base_data_type] = @parameter_type,
2016-09-12 08:04:27.66 spid20s [parameter_value] = @parameter_value
2016-09-12 08:04:27.66 spid20s WHERE [execution_parameter_id] = @execution_parameter_id
2016-09-12 08:04:27.66 spid20s inputbuf
2016-09-12 08:04:27.66 spid20s Proc [Database Id = 6 Object Id = 2102871154]
2016-09-12 08:04:27.66 spid20s resource-list
2016-09-12 08:04:27.66 spid20s keylock hobtid=72057594043564032 dbid=6 objectname=SSISDB.internal.execution_parameter_values indexname=PK_Execution_Parameter_value id=locka10a85180 mode=RangeS-S associatedObjectId=72057594043564032
2016-09-12 08:04:27.66 spid20s owner-list
2016-09-12 08:04:27.66 spid20s owner id=process3039868 mode=RangeS-S
2016-09-12 08:04:27.66 spid20s waiter-list
2016-09-12 08:04:27.66 spid20s waiter id=process504dc38 mode=X requestType=convert
2016-09-12 08:04:27.66 spid20s keylock hobtid=72057594043564032 dbid=6 objectname=SSISDB.internal.execution_parameter_values indexname=PK_Execution_Parameter_value id=locka10a82e00 mode=RangeS-S associatedObjectId=72057594043564032
2016-09-12 08:04:27.66 spid20s owner-list
2016-09-12 08:04:27.66 spid20s owner id=process504dc38 mode=RangeS-S
2016-09-12 08:04:27.66 spid20s waiter-list
2016-09-12 08:04:27.66 spid20s waiter id=process3039868 mode=X requestType=convert
2016-09-12 08:04:32.66 spid5s Deadlock encountered .... Printing deadlock information
2016-09-12 08:04:32.66 spid5s Wait-for graph
2016-09-12 08:04:32.66 spid5s
Cheers,
Got an idea..share it !!
DBA_Vishal
September 13, 2016 at 3:35 am
Yes, good point - Microsoft released a fix prior to SP3 that's supposed to have addressed stuff like this.
The only things I can recommend are updating statistics more often (with full scan if necessary), consider raising a Connect item (as Koen suggests) and doing a bit of deadlock analysis[/url].
John
September 13, 2016 at 3:44 am
Thank you so much , I am not sure what is raising a Connect item (as Koen suggests).
@Koen, Please help me more here.
Cheers,
Got an idea..share it !!
DBA_Vishal
September 13, 2016 at 3:53 am
https://connect.microsoft.com/SQLServer
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 13, 2016 at 7:27 am
no update from connect. Please suggest.
Cheers,
Got an idea..share it !!
DBA_Vishal
September 14, 2016 at 12:29 am
Please help to solve this issue as this is PROD Crititical
Cheers,
Got an idea..share it !!
DBA_Vishal
September 14, 2016 at 12:37 pm
When you open an issue on connect, I doubt you get an answer in a day. There are some bugs up there that have been active for years. When you have a critical issue and need Microsoft to respond that quickly, you need to open a support case with Microsoft.
In the meantime, not sure if you saw this KB article:
FIX: You experience a deadlock condition when you run multiple SSIS packages in SQL Server 2012
https://support.microsoft.com/en-us/kb/2699720
Sue
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply