Index rebuild performance increase

Question

Index rebuild performance increase

kellyrmartin-805750

SSChasing Mays

Points: 645
More actions
October 19, 2009 at 1:14 pm

#142581

Over the past month I have been hearing more and more complaints about slowness in applications that access our database. I tried many different things and finally did an index rebuild yesterday and magically everyone now reports that their speed issues are gone. Is it possible fragmented indexes can cause a noticeable decrease in DB performance?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 1

Heh.. Yes! Index fragmentation and out of date are the MAIN performance problems for a system, just after really poor design of tables and/or poorly written queries.

You can find the most demanding queries, or slower queries in your system, and check the execution plans to ensure the statistics are up-to date, and if the indexes are used (scans vs seeks). Looking at the most expensive queries usually tells you a lot about the slowness of a system.

Cheers,

J-F

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 2

Is it possible fragmented indexes can cause a noticeable decrease in DB performance?

Yes,

Merely having Indexes will not keep the system at high performance, with the increase in the data in the underlying table, the Indexes keeps fragmented.

Index rebuild will recreate the Indexes and all underlying stats, so it is a good maintenance practice to reindex the or defrag the Indexes regularly.