AI SQL Tuner Recommendations

• Why: Matches missing index with extremely high cost/impact and aligns with cached query pattern: PostHistory joined by PostId with PostHistoryTypeId IN (...), plus “latest by CreationDate”.
• Overhead: Adds storage + maintenance on PostHistory writes; justified given scan-heavy access and high estimated benefit. Keep keys minimal.
• Design: Key (PostId, PostHistoryTypeId, CreationDate DESC); INCLUDE (UserId, Comment) to cover “latest edit comment” query without lookups.

CREATE NONCLUSTERED INDEX IX_PostHistory_PostId_Type_CreationDate
ON dbo.PostHistory (PostId, PostHistoryTypeId, CreationDate DESC)
INCLUDE (UserId, Comment)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);

• Why: Plan cache includes: LEFT JOIN PostHistory PH ON T.UserId = PH.UserId AND PH.CreationDate = (SELECT MAX(... ) WHERE UserId = ...).
• Design: Key (UserId, CreationDate DESC) INCLUDE (Comment, PostId, PostHistoryTypeId).
• Overhead: Second index on same table; justified because this pattern is different from PostId-based access and otherwise forces scans/sorts.

CREATE NONCLUSTERED INDEX IX_PostHistory_UserId_CreationDate
ON dbo.PostHistory (UserId, CreationDate DESC)
INCLUDE (PostId, PostHistoryTypeId, Comment)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);

• Why: It overlaps poorly with the actual “MAX(CreationDate) per UserId/PostId” usage; the two indexes above more directly target the patterns and avoid a third partially redundant structure.

• Why: Missing index recommendation + clustered index shows 558 scans and 0 seeks. Cached queries frequently do:
  • WHERE V.CreationDate >= ... GROUP BY V.UserId
  • SUM(CASE WHEN VoteTypeId = 2/3 THEN ...) grouped by user
• Design: Key (UserId, VoteTypeId) INCLUDE (BountyAmount, PostId, CreationDate) to help both “recent votes” and joins from Votes to Posts.

CREATE NONCLUSTERED INDEX IX_Votes_UserId_VoteTypeId
ON dbo.Votes (UserId, VoteTypeId)
INCLUDE (BountyAmount, PostId, CreationDate)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);

• Why: Multiple queries aggregate votes per post and filter by VoteTypeId (2,3,8) and join on V.PostId.
• Design: Key (PostId, VoteTypeId) INCLUDE (UserId, BountyAmount).
• Overhead: Adds a second Votes index; justified because it supports a different leading key than (UserId,...). If write-heavy in reality, evaluate keeping only one based on workload; given provided scans, both are likely beneficial.

CREATE NONCLUSTERED INDEX IX_Votes_PostId_VoteTypeId
ON dbo.Votes (PostId, VoteTypeId)
INCLUDE (UserId, BountyAmount)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);

• Why: Several queries compute average/total bounty with VoteTypeId = 8. A filtered index can be much smaller and faster if VoteTypeId=8 is rare.
• Tradeoff: Only helps VoteTypeId=8 queries; keep only if those are important.

CREATE NONCLUSTERED INDEX IX_Votes_Bounties_PostId
ON dbo.Votes (PostId)
INCLUDE (BountyAmount, UserId, CreationDate)
WHERE VoteTypeId = 8
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);

• Why: Many cached queries group/filter by OwnerUserId and CreationDate (last 30 days / last year), and then sort by Score or aggregate ViewCount.
• Design: Key (OwnerUserId, CreationDate); INCLUDE (Id, Score, ViewCount, PostTypeId, AcceptedAnswerId, Title) to reduce lookups.

CREATE NONCLUSTERED INDEX IX_Posts_OwnerUserId_CreationDate
ON dbo.Posts (OwnerUserId, CreationDate)
INCLUDE (Id, PostTypeId, Score, ViewCount, AcceptedAnswerId, Title, LastActivityDate)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);

• Why: Queries filter by PostTypeId and CreationDate and order/rank by Score/ViewCount.
• Design: Key (PostTypeId, CreationDate); INCLUDE (Id, Score, ViewCount, Title, OwnerUserId).

CREATE NONCLUSTERED INDEX IX_Posts_PostTypeId_CreationDate
ON dbo.Posts (PostTypeId, CreationDate)
INCLUDE (Id, Score, ViewCount, Title, OwnerUserId)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);

• Why: Cached queries reference AcceptedAnswerId and there are FKs on ParentId and AcceptedAnswerId. Without supporting indexes, SQL Server often scans Posts for these joins.
• Design: Two narrow indexes.

CREATE NONCLUSTERED INDEX IX_Posts_AcceptedAnswerId
ON dbo.Posts (AcceptedAnswerId)
INCLUDE (Id, OwnerUserId, PostTypeId, CreationDate)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);

CREATE NONCLUSTERED INDEX IX_Posts_ParentId
ON dbo.Posts (ParentId)
INCLUDE (Id, OwnerUserId, PostTypeId, CreationDate)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);

• Why: Cached queries filter Users.Reputation > 1000 and order by Reputation DESC with OFFSET/FETCH. You already have IX_Users_Reputation</code with strong seek usage (135 seeks), so this is only recommended if that index does not include needed columns and plans show key lookups.
• Design: If lookups occur, replace/extend with INCLUDE to cover common projections (DisplayName, CreationDate).

/* If IX_Users_Reputation exists but causes key lookups, consider:
   (Use DROP/CREATE only if you confirm it's non-unique and safe to replace) */
CREATE NONCLUSTERED INDEX IX_Users_Reputation_Cover
ON dbo.Users (Reputation DESC)
INCLUDE (Id, DisplayName, CreationDate)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);