Existing system: We generally have a system in place which checks the fragmentation levels of various indexes and accordingly reorganizes or rebuilds them. I have noticed that there are various tables which get rebuilt everyday due to their fragmentation being greater than 40%.
Assumption: I would assume that since the Index pages are getting so highly fragmented on a very regular basis, throughout the day it might be happening that several pages must be getting split to accommodate new data. This page split operation is a very resource intensive operation and hence must be a constant hidden reason of heavy server load. The page split might be happening because we keep the index pages fully filled. This is determined by the fill factor which is applied at the server level and if there is no explicitly defined fill-factor at the index level then server settings get used by default. Server setting default Fill-factor is set to 100% so all the indexes must be getting filled completely.
Investigation: Once the above assumption is confirmed then we can try changing the fill-factor of various indexes to about 80 or 90% to start with so that there is enough space in the pages to accommodate the new data and hence page splits would get drastically reduced. We could then analyze the page splitting for next 3 to 4 months then again increase the fill factor based on our further analysis.
Few important points:
1.) If the clustered index is on Identity_key then there would not be any page splits as the data is entered in orderly fashion and hence there would not be any data to be inserted in between which would cause page splits. Hence the above theory would be applicable on any other type of indexes which is not incremental in nature (It could be Clustered or Non-Clustered Indexes)
2.) Be cautious when you add the fill factor to an existing index as I would believe that the size of the table would increase in accordance with the fill factor. So for e.g., if there are about 10K pages and if we add a fill –factor of 20% then the new index size would become about 12K.
3.) Another point to investigate is that since the number of pages would increase by adding the fill-factor then definitely the queries that might be accessing these tables would have to access more number of pages and hence there would be an increase in IO. So we would need to weigh this against the page split operations that would happen if pages are 100% filled up and see which one would be better option. I would believe that Page-splits would need to be drastically reduced as definitely it is a very heavy operation and hence needs to be reduced greatly
Assumption: I would assume that since the Index pages are getting so highly fragmented on a very regular basis, throughout the day it might be happening that several pages must be getting split to accommodate new data. This page split operation is a very resource intensive operation and hence must be a constant hidden reason of heavy server load. The page split might be happening because we keep the index pages fully filled. This is determined by the fill factor which is applied at the server level and if there is no explicitly defined fill-factor at the index level then server settings get used by default. Server setting default Fill-factor is set to 100% so all the indexes must be getting filled completely.
Investigation: Once the above assumption is confirmed then we can try changing the fill-factor of various indexes to about 80 or 90% to start with so that there is enough space in the pages to accommodate the new data and hence page splits would get drastically reduced. We could then analyze the page splitting for next 3 to 4 months then again increase the fill factor based on our further analysis.
Few important points:
1.) If the clustered index is on Identity_key then there would not be any page splits as the data is entered in orderly fashion and hence there would not be any data to be inserted in between which would cause page splits. Hence the above theory would be applicable on any other type of indexes which is not incremental in nature (It could be Clustered or Non-Clustered Indexes)
2.) Be cautious when you add the fill factor to an existing index as I would believe that the size of the table would increase in accordance with the fill factor. So for e.g., if there are about 10K pages and if we add a fill –factor of 20% then the new index size would become about 12K.
3.) Another point to investigate is that since the number of pages would increase by adding the fill-factor then definitely the queries that might be accessing these tables would have to access more number of pages and hence there would be an increase in IO. So we would need to weigh this against the page split operations that would happen if pages are 100% filled up and see which one would be better option. I would believe that Page-splits would need to be drastically reduced as definitely it is a very heavy operation and hence needs to be reduced greatly
No comments:
Post a Comment