SQL Server Index is the key solution for most of the performance issues that the T-SQL queries suffer from. It can be used to speed up the data retrieval process from your database tables, by seeking for the requested data in the index itself, rather than scanning the overall table rows.
In this article, we will discuss the difference between rebuilding the index using Online and Offline modes.
Index Rebuild Overview
The SQL Server transactional database tables are not static tables and changing very frequently. In addition, the changes that are performed on the underlying table should be reflected to the related table indexes. Over time, and after frequent insert, update and delete operations, or after inserting or updating data with values that cannot fit in the available free space in the current page and lead to splitting the current page into two pages, the table index will become highly fragmented. In other words, the index data pages will be unordered and require extra I/O operations to navigate the SQL server index and get the requested data. In this case, reading data from the index will be worse than reading the data directly from the underlying table, that cause performance degradation or lead the SQL Server Query Optimizer to ignore that highly fragmented index.
To overcome the performance degradation issue that results from the high index fragmentation percentage, the index should be rebuilt or reorganized in regular bases, based on the index fragmentation percentage value. During the index rebuild operation, the index will be dropped and created again, the index fragmentation will be removed from all index levels, the index pages will be compacted based on the configured fill factor value, the disk space will be reclaimed, and the SQL Server index records will be reordered in contiguous pages. Remember that rebuilding a disabled index enables it and brings it back for use.
SQL Server index reorganizes operation is always executed online. On the other hand, rebuilding an index can be executed online, without locking other queries when using SQL Server Enterprise edition, or offline, by holding locks on the database objects during the rebuild operation. Online index rebuild operation is not available for the XML index, Spatial index, index on a table with a LOB column and an index on a local temp table.
The SQL Server index can be rebuilt in Online mode using the “WITH ONLINE =ON” option in the ALTER INDEX REBUILD T-SQL command, as in the example below:
ALTER INDEX [PK__Employee__2623598B6920BD95] ON [dbo].[Employee_Main] REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, ONLINE = ON);
Rebuild Index using ApexSQL Defrag
ApexSQL Defrag Overview
ApexSQL Defrag tool is a 3rd party SQL index defragmentation tool that you can easily use to perform number of operations related to the SQL Server indexes. For example, you can use ApexSQL Defrag to review the index usage statistics and apply the suitable maintenance task to make sure that the indexes are functioning well. ApexSQL Defrag allows us also to analyze and review the fragmentation percentage for the available indexes at all levels, such as the SQL Server instance level, the database level, the table level or simply at the individual SQL Server index level.
You can install ApexSQL Defrag to your server after downloading it from ApexSQL Download Center by following the straight-forward installation wizard, that will ask for the ApexSQL Defrag agent service account, where to install ApexSQL Defrag and whether to create a shortcut icon for ApexSQL Defrag in the desktop for faster access, as follows:
When ApexSQL Defrag is installed completely to your PC, you will be notified about the installation process status and asked whether to launch the tool directly. After that, it will ask to confirm the creation of the central repository database at the first run, where the indexes information and tool configurations will be stored:
Add New Server
To take benefits from ApexSQL Defrag in rebuilding the table’s indexes on a specific SQL Server instance, this SQL Server instance should be added to the ApexSQL Defrag tool. To add a new server to ApexSQL Defrag, click on the Add button, under the Home tab, as shown below:
From the displayed Connect to SQL Server window, provide the name of the SQL Server instance, the authentication mode and an authorized user information, as below:
After validating the provided connection information, ApexSQL Defrag will connect to the specified SQL Server instance, provide a list for all SQL Server instance databases, then show statistical information about all SQL Server indexes available under that instance, as follows:
Prepare Test Environment
Now, let us study two scenarios in order to get the difference between rebuilding a highly fragmented index using online and offline modes. We will start by creating a new database table with one Clustered index on the phone number column, using the CREATE TABLE T-SQL statement below:
After creating the table, we will fill the table with 10M dummy records using ApexSQl Generate, test data generator tool, in order to have a real life index rebuild test scenario, as shown below:
Once the table is filled with the test data, we will analyze the database indexes again, using ApexSQL Defrag tool, by right-clicking on the database that contains our test table and choose to perform Fast or Deep SQL Server indexes analyzing, as shown below:
When the SQL indexes analyzing process completed successfully, drag the Table column in order to group the displayed information based on the table name column value, then check the fragmentation percentage for the newly created index, which is 99.2% as shown clearly below:
Perform Offline SQL Server Index Rebuild
Having an index with high fragmentation percentage of 99.2% is a disaster for the SQL Server queries performance. To overcome this high fragmentation percentage issue, we should rebuild the index to defrag the fragmented index pages. First, we will try to perform an offline index rebuild operation. To do that, check the index to be rebuilt from the list then click on the Rebuild button, under Fragmentation tab, as shown below:
In order to get statistical information about the index rebuild operation, we will run the SQL Profiler tool in the background while performing the index rebuild operation. At the same time, we will try to perform a simple SELECT operation from the table, during the SQL Server index rebuild operation, as shown below:
When the offline SQL Server index rebuild operation completed, you can see from the execution statistics collected by the SQL Server Profiler tool that, the offline index rebuild operation required:
- CPU time = 32,516
- Number of reads = 44,1679
- Number of writes = 113,155
- Duration = 7,216 ms, as shown below:
And the SELECT query that we executed during the offline index rebuild operation required:
- CPU time = 4,282
- Number of reads = 114,894
- Duration = 2,270 ms, as shown below:
Perform Online SQL Server Index Rebuild
Now, let us reset all the test environment again, by truncating the table, fill it with 10M records and analyze the indexes statistics using ApexSQL Defrag, on order to perform the SQL Server index Online rebuild process. To rebuild the test index in online mode, check the index then click on Rebuild Online button, under the Fragmentation tab, as shown below:
During the index online rebuild process, let us execute the previous SELECT statement that retrieves data from the same table, making sure that the SQL Profiler tool is running in the background during the online index rebuild process and the query execution.
When the online SQL Server index rebuild operation completed, you can see from the execution statistics collected by the SQL Server Profiler tool that, the online index rebuild operation required:
- CPU time = 73,875, which is about 2.27 times the CPU time required for the offline SQL Server index rebuild
- Number of reads = 3,190,029, which is about 7.2 times the number of reads required for the offline index rebuild
- Number of writes = 240,370, which is about 2.1 times the number of writes required for the offline index rebuild
- Duration = 15,326 ms, which is about 2.1 times the duration required in the offline index rebuild, as shown below:
And the SELECT query that we executed during the online index rebuild operation required:
- CPU time = 3,811, which is about 0.9 times of the CPU time required while rebuilding the index offline
- Number of reads = 116,255, similar to the number of reads required while rebuilding the index offline
- Duration = 1,706ms, which is about 0.75 times of the time required while rebuilding the index offline, as shown below:
It’s clear from the previous results that, although you can query the table’s data and get data faster during the online index rebuild process, the Online index rebuild consumes more resources to complete the rebuild process and takes longer time. In addition, the extra number of reads and writes operations is due to creating a second copy of the SQL Server index during the initial phase of the index rebuild and dropped automatically when the index rebuild process completed.
It is clear also how easy we can check the fragmentation percentage of the indexes directly from ApexSQL Defrag and perform the proper SQL Server index online or offline operation from the same centralized plan then recheck the new fragmentation percentage once the rebuild process completed successfully.
June 10, 2019
