Difference between Index Rebuild and Reorganize
Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. We use SQL Server Index Rebuild and Reorganize operation to remove fragmentation level of the indexes. Let’s have a look at the differences between rebuild and reorganize indexes.
Index Rebuild vs Index Reorganize
Index Rebuild operation first drops and then recreates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting. Microsoft recommends Index Rebuild operation to defrag indexes if the fragmentation level of your index is greater than 30%. You can execute Index rebuild operation online as well as offline. Online index rebuild operation is enterprise only feature and you can use it if you have SQL Server Enterprise edition.
Reorganizing an index uses minimal system resources. It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Microsoft recommends using Index Reorganize operation to defrag indexes if the fragmentation level of your index is greater than 5% and less than or equal to 30%. Reorganizing an index is always executed online.
Read More:
- What should be best value for fill factor in SQL Server?
- How to Avoid Page Split in SQL Server?
- SQL Server Interview Questions & Answers on Indexes
How to Check Fragmentation Level?
First, we detect the fragmentation level of the indexes. Once we have the details about fragmented indexes then we can decide whether to rebuild that index or reorganize it. We can get index fragmentation level using DMV sys.dm_db_index_physical_stats or by launching dashboard report. Find the average fragmentation percentage of all indexes in the table dbo.Salary of database techyaz by running below T-SQL script.
USE Techyaz;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'Techyaz'),
OBJECT_ID(N'dbo.salary'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
We can see the output of this code in below image. As i have only one table and two indexes in my database but if you have multiple tables and indexes then output will look accordingly.
Another way to check the fragmentation level of your indexes is by running dashboard report. Right click on your database, click on “Reports” then “Standard Reports” then finally click on “Index Physical Statistics” report. You can see this in below screenshot.
Once you click on “Index Physical Statistics” report, a dashboard report will run and appear on right side pane as per below screenshot. You will get fragmentation level for each index here.
How to Remove Fragmentation Level?
There are two ways to remove fragmentation level of indexes. These are by either using Index Rebuild Operation or by using Index Reorganize Operation. Here, i will show you how to remove fragmentation using both ways one after another.
Remove Fragmentation using Index Reorganize Operation
We can see our index has very low fragmentation level that is less than 30% in above images so we will reorganize it rather than rebuild. We will rebuild only those indexes that have more than 30% of fragmentation level. Run below T-SQL command to reorganize index IX_Salary_EMPID.
USE Techyaz;
GO
ALTER INDEX IX_Salary_EMPID
ON dbo.Salary
REORGANIZE ;
GO
If you want to reorganize all indexes of this table dbo.salary in one shot, you can use ALL keyword in Alter Index command. Run below command to reorganize all indexes of table dbo.salary in one go.
USE Techyaz;
GO
ALTER INDEX ALL ON dbo.Salary
REORGANIZE;
GO
Remove fragmentation using Index Rebuild Operation
Similarly, we will rebuild all those indexes that are highly fragmented. As I don’t have any such index but if you will get any index that has more than 30% of fragmentation then you can run below command to rebuild that index.
USE Techyaz;
GO
ALTER INDEX PK_Salary_EMPCODE ON dbo.Salary
REBUILD;
GO
If you want to REBUILD all indexes of dbo.salary table in go then again we will use ALL keyword in ALTER Index statement as we did in above example during REORGANIZE.
USE Techyaz;
GO
ALTER INDEX ALL ON dbo.Salary
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
If your system does not allow any maintenance window and run 24×7 then you can use online method to rebuild these indexes. Read attached article to learn how to rebuild indexes online in SQL Server. Even SQL Server 2017 has given more control on index rebuild operation by introducing a new feature called Resumable Online Index Rebuild. Read the attached article to learn about this feature in which we can pause a index rebuild operation and resume it later from the point it was stopped.
I hope you like this article. You can drop your questions and feedback in comment section. 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
1 Response
… [Trackback]
[…] Read More: techyaz.com/sql-server/indexes/difference-index-rebuild-reorganize/ […]