Our Production instance is running SQL Server 2014. There needs to be an automated Index defragmentation job that will either reorganize or rebuild the indexes based on the fragmentation level for a specific table in a specific database. This job needs to be scheduled and ran on a weekly basis due to large data load and the recent performance issues we have been facing.
Unfortunately with the maintenance plan in 2014, I am unable to set the % limit of when the index should be rebuilt or reorganized. I have tried to test our Ola Hallengren script, but that works best with 2016 or 2017.
I have a script that detects the fragmentation level:
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
Now all I need to do is to combine the rest of the requirement:
-if avg_fragmentation_in_percent > 5 AND < 30 , then ALTER INDEX REORGANIZE
-if avg_fragmentation_in_percent > 30, then ALTER INDEX REBUILD
Can someone please help me with the query? Thanks in advance.
**Edited/ Update:
When I execute this query:
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 T.name= 'WSSSync_Partition25'
ORDER BY DDIPS.avg_fragmentation_in_percent desc
This is my output (Take a note of the 99% fragementation) :
Then I run Ola's script:
EXECUTE dbo.IndexOptimize
@Databases = 'WSS_UsageApplication',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'WSS_UsageApplication.dbo.WSSSync_Partition25'
The script runs perfectly, however when I re run the first query to find the fragmentation level. The fragmentation remains the same at 99.6%.