SQL Server: Engine Estimated time to complete Online Index operations.

There is always a way to work around something even if we are not able find the absolute solution. So the show must go on .I am sure that in any enterprise where SQL Server database is in Terabytes and the indexes are huge this is a common question from the technical management to the DBAs:

Hello Dear DBA , approximately, how much time will it take to create\alter\rebuild an index? If this question has not been thrown at you, it will be …… sooner or later.

Well!!! I did get this question fired at me yesterday .Not at me exactly but I chose to be at the receiving end by readily accepting to find the way out if possible (Always remember to set the right expectations rather than committing straightaway and finding it difficult to achieve) .

We had an unsuccessful attempt to create a filtered index a few days back .We decided to convert an index of 70GB to a filtered index and expected it to complete in around 70 minutes guessing it will go at the rate of around 1GB/Min based on our past experience.

So, in the second attempt when I was involved it was asked by the project management team to know the status of index rebuild from time to time. At first I thought that sys.dm_exec_requests DMV would give the desired output .The testing proved otherwise. The next choice was of course BING\GOOGLE but that did not work either or might be I did not have the patience to scroll through hundreds of pages.

I then decided to see if there is a way to create a WMI alert on the same (I am a WMI foodie) .So next task was to find out which WMI class will fit in .I searched this link and found that there is no such class available as of now .The other option I thought was to check with X-Events and profiler. Since I had less time I chose to go with profiler first.

In Profiler, there is a class named Progress Report: Online Index Operation that can be used to capture the index create\alter\rebuild statistics (by the way this option is available from SQL Server 2005 and onwards).

This was working fine but I needed to find which columns do I need and what each column means in terms of understanding the rate at which index operation is being carried out. So, let’s reach to that stage first and we can then see what’s what.

  • Open SQL Server profiler and connect to the instance you wish to monitor.
  • You will see the default page

1_SQL_Server_Engine_Estimated_time_to_complete_Online_Index_operations

  • Select the square check box save to table .A pop – up will appear where you will need to select the database name and table name .I would suggest to have a new database rather that creating it in any other production database .Cross check if the table is created in the database by refreshing the table list in the management studio under the database name.

2_SQL_Server_Engine_Estimated_time_to_complete_Online_Index_operations

3_SQL_Server_Engine_Estimated_time_to_complete_Online_Index_operations

   
  • Next ,click on the Even selection section and uncheck everything from the square check boxes that is pre – checked .This is to ensure that we capture only selected information and consume minimal resources .Also I would suggest that you connect to the concerned instance from a remote machine .This will help in saving more resources .

4_SQL_Server_Engine_Estimated_time_to_complete_Online_Index_operations

  • Now , select the Progress Report: Online Index Operation event .By default , it will pre – select a lot of options that you do not actually need .I , for my requirement only selected BigintData1, BigintData2,SPID (you can’t uncheck it),Duration and ObjectName. You can select more or less as needed be but I think this is the minimum you should have.

5_SQL_Server_Engine_Estimated_time_to_complete_Online_Index_operations

  • The two important columns here are BigIntData1 and BigIntData2. BigIntData1 will capture the number of rows inserted for the online index operation. BigIntData2 captures the thread number that is inserting the rows (which you see in BigIntData1.In the screen shot below you can see that there are 4 threads inserting the rows because I have given the MaxDop = 4 hint in the index rebuild command.
USE [INDTEST]
GO
ALTER INDEX [IX_Test_OLD] ON [dbo].[LockESC] REBUILD PARTITION = ALL WITH
(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE=OFF, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON,ONLINE=ON,SORT_IN_TEMPDB=ON,MAXDOP=4)
GO

6_SQL_Server_Engine_Estimated_time_to_complete_Online_Index_operations

  • The value in BigIntData1 is cumulative for every thread id in BigIntData2 .Keeping this in mind ,at any point in time (while the index is being created\altered\rebuilt ) if you find the max values of all the threads and sum them up you will come to know how many rows have been processed .To find the percentage of work done you can use this simple formula.
Percentage Index operation complete= (Processed rows x 100)/total rows

Note : Total rows can be quickly found by using sp_spaceused (don’t use select count (*) as it takes time if the table is big).

  • Here is a simple piece of code that you can use to find the percentage of Index online operation completed :
/*Insert only the rows inserted into a new table*/
select MAX(bigintdata1)'rows_processed' 
into Index_trace_sum from dbo.Index_trace (nolock) 
group by bigintdata2 having Max(bigintdata1)  is not null
 
/*Insert total rows in the table into a new table*/
 create table spaceused 
 (name varchar(50),rows bigint,reserved nvarchar(20),data nvarchar(20),
 index_size nvarchar(20),unused nvarchar(20))
 go
 insert into spaceused exec sp_spaceused 'LockESC'
 
 
/*get the percentage of index operation completed*/
declare @val int
select @val= rows from spaceused 
select sum(rows_processed)*100/@val from Index_trace_sum
 
Drop table Index_trace_sum
truncate table spaceused

This is it .Hope you will like this blog and as usual please give you feedback if any.

 

Regards

Abhay Chaudhary

Like us on FaceBook Follow us on Twitter

Follow me on FaceBook | Join the fastest growing SQL Server group on FaceBook

   

6 Comments on “SQL Server: Engine Estimated time to complete Online Index operations.”

  1. Abhay, I have run into this situation few times earlier to estimate the time taken and was always disappointed to find no such mechanism. thanks very much for this interesting post.

  2. I followed the steps, but I’m not getting any data in the table. For how long do you need to run the trace? I’ve had it running for few minutes without any data coming back. “Progress Report: Online Index Operation” is the only selected event. The index creation statement I’m running is the following (it’s on an XML column):

    CREATE PRIMARY XML INDEX [IX_Indexname]
    ON dbo.TableName(XmlColumnName)

    The MDF file is continuously increasing in size, so I’m guessing the database is still creating the index, but alas, no data is showing up from the trace.

Leave a Reply

Your email address will not be published.