June 21, 2019 at 9:19 am
hello to everyone ,
i notice in database tha some indexes the total fragmentation is more than 50%,60%...
is this bad for the database performance.is there a way to reduse this percent, may be with rebuild index task?
June 21, 2019 at 12:19 pm
Let's start with the last question first, yes, an index rebuild will reduce the level of fragmentation.
First question, is fragmentation of indexes bad?
Well, probably not, no. Fragmentation hurts in a couple of ways. Because fewer rows are on a page, you waste a little bit of storage, and you have an index that's further spread out across the disk than it absolutely has to be. This can add a few reads, specifically when dealing with scans. Further, since SQL Server reads data page-by-page into memory from disk, fragmentation leads to some more memory use. This is again, made worse when dealing with scans.
However, there's also quite a lot of overhead involved in, first, defragging an index through a rebuild, and second, in splitting pages again because that's the nature of your data structures and indexes. There's quite a lot of evidence that the overhead of rebuilds and splits far outweighs the benefits of reduced storage and memory use. This is more especially true when dealing with queries that are primarily seek oriented.
So, would I spend lots of time on a modern system, with modern disks mucking about rebuilding the indexes constantly because of fragmentation? No. This does raise two points though. First, columnstore indexes have a secondary use for the index rebuild. With a columnstore, the rebuild will defragment the storage of the index, yes, but more importantly, it moves changes from the delta store within the columnstore index to the appropriately pivoted and compressed columnstore locations. I would still rebuild columnstore indexes fairly frequently because of this second thing, not the fragmentation. Second, rebuilding indexes updates the statistics on those indexes. People tend to seriously misunderstand the importance of statistics and statistics maintenance. Some rely on the index rebuild process to maintain their statistics, which is problematic. However, if they stop rebuilding indexes, they don't then start updating statistics, which has serious negative consequences. So, if you do stop rebuilding your indexes, make darned sure you're properly maintaining your statistics (which you should be doing independent of index maintenance anyway).
Final note, reorganize has long been recognized as one of the biggest waste of resources within the system. I would never recommend it's use.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply