Index Tuning Report – RockyPC/SQLStorm – 2026-06-16

Executive Summary

The workload is dominated by large analytic CTE queries that join Users, Posts, Votes, Comments, Badges and PostHistory, frequently aggregating by UserId/PostId and filtering on Reputation, CreationDate and VoteTypeId. All tables are read-only in this window (every index shows updates=0 and zero leaf write ops), so the maintenance cost of new indexes is effectively nil — write penalty is the major restraint that does not apply here. Page density is excellent (98–99%) and fragmentation negligible, so no rebuilds are warranted.

Top priorities:

1. P1 Create the high-impact missing index on dbo.PostHistory(PostId) INCLUDE(PostHistoryTypeId) — 99.64% avg user impact, supports the close-vote / history-count subqueries.
2. P1 Create dbo.Votes(UserId) INCLUDE(VoteTypeId, BountyAmount) — 96–99% impact; the existing IX_Votes_Bounty8_PostId is keyed on PostId and cannot serve UserId-grouped aggregates that pervade the workload.
3. P2 Re-enable the 18 untrusted foreign keys WITH CHECK so the optimizer can perform join elimination and predicate pruning on these heavily-joined tables.
4. P3 Add a narrow dbo.Users(Reputation) INCLUDE(DisplayName) filtered/non-filtered index to support the frequent Reputation > N and ORDER BY Reputation DESC ranking patterns.
5. INFO The dominant cost driver is Posts.Tags LIKE '%' + TagName + '%' — a non-SARGable cross-join that no index can fix; query rewrite is the only effective remedy.

Overall confidence: High for P1/P2 items; Medium for P3 and rewrite guidance.

Environment & Key Observations

• Enterprise engine features available: online index ops, data compression (PAGE in use), columnstore, partitioning.
• FORCE_LAST_GOOD_PLAN: Desired = ON, Actual = ON — correctly configured. No action required. No forced-plan failures present.
• All target tables 246K–926K rows — above the 10K threshold, so selective indexes are justified.
• Every index reports user_updates = 0 in this capture window → adding read-optimized indexes carries minimal write risk for the analytic workload shown.
• Page density 98–99% across all indexes; fragmentation <4%. No rebuild/reorg recommended.

Detailed Prioritized Recommendations

1. Missing Index: dbo.PostHistory(PostId) INCLUDE(PostHistoryTypeId) P1

Avg user impact 99.64%, avg total user cost 25,108. The clustered PK is keyed on Id, so any predicate on PostId currently scans 820 MB / 8,249 pages. PostId has ~246K distinct values (density 4.05E-06) — highly selective. Queries such as the close-vote subquery (FROM PostHistory WHERE PostId ... grouping/counting history rows) and history-count CTEs directly benefit.

Illustrative benefiting fragment: SELECT PostId, COUNT(*) FROM PostHistory WHERE CreationDate > @d GROUP BY PostId and FROM PostHistory ph WHERE ph.PostId = p.Id.

Confidence: High. Read-only table, no write penalty, est. storage ~19 MB.

2. Missing Index: dbo.Votes(UserId) INCLUDE(VoteTypeId, BountyAmount) P1

Two missing-index requests target Votes(UserId): one with includes VoteTypeId (96.21%) and one with VoteTypeId, BountyAmount (99.73%). They are left-prefix compatible — create the single wider index covering both. The existing IX_Votes_Bounty8_PostId (key PostId) cannot serve U.Id = V.UserId joins that appear in the majority of the top workload queries (UpVote/DownVote/Bounty aggregation per user).

Illustrative benefiting fragment: LEFT JOIN Votes V ON U.Id = V.UserId with SUM(CASE WHEN V.VoteTypeId=2 ...) and SUM(V.BountyAmount).

Note: UserId density 0.00107 (~929 distinct values present in the stat sample) is lower selectivity than PostId, but covering includes make this an efficient seek+covered-scan for per-user aggregation. Build the covering version to avoid maintaining two overlapping indexes.

Confidence: High. Est. storage ~28 MB, write penalty 0 in window.

3. Re-enable 18 Untrusted Foreign Keys WITH CHECK P2

All 18 FKs are NotTrusted=True, Disabled=False. Untrusted FKs block the optimizer from join elimination and predicate pruning — directly harmful to this join-heavy workload where many CTEs join through Users/Posts only to count. Re-enabling WITH CHECK validates existing data (a scan cost on Posts/PostHistory/Votes — the largest at 820 MB / 926K rows) but is a one-time cost. Keep them trusted to preserve plan-quality benefits.

Confidence: High for benefit; validate data compliance first. Validation scan cost Medium on large tables.

4. Index: dbo.Users(Reputation) INCLUDE(DisplayName) P3

