I have installed Ola's scripts:
- https://ola.hallengren.com/scripts/CommandLog.sql
- https://ola.hallengren.com/scripts/CommandExecute.sql
- https://ola.hallengren.com/scripts/IndexOptimize.sql
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)
IndexOptimize
to resolve this. – JP Hellemons Aug 17 '16 at 7:14