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
You didn’t really EXPLAIN the difference between the two.
Other than being able to reorganize XML indexes online, is there any difference in the final result when you use Rebuild vs. Reorganize? Is Rebuild recommended for heavliy fragmented tables because it’s faster than reorganizing heavliy fragmented tables?
Sorry, but I don’t see much “explaining” done here.
Me too, I miss 2 very important things:
1. rebuilding an index automatically also updates the statistics! If you reorganize, you should always update your statistics afterwards!
2. some mention the option ONLINE of rebuilding, but be aware that some cursors will fail when they are launched when the index rebuilding is done “error: schema modification was detected”
Hi all,
why REBUILD index causing the DB to grow ?
I rebuilt 1 off the indexes and DB grew from 270MB to 550MB .
My understand is that REBUILD should shrink or at least no allocate more space .
I was running REORGANIZE and the DB didn’t grow
Can you give more info: is your data or log file that grows, what’s your recovery model (simple? full?)
I m facing same problem.When I rebuild index the db size is grow and when I reorganize the db size is not grow.In my case log file grows.
good day…
I just want to know what to do when there are clustered index and non-clustered index in one table (i found out that the reason why i cant rebuild online is because of these – clustered and non-clustered in one table)
some field says rebuild and the other reorganize.
but when i tried to rebuild it i got an error because there are clustered index.
should i use rebuild index (online or offline)
or
should i just reorganize it?
thanks…
why my log files become full ?, from 5GB to 50GB when rebuilding all the indexes ? and after it finished, my log file didn’t go small…
I use FULL recovery model….
is there any way to prevent this ?
and I’ve wierd situation, sometimes, after I rebuild index the fragmentation of that index is still high, 70%
but when I do backup first, and doing rebuild index after that, the fragmentated index become small
how to rebuild index on a db as a whole?
Hi Dave,
Could You Please Provide Query Performance Tuning in Sql Server 2008.
Thanks
HariKrishna
Supposing my database is in Full Recovery mode. This means that doing an index rebuild will take some considerable size on the transaction log. (Index rebuild drops and recreates – using Create Index – all indexes). What about index reorganization? Is there also a considerable size impact on the transaction log?
Thanks!
George.
depends on the fragmentation and the size of the table. With reorganizzing you must be aware that this theoretical example will reorganize the whole table because it uses a kind of bubble-sort method: if the logical first page is physically the last.
Hi every one. I’m not a dba specialist, but a .net developer.
As i understand, Index Rebuild drops current index values and recreates new ones. In this case, what about the joined and related tables where the value is used as FK in another table ? This also needs to be updated to the new values?
Appreciate any explanation.
A FK has nothing to do with indexes. It is however recommended to put an index on a FK, but the FK itself is not an index, only a constraint.
I see a lot of people asking why thier databases and log files grow when they rebuild indexes. Mr. Pinal has explained that an index rebuild actually drops and then recreates the index. This means space is consumed in the process and if your database is set to full, this also means transactions are written to the log. As a part of your index rebuilding, you should backup both the database and the log in order to reclaim space used in the process.
Excuse for that I interfere ?At
me a similar situation. I invite to discussion. Write here or in PM.
How did Microsoft came with these thresholds?
if frag_level30% then REBUILD
The idea is that I dont have Enterprise Edition’in production environment and I want to be able to do index maintenance without blocking the tables. And so my only option is to ALTER INDEX … REORGANIZE.
I do a very compreshensive index maintenance which allows for rebuilds and reorgs based upon certain parameters of fragmentation. I also give the option to do Online Rebuilds. My process logs all fragmentation before and after the rebuild or reorg. Of course a rebuild reduces the fragmentation greater than a reorg. But I have found one problem. When I choose to do an Online Rebuild, the fragmentation actually is higher after the rebuild than it is before. The version of SQL Server is 2008 10.0.2734. Is there an explanation as to why the Online Rebuild would cause increased fragmentation whereas a regular rebuild or reorg would not?
MS
How to rebuild all the indexes in a database?Please suggest me.
This is very helpful but when I try to defrag my indexes, nothing happens…I drop and recreate them and they are still fragmented…and yes I have the update statistics command after rebuilding or reorganizing…if any of you guys know why this is happening, please let me know.
What size are the tables?
Small tables (for me around 200 pages) will always have high fragmentation. For these tables you don’t really gain much by having an index on them anyway.
The whitepaper on sql server 2000 indexing suggests ignoring anything under 1000 pages.
How to Change collation on master database, SQL server that has different collation on master and user database
Is there any metadata info which tells when a INDEX was last reorganised or rebuilt?
I have set up maintenance Plan for Rebuilding Indexes on a nightly basis. It runs successfully, but does not de-fragment the indexes. I have had a discussion on this with Microsoft Engineers. and They informed me that Rebuilding Indexes with Maintenance Plan sometimes does not de-fragment the indexes. But I am not happy with this statement. What do you think of it?
Hi Pinal,
Thank you.
Can you please explain the conditions under which SQL Optimizer uses a wrong index than what it should logically? For example, using a clustered index scan instead of using a NC which was created for that particular column. For example, I am searching for VENDORS and there is a NC index on VENDORS but SQL is using a Clustered INdex that is on an identity column.
Hemant
i am confused, when i used penal command on adventurework Db its works fine.
but when i used the following on sql2005 enviornment it always give me error, please advise
USE DB;
GO
ALTER INDEX ALL ON dbo.mytable REBUILD
GO
error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘INDEX’.
Probably you have a compatibilty level of SQL Server 2000 (80).
Hi Pinal,
My Full-Text search takes a long time to perform the search operation. The table on which the Full Text is enabled, contains 97 Lac records and every 1 hour 3 to 4k records are imported to this table.
During the FTS, CPU shows 100% performance on the database server with high CPU consumption of this process: MSFTESQL
The rebuilt on the full text catalog is performed every night. I also tried to do a reorganize every-night but it did’nt helped me.
Could you please guide me what should I do in this case and will Rebuild or Reorganize of FTI , boost performance.