SQL Server Performance

SQL 2008 R2 Index Reorg - Table Escalation

Discussion in 'ALL SQL SERVER QUESTIONS' started by cncncn, Mar 1, 2013.

  1. cncncn New Member

    Hi
    I was running a Index reorg at the partition level on a table with 300GB data and that caused blocking issue since the
    lock got escalated to the table level. This is what is saw on the error log.
    2013-02-28 19:00:13.47 spidxx Table 'xxx will not be available during reorganizing index 'xxx'. This is because the index reorganization operation performs inside a user transaction and the entire table is exclusively locked.
    My understanding is index reorg should not hold long term locks. But im surprised that it led to table level lock escalation.
    Did anyone come across this issue in SQL 2008 R2? This is the SQL version we have.
    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    Our server has 48 logical CPUs. I am not sure if this issue has anything to with lock partitioning.
    We did not enable partition level lock for this table. It is set to TABLE level (default setting)
  2. Shehap MVP, MCTS, MCITP SQL Server

    Actually , both index rebuild and r-organize index can expose impacts and schema locks while execution even if you are using online option that can be mitigated by some means like:

    · Enabling Auto option for escalation level of partitioned tables to switch escalation level from table to partition which can help well in reducing chances of reaching to a Full block of the entire of table , you can learn more about it at
    http://msdn.microsoft.com/en-us/library/ms184286(v=sql.105).aspx
    · Selecting the most idle times that have less DB activities to schedule your index maintenance

    · Try to reduce frequency of index re-organize /rebuild of huge data entities

    · Try to assign a specific resource pool with the minimum CPU like 4 CPU for SQL Service agent account or Job owner at least , you can try this using this new technique of 2008 “Resource Governor

    Please let me know if any further help is needed

Share This Page