Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO
Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO
Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.
Reference : Pinal Dave (https://blog.sqlauthority.com)
101 Comments. Leave new
Hey Pinal Dave,
U are awesome man. This site is very use-full to me .. God bless u man
These little nuggets
That preety neat.
thanks
Hey dave,
i wanted to find out that in my databases( tables, all) which indexed is being often used, and by which queries.
some hints to that,
i know i can run execution plan for queries and find what indexed are being used, but since then i have to run for all the queries.
use index usage report
Hi,
thank’s for your very helpfull site.
just one remark : it seems to me that you have reversed the 2 sql ‘alter index’ statements in relation to paragraphs.
best regards,
JM
this very OK!
thnx!
Dugi
You are too charming.
God bless you.
In the live server, Is it recommendable to do it.
Should we make two databases and do it on one and then take a backup and restore to another.
What you suggest in the ideal situation.
Please advise.
Rana
I think the T-SQL statement was switched between REORGANIZE and REBUILD in (SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script). Can you update them to avoid confusion.
Thanks.
Thank you Pinal, I understood difference between Rebuild & Reorgnanize
Hi Dave,
Very helpful info!
What’s the easiest way to find out index fragmentation percentage?
Thanks
Sam
Hi,
it was very easy to understand the difference.
Thanks so much,
Oana.
USE asg
GO
ALTER INDEX ALL ON dbo.city_area REBUILD
GO
I executed above rebuild statement, but avg_fragmentation_in_percent did not change.
please help.
Hi and thank you for your so useful and well organized site!
I have a question regarding indexes rebuild/reorganization: if I modify one of the table columns (just increasing its length), should I rebuild/reorganize index based on this column?
Thanks!
Thx..
Job Name Reorganize Index task MaintenancePlan for User DB
Step Name Subplan_1
Duration 00:00:15
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: MICROSOFT\SQLAdmin. …n 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:25:38 PM Progress: 2008-08-21 16:25:47.04 Source: {F2EF9F9E-923D-4BE6-B6A1-376E9171553A} Executing query “DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp”.: 100% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “USE [xxxx] “.: 5% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “ALTER INDEX [PK__ENUM_LIST_DEF__07F6335A] ON [dbo]”.: 11% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “USE [xxxx] “.: 16% complete End Progress Progress: 2008-08-21 16:25:51.05 Source: Reorganize Index Task 1 Executing query “ALTER INDEX [PK__ENUM_LIST_ENTRY__0BC6C43E] ON [db”.: 22% complete End Progress Progress… The package execution fa… The step failed.
Any one know whay above jobs failed
HI dhaka,
please post the error message that was displayed in the particular step which failed. only that will give a clear picture.
Rebuilding Partitioned Index with ONLINE = ON is this possible? How?
Regards
Hi Dave,
Thank you very much! this site is a very big help keep it up (^^,)
HI, i have a index with 93% fragmented, then rebuild it, and now this same index has 73%, why ?
What can i do to have percentage more low?
Drop and Create?
When rebuild were made with ONLINE=ON, affect that?
Thanks for your help!!!