D365FO Tier-2+ Azure SQL - Index Tuning / Performance Tuning for Non-Prod instances

With Tier-2+ non-prod instances MSFT has taken over running the index tuning scripts however they do not run the majority of time, and you need to run them yourself. While you cannot run their specific scripts, you can use the following to find the indexes that need tuned and then fix the fragmentation and update the sql optimizer about the data which will affect execution plans

 

1.) Run the script to grab all indexes that have a fragmentation >30%

2.) Copy the column "RebuildCommand" and run as separate query - this will fix the physical organization of the index

3.) Once that is done we need to do the same for the "UpdateStatisticsCommand" however because there may be index fragmentation on different indexes but on the same table there could be duplicates within the initial query export. To improve time to execute copy these values into excel and do a remove duplicate process and then run that group. - this will update the sql optimizer which can influence how seeks vs scans, join order, nested loops, which index to use.

4.) Rerun the script to check the new results. It is normal to still see some items in the return list because of system processing and how the main OOB system tables work.

SELECT
    OBJECT_SCHEMA_NAME(ps.object_id) AS SchemaName,
    OBJECT_NAME(ps.object_id) AS TableName,
    i.name AS IndexName,
    ps.index_id,
    SUM(ps.page_count) AS PageCount,
    CAST(SUM(ps.page_count) * 8.0 / 1024 AS DECIMAL(18,2)) AS SizeMB,
    CAST(MAX(ps.avg_fragmentation_in_percent) AS DECIMAL(5,2)) AS FragmentationPct,

    'ALTER INDEX ' + QUOTENAME(i.name) +
    ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(ps.object_id)) +
    '.' + QUOTENAME(OBJECT_NAME(ps.object_id)) +
    ' REBUILD;' AS RebuildCommand,

    'UPDATE STATISTICS ' +
    QUOTENAME(OBJECT_SCHEMA_NAME(ps.object_id)) +
    '.' + QUOTENAME(OBJECT_NAME(ps.object_id)) +
    ' WITH FULLSCAN;' AS UpdateStatisticsCommand

FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    NULL,
    NULL,
    NULL,
    'LIMITED'
    -- LIMITED  = database-wide fragmentation scan
    -- SAMPLED  = samples leaf-level pages
    -- DETAILED = scans all pages and index levels
) ps

INNER JOIN sys.indexes i
    ON ps.object_id = i.object_id
   AND ps.index_id  = i.index_id

WHERE ps.index_id > 0             -- 0 = heap; 1 = clustered; >1 = nonclustered
  AND i.is_disabled = 0           -- Exclude disabled indexes
  AND i.is_hypothetical = 0       -- Exclude hypothetical indexes
  AND i.name IS NOT NULL          -- Must have an actual index name

GROUP BY
    ps.object_id,
    i.name,
    ps.index_id

HAVING MAX(ps.avg_fragmentation_in_percent) > 30

ORDER BY
    FragmentationPct DESC,
    SizeMB DESC,
    TableName,
    IndexName;