Wednesday, December 26, 2012

How to measure the percentage of online index rebuild

 

Rebuilding indexes online is time consuming hence many DBAs need to monitor the event. There is a profiler event in SQL Profiler to capture the online index rebuilding.

In SQL Profiler, there is an event Progress Report: Online Index Operation under Progress Report category.

clip_image002

When this profiler is executing results can be obtained. Most of the columns are common like application name, database name spid , login etc. However, there are few uncommon columns which are shown in the below image.

clip_image004

Index ID – index ID from for the table.

Object Name : Index Name

BigIntData1: 0 = serial plan; otherwise, the thread ID during parallel execution.

BigIntData2: Number of rows inserted.

In the above example, non-clustered index BigintData2 in 0 which means index rebuild was done using a serial plan. In that example if you consider the BigintData1, it shows the cumulative rows rebuild. Since this table has 20,000 records last row for BigintData2 shows that number.

If you consider the clustered Index, there are 8 parallel plans have created to rebuild the indexes. If you sum all the max value for each plan, that again adds up to 20,000.

No comments:

Post a Comment