Online Index Operation Event Class

By Admin at 18 Jul 2016, 12:27 PM
  • Introduction:

    Rebuilding indexes on big tables is a time consuming operation, especially when the data is clustered or you alter the table by adding or removing an index. SQL Server provides a means to track the progress

    Progress Report:

    The Online Index Operation event class specifies the progress of online index build operation while the build procedure is running.

    Features

    The Profiler has a class: Progress Report, for capturing the data from the index create-alter-rebuild. SQL Server allows for tracking the progress of the online index operation by using Extended Events and also known as a Profiler. Events offered by the SQL Server Profiler are called a Progress Report: Online Index Operation.

    Online Index Operation Event Class

    The above picture displays the report output of the SQL server profiler when the Clustered and the Non-Clustered index was rebuilt online

    1. USE [database_1]
    2. GO
    3. ALTER INDEX ALL ON [dbo].[Table_1]
    4. REBUILD WITH (ONLINE = ON)
    5. GO

    Online Index Operation Event Class

    Looking at the above picture you will observe two stages: with stage 1 and stage 2 references in EventSubClass column.

    If it’s only the Non-Clustered index that are rebuilt online, then there will only be Stage 1 and is shown as an output like below:

    1. USE [database_1]
    2. GO
    3. ALTER INDEX [nci] ON [dbo].[Table_1]
    4. REBUILD WITH (ONLINE = ON)
    5. GO

    Online Index Operation Event Class

    Limitations

    Unfortunately there was no estimation of when this rebuild will complete and query below can always show 0 percent complete. Currently only an ALTER INDEX REORGANIZE can be executed to indicate the estimated percentage completed

    1. SELECT text,start_time,percent_complete
    2. CROSS APPLY sys.dm_exec_sql_text(ERR.sql_handle)
    3. WHERE 1=1
    4. AND command = 'ALTER INDEX'

    Online Index Operation Event Class

    If you notice that the Rebuild is consuming too much time, a simple check would be to take a look at the Index Properties and compare it against BigintData1 column in the Profiler. This would aid you to estimate the number of rows yet to be organized.

    Online Index Operation Event Class

    Best Practices

    You could also use a query to get this:

    1. USE [database_10]
    2. SELECT OBJECT_NAME(id) as tableName, name AS indexName,rowcnt
    3. FROM sys.sysindexes
    4. WHERE 1=1
    5. AND name = 'nci'
    6. GO

    Online Index Operation Event Class

    The new two columns, Partition ID & Partition Number exposes identical information similar to the extended events from SQL Server 2014.

    Online Index Operation Event Class


    Comments

     

    Post a comment

    Please correct the following: