Disk and Performance Impact of Online Index Rebuild Operation
Here, we are going to talk about disk and performance Impact of online Index rebuild operation. As we know index rebuild is offline operation due to which indexes become inaccessible. Here, I will describe about SQL Server rebuild index online that will keep SQL Server indexes accessible. I hope you are aware about the difference between Index rebuild and Index reorganize operation. If you are not aware please have a look at attached article. Index Reorganize is already an online operation whereas Index rebuild can be done using offline as well as online method. Performing online index operations include index creation, index rebuild and drop indexes. I will describe online rebuild index operation and its impact on disk and performance in this article and same concept will be applied to online index creation and online indexe drop as well.
There is new feature called Resumable Online Index Rebuild introduced in SQL Server 2017 that is an extension of online index rebuild operation. Learn about Resumable Online Index Rebuild operation in SQL Server 2017 in attached article.
Read More:
- What should be best value for fill factor in SQL Server?
- How to Avoid Page Split in SQL Server?
- How to use Resumable Online Rebuild Operation?
ONLINE Index Rebuild Operation
Rebuilding an index drops and re-creates the index. Indexes becomes unavailable during this exercise that causes an outage for that object. Microsoft has given a solution to avoid inaccessibility of the object during index rebuild operation. We can specify ONLINE keyword value as ON in CREATE INDEX or ALTER INDEX to rebuild indexes online.
When you are rebuilding an index and the ONLINE option is set to ON, the underlying objects, the tables and associated indexes, are available for queries and data modification. Row versioning is used to allow for transactional consistency. SQL Server reindex ONLINE option does not work if your table has BLOB data types or index is an XML index or Spatial index.
I have given the list of T-SQL statements that will rebuild indexes ONLINE on your table. You just need to change the index name, table name and column name from below statements.
Below SQL code will rebuild the clustered index on column EMPID on the table dbo.Salary. As we are rebuilding this cluster index using dropping and recreating the cluster indexes so existing index will be dropped in the process, but it will be available during the operation because the ONLINE option is specified.
CREATE CLUSTERED INDEX CI_Salary_EMPID ON dbo.Salary(EMPID)
WITH(DROP_EXISTING = ON, ONLINE = ON)
We can also use ALTER INDEX statement to rebuild a specific index or all indexes on a specified table. The ONLINE syntax remains the same as the CREATE INDEX statement if you want to run this operation online.
ALTER INDEX ALL ON [dbo.Salary]
REBUILD WITH(ONLINE = ON)
Above statement will rebuild all indexes on table Salary keeping everything accessible during this operation. Now, we can also rebuild clustered index only on the dbo.Salary table. The ONLINE option is specified, which means that the table will be accessible during the rebuild operation.
ALTER INDEX CI_Salary_EMPID ON dbo.salary
REBUILD WITH(ONLINE = ON)
But if we will remove ONLINE option from above statement, then table will NOT be accessible during rebuild operation. You can run below command to validate the accessibility of your table for which you are rebuilding the indexes.
ALTER INDEX CI_Salary_EMPID ON dbo.salary
REBUILD
Disk Space Considerations
Disk space is an important consideration when you create, rebuild, or drop indexes. Inadequate disk space can degrade performance or even cause the index operation to fail. There might be other major performance impact of Online Index Rebuild operation as well. Please consider below recommendations if you are using Online index operations or during SQL Server rebuild index online.
- During ONLINE index rebuild and index creation, additional space is required in the data file to keep the second copy of the index. This is because SQL Server creates a snapshot of the index and once index rebuild or creation completes, second copy of this index will be removed by SQL Server.
- Additional disk space is required for the temporary mapping index. This temporary index is used in online index operations that create, rebuild, or drop a clustered index.
- Make sure the transaction log has been backed up and truncated before running index operations online, and that the log has sufficient space to store the projected index and user transactions.
- Specify SORT_IN_TEMPDB option to ON for the index operation. This separates the index transactions from the concurrent user transactions. The index transactions will be stored in the tempdb transaction log, and the concurrent user transactions will be stored in the transaction log of the user database. Make sure to keep tempdb log and user database log files on separate drives to avoid any disk space issues.
- Verify that the tempdb database and transaction log have sufficient disk space to handle the index operation. The tempdb transaction log cannot be truncated until the index operation is completed.
- Do not run the online index operation in an explicit transaction. The log will not be truncated until the explicit transaction ends.
Performance Considerations
If we think about performance impact of Online Index Rebuild operation on SQL Server then it will put more burden on your database server than offline index operations. Because both the source and target structures are maintained during the online index operation.
ONLINE Index operations will be slower than equivalent offline index operations because insert, update, and delete transactions is increased, potentially up to double. This could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation. Online index operations are enterprise features and are fully logged operations.
It is recommended to use legacy way of index rebuild or index operations if your system allows a maintenance window. Remember users will be restricted to access data during the offline index operation, but the operation finishes faster and uses fewer resources.
If you have a system that runs 24×7 then ONLINE index rebuild or creation will be a better choice but make sure to consider all points described in this article before choosing online index operations. You need to think from disk space and performance both prospect and design your system that can handle the load and sustain during peak hours.
Related Articles
- SQL Server Interview Questions & Answers on Indexes
- Understanding Different types of Indexes in SQL Server
Here, I have described Disk and Performance Impact of Online Index Rebuild operation on SQL Server. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.
- How to Fix SQL Error 1005: A Comprehensive Guide - April 9, 2023
- How to Fix SQL Server Error 207 – Invalid Column Name - April 9, 2023
- How to Fix SQL Error 1045: Resolving Access Denied Issues - April 8, 2023
