Query optimization SQL Server

SELECT
    c.CustomerKey,      -- Uniquely identifies each customer
    c.CustomerName,     -- Displays the customer name in the result
    c.Segment,          -- Keeps the customer segment available for analysis
    SUM(s.TotalAmount) AS Revenue90Days, -- Sums revenue generated in the last 90 days
    COUNT(s.SaleKey) AS SalesCount       -- Counts how many sales the customer made
FROM gold.DimCustomer c
INNER JOIN gold.FactSales s
    ON s.CustomerKey = c.CustomerKey     -- Links each sale to its respective customer
WHERE
    CONVERT(DATE, s.SaleDate) >=
        CONVERT(DATE, DATEADD(DAY, -90, GETDATE()))
        -- Keeps only sales from the last 90 days.
        -- CONVERT on the SaleDate column is one of the points evaluated during optimization.

    AND UPPER(s.Status) = 'APPROVED'
        -- Includes only approved sales.
        -- UPPER on the column is also evaluated because it adds processing overhead.
GROUP BY
    c.CustomerKey,
    c.CustomerName,
    c.Segment           -- Groups sales to calculate totals by customer
ORDER BY
    Revenue90Days DESC; -- Shows customers with the highest revenue first

-- Shows how many data pages were read during the query
SET STATISTICS IO ON;

-- Shows CPU time and total execution time
SET STATISTICS TIME ON;

-- Run the original query here to measure its behavior

-- Disables statistics after the test
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

SELECT
    i.name AS IndexName,                       -- Name of the index found
    i.type_desc AS IndexType,                  -- Indicates whether the index is clustered or nonclustered
    i.is_unique AS IsUnique,                   -- Shows whether the index requires unique values
    c.name AS ColumnName,                      -- Column that is part of the index
    ic.key_ordinal AS KeyOrder,                -- Column order within the index key
    ic.is_included_column AS IsIncludedColumn  -- Indicates whether the column is only in INCLUDE
FROM sys.indexes i
INNER JOIN sys.index_columns ic
    ON i.object_id = ic.object_id
    AND i.index_id = ic.index_id               -- Links the index to its columns
INNER JOIN sys.columns c
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id             -- Retrieves the name of each column
WHERE
    i.object_id = OBJECT_ID('gold.FactSales')  -- Analyzes only the table used in the test
    AND i.is_hypothetical = 0                  -- Ignores hypothetical indexes created by the optimizer
ORDER BY
    i.name,
    ic.is_included_column,
    ic.key_ordinal,
    ic.index_column_id;                        -- Orders the result to make it easier to read

CREATE NONCLUSTERED INDEX IX_FactSales_Status_SaleDate_CustomerKey
ON gold.FactSales (
    Status,       -- Used in the filter to keep only APPROVED sales
    SaleDate,     -- Used in the last 90 days filter
    CustomerKey   -- Used in the join with DimCustomer
)
INCLUDE (
    TotalAmount   -- Needed to calculate revenue without looking up the column outside the index
);

-- Enables metrics to compare the new version with the original query
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- Calculates the start date once, outside the table filter
DECLARE @StartDate DATE;

SET @StartDate = DATEADD(DAY, -90, GETDATE());

SELECT
    c.CustomerKey,      -- Uniquely identifies each customer
    c.CustomerName,     -- Displays the customer name in the result
    c.Segment,          -- Keeps the segment for analysis
    SUM(s.TotalAmount) AS Revenue90Days, -- Sums revenue from the last 90 days
    COUNT(*) AS SalesCount               -- Counts the sales found for each customer
FROM gold.FactSales s
INNER JOIN gold.DimCustomer c
    ON c.CustomerKey = s.CustomerKey     -- Links the sale to the customer
WHERE
    s.SaleDate >= @StartDate
        -- Filters directly on SaleDate without applying CONVERT to the column

    AND s.Status = 'APPROVED'
        -- Filters directly by status without applying UPPER to the column
GROUP BY
    c.CustomerKey,
    c.CustomerName,
    c.Segment           -- Aggregates revenue and sales count by customer
ORDER BY
    Revenue90Days DESC; -- Prioritizes customers who generated the most revenue
GO

-- Disables metrics after the test
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO