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.
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.
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.
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.
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.
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
Area
Finding
Objects reviewed
20 complete objects: 19 stored procedures and 1 scalar function.
Objects not reviewed
None 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 ranking
Query Store total CPU over the last 30 days, state READ_WRITE, capture mode AUTO.
Server capabilities
SQL Server 2025, Enterprise engine capabilities, online index operations, compression, columnstore, CE feedback, DOP feedback and OPPO platform support.
Statistics age
Index usage, operational statistics, missing-index statistics and plan cache cover only 2.4 days since restart/database startup.
Collation
Database 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
Rank
Object
Workload
Finding
1
dbo.usp_FindCustomerByPhone
40.4 s CPU; 1,424,000 reads; ~4,000 calls
nvarchar-to-varchar comparison can convert the indexed column.
2
dbo.usp_GetCustomerOrderHistory
23.8 s CPU; 1,622,092 reads; ~2,000 calls
UPPER on CI email column prevents a direct seek.
3
dbo.usp_GetDailySales
8.5 s CPU; 364,205 reads; ~40 calls
DATEDIFF on indexed OrderDate is non-sargable.
4
dbo.fn_CustomerSpend
4.7 s CPU; 1,861,714 reads; ~100,000 calls
Scalar function is heavily called, primarily through loyalty recalculation.
5
dbo.usp_RecalcLoyaltyTiers
3.4 s CPU; 780,314 reads; cursor statement 100,004 executions
Row-by-row cursor and scalar-function invocation.
6–20
Remaining supplied procedures
0.1–2.5 s CPU each, as reported
No 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.
Retain. Removing UPPER should improve its eligibility for direct seeks.
IX_Orders_CustomerId
1,000,000-row table; 55,000 seeks, 20 scans, no lookups; 31.6 MB
Retain. It supports customer order access and includes OrderDate and Status.
IX_ProductReviews_ProductId
40,570-row table; 4,000 seeks; 1.6 MB
Retain. 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
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.