0

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

3
  • 2
    Why you don't use Ola scripts. They work perfectly in sql 2014 too. What has gone wrong in your test?
    – MBuschi
    Jan 21 at 14:50
  • @MBuschi please read my edited post above to see what has gone wrong in my test Jan 21 at 18:38
  • your problem is the default value of the @MinNumberOfPages parameter
    – MBuschi
    Jan 21 at 19:16
3

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.

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.