August 4, 2011 at 9:22 am
Hi,
SQL Server 2005 64 bit
Database was working fine. Nothing else has been released or changed since then.
But when i did a monthly index rebuild based on avg_fragmentation_in_percent > 80 , i am getting deadlock on few tables where i have rebuilded the indexes. Why?
Does this indexes rebuild has any relation with deadlocks?
Thanks.
August 4, 2011 at 9:24 am
The rebuild probably changed the execution plans. The queries had the potential to deadlock anyway, so you can't really say the rebuild caused it.
p.s. 80% fragmentation is incredibly high. The normal rebuild threshold is 30%.
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 5, 2011 at 3:14 am
Thanks.
So the possibility is there that rebuilding index can change things.
But how to narrow down this. what next step shall i take?
PS:
Well more than 80% and its not reducing even after rebuild because avg_fragment_size_in_pages <= 1.5.
can we do something in such cases?
August 5, 2011 at 3:20 am
How big's the index?
For deadlocks, you should fix the root cause, not worry about whether an index rebuild changed things so that the deadlock happened. 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 5, 2011 at 9:14 am
i am already running 1222, Following is the snapshot of one of the lock, if this gives you sufficent details for understanding.
So what do you suggest?
Thanks.
deadlock-list
deadlock victim=process1c8def2e8
process-list
process id=process3e29d68 taskpriority=0 logused=632 waitresource=OBJECT: 7:796781133:0 waittime=2262 ownerId=1428147122 transactionname=user_transaction lasttranstarted=2011-08-02T10:28:39.540 XDES=0x133d1a370 lockMode=S schedulerid=8 kpid=16088 status=suspended spid=91 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-08-02T10:28:39.540 lastbatchcompleted=2011-08-02T10:28:39.533 clientapp=.Net SqlClient Data Provider hostname=dbo hostpid=3308 loginname=MyLogin isolationlevel=read committed (2) xactid=1428147122 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=TestDB.dbo.usp_delete_update_insert line=3497 stmtstart=256814 stmtend=258924 sqlhandle=0x0300070031de055d8d83dd002b9f00000100000000000000
"UPDATE Table_01"
inputbuf
Proc [Database Id = 93 Object Id = 4790775650]
process id=process1c8def198 taskpriority=0 logused=0 waitresource=KEY: 7:72057611616190464 (cf0524ed4825) waittime=2496 ownerId=1428148262 transactionname=SELECT lasttranstarted=2011-08-02T10:28:39.673 XDES=0x227f0cbf8 lockMode=S schedulerid=3 kpid=15024 status=suspended spid=77 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2011-08-02T10:28:39.673 lastbatchcompleted=2011-08-02T10:28:38.660 lastattention=2011-08-05T10:15:42.463 clientapp=.Net SqlClient Data Provider hostname=MyHostName hostpid=660 loginname=MyLogin isolationlevel=read committed (2) xactid=1428148262 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=TestDB.dbo.AspNet_SqlCachePollingStoredProcedure line=2 stmtstart=142 stmtend=316 sqlhandle=0x03000700591bbc2d89b38c002a9a00000100000000000000
SELECT tableName, changeId FROM dbo.AspNet_SqlCacheTablesForChangeNotification
frame procname=adhoc line=1 sqlhandle=0x01000700d95c9e1eb0823a81000000000000000000000000
dbo.AspNet_SqlCachePollingStoredProcedure
inputbuf
dbo.AspNet_SqlCachePollingStoredProcedure
process id=process1c8def2e8 taskpriority=0 logused=0 waitresource=OBJECT: 7:5234763988:0 waittime=2371 ownerId=1428149181 transactionname=user_transaction lasttranstarted=2011-08-02T10:28:39.797 XDES=0x27caa8de0 lockMode=IX schedulerid=3 kpid=7724 status=suspended spid=87 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-08-02T10:28:39.797 lastbatchcompleted=2011-08-02T10:28:39.793 clientapp=.Net SqlClient Data Provider hostname=MyHostName hostpid=660 loginname=MyLogin isolationlevel=read committed (2) xactid=1428149181 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=TestDB.dbo.usp_delete_update_insert line=49 stmtstart=3186 stmtend=3996 sqlhandle=0x0300070031de055d8d83dd002b9f00000100000000000000
"Delete Table_02"
inputbuf
Proc [Database Id = 93 Object Id = 4790775650]
resource-list
keylock hobtid=72057611616190464 dbid=7 objectname=TestDB.dbo.AspNet_SqlCacheTablesForChangeNotification indexname=PK__AspNet_SqlCacheT__2ADFAEAE id=lock12fcc4600 mode=X associatedObjectId=72057611616190464
owner-list
owner id=process3e29d68 mode=X
waiter-list
waiter id=process1c8def198 mode=S requestType=wait
objectlock lockPartition=0 objid=796781133 subresource=FULL dbid=7 objectname=TestDB.dbo.Table_01 id=lock19ae6bc00 mode=IX associatedObjectId=796781133
owner-list
owner id=process1c8def2e8 mode=IX
waiter-list
waiter id=process3e29d68 mode=S requestType=convert
objectlock lockPartition=0 objid=5234763988 subresource=FULL dbid=7 objectname=TestDB.dbo.Table_02 id=lock1c51bc100 mode=X associatedObjectId=5234763988
owner-list
owner id=process3e29d68 mode=X
waiter-list
waiter id=process1c8def2e8 mode=IX requestType=wait
deadlock-list
deadlock victim=process3e28868
process-list
process id=processfe0da8 taskpriority=0 logused=1208 waitresource=OBJECT: 7:796781133:0 waittime=3744 ownerId=1428153986 transactionname=user_transaction lasttranstarted=2011-08-02T10:28:43.197 XDES=0x220e9ba90 lockMode=S schedulerid=4 kpid=10880 status=suspended spid=101 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-08-02T10:28:43.197 lastbatchcompleted=2011-08-02T10:28:43.197 clientapp=.Net SqlClient Data Provider hostname=MyHostName hostpid=660 loginname=MyLogin isolationlevel=read committed (2) xactid=1428153986 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=TestDB.dbo.usp_delete_update_insert line=3497 stmtstart=256814 stmtend=258924 sqlhandle=0x0300070031de055d8d83dd002b9f00000100000000000000
"UPDATE Table_01"
inputbuf
Proc [Database Id = 93 Object Id = 4790775650]
process id=process3e28868 taskpriority=0 logused=0 waitresource=OBJECT: 7:5234763988:0 waittime=3759 ownerId=1428155829 transactionname=user_transaction lasttranstarted=2011-08-02T10:28:43.390 XDES=0x27c2e5920 lockMode=IX schedulerid=8 kpid=6744 status=suspended spid=91 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-08-02T10:28:43.390 lastbatchcompleted=2011-08-02T10:28:43.387 clientapp=.Net SqlClient Data Provider hostname=dbo hostpid=3308 loginname=MyLogin isolationlevel=read committed (2) xactid=1428155829 currentdb=7 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
executionStack
frame procname=TestDB.dbo.usp_delete_update_insert line=49 stmtstart=3186 stmtend=3996 sqlhandle=0x0300070031de055d8d83dd002b9f00000100000000000000
"DELETE Table_02"
inputbuf
Proc [Database Id = 93 Object Id = 4790775650]
resource-list
objectlock lockPartition=0 objid=796781133 subresource=FULL dbid=7 objectname=TestDB.dbo.Table_01 id=lock14bbef800 mode=IX associatedObjectId=796781133
owner-list
owner id=process3e28868 mode=IX
waiter-list
waiter id=processfe0da8 mode=S requestType=convert
objectlock lockPartition=0 objid=5234763988 subresource=FULL dbid=7 objectname=TestDB.dbo.Table_02 id=lock2498e6900 mode=X associatedObjectId=5234763988
owner-list
owner id=processfe0da8 mode=X
waiter-list
waiter id=process3e28868 mode=IX requestType=wait
August 5, 2011 at 9:34 am
What on earth is usp_delete_update_insert doing that it's over 3000 lines long?
The problematic code is around line 49 and line=3497. You've changed the table names, so I can't comment further on those.
You need to identify the queries, look at the locks taken and the length of the transactions and optimise as much as possible. Short transactions, efficient queries and objects accessed in the same order are the keys to fixing deadlocks.
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 8, 2011 at 3:36 am
Hi,
Well 4000 lines are one transaction with most of the object of our xml model.
XML model input is joining with many different tables based on different condition in the proc and it either insert/update/delete where required and this all must happen in one transaction.
Maximum table rows in the biggest table is less than a million - Reserved 800MB index 300MB.
Changed the queries logic by adding table hint holdlock/updatelock/rowlock where necessary.
But still not getting the desired results.
So whats next?
August 8, 2011 at 3:40 am
GilaMonster (8/5/2011)
You need to identify the queries, look at the locks taken and the length of the transactions and optimise as much as possible. Short transactions, efficient queries and objects accessed in the same order are the keys to fixing deadlocks.
Lock hints should be the absolute last resort.
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 9, 2011 at 5:19 am
Hi,
What can be the advantage OR dis-advantage, if both of these are ON
ALLOW_PAGE_LOCKS
ALLOW_ROW_LOCKS
I have already studied your comment where both are off.
If turning these both ON is beneficial? then why these are not ON by default?
Will this create any impact on performance if both are ON for all tables in PRODUCTION?
Thanks.
August 9, 2011 at 6:08 am
Those are both on by default. If they're both off, SQL can only take table-level locks. Not good for concurrency.
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 10, 2011 at 10:02 am
Great.
So that was the base reason, which has cause the locking. REBUILD iNDEX commands for few indexes were issued without these two options and that has killed everything.
Re-enabling allow_page_lock and allow_row_locks using sp_indexoption has solved all know locking issues.
Thanks for help.
Cheers.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply