August 19, 2021 at 5:24 pm
So I tried Ola Hallengren's Index on one of the DB with parameters mentioned below. After running the script, indexes are still fragmented. Page count for atleast 20 indexes are over 4k. Any help is highly appreciated.
EXECUTE [dbo].[IndexOptimize]
@Databases = 'TestDB',
@LogToTable = 'Y',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 60,
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@SortInTempdb = 'Y'
August 19, 2021 at 5:34 pm
No wonder I am losing hair.....
So I created one of the table from an existing DB, created one non clustered index, inserted more records into it. Made it 85% fragmented. Executed Ola's Index rebuild script again and now fragmentation is below 3%, even though the table is still heap. I am not getting it. Why it didn't work for a different index but worked for this? What am I missing?
August 20, 2021 at 11:22 am
How big are those indexes? Small ones simply can't be defragmented.
BTW, common practice now is to kind of ignore fragmentation. It's just not that big a deal. In fact, constantly defragging, and then fragmenting again, is a bigger load on the system than the extra reads from fragmentation for most people most of the time. General practice now is to focus on getting a good fill factor and then leaving the indexes alone. Exception to this is columnstore.
"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
August 26, 2021 at 7:50 am
This was removed by the editor as SPAM
August 27, 2021 at 10:30 am
This was removed by the editor as SPAM
September 12, 2023 at 10:08 am
This was removed by the editor as SPAM
September 12, 2023 at 10:11 am
This was removed by the editor as SPAM
September 12, 2023 at 11:49 am
This was removed by the editor as SPAM
September 15, 2023 at 6:57 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply