| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2010-07-21 : 11:23:08
|
| Hi,We use MSSQL 2000 and our database is ~ 25GB in size. We do index defragmentation on a daily basis and also rebuild indexes once a week. The way our system is set up, about 17% of the data gets purged on a daily basis and new data comes in. After a week, most of our data has been deleted and reinserted.With this set up, it seems to me that rebuilding indexes at least once a week is very important.Currently, when indexes are rebuilt, we use a default fill factor of 80 for all the indexes.I did some reading a while back about fill factor, learning that different tables might require different fillfactor percentage, and best idea would be to test things out. I never got a chance to do it. But almost all of our tables are pretty much the same [i.e. data being purged and reinserted, with no static tables] and 80% seemed to not cause any obvious problems.Recently I began thinking about the fact that all of our tables have an identity column, which is the primary key [and clustered index is built on it], and whether it would make sense to set fill factor to 100% for all such clustered indexes.Could someone please shed some light on this?Thank you! |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-07-21 : 12:00:58
|
| If you never do updates on that data then a 100% fillfactor would be better. You should also use 100% on any non-clustered indexes. Also make sure PAD_INDEX is OFF. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-21 : 13:04:18
|
| I read somewhere that 100% on all indexes was a good policy - even if they have some inserts expected. Dunno if this was advice valid for SQL 2000 though ... I think the idea was that 100% fill reduces the overall index size and increases keys-per-page, so you gain on lookups; and although the very first insert will have to split the page so will the third or fourth (whatever) if you have 80% or 90% fill (and inserts are often clustered, so if there is one insert in a page there may well be others and a page split is imminent anyway).Maintenance Plan in SQL 2000 has an option to replace the fill factor; that wrecked all the carefully constructed fill factors that I had set up in my database (way back then ...), so beware of that if you use SQL 2000 maintenance plans (although you may be able to use it to your advantage if you want to set everything TO 100% ??) |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-21 : 13:59:49
|
| I think a 100% fill factor on a sequentially increasing clustered index is probably OK.I have seen cases for secondary indexes where reducing the fill factor seemed to improve performance by reducing the number of page splits. I found that certain indexes caused page splits at such a high rate that you rapidly ended up with just as many pages in the index as if you had created it with a smaller fill factor, and the scan fragmentation was very high. When I reduced the fill factor from 90% to 70%, there were far fewer page splits, and the scan fragmentation stayed much lower.CODO ERGO SUM |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-07-21 : 14:01:25
|
| robvolk - yes, we never do updates. It is straight inserts only throughout the day from publisher to subscribers through transactional replication. And at night we purge the data as such: Find set of IDs within a certain date range, and then purge data that matches those IDs.Kristen, we do not use a mantenance plan. I use the stored procedure shown below:CREATE PROCEDURE Rebuild_Indexes(@Fillfactor INT = 80)ASBEGIN DECLARE @TableName VARCHAR(255) DECLARE TableCursor CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN DBCC DBREINDEX(@TableName,' ',@Fillfactor) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursorENDGOWhere would I set PAD_INDEX to off here?Thank you! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-07-21 : 15:06:23
|
| MVJ is right.. if your inserts are always monotonically increasing then fillfactor of 90-100 is good. If you expect inserts coming in between rows you might want to leave more space on your index pages and hence lower fillfactor.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-21 : 18:30:54
|
OK, maybe I'm being thick here!If your inserts are always monotonically increasing why would you use anything other than 100%?One related point: if using GUIDS then sequential-GUIDs would be more fill-factor-100%-friendly  |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-07-21 : 19:36:41
|
quote:
If your inserts are always monotonically increasing why would you use anything other than 100%
Nullable and/or varying length columns that could be updated would likely split pages on a subsequent update if inserted at 100% fill. An example is loading (crappy) address data that gets updated by a certification program. Since he won't be updating that's not a consideration, and using 100% fill is a good move. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-22 : 02:27:54
|
| Ah, data page split ... good point, thanks. I was just thinking of pages of monotonically increasing index keys |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-07-22 : 09:54:52
|
| We were using 80% FillFactor due to the lack of understanding - obviously :) That's why I'm trying to learn now to improve the existing system [i.e. decrease index sizes as well as speed up the lookups]Thanks for the inputs! |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-07-28 : 13:16:04
|
| Hi,Just wanted to give an update. We have the following set up:1 Publisher and 5 subscribers in transactional replication. Subscribers are being hit by clients using the stored procedures we created. Yesterday I did the following test:a. Run server trace on all 5 servers, recording duration/read/write for all the stored procedures for 15 minsb. Rebuild indexes using the following fillfactors for each of the 5 machines respectively, 80, 85, 90, 95, and 100c. Repeat step a.d. Analyze results of server traces and compare a. to c. [i.e. before to after]Interestingly enough my results showed that as fillfactor percentage went up, the performance got worse. That is, I got best performance on the machine where fillfactor of 80 was used for all clustered and non-clustred indexes.The results appear to be counter intuitive, as I'd expect higher fillfactor to translate to faster index lookups [and yes, all sps use indexes].Any ideas?Thank you! |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-07-28 : 21:16:23
|
| Since this is replicated tables - I would doubt that you are using IDENTITY as the clustered key. I am guessing that you are probably using some sort of GUID. If so, then a higher fill factor would cause more page splits as data is inserted randomly throughout the index.And, if you are using GUID's - then they obviously take up much more room than an integer and that would be why you see the performance drop as the fill factor goes up. With a higher fill factor, there is less room to add rows to the page so the page is going to end of splitting faster. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-29 : 02:22:46
|
| Just to repeat my earlier point - in case GUIDs are being used:"One related point: if using GUIDS then sequential-GUIDs would be more fill-factor-100%-friendly" |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-07-29 : 14:14:11
|
| what is your workload like? do you have lots of deletes compared to inserts? if you are punching too many holes on the data pages, SQL Server has to read many pages even for few rows.. this degrades performance... so if you have lots of deletes during the day or some nightly batch processes deleting a lot of data its a good idea to at least ALTER INDEX REORGANIZE your data to defragment.. if you cannot do a full index rebuild...Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-07-29 : 17:21:41
|
| jeffw8713 - I guess you are partially correct. My initial description was not entirely true. The fuller picture of our system is as follows: We have a Staging environment where all tables have identity column. The data gets pulled from Staging using our built in pull jobs every 2-3 minutes to Publisher. The pull is strictly in order of ID increasing, so the data should be inserted in increasing order into the id column. Once inserted into Publisher, it is immediately replicated to subscribers (in that same order i'm assuming).So, the data in subscribers is being populated in order of the id (on which clustered index is built) ascending.dinakar - we purge ~ 17% of data every day and do INDEX DEFRAGMENTATION twice a day and OPTIMIZATION once a day.Would this new information change previous suggestions?Thank you! |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-08-09 : 19:39:23
|
| 17% is still lot of data (although your DB size is small).. you could start with 90% fillfactor and see if it gives you better performance..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
Fill Factor for Clustered Indexes with Identity