Code Review – RockyPC / WideWorldImporters – 2026-06-05

Executive summary

1. Highest priority: correct the data modification scope in DataLoadSimulation.ReceivePurchaseOrders. The current UPDATE Warehouse.StockItemHoldings joins to Purchasing.PurchaseOrderLines without restricting to the current @PurchaseOrderID. That can update far more rows than intended, inflate stock balances, and create unnecessary locking on a central inventory table.
   Confidence: 0.99
2. Second priority: remove row-by-row cursor logic from high-write procedures. DataLoadSimulation.PickStockForCustomerOrders, DataLoadSimulation.RecordInvoiceDeliveries, DataLoadSimulation.ReceivePurchaseOrders, and DataLoadSimulation.UpdateCustomFields all exhibit RBAR patterns that increase elapsed time, lock duration, and log volume.
   Confidence: 0.96
3. Third priority: add a small set of focused rowstore indexes that match the reviewed predicates. The code filters heavily on PickingCompletedWhen IS NULL, ConfirmedDeliveryTime IS NULL, IsFinalized = 0, and PurchaseOrderID; the usage summary shows many large indexes with zero activity, but not the exact narrow supporting indexes this code needs.
   Confidence: 0.93
4. The 16 reviewed archive triggers are set-based, but they extend the caller transaction, lack TRY/CATCH, and enforce a business rule through UPDATE(ValidFrom) on every change. On frequently updated entities such as Warehouse.StockItems and Application.People, that adds write amplification.
   Confidence: 0.88
5. Several procedures use ORDER BY NEWID() and repeated scalar subqueries for lookup IDs. These are avoidable plan and CPU costs.
   Confidence: 0.90

Review scope

• Reviewed procedures focus on data-load simulation, order picking, payment processing, purchase receipt processing, delivery recording, telemetry insertion, and JSON/custom-field updates.
• Reviewed triggers implement archive/history capture for multiple dimension/reference tables using AFTER INSERT, UPDATE logic.
• Large tables affecting recommendations include Warehouse.ColdRoomTemperatures_Archive (3.65M rows), Warehouse.StockItemTransactions (236K), Sales.OrderLines (231K), Sales.InvoiceLines (228K), Sales.CustomerTransactions (97K), Sales.Orders (73K), and Sales.Invoices (70K).
• The index usage summary shows many large indexes with zero observed usage. That can indicate restart/reset timing, low recent workload, or mismatched physical design; it should not be treated as a drop list by itself.

Detailed prioritized recommendations

1. Fix incorrect stock update scope in DataLoadSimulation.ReceivePurchaseOrders

   This is the most important correctness and concurrency issue in the review. The statement updating Warehouse.StockItemHoldings joins to all rows in Purchasing.PurchaseOrderLines and does not filter on pol.PurchaseOrderID = @PurchaseOrderID. That means each cursor iteration can update inventory for unrelated purchase orders.

   • Risk: incorrect stock balances.
   • Performance impact: unnecessarily broad updates against a central table.
   • Locking impact: longer transactions and larger lock footprints on Warehouse.StockItemHoldings.
   • Object affected: DataLoadSimulation.ReceivePurchaseOrders.

   Also cache lookup IDs such as transaction type and payment method once per execution instead of re-running scalar subqueries inside DML.

   Confidence: 0.99

2. Replace row-by-row picking logic in DataLoadSimulation.PickStockForCustomerOrders

   This procedure scans candidate order lines, computes available stock per row, uses a cursor, and performs a transaction per order line. On Sales.OrderLines at 231K rows, this pattern is expensive and holds locks longer than needed.

   • Cursor + per-row transaction = high log overhead and poor scaling.
   • MERGE against a table variable is unnecessary and adds complexity. For SQL Server, MERGE remains a construct to use sparingly in write-heavy code.
   • The procedure repeatedly queries Warehouse.StockItemHoldings and the allocation table variable one row at a time.

   Prefer a set-based approach: use a temp table for candidate lines, calculate available stock by stock item once, allocate only lines that fit, then update Sales.OrderLines and Sales.Orders in batches.

   The existing indexes IX_Sales_OrderLines_Perf_20160301_01 and IX_Sales_OrderLines_Perf_20160301_02 are close, but not ideal for the exact access pattern. A filtered index on rows where PickingCompletedWhen IS NULL will usually outperform wider general-purpose indexes for this procedure.

   Confidence: 0.95

3. Remove cursor-based invoice delivery updates in DataLoadSimulation.RecordInvoiceDeliveries

   The procedure iterates invoice by invoice, builds JSON repeatedly, and updates one row at a time in Sales.Invoices. With Sales.Invoices at 70K rows, a set-based preselection strategy is preferable.

   • The filter ConfirmedDeliveryTime IS NULL AND InvoiceDate < CAST(@StartingWhen AS date) should be supported by a targeted filtered index.
   • ORDER BY NEWID() for driver selection is expensive even though Application.People is modest today; it is still a poor pattern and non-scalable.
   • Repeated JSON modifications in a loop create extra CPU and log pressure.

   At minimum, materialize candidate invoices into a temp table, choose a driver once, and batch updates. If exact per-row randomization is required for test data, do it on a preselected small working set rather than on the base tables directly.

   Confidence: 0.90

4. Add targeted indexes for the reviewed code paths

   The supplied usage data shows many indexes with zero observed usage, but several reviewed procedures still lack narrow indexes aligned to their predicates. Prioritize additive indexes that reduce scans and shorten update transactions.

   Object / Query Pattern	Recommended Index Shape	Reason
   Sales.OrderLines where PickingCompletedWhen IS NULL	Filtered NCI on (OrderID, OrderLineID) include (StockItemID, Quantity, PickedQuantity)	Supports picking candidate search and order completion checks.
   Sales.Invoices where ConfirmedDeliveryTime IS NULL and InvoiceDate	Filtered NCI on (InvoiceDate, InvoiceID) include (CustomerID, ReturnedDeliveryData)	Supports delivery recording candidate scan.
   Sales.CustomerTransactions where IsFinalized = 0	Filtered NCI on (CustomerID, CustomerTransactionID) include (InvoiceID, OutstandingBalance)	Supports ProcessCustomerPayments.
   Purchasing.PurchaseOrderLines by PurchaseOrderID	NCI on (PurchaseOrderID, StockItemID) include (OrderedOuters, ReceivedOuters, ExpectedUnitPricePerOuter)	Supports purchase receipt processing and totals aggregation.
   Purchasing.PurchaseOrders where IsOrderFinalized = 0 and ExpectedDeliveryDate	Filtered NCI on (ExpectedDeliveryDate, PurchaseOrderID) include (SupplierID)	Supports receipt cursor elimination or faster candidate selection.

   Because this is SQL Server 2022 Enterprise-capable, use ONLINE = ON for eligible rowstore index operations in production-style environments.

   Confidence: 0.93

5. Reduce trigger overhead and transaction risk in the archive triggers

   All 16 reviewed triggers run in the caller transaction and insert full-row copies into archive tables on every update. The pattern is set-based, which is good, but it still increases write cost and lock duration.

   • Objects affected include Application.TR_Application_StateProvinces_DataLoad_Modify, Warehouse.TR_Warehouse_StockItems_DataLoad_Modify, Sales.TR_Sales_Customers_DataLoad_Modify, Purchasing.TR_Purchasing_Suppliers_DataLoad_Modify, and the related archive triggers listed in the input.
   • IF NOT UPDATE([ValidFrom]) is a brittle gate for business rules. It confirms the column appeared in the SET list, not that the value changed meaningfully.
   • THROW followed by ROLLBACK TRAN is redundant and unreachable in practical terms; THROW transfers control immediately.
   • Some triggers have zero Query Store executions in the last 30 days, indicating they may not be active in recent workload, but they still add maintenance surface.

   Recommended direction: keep the set-based archive pattern if required for the simulation, but standardize error handling, remove dead code after THROW, and consider disabling or removing unused simulation-only triggers outside data-load scenarios. Where the base table is already temporal-capable by design, prefer one history mechanism, not multiple overlapping patterns.

   Confidence: 0.88

6. Add consistent TRY/CATCH transaction handling to write procedures

   Multiple procedures open explicit transactions with XACT_ABORT ON but without TRY/CATCH. XACT_ABORT helps, but predictable rollback and rethrow logic is still preferable for maintainability and error diagnostics.

   • Objects affected: DataLoadSimulation.ProcessCustomerPayments, DataLoadSimulation.ReceivePurchaseOrders, DataLoadSimulation.PlaceSupplierOrders, DataLoadSimulation.PickStockForCustomerOrders.
   • Without TRY/CATCH, troubleshooting failed batches is harder and partial statement sequencing is less explicit to future maintainers.

   Standardize on a template: SET XACT_ABORT ON, BEGIN TRY, BEGIN TRAN, work, COMMIT, then in CATCH perform IF XACT_STATE() <> 0 ROLLBACK and THROW.

   Confidence: 0.91

7. Eliminate repeated lookup subqueries and ORDER BY NEWID()

   Several procedures fetch constant lookup IDs via repeated scalar subqueries inside DML and pick random staff records using ORDER BY NEWID().

   • DataLoadSimulation.ProcessCustomerPayments repeatedly looks up transaction type and payment method IDs.
   • DataLoadSimulation.ReceivePurchaseOrders does the same for stock receipt and supplier invoice types.
   • DataLoadSimulation.RecordInvoiceDeliveries, ReceivePurchaseOrders, PlaceSupplierOrders, and PickStockForCustomerOrders use SELECT TOP(1) ... ORDER BY NEWID() against Application.People.

   Cache lookup IDs in variables once per execution. For pseudo-random person selection, prefer a two-step method such as selecting by a random offset against a pre-materialized employee list, or simply choose a deterministic staff member for simulation workloads when randomness is not analytically important.

   Confidence: 0.90

8. Rewrite DataLoadSimulation.UpdateCustomFields to reduce write amplification

   This procedure performs many full-table or pattern-based updates against Warehouse.StockItems, incrementing ValidFrom between passes. Because Warehouse.TR_Warehouse_StockItems_DataLoad_Modify archives every qualifying update, this multiplies writes into both the base and archive tables.

   • Many passes update the same rows repeatedly.
   • Repeated LIKE '%...%' predicates are non-sargable.
   • Each pass extends log volume and trigger activity.

   Consolidate updates into a staged approach: populate a temp table of target stock items once, derive final JSON payloads once per row, then perform a single update per target row. This is especially important because Warehouse.StockItems also contains larger columns such as Photo, CustomFields, and comments fields.

   Confidence: 0.92

9. Review zero-usage indexes before keeping or dropping

   The index summary shows many large indexes with zero seeks, scans, lookups, and updates during the observed period, including large structures on Sales.Invoices, Sales.OrderLines, Sales.InvoiceLines, and Warehouse.StockItemTransactions.

   • Do not drop solely from this snapshot; DMVs reset on restart and may reflect a limited sample.
   • However, several existing indexes appear mismatched to the reviewed code paths, especially where filtered indexes would be more selective.
   • Candidate review areas: overlapping Sales.OrderLines indexes and rarely-touched foreign-key indexes on wide transactional tables.

   Keep the review evidence-based: capture a longer usage window, compare against Query Store runtime stats, then consolidate overlapping rowstore indexes where safe.

   Confidence: 0.78

10. Use SQL Server 2022 and Enterprise capabilities deliberately, but do not expect them to fix RBAR code

    The platform supports Parameter Sensitive Plan optimization, DOP feedback, online index operations, partition switching, and columnstore. These are helpful, but the biggest issues here are procedural design and write patterns.

    • PSP may help parameterized predicate variation, but it will not repair cursor-heavy or overly chatty transaction patterns.
    • DOP feedback is largely irrelevant for the reviewed row-by-row procedures.
    • Online index create/rebuild is useful for implementing the targeted indexes safely.
    • For very large history growth such as Warehouse.ColdRoomTemperatures_Archive, page compression or partitioning can be considered after code-path fixes; this is a secondary physical-design improvement.

    Confidence: 0.84

Visual aids

Write-path contention map for the reviewed code

The diagram highlights why the primary recommendations focus on narrowing update scope, replacing RBAR logic, and reducing repeated writes to Warehouse.StockItems and related history tables.

Scripts

Script 1: Create focused filtered and covering indexes for the reviewed procedures (implements Recommendation 4)

USE [WideWorldImporters];
GO

CREATE INDEX IX_Sales_OrderLines_PendingPick_Order
ON [Sales].[OrderLines] (OrderID, OrderLineID)
INCLUDE (StockItemID, Quantity, PickedQuantity)
WHERE PickingCompletedWhen IS NULL
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
GO

CREATE INDEX IX_Sales_Invoices_PendingDelivery_Date
ON [Sales].[Invoices] (InvoiceDate, InvoiceID)
INCLUDE (CustomerID, ReturnedDeliveryData)
WHERE ConfirmedDeliveryTime IS NULL
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
GO

CREATE INDEX IX_Sales_CustomerTransactions_Unfinalized_Customer
ON [Sales].[CustomerTransactions] (CustomerID, CustomerTransactionID)
INCLUDE (InvoiceID, OutstandingBalance)
WHERE IsFinalized = 0
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
GO

CREATE INDEX IX_Purchasing_PurchaseOrders_Open_ExpectedDeliveryDate
ON [Purchasing].[PurchaseOrders] (ExpectedDeliveryDate, PurchaseOrderID)
INCLUDE (SupplierID)
WHERE IsOrderFinalized = 0
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
GO

CREATE INDEX IX_Purchasing_PurchaseOrderLines_PurchaseOrderID_StockItemID
ON [Purchasing].[PurchaseOrderLines] (PurchaseOrderID, StockItemID)
INCLUDE (OrderedOuters, ReceivedOuters, ExpectedUnitPricePerOuter)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE);
GO

Script 2: Correct update scope and reduce repeated lookups in DataLoadSimulation.ReceivePurchaseOrders (implements Recommendations 1, 6, and 7)

USE [WideWorldImporters];
GO

CREATE OR ALTER PROCEDURE [DataLoadSimulation].[ReceivePurchaseOrders]
    @CurrentDateTime datetime2(7),
    @StartingWhen datetime,
    @EndOfTime datetime2(7),
    @IsSilentMode bit
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @IsSilentMode = 0
    BEGIN
        PRINT N'Receiving stock from purchase orders';
    END;

    DECLARE @StaffMemberPersonID int =
    (
        SELECT MIN(PersonID)
        FROM [Application].[People]
        WHERE IsEmployee <> 0
    );

    DECLARE @StockReceiptTransactionTypeID int =
    (
        SELECT TransactionTypeID
        FROM [Application].[TransactionTypes]
        WHERE TransactionTypeName = N'Stock Receipt'
    );

    DECLARE @SupplierInvoiceTransactionTypeID int =
    (
        SELECT TransactionTypeID
        FROM [Application].[TransactionTypes]
        WHERE TransactionTypeName = N'Supplier Invoice'
    );

    DECLARE @EFTPaymentMethodID int =
    (
        SELECT PaymentMethodID
        FROM [Application].[PaymentMethods]
        WHERE PaymentMethodName = N'EFT'
    );

    DECLARE @PurchaseOrderID int;
    DECLARE @SupplierID int;
    DECLARE @TotalExcludingTax decimal(18,2);
    DECLARE @TotalIncludingTax decimal(18,2);

    DECLARE PurchaseOrderList CURSOR FAST_FORWARD READ_ONLY FOR
        SELECT po.PurchaseOrderID, po.SupplierID
        FROM [Purchasing].[PurchaseOrders] AS po
        WHERE po.IsOrderFinalized = 0
          AND po.ExpectedDeliveryDate >= @StartingWhen;

    OPEN PurchaseOrderList;

    FETCH NEXT FROM PurchaseOrderList INTO @PurchaseOrderID, @SupplierID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            BEGIN TRAN;

            UPDATE pol
            SET
                pol.ReceivedOuters = pol.OrderedOuters,
                pol.IsOrderLineFinalized = 1,
                pol.LastReceiptDate = CAST(@StartingWhen AS date),
                pol.LastEditedBy = @StaffMemberPersonID,
                pol.LastEditedWhen = @StartingWhen
            FROM [Purchasing].[PurchaseOrderLines] AS pol
            WHERE pol.PurchaseOrderID = @PurchaseOrderID;

            UPDATE sih
            SET
                sih.QuantityOnHand = sih.QuantityOnHand + (pol.ReceivedOuters * si.QuantityPerOuter),
                sih.LastEditedBy = @StaffMemberPersonID,
                sih.LastEditedWhen = @StartingWhen
            FROM [Warehouse].[StockItemHoldings] AS sih
            INNER JOIN [Purchasing].[PurchaseOrderLines] AS pol
                ON sih.StockItemID = pol.StockItemID
            INNER JOIN [Warehouse].[StockItems] AS si
                ON sih.StockItemID = si.StockItemID
            WHERE pol.PurchaseOrderID = @PurchaseOrderID;  -- critical fix

            INSERT [Warehouse].[StockItemTransactions]
            (
                StockItemID, TransactionTypeID, CustomerID, InvoiceID, SupplierID,
                PurchaseOrderID, TransactionOccurredWhen, Quantity, LastEditedBy, LastEditedWhen
            )
            SELECT
                pol.StockItemID,
                @StockReceiptTransactionTypeID,
                NULL,
                NULL,
                @SupplierID,
                pol.PurchaseOrderID,
                @StartingWhen,
                pol.ReceivedOuters * si.QuantityPerOuter,
                @StaffMemberPersonID,
                @StartingWhen
            FROM [Purchasing].[PurchaseOrderLines] AS pol
            INNER JOIN [Warehouse].[StockItems] AS si
                ON pol.StockItemID = si.StockItemID
            WHERE pol.PurchaseOrderID = @PurchaseOrderID;

            UPDATE [Purchasing].[PurchaseOrders]
            SET
                IsOrderFinalized = 1,
                LastEditedBy = @StaffMemberPersonID,
                LastEditedWhen = @StartingWhen
            WHERE PurchaseOrderID = @PurchaseOrderID;

            SELECT
                @TotalExcludingTax = SUM(ROUND(pol.OrderedOuters * pol.ExpectedUnitPricePerOuter, 2)),
                @TotalIncludingTax = SUM(ROUND(pol.OrderedOuters * pol.ExpectedUnitPricePerOuter, 2))
                                     + SUM(ROUND(pol.OrderedOuters * pol.ExpectedUnitPricePerOuter * si.TaxRate / 100.0, 2))
            FROM [Purchasing].[PurchaseOrderLines] AS pol
            INNER JOIN [Warehouse].[StockItems] AS si
                ON pol.StockItemID = si.StockItemID
            WHERE pol.PurchaseOrderID = @PurchaseOrderID;

            INSERT [Purchasing].[SupplierTransactions]
            (
                SupplierID, TransactionTypeID, PurchaseOrderID, PaymentMethodID,
                SupplierInvoiceNumber, TransactionDate, AmountExcludingTax, TaxAmount,
                TransactionAmount, OutstandingBalance, FinalizationDate, LastEditedBy, LastEditedWhen
            )
            VALUES
            (
                @SupplierID,
                @SupplierInvoiceTransactionTypeID,
                @PurchaseOrderID,
                @EFTPaymentMethodID,
                CAST(ABS(CHECKSUM(NEWID())) % 10000 + 1 AS nvarchar(20)),
                CAST(@StartingWhen AS date),
                @TotalExcludingTax,
                @TotalIncludingTax - @TotalExcludingTax,
                @TotalIncludingTax,
                @TotalIncludingTax,
                NULL,
                @StaffMemberPersonID,
                @StartingWhen
            );

            COMMIT;
        END TRY
        BEGIN CATCH
            IF XACT_STATE() <> 0
                ROLLBACK;
            THROW;
        END CATCH;

        FETCH NEXT FROM PurchaseOrderList INTO @PurchaseOrderID, @SupplierID;
    END

    CLOSE PurchaseOrderList;
    DEALLOCATE PurchaseOrderList;
END;
GO

Script 3: Add safer transaction handling and lookup caching to DataLoadSimulation.ProcessCustomerPayments (implements Recommendations 6 and 7)

USE [WideWorldImporters];
GO

