1

I created a Maintenace Plan, first i reorganize the indexes, then rebuild, and final update the statistics every Saturday. I ran this script for verification:

SELECT 
    S.name as 'Schema',
    T.name as 'Table',
    I.name as 'Index',
    DDIPS.avg_fragmentation_in_percent,
    DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
    INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
    INNER JOIN sys.schemas S on T.schema_id = S.schema_id
    INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id 
        AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
    AND I.name is not null
    AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent DESC

But after the run, the fragmentation is still very high. Is that okay? What could be done?

Thank you for your help! :)

The result

2

Bottom line: fragmentation is irrelevant for small indexes. Never bother about it for indexes less than some 1000 pages, or perhaps we should say 10000 nowadays. If you remember cassette tapes, we always have some noise in the background (my analogy, perhaps works better in Swedish).

Tip1: don't use single quotes for column names in SELECT list. It divert from ANSI SQL and it is "weird". :-)

Tip2: you don't have to join all those tables. Use meta-data functions instead.

Tip3: Heaps don't have fragmentation in the same way as indexes do. So either filter out the heaps or learn about forwarding pointers/forwarded records and handle them separately from your indexes. This might be useful, for instance: https://karaszi.com/rebuild-all-fragmented-heaps.

Tip4: Jumping back and forth on disk isn't as costly as it used to be. Assuming you are on faster storage than old-school spinning disks, that is. I.e., that type of fragmentation might not hurt you much. There's still the aspect of read-ahead, though. See for instance this: http://sqlblog.karaszi.com/fragmentation-the-final-installment/.

Tip5: Use something more reasonable to filter on the fragmentation level, like > 5% instead of > 0%.

Here's a query, simplified and with my recommendations above. But, again, perhaps you shouldn't worry about jumping back and forth in the first place?

SELECT 
 OBJECT_SCHEMA_NAME(f.object_id) AS Schema_
,OBJECT_NAME(f.object_id) AS Table_
,i.name AS Index_
,f.avg_fragmentation_in_percent
,f.page_count
,i.type_desc
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS f
  INNER JOIN sys.indexes AS i On f.object_id = i.object_id
WHERE f.avg_fragmentation_in_percent > 5
 AND f.page_count > 1000
 AND i.type_desc NOT IN('HEAP', 'CLUSTERED COLUMNSTORE', 'NONCLUSTERED COLUMNSTORE')
ORDER BY avg_fragmentation_in_percent DESC
3
  • you shouldn't care if you use an SSD. But if is using an HDD fragmentation indeed can matter. – Francesco Mantovani Oct 13 '20 at 9:50
  • 1
    "Shouldn't care" is a bit of oversimplification IMO :-). My tests (SSD) showed between 30% and 0% performance hit. Numbers vary based on row width and query selectivity. Thanks for calling out HDD compared to radically faster storage like SSD, flash, etc - I forgot to mention that! :-) – Tibor Karaszi Oct 13 '20 at 13:06
  • Thank you very much! :) – Thomas Oct 19 '20 at 7:56
1

But after the run, the fragmentation is still very high. Is that okay? What could be done?

The reason lies in the last column of the output you have pasted. See the column page_count. Unless you have page_count value > 2000 there is no point in rebuilding, reorganizing and updating stats of that index. This is because and I am quoting from article I wrote. The lines are basically taken from SQL Server 2000 BOL which now is not present online and is removed but still holds quite true.

Fragmentation affects disk I/O. Therefore, focus on the larger indexes because their pages are less likely to be cached by SQL Server. Use the page count reported by DBCC SHOWCONTIG to get an idea of the size of the indexes (each page is 8 KB in size). Generally, you should not be concerned with fragmentation levels of indexes with less than 1,000 pages. In the tests, indexes containing more than 10,000 pages realized performance gains, with the biggest gains on indexes with significantly more pages (greater than 50,000 pages).

Also note that

Reason as to why fragmentation remains for small index even after rebuild is that after rebuild the pages which are allocated to index is from Mixed extent Jump . A mixed extent contains mixed pages and extent is collection of 8 pages, these are always the first 8 pages which would be allocated to database when it requires pages to write information. The first 8 pages will always be from mixed extent and after that it would allocate Uniform extents. Reason for allocating first 8 pages from mixed extent is database engine assumes that its quite possible table would be small(at the beginning) and there would not be much advantage in allocating uniform extent,so it internally decides to allocate first 8 pages from mixed extent. As the 8 pages limit is crossed it would start allocating Uniform extents. As the mixed extent is not allocated to any particular IAM chain, this means that it may hold pages allocated to possibly 8 separate IAM . This is very important fact. The first 8 pages allocated by mixed extent could be scattered anywhere and this is what accounts for fragmentation even after rebuild.

1
  • Thank you very much! :) – Thomas Oct 19 '20 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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