SQL Server Tuning Assessment – VMEnt\SQL25 – 2026-11-19
Tuning goal: Improve server reliability, remove blocking and deadlocks, and speed up the busiest database work.
Executive summary
Overall health: Needs attention. The server had no observed memory pressure, but missing recovery checks, an active blocking chain and repeated deadlocks create substantial reliability risk.
- Clear the ShopDemo blocking chain and remove the deliberate pause inside its open transaction so customer and order work can proceed.
- Establish and test backups, then check every listed database for damage so a failure can be recovered safely.
- Correct the two SalesDemo payment procedures that access the same tables in conflicting order, reducing recurring transaction failures.
- Update the server to an approved current build after testing, reducing avoidable stability and security risk.
- Correct the busiest inefficient lookups and inaccurate ForumDemo calculations to improve speed and prevent wrong results.
Checks covered server resources and maintenance, active blocking, captured deadlocks, database indexes and selected procedure definitions.
Detailed prioritized recommendations
Carry out operational changes under the normal change process. Script headings below identify the matching change batches; “None” means the available information does not support an executable change batch.
| Priority | Action | Why it matters | Database | Effort | Risk | Script heading |
|---|---|---|---|---|---|---|
| 1 | Stop the current blocking job through controlled job handling; remove its deliberate pause while a transaction is open. | Several waiting jobs were delayed for about five minutes. Confirm rollback and intended job behavior before changing it. | ShopDemo | Medium | High | None |
| 2 | Create, schedule and restore-test missing backups, including system databases; set a log-backup schedule where required. | Many databases lack a recorded full backup, putting recovery at risk. | Instance-wide | Medium | High | None |
| 3 | Check all listed databases for damage and schedule recurring checks. | No completed check was recorded for the listed user databases. | Instance-wide | Medium | Low | Check listed databases for damage |
| 4 | Rework both payment procedures to touch accounts before changing orders; test business and failure behavior before deployment. | Five captured deadlocks show the same conflicting sequence. | SalesDemo | High | High | Align payment table access; Align refund table access |
| 5 | Make the shipping procedure roll back cleanly when an update fails. | An error can otherwise leave locks in place and extend the ShopDemo queue. | ShopDemo | Low | Medium | Make shipping failures release the transaction |
| 6 | Test and install a current approved SQL Server update. | The installed build is the initial release; later servicing was identified. | Instance-wide | Medium | Medium | None |
| 7 | Correct two procedures that multiply counts or match the wrong person to a post. | They can return wrong business figures. Confirm intended output before replacement. | ForumDemo | Medium | Medium | Correct user and post counts; Correct post counts and badge selection |
| 8 | Remove the artificial pause between updates in one procedure. | It holds locks longer than needed. | ForumDemo | Low | Medium | Remove the pause between table updates |
| 9 | Restore automatic information updates used to choose efficient searches. | Disabled settings in three databases can lead to slower choices as data changes. | ForumDemo, SQLStormOltp, tpch10 | Low | Low | Restore automatic search information |
| 10 | Add one account-and-status search path for the repeated order-total calculation. | It can replace repeated broad reads, but adds work to frequent order changes. | SalesDemo | Low | Medium | Speed up account order totals |
| 11 | Add a targeted search path for customer balances. | The current balance calculation reads far more orders than it needs; it is not the cause of the leading blocker. | ShopDemo | Low | Medium | Speed up customer balance reads |
| 12 | Correct the phone-search parameter type and simplify the email comparison. | Existing search paths can then serve two busy lookups more efficiently. | RetailOps | Medium | Medium | Match the phone search to stored values; Simplify the email search |
| 13 | Search daily sales by a start and end date instead of calculating a date for each order. | This allows the existing date search path to be used. | RetailOps | Low | Low | Speed up the daily sales date search |
| 14 | Replace repeated per-customer loyalty calculations with one grouped update. | It avoids extensive repeated reads; schedule it away from busy updates. | RetailOps | Medium | Medium | Calculate loyalty tiers together |
| 15 | Protect simultaneous additions of the same cart item. | The current sequence can produce a duplicate-key error under concurrent calls; this is possible, but was not seen during the assessment. | RetailOps | Low | Medium | Protect simultaneous cart additions |
| 16 | Check existing relationships and restore trust only where the data is valid. | Untrusted relationships can prevent efficient searches; violations must be handled rather than hidden. | ForumDemo | Medium | Medium | Validate and restore trusted relationships |
| 17 | Add a search path for counting votes on a post. | A repeatedly slow count currently reads many entries; extra index maintenance is the trade-off. | ForumDemo | Low | Medium | Speed up votes by post |
| 18 | Add a targeted search path for overdue open orders. | It can reduce the work of the late-fee update, but qualifying rows still need updating. | ShopDemo | Low | Medium | Speed up overdue-order updates |
| 19 | Use one captured time when placing an order. | Order and due dates then come from the same instant, including around midnight. | ShopDemo | Low | Low | Use one time when placing an order |
| 20 | Set fixed transaction-log growth amounts after checking available disk space. | Growth time becomes more predictable; the chosen amounts are starting points, not measured demand. | ForumDemo, IndexTunerChallenge, SQLStormOltp, tpch10 | Low | Low | Make transaction-log growth predictable |
Server health
Recovery readiness is the main server-wide concern. No completed damage check was recorded for the listed user databases. Ten listed databases, including system databases, have no recorded full backup; several databases configured for point-in-time recovery have no recorded log backup. Update the backup policy, verify restores, run the checks below and install a tested current build. Memory was checked over about an hour and showed no pressure. Network connections observed across the network were encrypted.
- Put full and, where required, log backups on a tested schedule; include the system databases.
- Run the database checks during a suitable maintenance window, investigate any errors, then schedule repeat checks.
- Test and apply the approved server update.
- Check disk capacity and use predictable transaction-log growth for the four identified databases.
Technical details
- Installed build: 17.0.1000.7, SQL Server 2025 Enterprise Developer. The currency check identified build 17.0.5065.9 as CU11; confirm the currently approved build before patching.
- No recorded full backup: master, model, msdb, BenchDeploy, IndexTunerChallenge, IndexTunerChallenge_bench_challenge, TunerBench, RubricArchive, SQLStormOltp_bench_sqlstorm-oltp and tpch10_bench_tpch10-realistic.
- BenchDeploy, TunerBench and RubricArchive use FULL recovery without recorded log backups. Backup history does not establish whether external backup systems hold usable copies.
- The server-health compilation counters are not used as a workload finding: the assessment’s own data collection was excluded.
Locking and blocking
A ShopDemo job deliberately paused while keeping a transaction open, and five other jobs were waiting. The longest observed wait was about five minutes. The paused job held up shipping; the waiting shipping work then held up customer and order reads. End the current chain through controlled job handling, then remove the pause from inside the transaction. Improving balance reads and making shipping failures release their transaction address secondary exposure, not the original pause.
- Confirm the billing job’s rollback and retry behavior, then stop the current blocker through normal job control.
- Change the job step so any required delay occurs only after its transaction has finished.
- Deploy the shipping failure-handling change and the targeted balance search path after testing.
Technical details
- The lead batch runs
EXEC dbo.usp_ApplyLateFees, thenWAITFOR DELAY '00:15:00'before rolling back its open transaction. The job-step definition was not available for a safe replacement script. - The downstream shipping statement updates
dbo.Ordersafter updatingdbo.Customers. The observed balance query scanned about 200,000 Orders rows. - The observed waits were lock waits, not evidence of memory pressure. A table-level lock was present, but a lock-escalation event was not established.
Deadlocks
The captured SalesDemo deadlocks share one avoidable conflict. Payment work holds an account change while seeking order data; refund work changes an order before seeking the account. Each can wait for the other, forcing one transaction to fail. Five complete cases show this pattern. Test and deploy the two procedure changes together, and confirm callers can safely handle a failed transaction. An additional quick-read setting might reduce one reader-versus-writer conflict, but it does not replace the code fix and is not included in this change set.
- Test and deploy the paired payment and refund changes together, including checks for changed or missing orders and the required failure-handling behavior.
- Make job retries depend on proof that repeating a payment or refund cannot duplicate its business effect.
Technical details
- Resources in the complete graph are an
Orderspage and anAccountskey in SalesDemo. Payment requests a shared read on the order page; refund holds an intent lock there and requests the account key. - The paired scripts chosen below follow the complete deadlock graphs rather than the alternative procedure rewrites. Test carefully: these definitions read the order before opening a transaction and do not add transaction error handling; changed or missing rows and failure behavior need application-level review before release.
- The observed cycle does not establish that the separate ShopDemo blocking chain is a deadlock.
RetailOps indexes
Existing search paths are the best starting point. An index already exists for the suggested customer region search, and an existing order-date index can serve the daily report after its date condition is corrected. Three small indexes were fragmented, but the available evidence does not establish enough user-facing benefit to put a rebuild ahead of the code and recovery changes. The large order-lines table is a possible candidate for a separate reporting structure, but that added write and storage cost needs a measured test before deployment.
Technical details
dbo.Customersalready hasIX_Customers_Region; the server-wide missing-index suggestion overlaps it.- The date rewrite can use
dbo.Orders.IX_Orders_OrderDate. Its observed zero reads cover only the short post-restart interval and do not justify dropping it. dbo.OrderLineshas about 3 million rows. The proposed analytic index was estimated by the assessment at about 60–180 MB, based on four stored columns and unknown numeric widths; its net benefit was not measured.
RetailOps code
Several busy procedures do more work than necessary, and one cart procedure has a concurrency risk. The phone lookup scans an existing search path because its input type differs from the stored value. The email lookup changes the indexed value before comparing it, although the database comparison is already case-insensitive. Daily sales calculates a date for each order instead of searching a date range. Loyalty updates repeat customer-by-customer work. Test the matching procedure replacements together with their callers.
- Match the phone input to its stored format and compare email directly.
- Use a start and end date for daily sales, then check its actual runtime with the existing date index.
- Calculate loyalty tiers as a group during a suitable quiet period.
- Make simultaneous additions of the same cart item safe.
Technical details
Customers.Phoneisvarchar(20);usp_FindCustomerByPhonecurrently takesnvarchar(20). Check whether callers send characters the stored format cannot represent.usp_GetCustomerOrderHistoryappliesUPPERto indexedEmail; the database uses a case-insensitive collation.usp_GetDailySaleswrapsOrders.OrderDateinDATEDIFF. Its rewrite uses the existing column directly.- The loyalty procedure repeatedly invokes
fn_CustomerSpend; an inner statement ran about 300,000 times.
ForumDemo indexes
The post-vote count has a useful targeted index opportunity, while stored relationships need validation. The vote count repeatedly reads many entries for one post, so its new search path may reduce work at the cost of extra writes. Enabled but untrusted relationships can also prevent efficient searches. Validate their data before restoring trust. Other frequently run lookups were already fast per call, so wider new indexes are not included in this first change set.
- Add the post-vote search path after allowing for its write cost.
- Validate the identified relationships and investigate any violations that prevent trust from being restored.
Technical details
- The vote-count candidate is
dbo.Votes(PostId, VoteTypeId). The assessed workload averaged about 1,560 reads per call; the table also had substantial observed write activity. - The relationship inventory contains 19 names even though one summary says 18. The supplied script processes all 19 names and skips a relationship when it detects violating rows.
ForumDemo code
Two procedures can return inflated or mismatched results, and a third pauses while holding updates open. One combines votes and badges before counting them and matches a person identifier to a post identifier. Another combines comments and votes before counting and may select multiple badges. Replace these only after confirming the intended result contract. Removing a separate five-second pause will shorten the time updates hold locks; no observed blocking chain was attributed to this procedure.
- Check expected figures with business owners and deploy the two corrected result procedures.
- Remove the pause inside
usp_UpdateTablesWithDelay.
Technical details
dbo.sp00060contains the multiplying joins and the mismatched user-to-post comparison. Its replacement retains a tag-text match that still needs a separate data-model review.dbo.sp09994contains the other multiplying joins. Its replacement retains the procedure’s existing fixed historical date; confirm that date is intended.dbo.usp_UpdateTablesInLooprepeatedly chooses random rows and pauses; it appears suited to controlled test generation, not routine application traffic.
SalesDemo indexes
One additional search path addresses repeated account-total calculations. The payment workload frequently sums order amounts by account and status, while the current order access scans more widely. Add the consolidated account-and-status index after testing the extra cost on this write-heavy table. The existing primary-key searches and page density need no change.
- Build the single account-and-status index and assess payment and refund work after deployment.
Technical details
- The chosen index is
dbo.Orders(AccountId, Status) INCLUDE (Amount). It consolidates the overlapping server-wide, index and procedure suggestions into one change. - The alternative filtered index would largely duplicate this access path because the frequent calculation includes the common
Openstatus. Its script is omitted. - The table has about 50,000 rows. The assessment’s narrower candidate estimate was about 1.5 MB; the included
Amountmakes final size greater, but its exact width-related storage effect was not established.
SalesDemo code
The payment procedure’s repeated total calculation is the main measured code cost. Its new index addresses that calculation; the coordinated procedure replacements address the separate deadlock cycle described above. These changes must be tested together. Both existing procedures have explicit transactions without complete failure handling, and the selected deadlock fixes do not resolve that omission.
Technical details
dbo.usp_PayOrderrecorded about 14 million logical reads during its longer workload-history window.- Before production release, test the selected procedures for an absent order, changes between the initial read and the transaction, and errors after the transaction begins. No combined, tested rewrite addressing all three issues was provided.
ShopDemo code
Shipping needs safer failure handling, while two order searches can be narrowed. The shipping procedure can leave an open transaction if its later update fails; its replacement rolls back and passes the error on. An overdue-order search path can reduce late-fee scan work, while a separate customer-balance path reduces broad reads. Neither search path removes the deliberate pause in the billing job.
- Deploy and test the shipping failure-handling change.
- Add the balance and overdue-order search paths, allowing for extra work on order updates.
- Use a single captured time for each newly placed order.
Technical details
usp_ApplyLateFeesfiltersdbo.OrdersbyStatus = 'Open'and overdueDueDate. The filtered index supports that predicate.usp_CustomerBalancefilters byCustomerIdandStatusand totalsTotalplusLateFee. One covering-index script was selected; the duplicate was omitted.
Limitations
- Index-usage and recent runtime observations cover only about 32 hours after restart. They do not establish how infrequent or seasonal work behaves.
- Seven lower-load user databases were not examined in depth. ShopDemo was added because blocking was active there.
- Some summaries and script lists were shortened. One ForumDemo collection procedure was also incomplete; it is assessment machinery, not a basis for an application change.
- Backup history does not prove whether external backups exist or can be restored. Their location, retention and recovery requirements must be confirmed.
- The ShopDemo billing job definition was unavailable, so its unsafe pause cannot be replaced by a verified script here. The SalesDemo procedure scripts also need business-rule and failure-path testing before production use.
Scripts
These are the selected change batches, ordered to match the action plan where a script is available. Run only after the approvals and testing described above. Each block is copied from the corresponding recommendation; no additional batch has been constructed.
Check listed databases for damage — Server health — listed databases
USE [IndexTunerChallenge];
GO
DBCC CHECKDB ([IndexTunerChallenge]) WITH NO_INFOMSGS;
GO
USE [BenchDeploy];
GO
DBCC CHECKDB ([BenchDeploy]) WITH NO_INFOMSGS;
GO
USE [IndexTunerChallenge_bench_challenge];
GO
DBCC CHECKDB ([IndexTunerChallenge_bench_challenge]) WITH NO_INFOMSGS;
GO
USE [TunerBench];
GO
DBCC CHECKDB ([TunerBench]) WITH NO_INFOMSGS;
GO
USE [SQLStormOltp];
GO
DBCC CHECKDB ([SQLStormOltp]) WITH NO_INFOMSGS;
GO
USE [SQLStormOltp_bench_sqlstorm-oltp];
GO
DBCC CHECKDB ([SQLStormOltp_bench_sqlstorm-oltp]) WITH NO_INFOMSGS;
GO
USE [RubricArchive];
GO
DBCC CHECKDB ([RubricArchive]) WITH NO_INFOMSGS;
GO
USE [tpch10];
GO
DBCC CHECKDB ([tpch10]) WITH NO_INFOMSGS;
GO
USE [tpch10_bench_tpch10-realistic];
GO
DBCC CHECKDB ([tpch10_bench_tpch10-realistic]) WITH NO_INFOMSGS;
GO
USE [ForumDemo];
GO
DBCC CHECKDB ([ForumDemo]) WITH NO_INFOMSGS;
GO
USE [ShopDemo];
GO
DBCC CHECKDB ([ShopDemo]) WITH NO_INFOMSGS;
GO
USE [SalesDemo];
GO
DBCC CHECKDB ([SalesDemo]) WITH NO_INFOMSGS;
GO
USE [RetailOps];
GO
DBCC CHECKDB ([RetailOps]) WITH NO_INFOMSGS;
GO
Align payment table access — Deadlocks — SalesDemo
USE [SalesDemo];
GO
CREATE OR ALTER PROCEDURE dbo.usp_PayOrder @OrderId int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @AccountId int,
@Amount decimal(12,2);
SELECT
@AccountId = AccountId,
@Amount = Amount
FROM dbo.Orders
WHERE OrderId = @OrderId;
BEGIN TRANSACTION;
UPDATE dbo.Accounts
SET Balance = Balance - @Amount,
UpdatedAt = SYSDATETIME()
WHERE AccountId = @AccountId;
UPDATE dbo.Accounts
SET OpenOrderTotal =
(SELECT SUM(o.Amount)
FROM dbo.Orders AS o
WHERE o.AccountId = @AccountId
AND o.Status IN ('Open', 'Refunded'))
WHERE AccountId = @AccountId;
INSERT dbo.Payments (OrderId, Amount, Kind, CreatedAt)
VALUES (@OrderId, @Amount, 'Payment', SYSDATETIME());
UPDATE dbo.Orders
SET Status = 'Paid',
UpdatedAt = SYSDATETIME()
WHERE OrderId = @OrderId;
COMMIT;
END;
GO
Align refund table access — Deadlocks — SalesDemo
USE [SalesDemo];
GO
CREATE OR ALTER PROCEDURE dbo.usp_RefundOrder @OrderId int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @AccountId int,
@Amount decimal(12,2);
SELECT
@AccountId = AccountId,
@Amount = Amount
FROM dbo.Orders
WHERE OrderId = @OrderId;
BEGIN TRANSACTION;
UPDATE dbo.Accounts
SET Balance = Balance + @Amount,
UpdatedAt = SYSDATETIME()
WHERE AccountId = @AccountId;
UPDATE dbo.Orders
SET Status = 'Refunded',
UpdatedAt = SYSDATETIME()
WHERE OrderId = @OrderId;
INSERT dbo.Payments (OrderId, Amount, Kind, CreatedAt)
VALUES (@OrderId, -@Amount, 'Refund', SYSDATETIME());
COMMIT;
END;
GO
Make shipping failures release the transaction — Code — ShopDemo
USE [ShopDemo];
GO
CREATE OR ALTER PROCEDURE dbo.usp_ShipOrder
@OrderId int,
@CustomerId int
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.Customers
SET LastOrderDate = SYSDATETIME()
WHERE CustomerId = @CustomerId;
UPDATE dbo.Orders
SET Status = 'Shipped'
WHERE OrderId = @OrderId;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
THROW;
END CATCH;
END;
GO
Correct user and post counts — Code — ForumDemo
USE [ForumDemo];
GO
CREATE OR ALTER PROCEDURE dbo.sp00060
AS
BEGIN
SET NOCOUNT ON;
;WITH PostCounts AS
(
SELECT
p.OwnerUserId AS UserId,
COUNT_BIG(*) AS TotalPosts
FROM dbo.Posts AS p
WHERE p.OwnerUserId IS NOT NULL
GROUP BY p.OwnerUserId
),
VoteCounts AS
(
SELECT
p.OwnerUserId AS UserId,
SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) AS UpVotes,
SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) AS DownVotes
FROM dbo.Posts AS p
LEFT JOIN dbo.Votes AS v
ON v.PostId = p.Id
AND v.VoteTypeId IN (2, 3)
WHERE p.OwnerUserId IS NOT NULL
GROUP BY p.OwnerUserId
),
BadgeCounts AS
(
SELECT
b.UserId,
COUNT_BIG(*) AS BadgeCount
FROM dbo.Badges AS b
GROUP BY b.UserId
),
RankedPosts AS
(
SELECT
p.OwnerUserId,
p.Id AS PostId,
p.Title,
p.CreationDate,
ROW_NUMBER() OVER
(
PARTITION BY t.Id
ORDER BY p.CreationDate DESC, p.Id DESC
) AS rn,
t.TagName
FROM dbo.Posts AS p
INNER JOIN dbo.Tags AS t
ON p.Tags LIKE '%' + t.TagName + '%'
WHERE p.PostTypeId = 1
),
TopUsers AS
(
SELECT
u.Id AS UserId,
u.DisplayName,
CONVERT(int, ISNULL(pc.TotalPosts, 0)) AS TotalPosts,
CONVERT(int, ISNULL(vc.UpVotes, 0)) AS UpVotes,
CONVERT(int, ISNULL(vc.DownVotes, 0)) AS DownVotes,
CONVERT(int, ISNULL(bc.BadgeCount, 0)) AS BadgeCount,
DENSE_RANK() OVER
(
ORDER BY
ISNULL(pc.TotalPosts, 0) DESC,
ISNULL(vc.UpVotes, 0) - ISNULL(vc.DownVotes, 0) DESC
) AS UserRank
FROM dbo.Users AS u
LEFT JOIN PostCounts AS pc
ON pc.UserId = u.Id
LEFT JOIN VoteCounts AS vc
ON vc.UserId = u.Id
LEFT JOIN BadgeCounts AS bc
ON bc.UserId = u.Id
WHERE ISNULL(pc.TotalPosts, 0) > 10
)
SELECT
tu.UserRank,
tu.DisplayName,
tu.TotalPosts,
tu.UpVotes,
tu.DownVotes,
tu.BadgeCount,
rp.Title AS LatestPostTitle,
rp.CreationDate AS LatestPostDate,
CASE
WHEN rp.rn = 1 THEN 'Latest'
ELSE 'Older'
END AS PostStatus
FROM TopUsers AS tu
LEFT JOIN RankedPosts AS rp
ON rp.OwnerUserId = tu.UserId
ORDER BY
tu.UserRank,
rp.CreationDate DESC;
END;
GO
Correct post counts and badge selection — Code — ForumDemo
USE [ForumDemo];
GO
CREATE OR ALTER PROCEDURE dbo.sp09994
AS
BEGIN
SET NOCOUNT ON;
;WITH CommentCounts AS
(
SELECT
c.PostId,
COUNT_BIG(*) AS CommentCount
FROM dbo.Comments AS c
GROUP BY c.PostId
),
VoteCounts AS
(
SELECT
v.PostId,
SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) AS UpVotes,
SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) AS DownVotes
FROM dbo.Votes AS v
WHERE v.VoteTypeId IN (2, 3)
GROUP BY v.PostId
),
RankedPosts AS
(
SELECT
p.Id AS PostId,
p.OwnerUserId,
p.Title,
p.Score,
p.ViewCount,
p.CreationDate,
p.LastActivityDate,
CONVERT(int, ISNULL(cc.CommentCount, 0)) AS CommentCount,
CONVERT(int, ISNULL(vc.UpVotes, 0)) AS UpVotes,
CONVERT(int, ISNULL(vc.DownVotes, 0)) AS DownVotes,
ROW_NUMBER() OVER
(
ORDER BY
p.Score DESC,
p.ViewCount DESC,
p.CreationDate DESC,
p.Id DESC
) AS [Rank]
FROM dbo.Posts AS p
LEFT JOIN CommentCounts AS cc
ON cc.PostId = p.Id
LEFT JOIN VoteCounts AS vc
ON vc.PostId = p.Id
WHERE p.PostTypeId = 1
AND p.CreationDate >= DATEADD(YEAR, -1, CAST('2024-10-01T12:34:56' AS datetime))
),
TopPosts AS
(
SELECT
rp.PostId,
rp.OwnerUserId,
rp.Title,
rp.Score,
rp.ViewCount,
rp.CreationDate,
rp.LastActivityDate,
rp.CommentCount,
rp.UpVotes,
rp.DownVotes,
rp.[Rank]
FROM RankedPosts AS rp
WHERE rp.[Rank] <= 10
)
SELECT
tp.PostId,
tp.Title,
tp.Score,
tp.ViewCount,
tp.CreationDate,
tp.LastActivityDate,
tp.CommentCount,
tp.UpVotes,
tp.DownVotes,
COALESCE(b.Name, 'No Badge') AS TopBadge
FROM TopPosts AS tp
OUTER APPLY
(
SELECT TOP (1)
b.Name
FROM dbo.Badges AS b
WHERE tp.UpVotes > 100
AND b.UserId = tp.OwnerUserId
ORDER BY
b.Class ASC,
b.Date DESC,
b.Id DESC
) AS b
ORDER BY
tp.Score DESC,
tp.ViewCount DESC;
END;
GO
Remove the pause between table updates — Code — ForumDemo
USE [ForumDemo];
GO
CREATE OR ALTER PROCEDURE dbo.usp_UpdateTablesWithDelay
@UserId INT,
@NewReputation INT,
@PostId INT,
@NewScore INT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.Users
SET Reputation = @NewReputation,
LastAccessDate = GETDATE()
WHERE Id = @UserId;
DECLARE @RowsAffected1 INT = @@ROWCOUNT;
UPDATE dbo.Posts
SET Score = @NewScore,
LastActivityDate = GETDATE()
WHERE Id = @PostId;
DECLARE @RowsAffected2 INT = @@ROWCOUNT;
COMMIT TRANSACTION;
SELECT
'Success' AS Status,
'Both tables updated successfully' AS Message,
@RowsAffected1 AS UsersRowsAffected,
@RowsAffected2 AS PostsRowsAffected;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
SELECT
'Error' AS Status,
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_LINE() AS ErrorLine,
ERROR_PROCEDURE() AS ErrorProcedure;
THROW;
END CATCH;
END;
GO
Restore automatic search information — Server health — ForumDemo, SQLStormOltp and tpch10
USE [ForumDemo];
GO
ALTER DATABASE [ForumDemo] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [ForumDemo] SET AUTO_UPDATE_STATISTICS ON;
GO
USE [SQLStormOltp];
GO
ALTER DATABASE [SQLStormOltp] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [SQLStormOltp] SET AUTO_UPDATE_STATISTICS ON;
GO
USE [tpch10];
GO
ALTER DATABASE [tpch10] SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE [tpch10] SET AUTO_UPDATE_STATISTICS ON;
GO
Speed up account order totals — Indexes — SalesDemo
USE [SalesDemo];
GO
IF NOT EXISTS
(
SELECT 1
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Orders')
AND name = N'IX_Orders_AccountId_Status_Amount'
)
BEGIN
CREATE NONCLUSTERED INDEX [IX_Orders_AccountId_Status_Amount]
ON [dbo].[Orders] ([AccountId], [Status])
INCLUDE ([Amount])
WITH
(
ONLINE = ON,
SORT_IN_TEMPDB = ON,
DATA_COMPRESSION = NONE
);
END;
GO
Speed up customer balance reads — Indexes — ShopDemo
USE [ShopDemo];
GO
IF NOT EXISTS
(
SELECT 1
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Orders')
AND name = N'IX_Orders_Customer_Status_Balance'
)
BEGIN
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Status_Balance
ON dbo.Orders (CustomerId, Status)
INCLUDE (Total, LateFee)
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);
END;
GO
Match the phone search to stored values — Code — RetailOps
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
Simplify the email search — Code — RetailOps
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
Speed up the daily sales date search — Code — RetailOps
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
Calculate loyalty tiers together — Code — RetailOps
USE [RetailOps];
GO
CREATE OR ALTER PROCEDURE dbo.usp_RecalcLoyaltyTiers
@Region varchar(20),
@AsOf date
AS
BEGIN
SET NOCOUNT ON;
;WITH RegionalSpend AS
(
SELECT o.CustomerId,
SUM(ol.Quantity * ol.UnitPrice) AS Spend
FROM dbo.Customers AS c
JOIN dbo.Orders AS o
ON o.CustomerId = c.CustomerId
JOIN dbo.OrderLines AS ol
ON ol.OrderId = o.OrderId
WHERE c.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 COALESCE(rs.Spend, CONVERT(decimal(14, 2), 0)) >= 12500 THEN 'Gold'
WHEN COALESCE(rs.Spend, CONVERT(decimal(14, 2), 0)) >= 9500 THEN 'Silver'
ELSE 'Bronze'
END
FROM dbo.Customers AS c
LEFT JOIN RegionalSpend AS rs
ON rs.CustomerId = c.CustomerId
WHERE c.Region = @Region;
END;
GO
Protect simultaneous cart additions — Code — RetailOps
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, SERIALIZABLE)
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
Validate and restore trusted relationships — Indexes — ForumDemo
USE [ForumDemo];
GO
IF NOT EXISTS
(
SELECT 1
FROM [AIIndexTuner].[Recommendation] AS c
WHERE NOT EXISTS
(
SELECT 1
FROM [AIIndexTuner].[Run] AS p
WHERE p.[RunId] = c.[RunId]
)
)
BEGIN
ALTER TABLE [AIIndexTuner].[Recommendation]
WITH CHECK CHECK CONSTRAINT [FK_AIIndexTuner_Recommendation_Run];
END
ELSE
PRINT N'FK_AIIndexTuner_Recommendation_Run skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Badges] AS c
WHERE c.[UserId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Users] AS p
WHERE p.[Id] = c.[UserId]
)
)
BEGIN
ALTER TABLE [dbo].[Badges]
WITH CHECK CHECK CONSTRAINT [FK__Badges__UserId__6477ECF3];
END
ELSE
PRINT N'FK__Badges__UserId__6477ECF3 skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Comments] AS c
WHERE c.[PostId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS p
WHERE p.[Id] = c.[PostId]
)
)
BEGIN
ALTER TABLE [dbo].[Comments]
WITH CHECK CHECK CONSTRAINT [FK__Comments__PostId__4CA06362];
END
ELSE
PRINT N'FK__Comments__PostId__4CA06362 skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Comments] AS c
WHERE c.[UserId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Users] AS p
WHERE p.[Id] = c.[UserId]
)
)
BEGIN
ALTER TABLE [dbo].[Comments]
WITH CHECK CHECK CONSTRAINT [FK__Comments__UserId__4D94879B];
END
ELSE
PRINT N'FK__Comments__UserId__4D94879B skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[PostHistory] AS c
WHERE c.[PostHistoryTypeId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[PostHistoryTypes] AS p
WHERE p.[Id] = c.[PostHistoryTypeId]
)
)
BEGIN
ALTER TABLE [dbo].[PostHistory]
WITH CHECK CHECK CONSTRAINT [FK__PostHisto__PostH__5070F446];
END
ELSE
PRINT N'FK__PostHisto__PostH__5070F446 skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[PostHistory] AS c
WHERE c.[PostId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS p
WHERE p.[Id] = c.[PostId]
)
)
BEGIN
ALTER TABLE [dbo].[PostHistory]
WITH CHECK CHECK CONSTRAINT [FK__PostHisto__PostI__5165187F];
END
ELSE
PRINT N'FK__PostHisto__PostI__5165187F skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[PostHistory] AS c
WHERE c.[UserId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Users] AS p
WHERE p.[Id] = c.[UserId]
)
)
BEGIN
ALTER TABLE [dbo].[PostHistory]
WITH CHECK CHECK CONSTRAINT [FK__PostHisto__UserI__52593CB8];
END
ELSE
PRINT N'FK__PostHisto__UserI__52593CB8 skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[PostLinks] AS c
WHERE c.[LinkTypeId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[LinkTypes] AS p
WHERE p.[Id] = c.[LinkTypeId]
)
)
BEGIN
ALTER TABLE [dbo].[PostLinks]
WITH CHECK CHECK CONSTRAINT [FK__PostLinks__LinkT__571DF1D5];
END
ELSE
PRINT N'FK__PostLinks__LinkT__571DF1D5 skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[PostLinks] AS c
WHERE c.[PostId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS p
WHERE p.[Id] = c.[PostId]
)
)
BEGIN
ALTER TABLE [dbo].[PostLinks]
WITH CHECK CHECK CONSTRAINT [FK__PostLinks__PostI__5535A963];
END
ELSE
PRINT N'FK__PostLinks__PostI__5535A963 skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[PostLinks] AS c
WHERE c.[RelatedPostId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS p
WHERE p.[Id] = c.[RelatedPostId]
)
)
BEGIN
ALTER TABLE [dbo].[PostLinks]
WITH CHECK CHECK CONSTRAINT [FK__PostLinks__Relat__5629CD9C];
END
ELSE
PRINT N'FK__PostLinks__Relat__5629CD9C skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS c
WHERE c.[AcceptedAnswerId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS p
WHERE p.[Id] = c.[AcceptedAnswerId]
)
)
BEGIN
ALTER TABLE [dbo].[Posts]
WITH CHECK CHECK CONSTRAINT [FK__Posts__AcceptedA__48CFD27E];
END
ELSE
PRINT N'FK__Posts__AcceptedA__48CFD27E skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS c
WHERE c.[LastEditorUserId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Users] AS p
WHERE p.[Id] = c.[LastEditorUserId]
)
)
BEGIN
ALTER TABLE [dbo].[Posts]
WITH CHECK CHECK CONSTRAINT [FK__Posts__LastEdito__47DBAE45];
END
ELSE
PRINT N'FK__Posts__LastEdito__47DBAE45 skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS c
WHERE c.[OwnerUserId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Users] AS p
WHERE p.[Id] = c.[OwnerUserId]
)
)
BEGIN
ALTER TABLE [dbo].[Posts]
WITH CHECK CHECK CONSTRAINT [FK__Posts__OwnerUser__46E78A0C];
END
ELSE
PRINT N'FK__Posts__OwnerUser__46E78A0C skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS c
WHERE c.[ParentId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS p
WHERE p.[Id] = c.[ParentId]
)
)
BEGIN
ALTER TABLE [dbo].[Posts]
WITH CHECK CHECK CONSTRAINT [FK__Posts__ParentId__49C3F6B7];
END
ELSE
PRINT N'FK__Posts__ParentId__49C3F6B7 skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS c
WHERE c.[PostTypeId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[PostTypes] AS p
WHERE p.[Id] = c.[PostTypeId]
)
)
BEGIN
ALTER TABLE [dbo].[Posts]
WITH CHECK CHECK CONSTRAINT [FK__Posts__PostTypeI__45F365D3];
END
ELSE
PRINT N'FK__Posts__PostTypeI__45F365D3 skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Tags] AS c
WHERE c.[ExcerptPostId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS p
WHERE p.[Id] = c.[ExcerptPostId]
)
)
BEGIN
ALTER TABLE [dbo].[Tags]
WITH CHECK CHECK CONSTRAINT [FK__Tags__ExcerptPos__59FA5E80];
END
ELSE
PRINT N'FK__Tags__ExcerptPos__59FA5E80 skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Tags] AS c
WHERE c.[WikiPostId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Posts] AS p
WHERE p.[Id] = c.[WikiPostId]
)
)
BEGIN
ALTER TABLE [dbo].[Tags]
WITH CHECK CHECK CONSTRAINT [FK__Tags__WikiPostId__5AEE82B9];
END
ELSE
PRINT N'FK__Tags__WikiPostId__5AEE82B9 skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Votes] AS c
WHERE c.[UserId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[Users] AS p
WHERE p.[Id] = c.[UserId]
)
)
BEGIN
ALTER TABLE [dbo].[Votes]
WITH CHECK CHECK CONSTRAINT [FK__Votes__UserId__5EBF139D];
END
ELSE
PRINT N'FK__Votes__UserId__5EBF139D skipped because violating rows exist.';
GO
IF NOT EXISTS
(
SELECT 1
FROM [dbo].[Votes] AS c
WHERE c.[VoteTypeId] IS NOT NULL
AND NOT EXISTS
(
SELECT 1
FROM [dbo].[VoteTypes] AS p
WHERE p.[Id] = c.[VoteTypeId]
)
)
BEGIN
ALTER TABLE [dbo].[Votes]
WITH CHECK CHECK CONSTRAINT [FK__Votes__VoteTypeI__5DCAEF64];
END
ELSE
PRINT N'FK__Votes__VoteTypeI__5DCAEF64 skipped because violating rows exist.';
GO
Speed up votes by post — Indexes — ForumDemo
USE [ForumDemo];
GO
IF NOT EXISTS
(
SELECT 1
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Votes')
AND name = N'IX_Votes_PostId_VoteTypeId'
)
BEGIN
CREATE NONCLUSTERED INDEX [IX_Votes_PostId_VoteTypeId]
ON [dbo].[Votes]
(
[PostId] ASC,
[VoteTypeId] ASC
)
WITH
(
DATA_COMPRESSION = PAGE,
ONLINE = ON,
SORT_IN_TEMPDB = ON,
FILLFACTOR = 100
);
END;
GO
Speed up overdue-order updates — Indexes — ShopDemo
USE [ShopDemo];
GO
IF NOT EXISTS
(
SELECT 1
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.Orders')
AND name = N'IX_Orders_Open_DueDate'
)
BEGIN
CREATE NONCLUSTERED INDEX IX_Orders_Open_DueDate
ON dbo.Orders (DueDate)
INCLUDE (Total)
WHERE Status = 'Open'
WITH (ONLINE = ON, SORT_IN_TEMPDB = ON);
END;
GO
Use one time when placing an order — Code — ShopDemo
USE [ShopDemo];
GO
CREATE OR ALTER PROCEDURE dbo.usp_PlaceOrder
@CustomerId int,
@Total decimal(12,2)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Now datetime2(0) = SYSDATETIME();
INSERT dbo.Orders
(CustomerId, OrderDate, DueDate, Status, Total)
VALUES
(@CustomerId, @Now, DATEADD(day, 30, CAST(@Now AS date)), 'Open', @Total);
END;
GO
Make transaction-log growth predictable — Server health — ForumDemo, IndexTunerChallenge, SQLStormOltp and tpch10
USE [ForumDemo];
GO
ALTER DATABASE [ForumDemo]
MODIFY FILE
(
NAME = N'SQLStorm_log',
FILEGROWTH = 256MB
);
GO
USE [IndexTunerChallenge];
GO
ALTER DATABASE [IndexTunerChallenge]
MODIFY FILE
(
NAME = N'IndexTunerChallenge_log',
FILEGROWTH = 512MB
);
GO
USE [SQLStormOltp];
GO
ALTER DATABASE [SQLStormOltp]
MODIFY FILE
(
NAME = N'SQLStorm_log',
FILEGROWTH = 64MB
);
GO
USE [tpch10];
GO
ALTER DATABASE [tpch10]
MODIFY FILE
(
NAME = N'tpch10_log',
FILEGROWTH = 64MB
);
GO
Assessment run log
Started 2026-11-19 23:27 UTC, took 9m 43s, used 11 credits.
Databases analyzed: Chosen by share of plan-cache CPU across 10 user database(s): RetailOps 57%, ForumDemo 32.4%, SalesDemo 9.1%. 7 database(s) under 2% of the load were left out. Blocking was active in ShopDemo, so Locking and Blocking connected there and its code is reviewed too.
| Step | Outcome | Duration | Credit | Notes |
|---|---|---|---|---|
| Server Health | Completed | 42s | 1 | |
| Locking and Blocking | Completed | 57s | 1 | |
| Fix Deadlocks | Completed | 34s | 1 | |
| Index Tuning (RetailOps) | Completed | 54s | 1 | |
| Code Review (RetailOps) | Completed | 55s | 1 | |
| Index Tuning (ForumDemo) | Completed | 57s | 1 | |
| Code Review (ForumDemo) | Completed | 52s | 1 | |
| Index Tuning (SalesDemo) | Completed | 34s | 1 | |
| Code Review (SalesDemo) | Completed | 36s | 1 | |
| Code Review (ShopDemo) | Completed | 41s | 1 | |
| Assessment report | Completed | 1m 54s | 1 |