Missing-index request: inequality on Reputation, 26.19% impact, 33 seeks/scans. Many queries filter U.Reputation > 1000 / > 100 and rank ORDER BY Reputation DESC. Reputation has 2,443 distinct values (density 4.09E-04) — moderate selectivity; > 1000 predicates are reasonably selective. Adding DisplayName as an include covers the common projection. Lower priority because impact is moderate and the clustered Users scan is only 44.8 MB.

Illustrative benefiting fragment: FROM Users U WHERE U.Reputation > 1000.

Confidence: Medium. Est. storage ~4 MB.

5. Query Rewrites — the Real Bottleneck P2

The longest-running Query Store entries (each ~300,000,000 ms / hitting the 300s timeout in repeated runs and huge CPU) share the anti-pattern JOIN Tags t ON p.Tags LIKE '%' + t.TagName + '%'. This produces a Cartesian-style scan of Posts × Tags with a non-SARGable predicate — no index can help. Recommended:

• Normalize tags into a PostTags(PostId, TagName) bridge table (populated via STRING_SPLIT on '<tag>' delimiters), indexed on TagName — converts the LIKE join into an equijoin.
• Where STRING_SPLIT(Tags,'>') is already used, materialize once into a temp table rather than repeating per CTE.
• Replace repeated COUNT(DISTINCT ...) across multiple LEFT JOINs (fan-out) with pre-aggregated derived tables joined on the grain, eliminating row explosion that drives the multi-million-row actuals.

Confidence: High that LIKE-join is the dominant cost; Medium on exact rewrite shape without plans.

6. Columnstore Consideration (PostHistory / Votes / Posts) INFO

PostHistory (847K), Votes (926K) and Posts (246K) exceed the columnstore threshold and the workload is analytic/aggregation-heavy. A nonclustered columnstore index (NCCI) on the aggregated columns could dramatically cut the batch-mode aggregation cost — but only if the OLTP write pattern remains light. Given write activity is essentially zero here, an NCCI on Votes (UserId, PostId, VoteTypeId, BountyAmount) is worth piloting. Treated as INFO pending confirmation that this is not a transactional table during business hours.

Confidence: Medium — strong analytic fit, but verify write cadence before committing.

7. Automatic Tuning & Statistics P3

• FORCE_LAST_GOOD_PLAN is ON/ON — no change.
• Auto Create / Update Stats are ON; Auto Update Async is OFF. Given the large analytic queries and stable read-only data, enabling AUTO_UPDATE_STATISTICS_ASYNC avoids stalling these long queries on synchronous stat refreshes.
• After creating the new indexes, run a targeted UPDATE STATISTICS ... WITH FULLSCAN on PostHistory, Votes and Users to give the optimizer accurate distributions immediately.

Confidence: Medium.

Index Structure Overview

Snapshot of target tables, key usage and recommended change. All tables read-only in window (updates=0).

Scripts

Script 1 — Create missing index on PostHistory (Recommendation 1)

USE [SQLStorm];
GO
CREATE NONCLUSTERED INDEX IX_PostHistory_PostId_inclType
ON dbo.PostHistory (PostId)
INCLUDE (PostHistoryTypeId)
WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, FILLFACTOR = 100);
GO

Script 2 — Create covering index on Votes(UserId) (Recommendation 2)

USE [SQLStorm];
GO
-- Single covering index satisfies both missing-index requests
-- (UserId INCLUDE VoteTypeId) and (UserId INCLUDE VoteTypeId, BountyAmount)
CREATE NONCLUSTERED INDEX IX_Votes_UserId_inclTypeBounty
ON dbo.Votes (UserId)
INCLUDE (VoteTypeId, BountyAmount)
WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, FILLFACTOR = 100);
GO

Script 3 — Create Users(Reputation) index (Recommendation 4)

USE [SQLStorm];
GO
CREATE NONCLUSTERED INDEX IX_Users_Reputation_inclDisplayName
ON dbo.Users (Reputation)
INCLUDE (DisplayName)
WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, FILLFACTOR = 100);
GO

Script 4 — Validate & re-enable untrusted foreign keys WITH CHECK (Recommendation 3)

USE [SQLStorm];
GO
-- Run during a maintenance window: WITH CHECK scans each child table.
-- If any statement fails, data violates the FK and must be cleaned first.
ALTER TABLE dbo.Badges    WITH CHECK CHECK CONSTRAINT FK__Badges__UserId__6477ECF3;
ALTER TABLE dbo.Comments  WITH CHECK CHECK CONSTRAINT FK__Comments__PostId__4CA06362;
ALTER TABLE dbo.Comments  WITH CHECK CHECK CONSTRAINT FK__Comments__UserId__4D94879B;
ALTER TABLE dbo.PostHistory WITH CHECK CHECK CONSTRAINT FK__PostHisto__PostH__5070F446;
ALTER TABLE dbo.PostHistory WITH CHECK CHECK CONSTRAINT FK__PostHisto__PostI__5165187F;
ALTER TABLE dbo.PostHistory WITH CHECK CHECK CONSTRAINT FK__PostHisto__UserI__52593CB8;
ALTER TABLE dbo.PostLinks  WITH CHECK CHECK CONSTRAINT FK__PostLinks__LinkT__571DF1D5;
ALTER TABLE dbo.PostLinks  WITH CHECK CHECK CONSTRAINT FK__PostLinks__PostI__5535A963;
ALTER TABLE dbo.PostLinks  WITH CHECK CHECK CONSTRAINT FK__PostLinks__Relat__5629CD9C;
ALTER TABLE dbo.Posts      WITH CHECK CHECK CONSTRAINT FK__Posts__AcceptedA__48CFD27E;
ALTER TABLE dbo.Posts      WITH CHECK CHECK CONSTRAINT FK__Posts__LastEdito__47DBAE45;
ALTER TABLE dbo.Posts      WITH CHECK CHECK CONSTRAINT FK__Posts__OwnerUser__46E78A0C;
ALTER TABLE dbo.Posts      WITH CHECK CHECK CONSTRAINT FK__Posts__ParentId__49C3F6B7;
ALTER TABLE dbo.Posts      WITH CHECK CHECK CONSTRAINT FK__Posts__PostTypeI__45F365D3;
ALTER TABLE dbo.Tags       WITH CHECK CHECK CONSTRAINT FK__Tags__ExcerptPos__59FA5E80;
ALTER TABLE dbo.Tags       WITH CHECK CHECK CONSTRAINT FK__Tags__WikiPostId__5AEE82B9;
ALTER TABLE dbo.Votes      WITH CHECK CHECK CONSTRAINT FK__Votes__UserId__5EBF139D;
ALTER TABLE dbo.Votes      WITH CHECK CHECK CONSTRAINT FK__Votes__VoteTypeI__5DCAEF64;
GO
-- Verify all are now trusted (expect zero rows):
SELECT OBJECT_NAME(parent_object_id) AS TableName, name AS FK, is_not_trusted
FROM sys.foreign_keys
WHERE is_not_trusted = 1;
GO

Script 5 — Optional tag normalization bridge (Recommendation 5)

USE [SQLStorm];
GO
-- Replaces non-SARGable  Tags LIKE '%' + TagName + '%'  joins with an equijoin.
CREATE TABLE dbo.PostTags
(
    PostId  int       NOT NULL,
    TagName nvarchar(64) NOT NULL,
    CONSTRAINT PK_PostTags PRIMARY KEY CLUSTERED (PostId, TagName)
        WITH (DATA_COMPRESSION = PAGE)
);
GO
INSERT INTO dbo.PostTags (PostId, TagName)
SELECT p.Id,
       LTRIM(RTRIM(REPLACE(REPLACE(s.value,'<',''),'>','')))
FROM dbo.Posts p
CROSS APPLY STRING_SPLIT(p.Tags, '>') s
WHERE p.Tags IS NOT NULL
  AND LEN(LTRIM(RTRIM(REPLACE(REPLACE(s.value,'<',''),'>','')))) > 0;
GO
CREATE NONCLUSTERED INDEX IX_PostTags_TagName
ON dbo.PostTags (TagName) INCLUDE (PostId)
WITH (DATA_COMPRESSION = PAGE);
GO
-- Then rewrite queries:  JOIN dbo.PostTags pt ON pt.PostId = p.Id  (equijoin on pt.TagName = t.TagName)

Script 6 — Enable async stats update & refresh statistics (Recommendation 7)

USE [master];
GO
ALTER DATABASE [SQLStorm] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
GO
USE [SQLStorm];
GO
UPDATE STATISTICS dbo.PostHistory WITH FULLSCAN;
UPDATE STATISTICS dbo.Votes       WITH FULLSCAN;
UPDATE STATISTICS dbo.Users       WITH FULLSCAN;
GO

Script 7 — Optional: pilot nonclustered columnstore on Votes (Recommendation 6)

USE [SQLStorm];
GO
-- Pilot only after confirming Votes is not write-hot during business hours.
-- Evaluate batch-mode aggregation gains, then keep or drop.
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Votes_Analytics
ON dbo.Votes (UserId, PostId, VoteTypeId, BountyAmount)
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO
-- Rollback if not beneficial:
-- DROP INDEX NCCI_Votes_Analytics ON dbo.Votes;
GO

No index rebuilds are scripted: every analyzed index reports ≥98% page density and <4% fragmentation, so rebuild/reorg would yield no benefit. No index drops are scripted: no duplicate/overlapping indexes were detected, and all existing indexes back constraints or show read usage.