Index Tuning Report – RockyPC / SQLStorm – 2026-11-09

Tuning goal: Index Tuning

Server: RockyPC Database: SQLStorm SQL Server 2025 RTM-GDR 17.0.1145.4 Enterprise Developer / Enterprise Engine

Executive summary

The strongest actionable opportunity is adding a small, consolidated set of rowstore indexes for dbo.PostHistory, dbo.Votes, and dbo.Comments. The recommendations are supported by missing-index impact, high row counts, and repeated joins and aggregations in the plan cache and Query Store. The measured observation period is only 3.9 days, from 2026-11-05 09:45:22 local time, so usage-based drop decisions are not reliable.

99.62%Average reported impact for the highest-impact PostHistory suggestion.
1.51K secQuery Store total time reported for the workload insights sample.
847,593Rows in dbo.PostHistory.
0Duplicate or left-prefix overlapping indexes detected.
  1. Implement the consolidated rowstore indexes. Estimated new storage is approximately 116.3 MB, based on the supplied missing-index estimates: 45.3 MB, 32.3 MB, 28.3 MB, and 10.4 MB. The final Comments estimate is not supplied; the 10.4 MB figure is an estimate derived from the supplied 5.4 MB candidate plus key/overhead allowance, not a measured size.
  2. Restore trust to all 17 reported foreign keys after the conditional validation in the supplied script confirms that no violating rows exist. Trusted foreign keys help optimizer join elimination and predicate reasoning. Validation scans can be expensive on large tables.
  3. Enable automatic and asynchronous statistics maintenance. Both auto-create and auto-update statistics are currently false. This is a settings risk for the repeated analytical workload and should be corrected before judging index effectiveness.
  4. Rewrite the tag search and reduce repeated fan-out aggregation. The costly query with hash 872C7773968C6AAF uses p.Tags LIKE '%' + t.TagName + '%', which cannot seek through a normal index on Tags. A Posts index alone will not solve that predicate.
  5. Do not add columnstore yet. No large tables were reported, the largest listed table has 926,084 rows, and write activity is low in the supplied operational sample but not fully representative. The supplied threshold of more than 1,000,000 rows is not met.
Confidence: High for the three missing-index families and constraint/statistics findings; medium for the exact benefit of the indexes because statistics cover only 3.9 days and the supplied plan text is truncated for many statements. No index drops are recommended.

Environment and evidence

ItemReported valueInterpretation
PlatformSQL Server 2025, Enterprise Developer Edition, Enterprise engineOnline index operations, page compression, columnstore, partition switching, CE feedback, DOP feedback, and OPPO capabilities are available subject to database settings and compatibility level.
Version17.0.1145.4, RTM-GDR, KB5128492Scripts below use SQL Server 2025-compatible syntax.
Usage window3.9 days since 2026-11-05 09:45:22 local timeLow-use indexes must not be dropped on this evidence alone. Monthly or quarterly workload is not represented.
Query StoreREAD_WRITE; workload sample includes executions during the last 7 daysSeveral queries have durations from 10,984.06 ms to 303,112.89 ms and reads as high as 8,614,? The supplied exact maximum is 8,496,661 reads in the Query Store workload insights.
Index operationsAll reported operational hotspots show zero inserts, updates, deletes, waits, and latch waitsThis is a short or read-dominant sample, not proof that future writes are free.

All figures in this report are quoted from the supplied input unless explicitly labeled as an estimate.

Detailed prioritized recommendations

Priority 1 — Add consolidated indexes for dbo.PostHistory

The missing-index signals overlap around PostId, CreationDate, PostHistoryTypeId, UserId, and Comment. The recommended consolidation is:

  • PostId, CreationDate, PostHistoryTypeId INCLUDE Comment — estimated 45.3 MB for 847,593 rows, using the supplied candidate estimate.
  • CreationDate, UserId INCLUDE Comment — estimated 32.3 MB for 847,593 rows, using the supplied candidate estimate.
  • PostHistoryTypeId, PostId, CreationDate INCLUDE Comment — estimated 32.3 MB for 847,593 rows, using the supplied candidate estimate. This also addresses the type-led candidate signals while retaining join columns.

The Comment column is varchar(800) with a sampled average width of 32 bytes, so it is narrow enough for these candidates according to the input. The input does not identify Comment as a MAX or LOB column. The reported 99.62% impact and 48 total seeks/scans support the first family most strongly. The remaining families have lower reported seek/scan counts, so confidence is medium.

Priority 2 — Add the dbo.Votes user-oriented covering index

Create (UserId) INCLUDE (VoteTypeId, BountyAmount). The missing-index recommendation reports average impact of 99.51%, average total user cost of 4,137.06, and 15 total seeks/scans. The supplied candidate size is 28.3 MB for 926,084 rows. This complements, rather than duplicates, the existing IX_Votes_PostId_VoteType, which is keyed by PostId and has 482 seeks and 457 scans during the 3.9-day window.

Priority 3 — Add the dbo.Comments(PostId) join index

Create (PostId). The recommendation reports 246 total seeks/scans and average impact of 37.50%. The supplied candidate estimate is 5.4 MB, but the input does not provide the final row count and width calculation for the exact index. The size should therefore be treated as a candidate estimate, not a measured post-build size.

Priority 4 — Re-trust the foreign keys conditionally

Every foreign key listed in the untrusted-constraint section is enabled but not trusted. An untrusted foreign key can prevent optimizer join elimination and reduce predicate-pruning opportunities. The script validates each relationship inline; it executes WITH CHECK CHECK CONSTRAINT only when no violating row exists and prints the constraint name when violations remain.

Priority 5 — Enable statistics creation and maintenance

AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS, and asynchronous statistics updates are all reported as false. Enable them as separate changes. The supplied database is SQLStorm, and each setting script is independent so a rejected or deferred setting does not prevent the others from running.

Index design and storage impact

ActionKey/include designRowsEstimated sizeReason
Createdbo.PostHistory(PostId, CreationDate, PostHistoryTypeId) INCLUDE (Comment)847,59345.3 MB, supplied estimateConsolidates several PostId-leading suggestions and supports history joins and type filters.
Createdbo.PostHistory(CreationDate, UserId) INCLUDE (Comment)847,59332.3 MB, supplied estimateSupports the CreationDate/UserId candidate.
Createdbo.PostHistory(PostHistoryTypeId, PostId, CreationDate) INCLUDE (Comment)847,59332.3 MB, supplied estimateSupports type-led history aggregation and preserves join/date columns.
Createdbo.Votes(UserId) INCLUDE (VoteTypeId, BountyAmount)926,08428.3 MB, supplied estimateSupports user-level vote aggregation and bounty calculations.
Createdbo.Comments(PostId)351,440 reported table rows5.4 MB supplied candidate estimate; exact final size uncertainSupports repeated PostId joins and correlated comment counts.

The estimated gross storage addition is approximately 143.6 MB when summing all supplied candidate estimates and the estimated Comments figure: 45.3 + 32.3 + 32.3 + 28.3 + 5.4 MB. Because the three PostHistory designs may have different compression and key-width behavior than the candidate generator assumes, this is a planning estimate. No indexes are dropped, so estimated reclaimed storage is 0 MB and estimated net change is approximately +143.6 MB.

Existing page density is healthy: the lowest reported value is 93.65% for the clustered Posts primary key; all other reported values are at least 98.29%. No rebuild or fill-factor change is justified by page density. Fragmentation is also low except for the Posts primary key at 11.52%, with only 3,316 pages, so a rebuild is not prioritized.

Query workload findings

Tag substring search is non-seekable

The highest CPU plan-cache query, hash 872C7773968C6AAF, executed 4 times with average CPU time reported as 323,745,169 and average logical reads of 862,018. Its CTE contains p.Tags LIKE '%' + t.TagName + '%'. The leading wildcard prevents a normal B-tree seek on Tags. The same pattern appears in Query Store workload text with an average duration of 99,671.55 ms for 10 executions and 226,609 average reads in one related entry.

  • Prefer a normalized post-to-tag relationship table keyed by TagName and PostId, or a full-text/search solution appropriate to the required matching semantics.
  • Do not add an index merely on Posts.Tags for this predicate; it will not make the leading-wildcard expression seekable.
  • The existing IX_Posts_Question_TagScan may help filter question posts, but its 10.5 MB size, page compression, and 95 seeks do not solve the substring join.

Repeated fan-out aggregation should be decomposed

Many Query Store statements join Posts, Votes, Comments, and Badges before aggregating. This can multiply rows before COUNT(DISTINCT) and produce very high reads, writes, memory grants, and spills. Query hash 0D1DCC1B897CEF72 reports 42,503 spills and 594,368 KB maximum grant in plan cache. Query Store also reports examples with average durations from 147,742.26 ms to 303,112.89 ms.

  • Aggregate Votes, Comments, Badges, and PostHistory independently by their foreign-key column, then join the smaller aggregate results.
  • Keep the predicates on the bare columns. The supplied non-sargable-predicate analysis found no wrapped column predicates in the top 50 plan-cache statements, so no range rewrite is required for the reported date predicates.
  • Use Query Store to compare the rewritten statement against the original. No estimated execution plans were supplied, so a before/after operator-cost comparison cannot be stated.

Examples of statements benefiting from the recommended indexes

  • Query Store statements containing LEFT JOIN Comments c ON p.Id = c.PostId benefit from Comments(PostId).
  • Statements aggregating votes by user or joining v.UserId = u.Id benefit from Votes(UserId) INCLUDE (VoteTypeId, BountyAmount).
  • Statements containing ph.PostId, ph.PostHistoryTypeId, and date or close-history predicates benefit from the consolidated PostHistory indexes.

Constraints and statistics

Untrusted foreign keys

The input reports 17 enabled but untrusted foreign keys across Badges, Comments, PostHistory, PostLinks, Posts, Tags, and Votes. The conditional script does not alter data and skips any constraint for which a violating row is found. Validation requires scans and may be expensive on large tables.

Statistics settings

Auto-create statistics and auto-update statistics are both false. This can leave the optimizer without current single-column statistics for predicates and joins. Auto-update statistics async is also false. Enabling these settings is recommended. The input reports OPTIMIZED_SP_EXECUTESQL as OFF; it is intentionally not changed because the input contains no evidence of compilation storms.

Maintenance, drops, and columnstore assessment

  • No index drops: No duplicate or left-prefix overlaps were detected, and all read statistics cover only 3.9 days. No dependency conflicts were reported for the four checked nonclustered indexes.
  • No heap action: No large heaps or forwarded records were reported. The forwarded-record remediation rule is therefore not applicable.
  • No columnstore action: No large tables were reported. The largest explicitly listed table is dbo.Votes at 926,084 rows, below the 1,000,000-row consideration threshold. A columnstore recommendation would also need representative write volume; the Query Store write mix reports only 14 INSERT executions over the last 7 days and no UPDATE or DELETE statement count.
  • No filtered index: Skew exists in Badges.Class and PostLinks.LinkTypeId, but the input does not identify a frequent query filtering a rare literal. Posts.PostTypeId = 4, 5, or 6 are rare, but no specific repeated query and required output columns justify a filtered index. The common values 1 and 2 should not lead an unfiltered index.

SQL Server 2025 feature review

FeatureStatusFinding
FORCE_LAST_GOOD_PLANDesired ON; actual ONNo change required. Prefer this automatic plan correction when investigating regressions.
CE feedbackCan run; CE feedback and expression feedback ON; compatibility 160; Query Store READ_WRITENo feedback rows exist. It is working as designed, not a problem. Forcing a plan or adding a user query hint would stop CE feedback for that query.
DOP feedbackON and can run; compatibility 160; Query Store READ_WRITENo feedback rows exist. It applies only to repeating parallel queries and can lower DOP after verification. A MAXDOP query or Query Store hint would make that query ineligible for DOP feedback.
OPPONot active; compatibility level 160, needs 170No catch-all predicate was identified in the supplied text. Compatibility-level changes should be tested with Query Store; compatibility level should not be raised for OPPO alone.
Forced plan failuresNoneNo query_id, plan_id, failure count, or failure reason requires remediation.

Scripts

Conditionally re-trust the reported foreign keys

