0

I have installed Ola's scripts:

and ran:

EXECUTE dbo.IndexOptimize
@Databases = 'mydbnamehere',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

It took about 20 minutes. and after that I ran this SQL statement:

SELECT name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (
   DB_ID(N'mydbnamehere')
 , NULL
 , NULL
 , NULL
 , NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE avg_fragmentation_in_percent > 30

And I still had about 19 rows with an avg fragmentation between 99 and 40 %

99,65075669383
99,0767455279861
98,7414187643021
98,5574148874784
98,4420080784766
97,8723404255319
97,5903614457831
97,4522292993631
96,8911917098446
96,551724137931
95,4356846473029
87,5
76,1904761904762
75
51,063829787234
50
50
40
33,3333333333333

Did I make a mistake in my parameters? It did run and rebuilded and reorganized several indexes. It took down the list from about 25 to 19 rows. (4x foreign keys, 3x primary keys, 4 indexes and the rest without a name)

  • 1
    how many rows and pages in those tables that are flaged fragmented ? – Kin Shah Aug 16 '16 at 15:06
  • don't know, but average fragmentation is far over 30% even almost 100% I expected the IndexOptimize to resolve this. – JP Hellemons Aug 17 '16 at 7:14
1

Remember indices with less that 1000 pages are ignored by default, this can be changed via PageCountLevel parameter but it's based on Microsoft's recommendation according to ola hallegreen's reference. Check this other answer for further details.

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.