May 14, 2009 at 2:07 am
I would like to now if anybody knows if it is normal that an index goes from a fragmentation-level of 0% to over 30% in one day.
Table-facts:
- There is about 15-20 index on the tables in question.
- 2-3 of the indexes increase really fast in fragmentation.
- Number of rows are between 1,5 million - 2 million per table
- Inserts during a day maybe 1500 in each table.
- Deletes about 100 per table.
- Updates around 150 per table.
- Page-count around 6000 per index
Other indexes on the tables does not have nearly the same acceleration in fragmentation.
Any comments or thoughts appreciated!
/JP
May 14, 2009 at 3:14 am
Sounds quite possible and natural for the activity you are describing. Are you actually experiencing poor or reduced performance as a result of the fragmentation?
That said, 15 indexes does sound like quite a lot. How many columns are in the table? You may want to investigate your index utilisation to see if you have any indexes that could be candidates for removal.
Some additional things to consider and investigate in order to get a clearer picture of things.
- How many data pages are in each index?
- How many levels are there in the indexes with fragmentation?
For example, if you have an index that is made up of a relatively small number of data pages then performing data modifications will result in fragmentation relatively quickly, however the overall performance of the index will still be very good becuase there are not many data pages to be traversed by a given query.
- Consider the size and datatypes that are involved in the indexes too.
- Consider whether or not you can reduce the size of the index key by using included colums rather than having additional columns included in the index key.
May 14, 2009 at 3:49 am
You may already know this, in which case please excuse the lesson in egg sucking.
With an index prone to fragmentation, you may also like to review the settings for Fill Factor and Pad Index.
(Setting these lower reduces the need to split data pages as the index grows, but be aware this requires more storage space).
May 14, 2009 at 6:21 am
Indexes on high transactional tables that become fragmented in even hours can be normal in most OLTP configured instances. I have dozens of indexes that require maintenance every four hours. Statistics are also extermely important to maintain on these types of tables in short intervals. If you are on Enterprise use the reorg or if highly fragmented, rebuild online. If you are not on Enterprise then you have to weigh the performance factor of removing the indexes or altering the indexes vs. the way they are now.
Second thing I would do is monitor your index usage extremely closely for a few days to even see if the index is required. Use the sys.dm_db_index_usage_stats DMV to determine the usage. This will also help in determining the types of rebuilds you will want to perform.
May 14, 2009 at 6:25 am
Other indexes on the tables does not have nearly the same acceleration in fragmentation.
this could be due to the fact that the columns involved in these indexes are not amended as frequently, therefore the index will not be updated (and fragmented) as part of the dml statement.....
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
May 14, 2009 at 6:33 am
Thank you for all your replies, much appreciated!
My main concern was if this behavior was something unusual and that something was not correct with the db. Now I know that this is quite normal.
I already made a job that automatically defrags the needed indexes when server is idle every night. The job also stores info about fragmentation, page-count and time taken to defrag each index. Now this job takes about 1 minute for 6-7 indexes so it is no problem. It will also give me a good history of fragmentation rates and duration as time goes.
Next I will see what I can do about the statistics, to keep these up to date!
/JP
May 14, 2009 at 6:34 am
Looking at your figures reducing the fill factor on those indexes may have spectacular results.
I have a few examples of production indexes that would fragment by 50% over 24 hours when set at 100% fill factor.
Reduced the fill factor to 95% - same indexes now lasted a month with only 10% fragmentation.
It's because at 100% fill SQL only leaves one empty row per page before a page split is necessary. For a randomly inserted index (e.g. an ordering system with an index on surname), this can rapidly prove inadequate.
Hope this helps.
Tim
.
May 14, 2009 at 6:37 am
Next I will see what I can do about the statistics
Just to throw it out there. If you are rebuilding the indexes it will also update stats with a full scan. I didn't want to sound missleading in my other reply and cause you to start updating stats after rebuilds. I've seen that often and it is not necessary.
I think SSC-Enthusiastic is on the money as well.
May 19, 2009 at 5:53 am
Just to rap this one up. I did what Tim suggested and set the fill-factor to 90%. When doing this fragmentation basically stopped. After 3 days it is now 0.03 fragmented.
Thanks again for answering my question.
May 19, 2009 at 5:56 am
Thanks, I'm glad it worked so well for you.
Tim
.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply