Solved

Benefit of Re-indexing

Posted on 2009-07-16
7
367 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now