BLOCKING CAUSED BY REORGANIZE OF INDEX JOB IN SQL SERVER 2005

  • hi all,

    need your help on this.

    i have a job which is reorganizing the index and with that i am having a issue of blocking when ever the job is running the blocking is coming up.AS per my knowledge reorganize of index is online and it should not create long holding blocking or blocking due to any update or select.

    guide me if i am wrong and suggest for a solution and why the blocking happening ?

    thanks

    Ivan 🙂

  • Two points ...

    1) are you running this maintenance job in peak hours... it should be run in nights (OFF - peak hours)

    2) have you selectiong the advanced option in reindxing/rebuilding" like 'Sort in Tempdb ' and "Keep index online while .."

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Rebuilding indexes will take locks; reorganising won't. Please will you post your code?

    John

  • Reorganize of index is always online by-Default and we run it off hours less transaction.

  • below is the script that i use to reorganize the index

    DECLARE @Database VARCHAR(255)

    DECLARE @Table VARCHAR(255)

    DECLARE @cmd NVARCHAR(500)

    DECLARE @fillfactor INT

    DECLARE @Statement NVARCHAR(300)

    SET @fillfactor = 90

    DECLARE DatabaseCursor CURSOR FOR

    SELECT name

    FROM sys.databases

    WHERE name NOT IN (''master'',''msdb'',''tempdb'',''model'')

    AND DATABASEPROPERTYEX(name, ''Status'')=''ONLINE'' AND is_read_only<>1

    ORDER BY 1

    OPEN DatabaseCursor

    FETCH NEXT FROM DatabaseCursor INTO @Database

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @cmd = ''DECLARE TableCursor CURSOR FOR SELECT ''''['''' + table_catalog + ''''].['''' + table_schema + ''''].['''' +

    table_name + '''']'''' as tableName FROM '' + @Database + ''.INFORMATION_SCHEMA.TABLES

    WHERE table_type = ''''BASE TABLE''''''

    -- create table cursor

    EXEC (@cmd)

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO @Table

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9)

    BEGIN

    -- SQL 2005 or higher command

    SET @cmd = ''ALTER INDEX ALL ON '' + @Table + '' REORGANIZE ''

    EXEC (@cmd)

    --PRINT (@cmd)

    END

    ELSE

    BEGIN

    -- SQL 2000 command

    DBCC DBREINDEX(@Table,'' '',@fillfactor)

    END

    --PRINT N''UPDATING STATISTICS '' + @Table

    SET @Statement = ''UPDATE STATISTICS '' + @Table + '' WITH FULLSCAN''

    EXEC sp_executesql @Statement

    --PRINT @Statement

    FETCH NEXT FROM TableCursor INTO @Table

    END

    CLOSE TableCursor

    DEALLOCATE TableCursor

    FETCH NEXT FROM DatabaseCursor INTO @Database

    END

    CLOSE DatabaseCursor

    DEALLOCATE DatabaseCursor

  • I don't know why that would hold a lock for a long period of time. Have you tried to use sys.dm_os_waiting_tasks to see which session is doing the blocking?

    John

  • yes that is what i am asking i am getting blocking email when ever this job is running so asked you all to help me to find a solution .

  • Do we need to update statistics AFTER index rebuild/reorgainze ??

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Well that is not the issue over here plz read my question throughly and then reply.

  • What is a blocking email and under what circumstances do you get one? Have you managed to ascertain which particular statement is causing the blocking?

    John

  • John Mitchell-245523 (9/30/2013)


    Have you tried to use sys.dm_os_waiting_tasks to see which session is doing the blocking?

    ?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • john

    yes i have automatic blocking alert also with the details which is causing the blocking with query and time and etc .

    but here my question is when i reorganize the index why the blocking occurs which should not come as per the reorganize index criteria reorganize of index is online and lock does not occurs at that point of time as the index are available for use.

  • well gila i have the details of which all others query was running @ that point of time but alter index reorganize is the head of the blocking chain.

  • Yes, but how do you know it's the ALTER INDEX...REORGANIZE statement that's doing the blocking? The code you posted does more than that.

    How long does the blocking have to last before the alert is sent?

    John

  • Ivan Mohapatra (9/30/2013)


    well gila i have the details of which all others query was running @ that point of time but alter index reorganize is the head of the blocking chain.

    Ah, that answers one of my questions. What was the query that was being blocked?

    John

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply