Report template library

/***********************************************************************/
/* Power BI Sentinel                                                   */
/* Process to change the clustered index for dbo.LogRecord             */
/* Last Updated: 27th February 2026                                    */
/*                                                                     */
/* Process:                                                            */
/* 1 - Check the current clustered index to see if it needs updating  */
/* 2 - Upgrade the database to a high spec, recommended S7, 800 DTUs  */
/* 3 - Run the clustered index upgrade script                          */
/* 4 - Downgrade the database back to the usual spec                  */
/* 5 - Run the first query again to check the new clustered index      */
/*                                                                     */
/***********************************************************************/

/* 1 - Check the current clustered index to see if it needs updating  */
/* The KeyColumns column should be 'CreationTime ASC, Id ASC'         */
/* If it is, no update is required                                     */
/* If it is different, please proceed to update the clustered index    */

SELECT
    s.name                          AS SchemaName,
    t.name                          AS TableName,
    i.name                          AS IndexName,
    i.type_desc                     AS IndexType,
    STRING_AGG(
        CASE
            WHEN ic.is_included_column = 0
            THEN c.name +
                CASE WHEN ic.is_descending_key = 1
                    THEN ' DESC'
                    ELSE ' ASC'
                END
        END, ', '
    ) WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns,
    CASE WHEN STRING_AGG(
        CASE
            WHEN ic.is_included_column = 0
            THEN c.name +
                CASE WHEN ic.is_descending_key = 1
                    THEN ' DESC'
                    ELSE ' ASC'
                END
        END, ', '
    ) WITHIN GROUP (ORDER BY ic.key_ordinal) = 'CreationTime ASC, Id ASC'
    THEN 'OK' ELSE 'Needs Updating' END AS [Status]
FROM sys.tables t
JOIN sys.schemas s
    ON t.schema_id = s.schema_id
JOIN sys.indexes i
    ON t.object_id = i.object_id
JOIN sys.index_columns ic
    ON i.object_id = ic.object_id
    AND i.index_id = ic.index_id
JOIN sys.columns c
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
WHERE
    s.name = 'dbo'
    AND t.name = 'LogRecord'
    AND i.type = 1  -- 1 = Clustered index
GROUP BY
    s.name,
    t.name,
    i.name,
    i.type_desc,
    i.is_unique,
    i.is_primary_key,
    i.fill_factor,
    i.filter_definition;

/***********************************************************************/
/* 2 - Upgrade the database to a high spec, recommended S7, 800 DTUs  */
/* BEFORE updating the clustered index, temporarily upgrade the        */
/* database. Changing this table is a very intensive process and will  */
/* take time. To ensure it runs quickly and doesn't timeout, it is     */
/* important to maximise the power of the database.                    */
/* The actual spec required will depend on the size of your LogRecord  */
/* table.                                                              */
/***********************************************************************/

/***********************************************************************/
/* 3 - Run the clustered index upgrade script                          */
/* The database will be available and online during this process       */
/***********************************************************************/

CREATE CLUSTERED INDEX [IX_CLUS_LogRecord]
ON dbo.LogRecord (CreationTime, Id)
WITH
(
    DROP_EXISTING = ON,
    ONLINE        = ON,
    RESUMABLE     = ON,
    MAXDOP        = 1
);

/* Check progress in a separate query window:                          */
SELECT *
FROM sys.index_resumable_operations
WHERE object_id = OBJECT_ID('dbo.LogRecord');

/* Pause, resume, or abort the operation if needed:                    */
--ALTER INDEX IX_CLUS_LogRecord ON dbo.LogRecord PAUSE;
--ALTER INDEX IX_CLUS_LogRecord ON dbo.LogRecord RESUME;
--ALTER INDEX IX_CLUS_LogRecord ON dbo.LogRecord ABORT;

/***********************************************************************/
/* 4 - Downgrade the database back to the usual spec                   */
/***********************************************************************/

/***********************************************************************/
/* 5 - Re-run the first query to confirm the new clustered index       */
/***********************************************************************/