Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Benefit of Re-indexing

Posted on 2009-07-16
7
Medium Priority
?
378 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 800 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 600 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 300 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 300 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

722 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