SQL Server Performance Forum – Threads Archive
Should fill factor be set manually?I’m reading about the fill factor setting in Sql Server 2005. Most sources recommend a setting of 80 to 90, depending on how the table is used. When I started looking at our current productions settings, though, I find nothing has been set at all. All the fill factor boxes are unchecked and no value has been entered. So, do most of you end up leaving the fill factor unset? Does this work best in most cases?
Default vlaue is zero (100)…I change the setting but not in all tables/indexes…. Read the following… Read BOL SQL Server 2005 Fill Factor http://msdn2.microsoft.com/en-us/library/ms177459.aspx If you are not sure what to make the fill factor for your indexes, your first step is to determine the ratio of disk writes to reads. The way to do this is to use these two counters: Physical Disk Object: % Disk Read Time and Physical Disk Object: % Write Time. When you run both counters on an array, you should get a good feel for what percentage of your I/O is reads and writes. You will want to run this over a period of time representative of your typical server load. If your percentage writes greatly exceeds the percentage of reads, then a lower fill factor is called for. If your percentage of reads greatly exceeds the percentage of writes, then a higher fill factor is called for. Another Performance Monitor counter you can use to help you select the ideal fill factor for your environment is the SQL Server Access Methods: Pages Splits/Sec. This counter measures the number of page splits that are occurring in SQL Server every second. For best performance, you will want this counter to be as low as possible, as page splits incur extra server overhead, hurting performance. If this number is relatively high, then you may need to lower the fill factor in order to prevent new page splits. If this counter is very low, then the fill factor you have is fine, or it could be a little too low. You won’t know unless you increase the fill factor and watch the results. Ideally, you want a fill factor that prevents excessive page splits, but not so low as to increase the size of the database, which in turn can reduce read performance because of all the extra data pages that need to be read. http://www.sql-server-performance.com/rebuilding_indexes.asp
Thanks much, MohammedU. That helps me significantly.