Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
The online rebuild feature came with SQL Server 2005 to prevent the application from being affected while rebuilding the indexes. However, according to my personal experience, it is not really online until SQL Server 2012. In SQL Server 2008, I couldn’t perform online rebuild indexing during business hours. The rebuild process we initiated lock the queries, and after a few minutes the phone rang. The same is true for DBCC CHECKDB. With SQL Server 2012, we are able to do this. So, it was really ONLINE with SQL Server 2012.
Another shortcoming of the online Index Rebuild process is that we cannot see the percentage of the process. When we take backup, we can see the percentage of backup using dmws and we have a chance to guess when it will end. You can’t see the same thing using the dmws in the Online Index Create Rebuild process.
We may use Extended Events or Profiler to estimate when the Online Index Rebuild process will end. As you know, Extended Events will replace Profiler and Microsoft will remove Profiler after a while. But users are still accustomed to using Profiler, and still have not removed. We will do this process with Extended Events.
Let’s run the following script to rebuild a large index online for testing.
1 2 3 4 5 6 | USE [your_db_name] GO ALTER INDEX [index_name] ON [schema_name].[table_name] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) GO |
We will calculate the estimated end time of this process while this process is in progress. From the SSMS, right-click on the Sessions in Management-> Extended Events and click on the New Session Wizard.
In the next screen, select “Do not show this page again” and click Next.
We need to give a name to the Extended Event Session that we will create on the next screen. I’m going to call it “OnlineIslemler”. If I choose “Start the event session ar server startup”, this session will start automatically when sql server starts. I don’t make this choice just because I’m gonna run it when I need it.
In the next screen, select “Do not use template” and click Next.
In the screen that appears, write “online” in the “Event Library” section, and select the progress report online index operation by double-clicking.
The selected event will be listed in the selected events section. Click Next to proceed.
After this stage, we are proceeding by clicking next, next and finish to create the session.
After the session is created, right click on the session to start and click watch live data.
While index rebuild is in progress, you can see the number of rows inserted in the screen after clicking Watch Live Data.
For example, as you can see on the screen below, the 1380385 record has been inserted.
To view the total number of records in the index, you must run a script for that table as follows.
1 | exec sp_spaceused '[schema_name].[table_name]' |
The screen output you see below shows the number of records in the table where Index is located.
As you can see, there are approximately 30 million records in the table. We found that the number of records added by the index rebuild process was around 1 million 300 thousand. We can calculate the percentage of the index rebuild process with the following method.
1300000*100/30000000=4.33
