Index Tuning

Index Tuning Report – RockyPC / SQLStormOltp – 2026-11-05

Tuning goal: Index Tuning

ServerRockyPC
DatabaseSQLStormOltp
EngineSQL Server 2025 RTM-GDR 17.0.1145.4
EditionEnterprise Developer / Enterprise engine
Evidence window6.7 hours, since 2026-11-05 09:45:22 local time

Executive summary

Overall assessment: The database has a generally effective rowstore layout for the observed workload, but the evidence window is only 6.7 hours. The highest-confidence changes are removing two provably redundant nonclustered indexes, adding a selective dbo.Votes(PostId, VoteTypeId) index for a repeated aggregation, and adding ordered covering access paths for frequently executed Posts and Comments queries.
  1. Remove redundant indexes. Drop dbo.Badges.IX_Badges_UserId and dbo.Comments.IX_Comments_PostId_2. The report marks both NarrowerIsRedundant=True; they enforce no constraint and have no listed dependencies. Estimated storage reclaimed is approximately 11.6 MB based on reported sizes of 6.1 MB and 5.5 MB.
  2. Add the Votes aggregation index. Create a nonclustered index on dbo.Votes(PostId, VoteTypeId). The overlap analysis identifies PostId as the candidate key on a 948,804-row table, and Query Store shows 82 executions of the related aggregation averaging 97.99 ms and 1,547 reads. Estimated size is 14.5–20 MB; the lower bound is the reported candidate estimate and the range allows for the additional VoteTypeId key.
  3. Improve ordered Posts access. Add (OwnerUserId, CreationDate DESC, Id DESC) with narrow includes for the 16,371-execution query averaging 0.56 ms and 319 reads. The existing IX_Posts_OwnerUserId does not provide the requested ordering or projected columns. A firm size is unavailable because the input does not provide the candidate's combined width; estimate 3.3–12 MB using the existing 3.3 MB single-key index as the lower reference and the added keys/includes as the uncertainty.
  4. Improve ordered Comments access. Add (PostId, CreationDate DESC, Id DESC) with narrow includes for the 32,771-execution query averaging 0.05 ms and 12 reads. Do not include the wide Text value; selective results can use key lookups. Estimated size is 5.5–15 MB, based on the reported 5.5 MB duplicate PostId index plus the added ordering key and narrow includes.
  5. Restore trusted foreign keys. All 18 listed foreign keys are untrusted. Trusted constraints enable optimizer join elimination and predicate pruning. The supplied script validates each relationship inline and skips, with a printed message, any constraint whose existing data violates the relationship.

Confidence: High for redundant-index removal and untrusted-constraint findings; Confidence: Medium for new index benefit and size estimates because plan cache is empty and the statistics window is short.

Scope and evidence

  • Index usage, missing-index data, operational statistics, and plan cache cover only 6.7 hours since the latest database startup. Low-use index drops are therefore not recommended solely because of low reads.
  • No missing-index recommendations were reported. This does not override the workload evidence: the Votes overlap signal and Query Store statements justify targeted indexes not present in the missing-index report.
  • No plan cache rows and no non-sargable predicates were reported for this database.
  • No large tables were reported by the large-table section, so no columnstore index is recommended. The largest reported tables are still substantial—dbo.PostHistory has 847,593 rows and dbo.Votes has 948,804 rows—but they are below the stated large-table/columnstore threshold of more than 1,000,000 rows.

Detailed prioritized recommendations

1. Drop redundant Badges and Comments indexes

Action: Drop dbo.Badges.IX_Badges_UserId and dbo.Comments.IX_Comments_PostId_2. The wider Badges index has keys UserId, Date DESC and includes Class, Name, covering the narrower UserId index. The Comments indexes have exact duplicate keys on PostId; retain IX_Comments_PostId, which recorded 32,800 seeks, and remove the duplicate, which recorded zero reads.

Write overhead: Both Votes indexes each recorded 6,560 updates, while the duplicate Comments index recorded 4,920 updates. Removing redundant structures reduces future maintenance work. The drop is recommended despite the short observation window because redundancy is structural, not a low-use inference.

Estimated storage: Reclaim approximately 11.6 MB total: 6.1 MB for Badges and 5.5 MB for Comments. Actual reclaimed space depends on allocation and deallocation behavior.

2. Add the Votes aggregation index

The Query Store statement SELECT v.VoteTypeId, COUNT_BIG(*) ... WHERE v.PostId = @PostId GROUP BY v.VoteTypeId ran 82 times in the last 7 days, averaging 97.99 ms and 1,547 reads. The existing indexes on UserId and VoteTypeId do not lead with PostId. Use PostId, VoteTypeId so the equality predicate is seekable and the grouping column is ordered within each post.

The table has 948,804 rows and the new index adds write work to a table with 6,560 observed updates. The overlap report estimates 14.5 MB for a PostId candidate with an estimated write penalty of 6,560; including VoteTypeId makes a reasonable estimate 14.5–20 MB.

3. Add an ordered Posts index for OwnerUserId queries

The repeated statement selecting the latest 20 posts by owner executed 16,371 times, averaging 0.56 ms and 319 reads. Create (OwnerUserId, CreationDate DESC, Id DESC) and include Title, PostTypeId. This supports the equality predicate, requested ordering, and narrow projection without copying wide columns.

The existing IX_Posts_OwnerUserId has 16,523 seeks and no observed writes in the short window. Build the new index before dropping the old one; the change script then removes the narrower structure after the wider one exists. Estimated new size is 3.3–12 MB; the input supplies the existing 3.3 MB size but not a candidate size or complete row-width estimate.

4. Add an ordered Comments index

The latest-comments statement executed 32,771 times, averaging 0.05 ms and 12 reads. Create (PostId, CreationDate DESC, Id DESC) and include Score, UserId. The wide Text column is intentionally excluded. For the few rows returned by TOP (20), a key lookup is preferable to storing the wide value in every index row.

The index is estimated at 5.5–15 MB. The lower reference is the reported 5.5 MB duplicate PostId index; the range reflects the additional ordering keys and narrow included columns. The existing used IX_Comments_PostId is replaced only after the new index is created.

5. Re-trust the foreign keys after inline validation

All 18 listed foreign keys are marked NotTrusted=True and are enabled. Untrusted foreign keys prevent the optimizer from safely applying join elimination and related predicate pruning. The script uses an existence check for violating rows before each ALTER TABLE ... WITH CHECK CHECK CONSTRAINT; constraints with violations are not re-trusted and are explicitly printed.

Validation can require scans and may be expensive on the larger tables, including dbo.PostHistory with 847,593 rows and dbo.Votes with 948,804 rows.

6. Enable automatic and asynchronous statistics maintenance

AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS, and asynchronous statistics updates are all reported as disabled. Enabling automatic creation and updating is recommended because the workload contains selective predicates and the database is eligible for CE feedback. Each setting is provided as a separate batch.

The input reports OPTIMIZED_SP_EXECUTESQL as OFF; no change is recommended because this input contains no evidence of compilation storms. The conditional recommendation for low-priority asynchronous statistics waits does not apply because the required reported combination includes optimized stored procedure execution being ON.

7. Maintain current platform feedback configuration

  • CE feedback: Available and active for this database: CE feedback is ON, compatibility level is 160, and Query Store is READ_WRITE. The five NO_RECOMMENDATION rows are normal outcomes, not failures. CE feedback for expressions is ON. Do not force plans or add query hints to these queries without recognizing that such actions can prevent CE feedback from adjusting estimates.
  • DOP feedback: ON and available at compatibility level 160 with Query Store READ_WRITE. No DOP feedback rows are present. A forced Query Store plan would not stop DOP feedback, but a MAXDOP query or Query Store hint would make that statement ineligible. No MAXDOP change is justified by the supplied data.
  • OPPO: Not active because compatibility level is 160 and OPPO requires level 170. No catch-all query requiring an OPPO-specific rewrite was identified in the supplied workload. Do not raise compatibility solely for OPPO; if compatibility is changed for broader reasons, test the workload and use Query Store to detect regressions.
  • Automatic tuning: FORCE_LAST_GOOD_PLAN is already ON and its desired and actual states match. No change is required. No forced plan failures were reported.