USE [SQLStorm];
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Badges AS c LEFT JOIN dbo.Users AS p ON p.Id = c.UserId WHERE c.UserId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Badges WITH CHECK CHECK CONSTRAINT [FK__Badges__UserId__6477ECF3];
ELSE PRINT N'Skipped FK__Badges__UserId__6477ECF3 because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Comments AS c LEFT JOIN dbo.Posts AS p ON p.Id = c.PostId WHERE c.PostId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Comments WITH CHECK CHECK CONSTRAINT [FK__Comments__PostId__4CA06362];
ELSE PRINT N'Skipped FK__Comments__PostId__4CA06362 because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Comments AS c LEFT JOIN dbo.Users AS p ON p.Id = c.UserId WHERE c.UserId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Comments WITH CHECK CHECK CONSTRAINT [FK__Comments__UserId__4D94879B];
ELSE PRINT N'Skipped FK__Comments__UserId__4D94879B because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.PostHistory AS c LEFT JOIN dbo.PostHistoryTypes AS p ON p.Id = c.PostHistoryTypeId WHERE c.PostHistoryTypeId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.PostHistory WITH CHECK CHECK CONSTRAINT [FK__PostHisto__PostH__5070F446];
ELSE PRINT N'Skipped FK__PostHisto__PostH__5070F446 because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.PostHistory AS c LEFT JOIN dbo.Posts AS p ON p.Id = c.PostId WHERE c.PostId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.PostHistory WITH CHECK CHECK CONSTRAINT [FK__PostHisto__PostI__5165187F];
ELSE PRINT N'Skipped FK__PostHisto__PostI__5165187F because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.PostHistory AS c LEFT JOIN dbo.Users AS p ON p.Id = c.UserId WHERE c.UserId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.PostHistory WITH CHECK CHECK CONSTRAINT [FK__PostHisto__UserI__52593CB8];
ELSE PRINT N'Skipped FK__PostHisto__UserI__52593CB8 because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.PostLinks AS c LEFT JOIN dbo.LinkTypes AS p ON p.Id = c.LinkTypeId WHERE c.LinkTypeId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.PostLinks WITH CHECK CHECK CONSTRAINT [FK__PostLinks__LinkT__571DF1D5];
ELSE PRINT N'Skipped FK__PostLinks__LinkT__571DF1D5 because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.PostLinks AS c LEFT JOIN dbo.Posts AS p ON p.Id = c.PostId WHERE c.PostId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.PostLinks WITH CHECK CHECK CONSTRAINT [FK__PostLinks__PostI__5535A963];
ELSE PRINT N'Skipped FK__PostLinks__PostI__5535A963 because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.PostLinks AS c LEFT JOIN dbo.Posts AS p ON p.Id = c.RelatedPostId WHERE c.RelatedPostId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.PostLinks WITH CHECK CHECK CONSTRAINT [FK__PostLinks__Relat__5629CD9C];
ELSE PRINT N'Skipped FK__PostLinks__Relat__5629CD9C because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Posts AS c LEFT JOIN dbo.Posts AS p ON p.Id = c.AcceptedAnswerId WHERE c.AcceptedAnswerId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Posts WITH CHECK CHECK CONSTRAINT [FK__Posts__AcceptedA__48CFD27E];
ELSE PRINT N'Skipped FK__Posts__AcceptedA__48CFD27E because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Posts AS c LEFT JOIN dbo.Users AS p ON p.Id = c.LastEditorUserId WHERE c.LastEditorUserId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Posts WITH CHECK CHECK CONSTRAINT [FK__Posts__LastEdito__47DBAE45];
ELSE PRINT N'Skipped FK__Posts__LastEdito__47DBAE45 because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Posts AS c LEFT JOIN dbo.Users AS p ON p.Id = c.OwnerUserId WHERE c.OwnerUserId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Posts WITH CHECK CHECK CONSTRAINT [FK__Posts__OwnerUser__46E78A0C];
ELSE PRINT N'Skipped FK__Posts__OwnerUser__46E78A0C because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Posts AS c LEFT JOIN dbo.Posts AS p ON p.Id = c.ParentId WHERE c.ParentId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Posts WITH CHECK CHECK CONSTRAINT [FK__Posts__ParentId__49C3F6B7];
ELSE PRINT N'Skipped FK__Posts__ParentId__49C3F6B7 because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Posts AS c LEFT JOIN dbo.PostTypes AS p ON p.Id = c.PostTypeId WHERE c.PostTypeId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Posts WITH CHECK CHECK CONSTRAINT [FK__Posts__PostTypeI__45F365D3];
ELSE PRINT N'Skipped FK__Posts__PostTypeI__45F365D3 because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Tags AS c LEFT JOIN dbo.Posts AS p ON p.Id = c.ExcerptPostId WHERE c.ExcerptPostId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Tags WITH CHECK CHECK CONSTRAINT [FK__Tags__ExcerptPos__59FA5E80];
ELSE PRINT N'Skipped FK__Tags__ExcerptPos__59FA5E80 because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Tags AS c LEFT JOIN dbo.Posts AS p ON p.Id = c.WikiPostId WHERE c.WikiPostId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Tags WITH CHECK CHECK CONSTRAINT [FK__Tags__WikiPostId__5AEE82B9];
ELSE PRINT N'Skipped FK__Tags__WikiPostId__5AEE82B9 because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Votes AS c LEFT JOIN dbo.Users AS p ON p.Id = c.UserId WHERE c.UserId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Votes WITH CHECK CHECK CONSTRAINT [FK__Votes__UserId__5EBF139D];
ELSE PRINT N'Skipped FK__Votes__UserId__5EBF139D because violating rows exist.';
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Votes AS c LEFT JOIN dbo.VoteTypes AS p ON p.Id = c.VoteTypeId WHERE c.VoteTypeId IS NOT NULL AND p.Id IS NULL)
    ALTER TABLE dbo.Votes WITH CHECK CHECK CONSTRAINT [FK__Votes__VoteTypeI__5DCAEF64];
