tweaking sql server 2000 indexes 1

[shaminda]

In one of my visual basic programs on and off the program runs slow. It used to run slow all the time. After adding some indexes it is much better. I want to further tweak the indexes and see whether there is a performance improvement. Here are some of the questions I have?

1) How often should I re-index? At present we do it once every Sunday.
2) When ever we run our backups it slows it down. Do other jobs affect the database? We have a job that run every 15 minutes. Another job that run every hour.
3) The fill factor on the indexes is set to 90%. Will it make it a difference if I change it to 80%? In one of my tables that I have four indexes every 30 seconds I do an insert an then about 4 SQL statements.

Thanks in advance for any help.

 

[mrdenny]

1) That depends on how often your indexes get heavily fragmented. Weekly is what most people do.

2) That's not uncommon. When you backup the database you are putting a large strain on the disks which is why things are going to be slowing down. Anything which runs against the database will impact performance.

3) Lowing the fill factor could help. Do a DBCC SHOWCONTIG and see how full the indexes are. If they are highly fragmented by the time the week is over you need to increase the white space by lowering the fill factor.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog