SQL Server Performance

Alter index on online=on causes deadlock

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by NewDBA, Jul 19, 2007.

  1. NewDBA New Member

    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

  2. NewDBA New Member

    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?
  3. MohammedU New Member

    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.
  4. satya Moderator

    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.
  5. NewDBA New Member

    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.

  6. NewDBA New Member

  7. MohammedU New Member

    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.
  8. NewDBA New Member

    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.
  9. MohammedU New Member

    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.
  10. satya Moderator

    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.

Share This Page