Solved

Benefit of Re-indexing

Posted on 2009-07-16
7
370 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 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