Solved

Benefit of Re-indexing

Posted on 2009-07-16
7
368 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

863 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

27 Experts available now in Live!

Get 1:1 Help Now