SQL Server Code Review – VMEnt\SQL25 / RetailOps – 2026-11-13

Tuning goal: Code Review

Server: VMEnt\SQL25  |  Database: RetailOps  |  Version: SQL Server 2025 RTM, 17.0.1000.7  |  Edition: Enterprise Developer / Engine Edition Enterprise

Executive summary

This review covers the 20 supplied, complete definitions: 19 stored procedures and one scalar-valued function. The objects were selected from 35 eligible objects ranked by Query Store total CPU over the last 30 days; the supplied objects are the highest-ranked portion of that list. No object was marked NOT INCLUDED or NOT AVAILABLE. Query Store is READ_WRITE and its workload figures cover the last 30 days, while index and plan statistics cover only the 2.4 days since the latest startup.

  1. Fix the two highest-impact non-sargable or conversion-sensitive lookups. P1

    dbo.usp_FindCustomerByPhone ranked first with 40.4 seconds CPU, 1,424,000 logical reads and approximately 4,000 calls. Its nvarchar(20) parameter is compared with the declared varchar(20) indexed column under the SQL collation SQL_Latin1_General_CP1_CI_AS; the column can be implicitly converted, preventing an efficient seek. dbo.usp_GetCustomerOrderHistory ranked second with 23.8 seconds CPU and 1,622,092 logical reads; UPPER(c.Email) is redundant under the database's case-insensitive collation and blocks a direct seek on UX_Customers_Email.

  2. Make the daily-sales date predicate seekable. P1

    dbo.usp_GetDailySales ranked third with 8.5 seconds CPU and 364,205 logical reads. Line 13 applies DATEDIFF to the indexed Orders.OrderDate column, making the predicate non-sargable. Rewriting it as a half-open range preserves the same date semantics and allows the existing date index to be considered.

  3. Replace the cursor and repeated scalar-function calls in loyalty recalculation. P1

    dbo.usp_RecalcLoyaltyTiers is ranked fifth and its most-executed cursor statement ran 100,004 times. Lines 9–24 process customers one at a time, and line 15 invokes dbo.fn_CustomerSpend per customer. A set-based aggregate and one update remove the row-by-row execution pattern, reduce repeated locking activity, and preserve the Bronze/Silver/Gold outcome.

  4. Close the concurrent update/insert race in cart-item creation. P2

    dbo.usp_AddCartItem performs an update followed by an insert without an explicit transaction or serialization protection. Concurrent calls for the same (CartId, ProductId) can both observe no updated row and race to insert. A short transaction using the existing clustered key and update/range locks makes the operation deterministic.

  5. Do not drop indexes solely from this short observation window. P2

    IX_Orders_OrderDate reports zero seeks, scans and lookups, but the usage window is only 2.4 days after restart/database startup. First apply the date-predicate correction and reassess over a representative workload. No index drop is included in the Scripts section.

Expected priority impact: The first five ranked objects account for 80.3% of the recorded CPU of the ranked objects, based on the supplied percentages: 46.7% + 27.4% + 9.8% + 5.4% + 4.0%. The estimate is workload attribution from Query Store and does not include dynamic SQL or called procedures outside the statement's containing module.

Scope and environment

AreaFinding
Objects reviewed20 complete objects: 19 stored procedures and 1 scalar function.
Objects not reviewedNone marked NOT INCLUDED or NOT AVAILABLE in the supplied input. The input states that only the highest-ranked objects were provided; 15 of the 35 eligible objects were not supplied for code review.
Workload rankingQuery Store total CPU over the last 30 days, state READ_WRITE, capture mode AUTO.
Server capabilitiesSQL Server 2025, Enterprise engine capabilities, online index operations, compression, columnstore, CE feedback, DOP feedback and OPPO platform support.
Statistics ageIndex usage, operational statistics, missing-index statistics and plan cache cover only 2.4 days since restart/database startup.
CollationDatabase default is SQL_Latin1_General_CP1_CI_AS: case-insensitive, accent-sensitive SQL collation.

No deadlock graphs or blocking-chain data were supplied. Therefore, this review identifies locking risks visible in the code but does not claim that a deadlock or blocking incident occurred.

Detailed prioritized recommendations

1. Correct the phone lookup parameter type

Object: dbo.usp_FindCustomerByPhone, rank 1.

Line 3 declares @Phone nvarchar(20), while metadata declares Customers.Phone varchar(20) NOT NULL with index key IX_Customers_Phone. Under the SQL collation reported in the input, comparing the varchar column with an nvarchar parameter can apply CONVERT_IMPLICIT to the column and prevent a normal index seek. The index reports zero seeks and 2,000 scans during the 2.4-day usage window, which is consistent with this concern, although the counters alone do not prove every scan came from this procedure.

Declare the parameter as varchar(20). This returns the same rows for values representable in the column's varchar code page. If callers currently send characters that cannot be represented in that code page, conversion changes the lookup value and those characters cannot be preserved; that is a required behavioral consideration before deployment.

Confidence: High, 95%, because both declared types, the SQL collation and the index are supplied.

2. Remove redundant UPPER calls from the customer email lookup

Object: dbo.usp_GetCustomerOrderHistory, rank 2.

Line 10 applies UPPER to both c.Email and @Email. Customers.Email is nvarchar(200) NOT NULL, the parameter is the matching nvarchar(200), and the database collation is case-insensitive. Therefore, removing UPPER from both sides returns the same rows while allowing the optimizer to use the existing unique index UX_Customers_Email directly.

The query uses SELECT TOP (20) * at line 7. The output shape is therefore dependent on the table definition and may require lookups after finding the customer. No covering index is recommended because the supplied column metadata does not enumerate every column returned by *.

Confidence: High, 98%, based on the explicitly reported CI collation, matching parameter/column types and existing email index.

3. Replace DATEDIFF on OrderDate with a half-open date range

Object: dbo.usp_GetDailySales, rank 3.

Line 13 uses DATEDIFF(day, o.OrderDate, @ReportDate) = 0. Applying the function to the datetime2(0) indexed column prevents a normal seek on IX_Orders_OrderDate. Use o.OrderDate >= @ReportDate AND o.OrderDate < DATEADD(day, 1, @ReportDate). The date parameter is converted to the column's datetime2 type as a value, not by applying a conversion to the column, and the half-open range includes exactly the same calendar date.

The procedure currently reports 8.5 seconds CPU, 3.8 seconds duration and 364,205 logical reads over approximately 40 calls. The supplied index reports zero seeks and zero scans during the 2.4-day usage window; after this rewrite, reassess whether it is used before considering any index change.

Confidence: High, 95%, for the sargability defect; exact runtime improvement is not estimated because execution plans and selectivity were not supplied.

4. Replace cursor-based loyalty recalculation with a set-based update

Object: dbo.usp_RecalcLoyaltyTiers, rank 5.

Lines 9–24 declare, open, fetch and close a cursor. Line 15 calls the scalar function for every customer, and the supplied workload says the most-executed cursor statement ran 100,004 times. This creates repeated function execution, repeated order/order-line access and repeated customer updates. The replacement aggregates eligible customer spend once and updates all customers in the region in one statement.

The new statement retains the function's date boundaries from lines 11–12, excludes Cancelled orders as in line 13, assigns zero spend to customers without qualifying orders, and preserves the existing tier thresholds from lines 17–19. The one-statement update is atomic, but it can hold update locks for the duration of the set operation; this is generally preferable to 100,000 repeated operations, but execution should be scheduled with the normal workload in mind.

Confidence: High, 93%, for eliminating the row-by-row pattern and preserving the shown logic. Exact duration reduction is unknown because plans, customer distribution and order selectivity were not supplied.

5. Serialize the cart update/insert operation

Object: dbo.usp_AddCartItem, rank 10.

Lines 6–7 update an existing row and insert if @@ROWCOUNT = 0. Without a transaction and serialization, two concurrent executions for the same cart/product can both reach the insert path. The existing clustered primary key on CartItems(CartId, ProductId) prevents duplicate committed keys but can still produce a duplicate-key error under the race.

A short TRY/CATCH transaction with UPDLOCK, HOLDLOCK protects the key/range while the update and possible insert complete. This increases lock duration only for this short operation and removes the race visible in the code. The supplied workload is approximately 8,000 calls and 24,015 logical reads, so the issue is operationally relevant even though its recorded CPU is low.

Confidence: High, 90%, for the race condition. The exact blocking impact is unknown because no blocking-chain data was supplied.

6. Preserve the existing useful access paths

dbo.usp_GetLatestOrder uses Orders.CustomerId and orders by OrderDate DESC; the existing IX_Orders_CustomerId has OrderDate, Status included and reports 55,000 seeks with no lookups. dbo.usp_ListRecentReviews is covered by IX_ProductReviews_ProductId, which includes Rating, CreatedAt and reports 4,000 seeks. Shipment lookup procedures also have matching indexes. These are positive findings; no redundant replacement indexes are recommended.

Object findings

RankObjectWorkloadFinding
1dbo.usp_FindCustomerByPhone40.4 s CPU; 1,424,000 reads; ~4,000 callsnvarchar-to-varchar comparison can convert the indexed column.
2dbo.usp_GetCustomerOrderHistory23.8 s CPU; 1,622,092 reads; ~2,000 callsUPPER on CI email column prevents a direct seek.
3dbo.usp_GetDailySales8.5 s CPU; 364,205 reads; ~40 callsDATEDIFF on indexed OrderDate is non-sargable.
4dbo.fn_CustomerSpend4.7 s CPU; 1,861,714 reads; ~100,000 callsScalar function is heavily called, primarily through loyalty recalculation.
5dbo.usp_RecalcLoyaltyTiers3.4 s CPU; 780,314 reads; cursor statement 100,004 executionsRow-by-row cursor and scalar-function invocation.
6–20Remaining supplied procedures0.1–2.5 s CPU each, as reportedNo higher-confidence code defect requiring a replacement definition was identified from the supplied metadata.

The remaining reviewed procedures include category and region aggregation, latest-order retrieval, product/category retrieval, review retrieval, login and page-view writes, cart creation/retrieval, order and order-line retrieval, shipment retrieval/tracking and customer email retrieval. Their parameters match the named column types where metadata is available, and no dynamic SQL, catch-all predicates, query hints or explicit transactions were present in their signals.

Index and storage review

IndexReported usage and sizeRecommendation
IX_Orders_OrderDate1,000,000-row table; 0 seeks, 0 scans, 0 lookups; 15.5 MBDo not drop yet. The current DATEDIFF predicate explains why it may not be chosen. Reassess after recommendation 3 over a representative workload.
IX_Customers_Phone100,000-row table; 0 seeks, 2,000 scans; 2.7 MBRetain. Correct the parameter type first so this existing index can be used.
IX_Customers_Email / UX_Customers_Email100,000-row table; 2,000 seeks, 1,000 scans; 6.2 MBRetain. Removing UPPER should improve its eligibility for direct seeks.
IX_Orders_CustomerId1,000,000-row table; 55,000 seeks, 20 scans, no lookups; 31.6 MBRetain. It supports customer order access and includes OrderDate and Status.
IX_ProductReviews_ProductId40,570-row table; 4,000 seeks; 1.6 MBRetain. It covers the recent-review procedure's named output columns.

Storage impact of this review's index recommendations: none. No index is added, modified or dropped in the recommended scripts. Therefore estimated new storage is 0 MB, reclaimed storage is 0 MB, and net storage change is 0 MB. This is deliberate because the input does not provide execution plans, missing-index candidates, column distribution, or a workload window long enough to justify a physical-design change.

SQL Server 2025 feature review

  • Dynamic SQL and optimized sp_executesql: None of the reviewed objects calls dynamic SQL. The input does not provide the sp_executesql compilation-settings section, so no compilation-storm conclusion is made and no configuration change is recommended.
  • Cardinality estimation feedback: The input does not provide CE_FEEDBACK state, compatibility level or Query Store settings for this database. The reviewed modules have no query hints according to their Signals lines, so there is no hard-coded CE-model hint to assess. CE feedback is not presented as active or available for this database based on the supplied evidence.
  • Degree of parallelism feedback: The input states that SQL Server 2025 supports DOP feedback, but it does not provide the database's DOP_FEEDBACK state, compatibility level or Query Store readiness. No reviewed module has an OPTION clause or MAXDOP hint. DOP feedback is therefore not claimed to be active.
  • Optional parameter plan optimization: No reviewed object has a confirmed catch-all predicate. The Signals lines report CatchAllPredicates=No for every supplied object. OPPO is not relevant to the shown code, and no compatibility-level or database-scoped configuration change is recommended.

Scripts

1. Make the phone lookup use the varchar phone index

USE [RetailOps];
GO

CREATE OR ALTER PROCEDURE dbo.usp_FindCustomerByPhone
    @Phone varchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT CustomerId,
           FirstName,
           LastName,
           Email,
           Phone,
           Region,
           LoyaltyTier
    FROM dbo.Customers
    WHERE Phone = @Phone;
