RE-Index Problems

[outofservice]

Ive been posed this question but not quite sure how to answer it apart from the obvious answers.

What would cause a table to require reindexing on a daily basis if it has 100 transactions per second writing to it? Also, the sql service cannot be stopped for down time and the whole process of data writes and daily reindexing is grinding the database to a halt.

Now I thought it could be due to using the wrong type of index, too many indexes, too few but could it be something else? What (or where) would I start to look to increase the current performance and avoid re-indexing so often?

Any thoughts?!
Lauryn Bradley
SQL Server DBA

 

[tlbroadbent]

There could be many factors, such as those you've mentioned. Reindexing is requried because indexes become fragmented. Numerous transactions, poor index design or even poor application design can cause this fragmentation.

Perhaps the transactions are inserts and the rows inserted are causing data or index pages to be split. Maybe the transactions are modifying indexed columns. When this happens, index rows and even pages must be moved.

We have a 3rd party software package that deletes and then re-inserts rows with the same values in a relatively small table. This table is a master table used in many views and stored procedures. All processes slow as the table is updated because indexes become fragmented very quickly.

Sometimes a clustered index on an identity or date column causes fragmentation problems. Changing the clustered index to a different column may help.

Creating compound indexes on a lot of columns can cause problems, especially if the compound index is the clustered index. I recently saw an example where nearly every column was included in a clustered index. The table also had three other indexes. These indexes each carry the clustered index so the indexes were huge. Reindexing was taking hours because of this. Clustered indexes should be small.

Some fragmentation problems can be alleviated by using a smaller fill factor on the indexes because this leaves room for growth on pages without splitting pages.

SQL Server 2000 includes DBCC INDEXDEFRAG which can be run without having the same performance impact of DBREINDEX. INDEXDEFRAG is an online operation that does not hold long locks and does not block queries and updates. Check it out if you are running SQL 2000. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.