AI SQL Tuner Recommendations

Executive summary

1. Highest priority: add dbo.Comments(PostId) and dbo.Votes(PostId, VoteTypeId) indexes. The dominant workload repeatedly joins and aggregates comments and votes by PostId, while current usage shows heavy clustered scans on both tables:
   • dbo.Comments clustered index: 702 scans, no seek-supporting PostId index present.
   • dbo.Votes clustered index: 1435 scans, many queries count votes by PostId and VoteTypeId.
2. High priority: add dbo.Badges(UserId) INCLUDE(Class). Missing index data is strong, UserId is highly selective in Badges, and plan cache / Query Store show frequent badge aggregations per user.
3. Medium priority: add dbo.Votes(UserId, VoteTypeId) INCLUDE(BountyAmount) for user-centric vote and bounty rollups. This is useful, but lower priority than the PostId-based Votes index because Votes.UserId cardinality is relatively low in the provided stats.
4. Low priority: consider dropping dbo.PostLinks.IX_PostLinks_RelatedPostId if the observation window is representative. It has 0 seeks, 0 scans, 0 lookups.
5. Code optimization is required: the biggest Query Store regressions are driven by query shape, not just missing indexes. Repeated patterns such as Posts.Tags LIKE '%' + TagName + '%', correlated scalar subqueries against Votes/Comments, and many-way joins before aggregation are causing massive logical reads and extreme durations.
6. No rebuild emergency: page density is excellent across the sampled indexes (98.3% to 99.9%) and fragmentation is negligible. No index rebuild recommendation is justified from the supplied page-density data.
7. Automatic tuning status: FORCE_LAST_GOOD_PLAN desired state is ON and actual state is ON. No corrective action needed.
8. Forced plan failures: none detected.

Detailed prioritized recommendations

Platform and automatic tuning review

• Version/edition suitability: SQL Server 2022 Enterprise capabilities fully support ONLINE = ON, PAGE compression, and advanced indexing options used below.
• Automatic tuning: FORCE_LAST_GOOD_PLAN desired state ON, actual state ON. No action required.
• Forced plan failures: none detected; no unforce action required.
• Constraints impact: no recommendation requires dropping or recreating any primary key or foreign key.

Implementation scripts

Create 1: dbo.Comments(PostId)

USE [SQLStorm];
GO

CREATE INDEX [IX_Comments_PostId]
ON [dbo].[Comments] ([PostId])
INCLUDE ([Id], [CreationDate], [Score])
WITH (
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE,
    FILLFACTOR = 100
);
GO

Create 2: dbo.Votes(PostId, VoteTypeId)

USE [SQLStorm];
GO

CREATE INDEX [IX_Votes_PostId_VoteTypeId]
ON [dbo].[Votes] ([PostId], [VoteTypeId])
INCLUDE ([UserId], [BountyAmount], [CreationDate])
WITH (
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE,
    FILLFACTOR = 100
);
GO

Create 3: dbo.Badges(UserId) INCLUDE(Class)

USE [SQLStorm];
GO

CREATE INDEX [IX_Badges_UserId]
ON [dbo].[Badges] ([UserId])
INCLUDE ([Class])
WITH (
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE,
    FILLFACTOR = 100
);
GO

Create 4: dbo.Votes(UserId, VoteTypeId) INCLUDE(BountyAmount)

USE [SQLStorm];
GO

CREATE INDEX [IX_Votes_UserId_VoteTypeId]
ON [dbo].[Votes] ([UserId], [VoteTypeId])
INCLUDE ([BountyAmount])
WITH (
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE,
    FILLFACTOR = 100
);
GO

Alternative smaller filtered index for user bounty workload

USE [SQLStorm];
GO

CREATE INDEX [IX_Votes_UserId_Bounty_VT8]
ON [dbo].[Votes] ([UserId])
INCLUDE ([BountyAmount], [CreationDate])
WHERE [VoteTypeId] = 8 AND [UserId] IS NOT NULL
WITH (
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE,
    FILLFACTOR = 100
);
GO

Optional drop: unused dbo.PostLinks index

USE [SQLStorm];
GO

DROP INDEX [IX_PostLinks_RelatedPostId]
ON [dbo].[PostLinks];
GO

Statistics refresh after index deployment

USE [SQLStorm];
GO

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

Query rewrite pattern: replace correlated subqueries with pre-aggregation

/* Pattern example */

WITH VoteAgg AS
(
    SELECT
        v.PostId,
        SUM(CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE 0 END) AS UpVotes,
        SUM(CASE WHEN v.VoteTypeId = 3 THEN 1 ELSE 0 END) AS DownVotes,
        SUM(CASE WHEN v.VoteTypeId = 8 THEN ISNULL(v.BountyAmount,0) ELSE 0 END) AS TotalBounty
    FROM dbo.Votes AS v
    GROUP BY v.PostId
),
CommentAgg AS
(
    SELECT
        c.PostId,
        COUNT(*) AS CommentCount
    FROM dbo.Comments AS c
    GROUP BY c.PostId
)
SELECT
    p.Id,
    p.Title,
    ISNULL(va.UpVotes,0) AS UpVotes,
    ISNULL(va.DownVotes,0) AS DownVotes,
    ISNULL(va.TotalBounty,0) AS TotalBounty,
    ISNULL(ca.CommentCount,0) AS CommentCount
FROM dbo.Posts AS p
LEFT JOIN VoteAgg AS va
    ON va.PostId = p.Id
LEFT JOIN CommentAgg AS ca
    ON ca.PostId = p.Id
WHERE p.CreationDate >= DATEADD(YEAR, -1, @AsOfDate);

Schema pattern: normalize tags to eliminate LIKE '%tag%'

/* Suggested model */

CREATE TABLE dbo.PostTags
(
    PostId int NOT NULL,
    TagId  int NOT NULL,
    CONSTRAINT PK_PostTags PRIMARY KEY CLUSTERED (PostId, TagId),
    CONSTRAINT FK_PostTags_Posts FOREIGN KEY (PostId) REFERENCES dbo.Posts(Id),
    CONSTRAINT FK_PostTags_Tags  FOREIGN KEY (TagId)  REFERENCES dbo.Tags(Id)
);
GO

CREATE INDEX IX_PostTags_TagId_PostId
ON dbo.PostTags(TagId, PostId)
WITH (
    ONLINE = ON,
    SORT_IN_TEMPDB = ON,
    DATA_COMPRESSION = PAGE,
    FILLFACTOR = 100
);
GO