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.
PostHistory suggestion.dbo.PostHistory.- 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
Commentsestimate 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. - 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.
- 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.
- Rewrite the tag search and reduce repeated fan-out aggregation. The costly query with hash
872C7773968C6AAFusesp.Tags LIKE '%' + t.TagName + '%', which cannot seek through a normal index onTags. A Posts index alone will not solve that predicate. - 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.
Environment and evidence
| Item | Reported value | Interpretation |
|---|---|---|
| Platform | SQL Server 2025, Enterprise Developer Edition, Enterprise engine | Online index operations, page compression, columnstore, partition switching, CE feedback, DOP feedback, and OPPO capabilities are available subject to database settings and compatibility level. |
| Version | 17.0.1145.4, RTM-GDR, KB5128492 | Scripts below use SQL Server 2025-compatible syntax. |
| Usage window | 3.9 days since 2026-11-05 09:45:22 local time | Low-use indexes must not be dropped on this evidence alone. Monthly or quarterly workload is not represented. |
| Query Store | READ_WRITE; workload sample includes executions during the last 7 days | Several 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 operations | All reported operational hotspots show zero inserts, updates, deletes, waits, and latch waits | This 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
| Action | Key/include design | Rows | Estimated size | Reason |
|---|---|---|---|---|
| Create | dbo.PostHistory(PostId, CreationDate, PostHistoryTypeId) INCLUDE (Comment) | 847,593 | 45.3 MB, supplied estimate | Consolidates several PostId-leading suggestions and supports history joins and type filters. |
| Create | dbo.PostHistory(CreationDate, UserId) INCLUDE (Comment) | 847,593 | 32.3 MB, supplied estimate | Supports the CreationDate/UserId candidate. |
| Create | dbo.PostHistory(PostHistoryTypeId, PostId, CreationDate) INCLUDE (Comment) | 847,593 | 32.3 MB, supplied estimate | Supports type-led history aggregation and preserves join/date columns. |
| Create | dbo.Votes(UserId) INCLUDE (VoteTypeId, BountyAmount) | 926,084 | 28.3 MB, supplied estimate | Supports user-level vote aggregation and bounty calculations. |
| Create | dbo.Comments(PostId) | 351,440 reported table rows | 5.4 MB supplied candidate estimate; exact final size uncertain | Supports 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
TagNameandPostId, or a full-text/search solution appropriate to the required matching semantics. - Do not add an index merely on
Posts.Tagsfor this predicate; it will not make the leading-wildcard expression seekable. - The existing
IX_Posts_Question_TagScanmay 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.PostIdbenefit fromComments(PostId). - Statements aggregating votes by user or joining
v.UserId = u.Idbenefit fromVotes(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.Votesat 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.ClassandPostLinks.LinkTypeId, but the input does not identify a frequent query filtering a rare literal.Posts.PostTypeId = 4,5, or6are 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
| Feature | Status | Finding |
|---|---|---|
| FORCE_LAST_GOOD_PLAN | Desired ON; actual ON | No change required. Prefer this automatic plan correction when investigating regressions. |
| CE feedback | Can run; CE feedback and expression feedback ON; compatibility 160; Query Store READ_WRITE | No 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 feedback | ON and can run; compatibility 160; Query Store READ_WRITE | No 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. |
| OPPO | Not active; compatibility level 160, needs 170 | No 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 failures | None | No 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.