END;
GO

2. Remove redundant case normalization from customer order history

USE [RetailOps];
GO

CREATE OR ALTER PROCEDURE dbo.usp_GetCustomerOrderHistory
    @Email nvarchar(200)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (20) *
    FROM dbo.Customers AS c
    JOIN dbo.Orders AS o
        ON o.CustomerId = c.CustomerId
    WHERE c.Email = @Email
    ORDER BY o.OrderDate DESC;
END;
GO

3. Make the daily sales date filter sargable

USE [RetailOps];
GO

CREATE OR ALTER PROCEDURE dbo.usp_GetDailySales
    @ReportDate date
AS
BEGIN
    SET NOCOUNT ON;

    SELECT p.Category,
           COUNT(DISTINCT o.OrderId) AS Orders,
           SUM(ol.Quantity * ol.UnitPrice) AS Revenue
    FROM dbo.Orders AS o
    JOIN dbo.OrderLines AS ol
        ON ol.OrderId = o.OrderId
    JOIN dbo.Products AS p
        ON p.ProductId = ol.ProductId
    WHERE o.OrderDate >= @ReportDate
      AND o.OrderDate < DATEADD(day, 1, @ReportDate)
      AND o.Status <> 'Cancelled'
    GROUP BY p.Category
    ORDER BY Revenue DESC;
END;
GO

4. Replace cursor-based loyalty recalculation with one set-based update

USE [RetailOps];
GO

CREATE OR ALTER PROCEDURE dbo.usp_RecalcLoyaltyTiers
    @Region varchar(20),
    @AsOf date
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH Spend AS
    (
        SELECT o.CustomerId,
               SUM(ol.Quantity * ol.UnitPrice) AS Spend
        FROM dbo.Customers AS rc
        JOIN dbo.Orders AS o
            ON o.CustomerId = rc.CustomerId
        JOIN dbo.OrderLines AS ol
            ON ol.OrderId = o.OrderId
        WHERE rc.Region = @Region
          AND o.OrderDate >= DATEADD(year, -1, @AsOf)
          AND o.OrderDate < @AsOf
          AND o.Status <> 'Cancelled'
        GROUP BY o.CustomerId
    )
    UPDATE c
       SET LoyaltyTier =
           CASE
               WHEN ISNULL(s.Spend, 0) >= 12500 THEN 'Gold'
               WHEN ISNULL(s.Spend, 0) >= 9500 THEN 'Silver'
               ELSE 'Bronze'
           END
    FROM dbo.Customers AS c
    LEFT JOIN Spend AS s
        ON s.CustomerId = c.CustomerId
    WHERE c.Region = @Region;
END;
GO

5. Serialize concurrent cart-item update/insert operations

USE [RetailOps];
GO

CREATE OR ALTER PROCEDURE dbo.usp_AddCartItem
    @CartId int,
    @ProductId int,
    @Quantity int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE ci WITH (UPDLOCK, HOLDLOCK)
           SET Quantity = Quantity + @Quantity
        FROM dbo.CartItems AS ci
        WHERE ci.CartId = @CartId
          AND ci.ProductId = @ProductId;

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT dbo.CartItems
                (CartId, ProductId, Quantity)
            VALUES
                (@CartId, @ProductId, @Quantity);
        END;

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

        THROW;
    END CATCH;
END;
GO

Confidence and limitations

  • Overall code-review confidence: High, approximately 92%, for the identified type-conversion, CI-collation, non-sargable predicate, cursor and concurrency patterns because the complete definitions and relevant column metadata were supplied.
  • Performance-improvement confidence: Moderate, approximately 80%, because the workload ranking and logical reads are supplied but estimated and actual execution plans, row counts by predicate, wait statistics and runtime parameter distributions are not.
  • Locking/deadlock confidence: Moderate for the cart race, approximately 90% for the code-level race, but low for any claim about observed blocking or deadlocks because no blocking chains or deadlock graphs were supplied.
  • Index recommendation confidence: Conservative. No new index is prescribed because the short 2.4-day usage window and lack of plans/selectivity data do not support a reliable size or benefit estimate. Existing index sizes and row counts are reported above only for the supplied structures.
  • Scope limitation: The input states that 35 objects were eligible, but only the highest-ranked 20 were provided for code review. The 15 not supplied are not reviewed and no code changes are recommended for them.