ELSE PRINT N'Skipped FK__Votes__VoteTypeI__5DCAEF64 because violating rows exist.';
GO

Create the consolidated PostHistory indexes

USE [SQLStorm];
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.PostHistory') AND name = N'IX_PostHistory_PostId_CreationDate_Type')
    CREATE NONCLUSTERED INDEX [IX_PostHistory_PostId_CreationDate_Type]
    ON dbo.PostHistory ([PostId], [CreationDate], [PostHistoryTypeId])
    INCLUDE ([Comment])
    WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.PostHistory') AND name = N'IX_PostHistory_CreationDate_UserId')
    CREATE NONCLUSTERED INDEX [IX_PostHistory_CreationDate_UserId]
    ON dbo.PostHistory ([CreationDate], [UserId])
    INCLUDE ([Comment])
    WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.PostHistory') AND name = N'IX_PostHistory_Type_PostId_CreationDate')
    CREATE NONCLUSTERED INDEX [IX_PostHistory_Type_PostId_CreationDate]
    ON dbo.PostHistory ([PostHistoryTypeId], [PostId], [CreationDate])
    INCLUDE ([Comment])
    WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
GO

Create the Votes user aggregation index

USE [SQLStorm];
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.Votes') AND name = N'IX_Votes_UserId_Cover')
    CREATE NONCLUSTERED INDEX [IX_Votes_UserId_Cover]
    ON dbo.Votes ([UserId])
    INCLUDE ([VoteTypeId], [BountyAmount])
    WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
GO

Create the Comments PostId join index

USE [SQLStorm];
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.Comments') AND name = N'IX_Comments_PostId')
    CREATE NONCLUSTERED INDEX [IX_Comments_PostId]
    ON dbo.Comments ([PostId])
    WITH (DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
GO

Enable automatic statistics creation

USE [SQLStorm];
GO
ALTER DATABASE [SQLStorm] SET AUTO_CREATE_STATISTICS ON;
GO

Enable automatic statistics updates

USE [SQLStorm];
GO
ALTER DATABASE [SQLStorm] SET AUTO_UPDATE_STATISTICS ON;
GO

Enable asynchronous automatic statistics updates

USE [SQLStorm];
GO
ALTER DATABASE [SQLStorm] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
GO

Allow low-priority waiting for asynchronous statistics updates

USE [SQLStorm];
GO
ALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON;
GO

Supporting scripts (not part of the change)

No supporting diagnostic, rollback, monitoring, or measurement scripts are included. The report input did not provide object definitions or estimated plans needed to produce a safe, database-specific query rewrite script.