2

I have some index with very high fragmentation, 85-100% and rebuilding does not help

The only clue I have is that it may not work if the data file is fragmented on disk and that could very well be the case. However, data is stored on a high performance SSD SAN. Disk fragmentation doesn't really mean anything.

I have the same problem with multiple index in the same database but the one I am most concerned with has 847653 pages and fillfactor 75%. Most other index have fill factor 90% and many of them has the same issue.

This is the script used

SELECT
    CASE WHEN MAX(ps.avg_fragmentation_in_percent) > 30 THEN
        'ALTER INDEX [' + i.name + '] ON [' + sc.NAME + '].[' + so.NAME + '] REBUILD With (maxdop = 1);'
    ELSE
        'ALTER INDEX [' + i.name + '] ON [' + sc.NAME + '].[' + so.NAME + '] REORGANIZE;'
    END AS [query], max( ps.avg_fragmentation_in_percent)
FROM
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
    INNER JOIN sys.indexes i
        ON ps.OBJECT_ID = i.OBJECT_ID
            AND ps.index_id = i.index_id
            AND ps.database_id = DB_ID()
            AND i.name is not NULL
            AND ps.avg_fragmentation_in_percent > 5 -- min % to return
            AND ps.page_count > 1000 -- otherwise table is to smal.
    INNER JOIN sys.objects so
        ON i.OBJECT_ID = so.OBJECT_ID
    INNER JOIN sys.schemas sc
        ON sc.SCHEMA_ID = so.SCHEMA_ID
GROUP BY sc.NAME, so.NAME, ps.index_id, i.name
ORDER BY sc.NAME, so.NAME, ps.index_id DESC;

Since the question is closed being too broad, I have edited it back to the original question.

Improve this question
11
  • 2
    Why do you say rebuild does not work? Do you get an error?
    – Anthony Genovese
    Oct 7, 2019 at 14:32
  • 3
    Have you checked FILL-FACTOR of the particular index?
    – Shekar Kola
    Oct 7, 2019 at 15:37
  • 5
    How many pages are in these indexes. I have seen indexes do this when there are too few pages to logically order them, and one insert or update could literally take it from 0 to 99% fragmented or rebuilding does not have any effect. This is especially true when you have less than 4 pages. As a general rule of thumb though, an index with less than 1000 pages can be skipped from re-indexing tasks.
    – WadeH
    Oct 7, 2019 at 15:39
  • 1
    Please post the script you ran to rebuild your indexes. A lot of things could be factored including the minimum page count, rebuild threshold, if you use online but the index type doesn't allow an online rebuild. Also, did you do anything AFTER you rebuilt them... like shrink your database? And how big are these tables? If it's not fragmented and then a few CRUD operations are done and it's 90% fragmented, then i'd guess they are very small (like, almost empty tables) and thus have very few pages or your fill factor is something insane, like 5%
    – S3S
    Oct 7, 2019 at 15:46
  • It is many index that has the same problem in this database. The script is looking at index with more than 8 pages but on the table with performance problem I currently have fill factor 75, 847653 pages and 85% fragmentation. I have rebuilt in many times manually without error messages. I do not do anything with the database afterwards and I am sure the index is not actually fragmented by inserts or updates in the time it takes for it to return a high number of avg_frag.
    – vikjon0
    Oct 7, 2019 at 17:00

1 Answer 1

Reset to default
0

As mentioned in the comments there are various factors that could cause fragmentation, and following are the areas that i can think of in your case.

Fill Factor
Seems it's already lower than normal on particular index. However, consider fine tuning with it (in development server) if following options are not applicable in your scenario.

Auto Shrink
Verify by any chance the AUTO SHRINK option set to ON at database level as this could cause heavy fragmentation

GUID Columns
There is also possibility when the entries are inserted without an order, switching to SEQUENTIAL-GUID would be better option (if possible)

Improve this answer
6
  • Lower the fillfactor further helps my performance issue but does not explain the numbers. Autoshrink is not enabled, but you agree physical fragmentation on disk is a factor? How do I deal with that on a ssd SAN?
    – vikjon0
    Oct 7, 2019 at 18:36
  • Sequential guid looks interesting. The current clustered index on the main problem table is on insert date but many other index are on primary key. I will bring this up with the supplier.
    – vikjon0
    Oct 7, 2019 at 18:41
  • Hi @vikjon0, I believe, the physical disk fragmentation depends on file level movement i.e. .mdf, .ndf, .ldf or any other files stores in disk wherein the Index fragmentation depends on 8KB data pages movement. So the disk fragment can only be considered when there is rapid growth/shrink of database files or any-other files movements (copy/delete)
    – Shekar Kola
    Oct 8, 2019 at 7:04
  • Thanks, I am not sure I understand exactly but I will try to read up on it. I assumed the mdf would be fragmented from the beginning if I copied it to a disk with lots of other smaller files - that does shrink and grow.
    – vikjon0
    Oct 8, 2019 at 9:21
  • Maybe the correct question is how does sys.dm_db_index_physical_stats work with disk fragmentation and how to deal with that on SSD
    – vikjon0
    Oct 8, 2019 at 10:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.