SQL Server Performance Forum – Threads Archive

Alter index on online=on causes deadlock

Hi I have 122 G database on SQL 2005 ( cluster) I was trying to use online reindexing and run into deadlock. while other users were successfully updating database – Activity monitor showed that my process with statement for reindex deadlocked itself. There were 3 records of process id=57(MY reindex statement) first one showed my sql statement in Last T-SQL batch and 2 others where empty. Those 2 others were blocked by process id 57 SQL log showed "unresolved deadlock" ; "stack dump…" messages. Looks like my reindex deadlocked itseft ??? I restarted sql server and started reindexing again. currently it not blocking itself right away but I do see status for my statement saying (suspended) My statement: ALTER INDEX ALL ON TABLENAME REBUILD WITH (ONLINE=ON, FILLFACTOR=80) please help

I did rebuild indexes on line in my second attempt, but it doesn’t make the issue clear: i had a deadlock on my db caused by online reindexing. Now searching for the answer on internet I found this article, which says: "Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. In these rare cases, the SQL Server 2005 Database Engine will select the user or application activity as a deadlock victim." http://technet.microsoft.com/en-us/library/ms190981.aspx
I am not sure what are those "these rare cases" ??? Is there a way to avoid those cases? Or is that means that online operation is basically not online operation? and in production environment should never be used? Could someone give me some hints, ideas, links , anything?

As per MS, user or application is the deadlock victim not the onlin indexing operation…
Run the sql profiler for deadlock to see what is happening… From BOL…
Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. In these rare cases, the SQL Server 2005 Database Engine will select the user or application activity as a deadlock victim.
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Have you checked the TEMPDB contention or disk sizes where TEMPDB is located, I guess that will also have such issues. http://sqlserver-qa.net/blogs/tools…server-index-optimization-best-practices.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

my temp db is located on dedicated RAID aray . space is not the issue – I have 200G there and transaction log set to autogrow. I can’t belive that ONLINE reindexing that widely advertised as one of the best
fitures of SQL 2005 – doesn’t actually exist.

OK – i figured out what is happenig here to my server:
http://support.microsoft.com/kb/923605 Maybe someone will spend less time for searching.

Thanks for the feedback…
So your deadlock is nothing to do with REINDEXING with ONLINE option? MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I didn’t apply hot fix yet -waiting for the vendor to certify this hot fix.
But as per this article – looks like it doesn’t . Basically if I understood that correctly any query can run into this endless deadlock if server has more than 1 processors. I will update this topic – once I apply that hot fix – hopefully it will fix my issue.

Thanks…
It is parallelism issue…if you don’t want to apply the hotfix you can MAXDOP option in the query to limit the number of processor utilization…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

See thishttp://mssqltips.com/tip.asp?tip=1028 excellent information about there. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>