what is the optimal fillfactor for an Index?

[Glowworm27]

Hello all,

Was setting up some indexes because we have a huge database, over 65 Gigabytes, and am having problems with non-responsive queries.

What would be an Ideal fill factor for such a big database?

or would it be best to leave it blank and let sql decide?


Thanks


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[jitter]

If it is a reporting type database (OLAP) then you can set your fill factor high, even 100 if you want. This will help reporting time because sql server does not have to look at as many pages. In this type you want your data load to occurr off hours since the fill factor will slow it down.

If it is an (OLTP) with transactions then you might want to set the fill factor lower. If it is to high then this will cause more page splits when data is entered and can cause performance degredation. You could use 60 or 70 for your fill factor in this type of situation and that way SQL Server can run inserts and not have to have page splits as often.

I am no expert but that is my take on it. Someone may know better.

jitter