CREATE OR ALTER PROCEDURE [DataLoadSimulation].[ProcessCustomerPayments]
    @CurrentDateTime datetime2(7),
    @StartingWhen datetime,
    @EndOfTime datetime2(7),
    @IsSilentMode bit
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @IsSilentMode = 0
    BEGIN
        PRINT N'Processing customer payments';
    END;

    DECLARE @StaffMemberPersonID int =
    (
        SELECT MIN(PersonID)
        FROM [Application].[People]
        WHERE IsEmployee <> 0
    );

    DECLARE @CustomerPaymentReceivedTypeID int =
    (
        SELECT TransactionTypeID
        FROM [Application].[TransactionTypes]
        WHERE TransactionTypeName = N'Customer Payment Received'
    );

    DECLARE @EFTPaymentMethodID int =
    (
        SELECT PaymentMethodID
        FROM [Application].[PaymentMethods]
        WHERE PaymentMethodName = N'EFT'
    );

    DECLARE @TransactionsToReceive TABLE
    (
        CustomerTransactionID int PRIMARY KEY,
        CustomerID int,
        InvoiceID int NULL,
        OutstandingBalance decimal(18,2)
    );

    INSERT @TransactionsToReceive (CustomerTransactionID, CustomerID, InvoiceID, OutstandingBalance)
    SELECT CustomerTransactionID, CustomerID, InvoiceID, OutstandingBalance
    FROM [Sales].[CustomerTransactions]
    WHERE IsFinalized = 0;

    BEGIN TRY
        BEGIN TRAN;

        UPDATE ct
        SET
            ct.OutstandingBalance = 0,
            ct.FinalizationDate = @StartingWhen,
            ct.LastEditedBy = @StaffMemberPersonID,
            ct.LastEditedWhen = @StartingWhen
        FROM [Sales].[CustomerTransactions] AS ct
        INNER JOIN @TransactionsToReceive AS ttr
            ON ct.CustomerTransactionID = ttr.CustomerTransactionID;

        INSERT [Sales].[CustomerTransactions]
        (
            CustomerID, TransactionTypeID, InvoiceID, PaymentMethodID, TransactionDate,
            AmountExcludingTax, TaxAmount, TransactionAmount, OutstandingBalance,
            FinalizationDate, LastEditedBy, LastEditedWhen
        )
        SELECT
            ttr.CustomerID,
            @CustomerPaymentReceivedTypeID,
            NULL,
            @EFTPaymentMethodID,
            CAST(@StartingWhen AS date),
            0,
            0,
            -SUM(ttr.OutstandingBalance),
            0,
            CAST(@StartingWhen AS date),
            @StaffMemberPersonID,
            @StartingWhen
        FROM @TransactionsToReceive AS ttr
        GROUP BY ttr.CustomerID;

        COMMIT;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK;
        THROW;
    END CATCH;
END;
GO

Script 4: Simplify trigger error path and remove unreachable rollback code pattern (implements Recommendation 5)

USE [WideWorldImporters];
GO

CREATE OR ALTER TRIGGER [Application].[TR_Application_Countries_DataLoad_Modify]
ON [Application].[Countries]
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT UPDATE([ValidFrom])
    BEGIN
        THROW 51000, '[ValidFrom] must be updated when simulating data loads', 1;
    END;

    INSERT [Application].[Countries_Archive]
    (
        [CountryID], [CountryName], [FormalName], [IsoAlpha3Code], [IsoNumericCode],
        [CountryType], [LatestRecordedPopulation], [Continent], [Region], [Subregion],
        [Border], [LastEditedBy], [ValidFrom], [ValidTo]
    )
    SELECT
        d.[CountryID], d.[CountryName], d.[FormalName], d.[IsoAlpha3Code], d.[IsoNumericCode],
        d.[CountryType], d.[LatestRecordedPopulation], d.[Continent], d.[Region], d.[Subregion],
        d.[Border], d.[LastEditedBy], d.[ValidFrom], i.[ValidFrom]
    FROM inserted AS i
    INNER JOIN deleted AS d
        ON i.[CountryID] = d.[CountryID];
END;
GO

Script 5: Capture longer-horizon evidence for zero-usage indexes before making drop decisions (implements Recommendation 9)

USE [WideWorldImporters];
GO

SELECT
    DB_NAME() AS DatabaseName,
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS ObjectName,
    i.name AS IndexName,
    i.type_desc,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates,
    p.rows AS ApproxRows,
    CAST(SUM(a.total_pages) * 8.0 / 1024 AS decimal(18,2)) AS SizeMB
FROM sys.indexes AS i
INNER JOIN sys.partitions AS p
    ON i.object_id = p.object_id
   AND i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a
    ON p.partition_id = a.container_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
WHERE i.object_id > 100
  AND i.is_hypothetical = 0
  AND i.index_id > 0
GROUP BY
    i.object_id, i.name, i.type_desc,
    us.user_seeks, us.user_scans, us.user_lookups, us.user_updates,
    p.rows
ORDER BY SizeMB DESC, SchemaName, ObjectName, IndexName;
GO