Solved

Benefit of Re-indexing

Posted on 2009-07-16
7
369 Views
Last Modified: 2012-05-07
Can you please explain in simple terms how exactly does re-indexing periodically help a database, in performance?

Thanks
0
Comment
Question by:anushahanna
7 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 200 total points
ID: 24868642
For one, it makes is so when you're searching or sorting data, less pages are traversed to get the data together and to find it.  When you reindex (on a clustered index, mainly), it reorders the rows physically, allowing for easier returning of data, without having to walk the linked list of data rows to make sure all of the data is found. Does that make sense?
0
 
LVL 40

Assisted Solution

by:mrjoltcola
mrjoltcola earned 150 total points
ID: 24868780
I agree with chapmandew, but a side note: this is the topic of much debate among even experienced DBAs; whether reindexing (rebuilding an index) is a worthwhile practice. There are two schools of thought, one that acknowledges the advantage of compaction, as chapmandew says, and the other that thinks there is neglible benefit due to the scalability of B-Trees, and prefers the "don't tinker with something that isn't broken" approach.

In academic terms, rebuilding an index can reduce the depth of the tree and completely balance it again, as well as compact more keys into a given amount of nodes by reclaiming dead space, but the academic benefit is often not measurable since B-Trees are extremely scalable in the first place.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 24868899
I see what you saying in plain terms is, re-indexing will make performance faster/better.

How does this relate to some's idea of 'don't tinker with something that isn't broken'.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 60

Expert Comment

by:chapmandew
ID: 24868943
well, there are routines you can run to see if something is fragmented...but it is up to you based on your system, its uptime requirements, SLAs, etc whether or not you need to do it.  Reindexing does lock the table, so it will be blocking user requests while it occurs.  Defragmenting does not, but it doesn't do as much compacting as reindexing either.  

Here is an article I wrote for determining fragmentation:
http://blogs.techrepublic.com.com/datacenter/?p=251
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24869006
>>re-indexing will make performance faster/better.

Actually that is not what I said. To paraphrase, it _can_ and really _should_ improve, but since B-Tree indexes are extremely scalable in the first place, often there is no measurable performance change. It takes quite a large number of additional blocks in an index to show any IO differences. In an extremely high insert/delete environment for one customer I have a bi-monthly process that rebuilds specific indexes on a 75 million row table. Through the life of the system noone has ever noticed or been able to show through Tivoli or Oracle monitoring that there are any performance differences, pre/post rebuild, except during the index rebuild phase, where there is some activity spike. We measure performance from multiple angles on a 5-minute granularity. So I personally believe that rebuilding indexes too often is a waste of time, but may be worthwhile every so often. But not weekly as some DBAs like to do. Its is good to measure the benefit and prove to yourself just how scalable an index really is.

An indirect benefit is that the index is compacted and may significantly reduce your memory overhead to cache the index, so this is not to be ignored. The IOs may not measurably change but reclaiming 50mb of cache memory might be worthwhil (assuming you were caching the complete index).
0
 
LVL 13

Assisted Solution

by:St3veMax
St3veMax earned 75 total points
ID: 24869328
Put it in simple terms...You have a 'live' encyclopedia which is constantly changing. You want to find out about Spiders for example. Instead of flicking through the entire encyclopedia to find spiders, you refer to the index, which tells you exactly which page to go to.

A SQL Index works on exactly the same principal, by redoing the index's you're updating where to find things, only in this case, the encyclopedia is the table.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 75 total points
ID: 24874620
I'd advise to take a *on-need* approach on index management (rebuilds reorg).  

If on 2005 and above, one can easily determine the need rebuild and reindex based on the usage made of them (DMV's)...

On large and intensive environments (strongly updated/read tables > 1 billion rows), not having a regular reindexing or reorg will cause performance to degrade very quickly.  

hth
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
migration MS SQL database to Oracle 30 62
Query Help - MSSQL - Averages 5 27
Microsoft Access Write errors seem to be caused by bit fields 4 37
syntax sql error 2 14
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question