February 9, 2009 at 4:37 am
Hi Experts,
We have a performance issue with one of our server. Can anyone please explain what is the use of doing index rebuild\reorganize and updating the statistics.Which one is better?
What is the difference in doing rebuild\reorganize and update statistics?
TIA
February 10, 2009 at 8:45 am
Doing a reindex, is particularly important if you have done any bulk inserts or there have been any big changes to the data in the DB.
It is basically containing a sort order of the data contained within the table to allow for faster searching.
Updating the statistics, will tell SQL Server how many rows/The size of the tables/indexes etc to give an accurate reflection opf what is in the table, and again if the stats are inaccurate then you may see performance issues as a result.
February 10, 2009 at 8:48 am
If the issue is server-wide as you suggest though, then you'll have to narrow it down to slow running queries to see if it is infact, an indexing issue, or stats, or if the problem lies elsewhere.
February 10, 2009 at 8:51 am
The difference is that reorganizing an index just defragments the index, while rebuilding the index creates a fresh copy of it, and updating the statistics gives SQL Server an idea of how many rows have how many different values. They aren't the same thing at all.
To determine what you need to do to fix the performance problems, I recommend looking at the execution plans for your most demanding queries. Check the indexes they use for fragmentation. The execution plan itself will usually tell you if the stats are out-of-date.
If you don't know which queries are running too slowly, start with a trace on batch completion.
Once you know what queries, check execution plans.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply