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

Executive summary

Top priorities

1. Create consolidated indexes on PostHistory, Votes, Badges, Comments, and Posts. These tables exceed 200,000 rows, appear throughout the most expensive queries, and currently have only clustered primary-key indexes.
2. Rewrite multi-child aggregates. Queries joining Posts, Votes, Comments, Badges, and PostHistory before grouping generate millions of intermediate rows, excessive memory grants up to approximately 2.3 GB, and spills as high as 737,146.
3. Restore trust on all 18 untrusted foreign keys. This improves relational metadata available for join elimination and cardinality reasoning, after orphan validation and during a maintenance window.
4. Refresh statistics after deployment and measure by query hash. Cardinality data for Votes.UserId is notably skewed: approximately 929 distinct users across 926,084 rows.

Overall confidence: 91% for the first-wave indexes, 82% for the second-wave indexes, and 96% that query rewrites will produce a larger improvement than indexes alone.

Detailed prioritized recommendations

1. Create a consolidated PostHistory access path

   Priority 1 Risk: Low Confidence: 96%

   Create IX_PostHistory_PostId_CreationDate on (PostId, CreationDate DESC), including PostHistoryTypeId, UserId, and Comment.

   • PostId has approximately 246,673 distinct values across 847,593 rows and is highly selective for individual posts.
   • The missing-index recommendation estimates 99.63% impact over 30 observations.
   • The second key supports latest-history queries using partitioning or ordering by CreationDate DESC.
   • The consolidated design replaces the need for separate recommendations on PostId alone and PostId, CreationDate.
   • Estimated storage is approximately 39 MB before compression, modest relative to the 820.3 MB clustered index.

   Benefiting workload examples: query hashes 5B44F69546E00566, 2614331B5F50B494, 09E88EFBFD73A882, and queries calculating recent close or edit history per post.

2. Create complementary Votes indexes for post and user aggregation

   Priority 1 Risk: Low Confidence: 95%

   Create one index beginning with PostId and one beginning with UserId, both including VoteTypeId and BountyAmount.

   • PostId has approximately 242,634 distinct values and generated 198 missing-index observations.
   • The UserId recommendations report 97.72%–99.73% estimated impact. Although only about 929 distinct non-null values are estimated, the index enables ordered aggregation and avoids repeatedly scanning 926,084 rows.
   • Separate leading keys are justified because neither index can efficiently satisfy both join directions.
   • The broader included-column design consolidates the duplicate missing-index suggestions for UserId.
   • Expected combined uncompressed footprint is roughly 50 MB; PAGE compression should reduce this.

   Benefiting workload examples: query hashes 563E43D720E73157, 0EAB7945FE874674, 368446D439EFFAFF, 5B44F69546E00566, and D3F91D01D1CAE60B.

3. Consolidate all Badges(UserId) recommendations into one index

   Priority 1 Risk: Low Confidence: 95%

   Create IX_Badges_UserId on UserId, including Class and Name.

   • UserId has approximately 154,016 distinct values across 439,352 rows.
   • Three missing-index entries share the same key and report estimated impacts from 75.84% to 98.36%.
   • One covering index is preferable to three overlapping indexes.
   • Class has only two estimated values and should remain an included column rather than a leading key.

   Benefiting workload examples: query hashes 0FA8B0C9F3268C12, 8F85C246DC0A4F1F, 6394F2FCD1F2AD39, and CAA832839D207CAE.

4. Add Comments(PostId) and Posts(OwnerUserId, CreationDate) indexes

   Priority 1 Risk: Low Confidence: 92%

   • Comments.PostId has approximately 122,541 distinct values, appears in 327 missing-index observations, and supports nearly every per-post comment count.
   • The clustered key Comments.Id is automatically available in the nonclustered index, so a separate include is unnecessary for COUNT(Id).
   • Posts.OwnerUserId has approximately 65,118 distinct values and is central to user-level reporting.
   • Adding CreationDate DESC supports per-user recent-post ranking. Included numeric attributes reduce lookups without copying wide Title, Body, or Tags columns.

   Benefiting workload examples: query hashes 8E0DB20F8C836D8C, D3F91D01D1CAE60B, 6394F2FCD1F2AD39, 84669A8D2DB48FF8, and 92BF2F60806E286F.

5. Add second-wave indexes for Comments(UserId) and Users(Reputation)

   Priority 2 Risk: Medium Confidence: 82%

   Deploy these only after first-wave validation demonstrates that the workload remains read-heavy.

   • Comments.UserId is not in the supplied missing-index list, but many of the longest Query Store statements aggregate comments directly by user. It has approximately 41,822 distinct values.
   • Users.Reputation DESC supports frequent filters and ranking. Its missing-index impact is only 8.10%, so it is intentionally deferred.
   • The Users index includes only DisplayName and CreationDate, avoiding wide user profile columns.
   • Recorded user updates are zero and Query Store reports no recent write mix, but these counters may have been reset. Validate real write rates before retaining second-wave indexes.

6. Do not rebuild, reorganize, drop, or replace existing indexes

   Priority 2 Risk: Low Confidence: 99%

   • Page density ranges from 98.29% to 99.85%, comfortably above the 75% concern threshold.
   • Fragmentation ranges from 0.00% to 0.33%, so rebuilds would consume resources without measurable benefit.
   • No exact duplicates or left-prefix overlaps were detected.
   • All existing indexes enforce primary keys; none should be dropped.
   • Keep fill factor at 100 for the proposed indexes because the supplied workload is read-oriented and there is no page-split evidence.

7. Do not deploy columnstore at the current scale

   Priority 3 Risk: Low Confidence: 88%

   The largest supplied row count is Votes at 926,084 rows, below the one-million-row threshold for active consideration. The workload is analytical, so a nonclustered columnstore could eventually help broad scans, but adding one now would duplicate storage and complicate maintenance before the row-expansion problem is corrected. Reassess if Votes or PostHistory exceeds one million rows and scan-heavy reports remain dominant.

Recommended index design

Filtered-index assessment

A filtered Votes index for bounty vote types 8 and 9 could be useful because many reports use that predicate. It is not recommended in the initial deployment because the general Votes(PostId) and Votes(UserId) indexes already cover those rows, the exact distribution by vote type was not supplied, and an additional filtered index would create overlapping maintenance. Reassess only if validation shows bounty queries remain expensive and types 8/9 represent a small fraction of Votes.

Query code optimization

Constraint health

Finding: all 18 reported foreign keys are enabled but untrusted. Untrusted constraints continue to validate new modifications but do not prove that all existing rows comply. This prevents the optimizer from safely using the relationships for some join-elimination and predicate-pruning transformations.

Priority: 2. Expected risk: Medium due to validation scans and possible blocking. Confidence: 99% that trust should be restored after data compliance is confirmed.

Maintenance and statistics

Page density and fragmentation

Statistics

• Keep automatic statistics creation and updates enabled; both are already ON.
• Refresh statistics with FULLSCAN on the six main workload tables after index deployment and constraint validation.
• The refresh is especially important for skewed columns such as Votes.UserId, VoteTypeId, BountyAmount, and PostHistoryTypeId.
• Do not drop existing statistics. No redundant or clearly obsolete statistics were reported.

Compression and fill factor

Retain PAGE compression on the large clustered indexes and use PAGE compression for the proposed nonclustered indexes. Use fill factor 100 because no write pressure, page splits, or latch waits were observed. Revisit fill factor only if future operational statistics show sustained random inserts and page-split pressure.

Query Store and automatic tuning

Forced-plan failures

No Query Store forced-plan failures were detected.

• No affected query_id or plan_id exists in the supplied data.
• No failure classification or forced-plan remediation is required.
• Runtime percentile metrics p50/p95/p99 and compile-time timelines were not included, so validation should capture them after deployment.

Automatic Plan Correction

The Query Store export contains internally inconsistent labels for several execution and time fields, including values where “last 7d executions” resemble elapsed seconds. Use direct Query Store validation queries before treating those fields as execution counts. The consistent findings are that many statements run for minutes, consume substantial CPU and reads, and use only one captured plan.

Version posture

The server reports SQL Server 2022 RTM-GDR build 16.0.1190.2. This report does not identify a forced-plan failure or symptom that directly maps to a known engine defect. Nevertheless, RTM-family deployments should be compared with the current supported SQL Server 2022 CU/GDR servicing level before production rollout, particularly when investigating memory-grant feedback, Query Store, or plan-forcing anomalies.

Safe implementation playbook

1. Capture a baseline, deploy first-wave indexes, and update statistics.
   • Rationale: these indexes have the strongest missing-index and workload support.
   • Risk: Low; temporary CPU, transaction-log, tempdb, and I/O consumption during online builds.
   • Validation: confirm all six indexes are enabled, inspect build duration and log growth, and compare target query logical reads, duration, CPU, spills, and grants.
2. Rewrite one highest-cost query to aggregate child tables before joining.
   • Rationale: indexes cannot fully correct multiplicative join expansion.
   • Risk: Medium because aggregate semantics must remain identical.
   • Validation: compare complete result sets, row counts, null handling, totals, actual rows per operator, memory grants, spills, and Query Store p50/p95 duration.
3. Validate and re-trust foreign keys, then consider second-wave indexes.
   • Rationale: trusted constraints improve optimizer metadata; second-wave indexes should be retained only if measured reads justify their maintenance cost.
   • Risk: Medium due to table scans and blocking during validation.
   • Validation: verify zero orphan rows, is_not_trusted = 0, is_disabled = 0, and positive seek/scan activity on retained second-wave indexes.

Rollback criteria

• Drop a newly added index if representative query p95 duration or CPU regresses by more than 15% after statistics and plan stabilization.
• Drop or defer an index if its maintenance writes materially exceed useful reads over a representative workload window.
• Stop constraint validation if it causes unacceptable blocking or log/I/O pressure; resume in a maintenance window.
• Revert a query rewrite if result-set comparison shows any semantic difference.
• Do not disable Automatic Plan Correction as part of rollback.

Scripts

Execute first-wave creation separately from second-wave creation. All index builds use SQL Server 2022 Developer Edition capabilities: online operations, PAGE compression, and fill factor 100.

Create the six first-wave indexes for recommendations 1–4

USE [SQLStorm];
GO

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

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

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

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

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Badges')
      AND name = N'IX_Badges_UserId'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Badges_UserId]
    ON [dbo].[Badges] ([UserId])
    INCLUDE ([Class], [Name])
    WITH
    (
        ONLINE = ON,
        SORT_IN_TEMPDB = ON,
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 100
    );
END;
GO

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Comments')
      AND name = N'IX_Comments_PostId'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Comments_PostId]
    ON [dbo].[Comments] ([PostId])
    WITH
    (
        ONLINE = ON,
        SORT_IN_TEMPDB = ON,
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 100
    );
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'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Posts_OwnerUserId_CreationDate]
    ON [dbo].[Posts] ([OwnerUserId], [CreationDate] DESC)
    INCLUDE ([PostTypeId], [Score], [ViewCount], [AcceptedAnswerId])
    WITH
    (
        ONLINE = ON,
        SORT_IN_TEMPDB = ON,
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 100
    );
END;
GO

Create the two conditional second-wave indexes for recommendation 5

USE [SQLStorm];
GO

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

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

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.Users')
      AND name = N'IX_Users_Reputation'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Users_Reputation]
    ON [dbo].[Users] ([Reputation] DESC)
    INCLUDE ([DisplayName], [CreationDate])
    WITH
    (
        ONLINE = ON,
        SORT_IN_TEMPDB = ON,
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 100
    );
END;
GO

Refresh full-scan statistics after index deployment

USE [SQLStorm];
GO

SET NOCOUNT ON;
GO

UPDATE STATISTICS [dbo].[Votes] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[PostHistory] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Badges] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Comments] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Posts] WITH FULLSCAN;
GO
UPDATE STATISTICS [dbo].[Users] WITH FULLSCAN;
GO

Report orphan counts before restoring foreign-key trust

USE [SQLStorm];
GO

SET NOCOUNT ON;
GO

SELECT N'dbo.Badges.UserId' AS relationship_name, COUNT_BIG(*) AS orphan_count
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
UNION ALL
SELECT N'dbo.Comments.PostId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.Comments.UserId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.PostHistory.PostHistoryTypeId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.PostHistory.PostId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.PostHistory.UserId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.PostLinks.LinkTypeId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.PostLinks.PostId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.PostLinks.RelatedPostId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.Posts.AcceptedAnswerId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.Posts.LastEditorUserId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.Posts.OwnerUserId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.Posts.ParentId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.Posts.PostTypeId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.Tags.ExcerptPostId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.Tags.WikiPostId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.Votes.UserId', COUNT_BIG(*)
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
UNION ALL
SELECT N'dbo.Votes.VoteTypeId', COUNT_BIG(*)
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
ORDER BY relationship_name;
GO

Restore trust on all compliant foreign keys for the constraint recommendation

USE [SQLStorm];
GO

ALTER TABLE [dbo].[Badges]
WITH CHECK CHECK CONSTRAINT [FK__Badges__UserId__6477ECF3];
GO

ALTER TABLE [dbo].[Comments]
WITH CHECK CHECK CONSTRAINT [FK__Comments__PostId__4CA06362];
GO
ALTER TABLE [dbo].[Comments]
WITH CHECK CHECK CONSTRAINT [FK__Comments__UserId__4D94879B];
GO

ALTER TABLE [dbo].[PostHistory]
WITH CHECK CHECK CONSTRAINT [FK__PostHisto__PostH__5070F446];
GO
ALTER TABLE [dbo].[PostHistory]
WITH CHECK CHECK CONSTRAINT [FK__PostHisto__PostI__5165187F];
GO
ALTER TABLE [dbo].[PostHistory]
WITH CHECK CHECK CONSTRAINT [FK__PostHisto__UserI__52593CB8];
GO

ALTER TABLE [dbo].[PostLinks]
WITH CHECK CHECK CONSTRAINT [FK__PostLinks__LinkT__571DF1D5];
GO
ALTER TABLE [dbo].[PostLinks]
WITH CHECK CHECK CONSTRAINT [FK__PostLinks__PostI__5535A963];
GO
ALTER TABLE [dbo].[PostLinks]
WITH CHECK CHECK CONSTRAINT [FK__PostLinks__Relat__5629CD9C];
GO

ALTER TABLE [dbo].[Posts]
WITH CHECK CHECK CONSTRAINT [FK__Posts__AcceptedA__48CFD27E];
GO
ALTER TABLE [dbo].[Posts]
WITH CHECK CHECK CONSTRAINT [FK__Posts__LastEdito__47DBAE45];
GO
ALTER TABLE [dbo].[Posts]
WITH CHECK CHECK CONSTRAINT [FK__Posts__OwnerUser__46E78A0C];
GO
ALTER TABLE [dbo].[Posts]
WITH CHECK CHECK CONSTRAINT [FK__Posts__ParentId__49C3F6B7];
GO
ALTER TABLE [dbo].[Posts]
WITH CHECK CHECK CONSTRAINT [FK__Posts__PostTypeI__45F365D3];
GO

ALTER TABLE [dbo].[Tags]
WITH CHECK CHECK CONSTRAINT [FK__Tags__ExcerptPos__59FA5E80];
GO
ALTER TABLE [dbo].[Tags]
WITH CHECK CHECK CONSTRAINT [FK__Tags__WikiPostId__5AEE82B9];
GO

ALTER TABLE [dbo].[Votes]
WITH CHECK CHECK CONSTRAINT [FK__Votes__UserId__5EBF139D];
GO
ALTER TABLE [dbo].[Votes]
WITH CHECK CHECK CONSTRAINT [FK__Votes__VoteTypeI__5DCAEF64];
GO

Validate index usage, size, density, constraints, and automatic tuning

USE [SQLStorm];
GO

SET NOCOUNT ON;
GO

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
    OBJECT_NAME(i.object_id) AS table_name,
    i.name AS index_name,
    i.is_disabled,
    i.fill_factor,
    COALESCE(us.user_seeks, 0) AS user_seeks,
    COALESCE(us.user_scans, 0) AS user_scans,
    COALESCE(us.user_lookups, 0) AS user_lookups,
    COALESCE(us.user_updates, 0) AS user_updates,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024.0 AS decimal(18,2)) AS reserved_mb
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON us.database_id = DB_ID()
   AND us.object_id = i.object_id
   AND us.index_id = i.index_id
LEFT JOIN sys.dm_db_partition_stats AS ps
    ON ps.object_id = i.object_id
   AND ps.index_id = i.index_id
WHERE i.name IN
(
    N'IX_PostHistory_PostId_CreationDate',
    N'IX_Votes_PostId',
    N'IX_Votes_UserId',
    N'IX_Badges_UserId',
    N'IX_Comments_PostId',
    N'IX_Posts_OwnerUserId_CreationDate',
    N'IX_Comments_UserId',
    N'IX_Users_Reputation'
)
GROUP BY
    i.object_id,
    i.name,
    i.is_disabled,
    i.fill_factor,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates
ORDER BY schema_name, table_name, index_name;
GO

SELECT
    OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.page_count,
    CAST(ips.avg_page_space_used_in_percent AS decimal(6,2)) AS page_density_percent,
    CAST(ips.avg_fragmentation_in_percent AS decimal(6,2)) AS fragmentation_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
    ON i.object_id = ips.object_id
   AND i.index_id = ips.index_id
WHERE i.name IN
(
    N'IX_PostHistory_PostId_CreationDate',
    N'IX_Votes_PostId',
    N'IX_Votes_UserId',
    N'IX_Badges_UserId',
    N'IX_Comments_PostId',
    N'IX_Posts_OwnerUserId_CreationDate',
    N'IX_Comments_UserId',
    N'IX_Users_Reputation'
)
  AND ips.index_level = 0
ORDER BY page_density_percent, fragmentation_percent DESC;
GO

SELECT
    OBJECT_SCHEMA_NAME(parent_object_id) AS schema_name,
    OBJECT_NAME(parent_object_id) AS table_name,
    name AS constraint_name,
    is_disabled,
    is_not_trusted
FROM sys.foreign_keys
WHERE is_disabled = 1
   OR is_not_trusted = 1
ORDER BY schema_name, table_name, constraint_name;
GO

SELECT
    name,
    desired_state_desc,
    actual_state_desc,
    reason_desc
FROM sys.database_automatic_tuning_options
WHERE name = N'FORCE_LAST_GOOD_PLAN';
GO

Inspect target plan-cache metrics by query hash after deployment

USE [SQLStorm];
GO

SET NOCOUNT ON;
GO

SELECT TOP (100)
    CONVERT(varchar(18), qs.query_hash, 1) AS query_hash,
    CONVERT(varchar(18), qs.query_plan_hash, 1) AS query_plan_hash,
    qs.execution_count,
    qs.total_worker_time / NULLIF(qs.execution_count, 0) AS avg_cpu_microseconds,
    qs.total_elapsed_time / NULLIF(qs.execution_count, 0) AS avg_elapsed_microseconds,
    qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS avg_logical_reads,
    qs.max_grant_kb,
    qs.max_spills,
    qs.last_execution_time,
    LEFT(st.text, 4000) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.query_hash IN
(
    0x563E43D720E73157,
    0x8E0DB20F8C836D8C,
    0x0FA8B0C9F3268C12,
    0x0EAB7945FE874674,
    0x368446D439EFFAFF,
    0x8F85C246DC0A4F1F,
    0x5B44F69546E00566,
    0xD3F91D01D1CAE60B
)
ORDER BY avg_elapsed_microseconds DESC;
GO

Rollback all indexes created by this report if validation criteria fail

USE [SQLStorm];
GO

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

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

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

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

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

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

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

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

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

Validation checklist

• Confirm all first-wave indexes exist, are enabled, and use PAGE compression.
• Run representative statements at least five times after cache and Query Store metrics stabilize.
• Compare median and p95 duration, CPU, logical reads, physical reads, memory grants, spills, and returned row counts.
• Confirm query hash 563E43D720E73157 no longer records extreme spills or multi-gigabyte grants.
• Verify first-wave indexes accumulate seeks or useful ordered scans; compare reads with user_updates.
• Retain second-wave indexes only when their measured reads justify their storage and update cost.
• Verify all foreign keys report is_not_trusted = 0 and is_disabled = 0.
• Confirm FORCE_LAST_GOOD_PLAN remains ON with desired and actual states aligned.
• Verify no query result changes after aggregate-before-join rewrites.
• Do not schedule index rebuilds while density remains above 75% and fragmentation remains negligible.