8. Page density and compression assessment

No index has average page density below the approximately 75% threshold. The lowest reported value is 93.51% for the clustered Posts primary key, with 11.80% fragmentation and 3,321 pages. Rebuilds or fill-factor changes are not justified by the supplied density evidence. The Badges ordered index has 1,359 page latch waits over 1,098 ms and 439,352 inserts, but its page density is 99.22%; reducing fill factor without longer-term evidence would add space and I/O without a demonstrated benefit.

Index usage findings

ObjectObserved readsObserved writesFinding
dbo.Votes.IX_Votes_UserId0 seeks, 0 scans, 0 lookups6,560 updatesDo not drop solely for low use because the window is 6.7 hours; not structurally redundant in the report.
dbo.Votes.IX_Votes_VoteTypeId0 seeks, 0 scans, 0 lookups6,560 updatesRetain pending broader workload evidence; the new PostId-led index addresses the reported query.
dbo.PostHistory.IX_PostHistory_UserId, PostId, PostHistoryTypeId0 reads each0 updatesDo not drop now; usage covers only 6.7 hours and monthly or periodic work may not have run.
dbo.Badges.IX_Badges_UserId_Date12,300 seeks0 updates in usage stats; 439,352 inserts in operational statsRetain. It supports the active Badges query and has a useful ordered key.
dbo.Posts clustered PK78,187 seeks, 41,000 lookups19,680 updatesRetain. It is heavily used and enforces the primary key.

Low-use nonredundant indexes are not dropped because the evidence window is shorter than one week. Exact duplicates and indexes explicitly marked structurally redundant are the exception and are removed in the change script.

Workload and platform findings

AreaEvidenceAssessment
Missing indexesNone reportedTargeted workload indexes are still warranted where Query Store and overlap evidence identify a clear access pattern.
Non-sargabilityNo affected predicates in the top 50 database plan-cache statementsNo query rewrite required from this section.
Plan cacheNo database plan-cache rowsPlan-cache-based recommendations and examples are unavailable; Query Store is the primary workload source.
ColumnstoreNo large tables reported; writes include 19,593 updates and 11,633 inserts in the last 7 daysNo columnstore recommendation. Analytic benefit is not demonstrated, while write activity would add delta-store and delete-bit overhead.
CompressionSeveral clustered indexes use PAGE compression; new nonclustered candidates are estimated without compression dataUse PAGE compression only after testing CPU and maintenance impact; scripts retain default compression for the new OLTP indexes.

The Query Store workload includes high-frequency point lookups and writes, so narrowly targeted rowstore indexes are preferred over broad covering indexes. Wide columns such as Posts Body and Comments Text are not copied into the new indexes.

Storage impact

ChangeRow countEstimated size or reclaimBasis and assumptions
Drop dbo.Badges.IX_Badges_UserId439,352−6.1 MBReported existing index size.
Drop dbo.Comments.IX_Comments_PostId_2368,479 index records reported−5.5 MBReported existing index size.
Create dbo.Votes(PostId, VoteTypeId)948,804+14.5–20 MB14.5 MB reported candidate estimate for PostId, widened for the added VoteTypeId key.
Create dbo.Posts(OwnerUserId, CreationDate, Id)246,672+3.3–12 MBExisting OwnerUserId index is 3.3 MB; input lacks complete candidate width.
Create dbo.Comments(PostId, CreationDate, Id)419,600 clustered rows reported+5.5–15 MB5.5 MB existing PostId index used as lower reference; Text is excluded.

Net storage change: approximately +7.2 to +30.4 MB. This range adds 14.5–20 MB, 3.3–12 MB, and 5.5–15 MB, then subtracts 11.6 MB reclaimed by the two redundant-index drops. The range is an estimate because candidate row widths and final page allocation were not supplied.

Scripts

1. Create targeted indexes and remove the two structurally redundant indexes

USE [SQLStormOltp];
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], [VoteTypeId])
    WITH (SORT_IN_TEMPDB = ON, ONLINE = ON);
END;
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Posts')
      AND name = N'IX_Posts_OwnerUserId_CreationDate_Id'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Posts_OwnerUserId_CreationDate_Id]
    ON [dbo].[Posts] ([OwnerUserId], [CreationDate] DESC, [Id] DESC)
    INCLUDE ([Title], [PostTypeId])
    WITH (SORT_IN_TEMPDB = ON, ONLINE = ON);
END;
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Comments')
      AND name = N'IX_Comments_PostId_CreationDate_Id'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Comments_PostId_CreationDate_Id]
    ON [dbo].[Comments] ([PostId], [CreationDate] DESC, [Id] DESC)
    INCLUDE ([Score], [UserId])
    WITH (SORT_IN_TEMPDB = ON, ONLINE = ON);
END;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Badges')
      AND name = N'IX_Badges_UserId'
)
BEGIN
    DROP INDEX [IX_Badges_UserId] ON [dbo].[Badges];
END;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Comments')
      AND name = N'IX_Comments_PostId_2'
)
BEGIN
    DROP INDEX [IX_Comments_PostId_2] ON [dbo].[Comments];
END;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Posts')
      AND name = N'IX_Posts_OwnerUserId'
)
AND NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Posts')
      AND name = N'IX_Posts_OwnerUserId_CreationDate_Id'
)
BEGIN
    DROP INDEX [IX_Posts_OwnerUserId] ON [dbo].[Posts];
END;
GO

IF EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Comments')
      AND name = N'IX_Comments_PostId'
)
AND EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Comments')
      AND name = N'IX_Comments_PostId_CreationDate_Id'
)
BEGIN
    DROP INDEX [IX_Comments_PostId] ON [dbo].[Comments];
END;
GO

2. Enable automatic statistics creation

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

3. Enable automatic synchronous statistics updates

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

4. Enable asynchronous statistics updates

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

5. Re-trust each foreign key only when no violating rows exist

USE [SQLStormOltp];
GO

IF NOT EXISTS (SELECT 1 FROM dbo.Badges AS c LEFT JOIN dbo.Users AS p ON p.Id = c.UserId WHERE p.Id IS NULL AND c.UserId IS NOT NULL)
    ALTER TABLE dbo.Badges WITH CHECK CHECK CONSTRAINT [FK__Badges__UserId__6477ECF3];
ELSE
    PRINT N'FK__Badges__UserId__6477ECF3 skipped 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 p.Id IS NULL AND c.PostId IS NOT NULL)
    ALTER TABLE dbo.Comments WITH CHECK CHECK CONSTRAINT [FK__Comments__PostId__4CA06362];
ELSE
    PRINT N'FK__Comments__PostId__4CA06362 skipped 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 p.Id IS NULL AND c.UserId IS NOT NULL)
    ALTER TABLE dbo.Comments WITH CHECK CHECK CONSTRAINT [FK__Comments__UserId__4D94879B];
ELSE
    PRINT N'FK__Comments__UserId__4D94879B skipped 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 p.Id IS NULL AND c.PostHistoryTypeId IS NOT NULL)
    ALTER TABLE dbo.PostHistory WITH CHECK CHECK CONSTRAINT [FK__PostHisto__PostH__5070F446];
ELSE
    PRINT N'FK__PostHisto__PostH__5070F446 skipped 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 p.Id IS NULL AND c.PostId IS NOT NULL)
    ALTER TABLE dbo.PostHistory WITH CHECK CHECK CONSTRAINT [FK__PostHisto__PostI__5165187F];
ELSE
    PRINT N'FK__PostHisto__PostI__5165187F skipped 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 p.Id IS NULL AND c.UserId IS NOT NULL)
    ALTER TABLE dbo.PostHistory WITH CHECK CHECK CONSTRAINT [FK__PostHisto__UserI__52593CB8];
ELSE
    PRINT N'FK__PostHisto__UserI__52593CB8 skipped 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 p.Id IS NULL AND c.LinkTypeId IS NOT NULL)
    ALTER TABLE dbo.PostLinks WITH CHECK CHECK CONSTRAINT [FK__PostLinks__LinkT__571DF1D5];
ELSE
    PRINT N'FK__PostLinks__LinkT__571DF1D5 skipped 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 p.Id IS NULL AND c.PostId IS NOT NULL)
    ALTER TABLE dbo.PostLinks WITH CHECK CHECK CONSTRAINT [FK__PostLinks__PostI__5535A963];
ELSE
    PRINT N'FK__PostLinks__PostI__5535A963 skipped 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 p.Id IS NULL AND c.RelatedPostId IS NOT NULL)
    ALTER TABLE dbo.PostLinks WITH CHECK CHECK CONSTRAINT [FK__PostLinks__Relat__5629CD9C];
ELSE
    PRINT N'FK__PostLinks__Relat__5629CD9C skipped 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 p.Id IS NULL AND c.AcceptedAnswerId IS NOT NULL)
    ALTER TABLE dbo.Posts WITH CHECK CHECK CONSTRAINT [FK__Posts__AcceptedA__48CFD27E];
ELSE
    PRINT N'FK__Posts__AcceptedA__48CFD27E skipped 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 p.Id IS NULL AND c.LastEditorUserId IS NOT NULL)
    ALTER TABLE dbo.Posts WITH CHECK CHECK CONSTRAINT [FK__Posts__LastEdito__47DBAE45];
ELSE
    PRINT N'FK__Posts__LastEdito__47DBAE45 skipped 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 p.Id IS NULL AND c.OwnerUserId IS NOT NULL)
    ALTER TABLE dbo.Posts WITH CHECK CHECK CONSTRAINT [FK__Posts__OwnerUser__46E78A0C];
ELSE
    PRINT N'FK__Posts__OwnerUser__46E78A0C skipped 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 p.Id IS NULL AND c.ParentId IS NOT NULL)
    ALTER TABLE dbo.Posts WITH CHECK CHECK CONSTRAINT [FK__Posts__ParentId__49C3F6B7];
ELSE
    PRINT N'FK__Posts__ParentId__49C3F6B7 skipped 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 p.Id IS NULL AND c.PostTypeId IS NOT NULL)
    ALTER TABLE dbo.Posts WITH CHECK CHECK CONSTRAINT [FK__Posts__PostTypeI__45F365D3];
ELSE
    PRINT N'FK__Posts__PostTypeI__45F365D3 skipped 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 p.Id IS NULL AND c.ExcerptPostId IS NOT NULL)
    ALTER TABLE dbo.Tags WITH CHECK CHECK CONSTRAINT [FK__Tags__ExcerptPos__59FA5E80];
ELSE
    PRINT N'FK__Tags__ExcerptPos__59FA5E80 skipped 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 p.Id IS NULL AND c.WikiPostId IS NOT NULL)
    ALTER TABLE dbo.Tags WITH CHECK CHECK CONSTRAINT [FK__Tags__WikiPostId__5AEE82B9];
ELSE
    PRINT N'FK__Tags__WikiPostId__5AEE82B9 skipped 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 p.Id IS NULL AND c.UserId IS NOT NULL)
    ALTER TABLE dbo.Votes WITH CHECK CHECK CONSTRAINT [FK__Votes__UserId__5EBF139D];
ELSE
    PRINT N'FK__Votes__UserId__5EBF139D skipped 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 p.Id IS NULL AND c.VoteTypeId IS NOT NULL)
    ALTER TABLE dbo.Votes WITH CHECK CHECK CONSTRAINT [FK__Votes__VoteTypeI__5DCAEF64];
ELSE
    PRINT N'FK__Votes__VoteTypeI__5DCAEF64 skipped because violating rows exist.';
GO

Supporting scripts (not part of the change)

No supporting diagnostic or rollback scripts are included. The requested output is limited to prioritized change scripts.