Sql-server – Index automation job script

indexindex-tuningjobssql server 2014t-sql

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) :
enter image description here

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%.

Best Answer

I would say that Ola scripts work well for SQL 2014 too.

Here is what you should do:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

Anyway I would like to suggest you also to use higher thresholds like 50-80. With modern storages it is better to focus IO on statistics update.

For your query above...

SELECT S.name as 'Schema',
 T.name as 'Table',
 I.name as 'Index',
 DDIPS.avg_fragmentation_in_percent,
 DDIPS.page_count
 CASE
  WHEN avg_fragmentation_in_percent BETWEEN 5 AND  30  then 'ALTER INDEX REORGANIZE'
  WHEN avg_fragmentation_in_percent > 30, then 'ALTER INDEX REBUILD'
  ELSE ''
 END AS CommandType
 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

Please note that this parameter exists in Ola reindexing scrips:

@MinNumberOfPages

Set a size, in pages; indexes with fewer number of pages are skipped for index maintenance. The default is 1000 pages. This is based on Microsoft’s recommendation. IndexOptimize checks page_count in sys.dm_db_index_physical_stats to determine the size of the index.

Related Question