AI SQL Tuner Recommendations

Executive Summary

1. Create a nonclustered index on dbo.Users(Reputation) (highest priority). Missing-index signal is strong (high cost/impact), and workload repeatedly ranks/filters users by reputation.
2. Add a general Votes(PostId, VoteTypeId) index to reduce repeated clustered scans on dbo.Votes during joins/aggregations from Posts.
3. Add a filtered Votes(UserId, VoteTypeId) index for vote types (2,3,8,9) to accelerate the most common user scoring/bounty aggregations while keeping index size modest.
4. Address the dominant non-SARGable pattern: Posts.Tags LIKE '%' + Tags.TagName + '%'. This is a major CPU/read driver; index-only tuning cannot fully solve it. Normalize tags or materialize a split-tag bridge table.
5. Drop unused index dbo.PostLinks.IX_PostLinks_RelatedPostId (optional) after validation window; currently zero reads.

• No table exceeds 1,000,000 rows in supplied data; columnstore is not recommended at this time.
• User updates are effectively 0 in provided usage stats, so added nonclustered index write overhead appears low in this captured window.

Detailed Prioritized Recommendations

1. Index: dbo.Users(Reputation DESC, Id) with includes

   • Why: Missing index recommendation on Users.Reputation with very high estimated cost and impact; Users clustered index has heavy scans (2409).
   • Query patterns helped: frequent ROW_NUMBER()/RANK() OVER (ORDER BY u.Reputation DESC) and filters like WHERE u.Reputation > 0 or > 1000.
   • Example workload statements: Query hashes 2E567D0C93084FF3, BBA5F673A22F221C, 29216C0CE50B44B8.
   • Confidence: 0.95 (High)

2. Index: dbo.Votes(PostId, VoteTypeId) INCLUDE (UserId, BountyAmount, CreationDate)

   • Why: Votes clustered index shows very high scans (1396). Many heavy queries join Posts p to Votes v ON p.Id = v.PostId and aggregate by vote type.
   • Current indexes are partial: one key starts with UserId, one filtered index on (PostId,VoteTypeId) may not cover full vote-type usage.
   • Example workload statements: Query hashes 4B3350A3994051A4, DE0B35870AFD3E7B, 23EF7978955152DC.
   • Confidence: 0.88 (High)

3. Filtered index: dbo.Votes(UserId, VoteTypeId, PostId) for VoteTypeId IN (2,3,8,9)

   • Why: These vote types dominate user reputation/upvote/downvote/bounty analytics in Query Store text. Filtered index reduces pages and memory grants for common aggregates.
   • Example workload statements: Query hashes E8BFCC27A6442112, EF08FAF0DCA6B94D, 211312-series UserActivity/UserStats patterns.
   • Overhead: low-to-moderate; narrower than full unfiltered alternatives.
   • Confidence: 0.82 (High-Moderate)

4. Optional cleanup: drop unused dbo.PostLinks.IX_PostLinks_RelatedPostId

   • Why: 0 seeks / 0 scans / 0 lookups in captured period.
   • Caution: retain if periodic/ETL workloads use it outside capture window.
   • Confidence: 0.63 (Moderate)

5. Do not add indexes to tiny reference tables

   • Tables like LinkTypes, PostTypes, VoteTypes, CloseReasonTypes are tiny and mostly scanned; additional indexes are unnecessary.
   • Confidence: 0.97 (High)

Implementation Scripts

1) Create Users reputation index

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

2) Create general Votes PostId/VoteTypeId 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_PostId_VoteTypeId_All'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_Votes_PostId_VoteTypeId_All
    ON dbo.Votes (PostId, VoteTypeId)
    INCLUDE (UserId, BountyAmount, CreationDate)
    WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);
END
GO

3) Create filtered Votes UserId/VoteTypeId index (common analytic vote types)

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_VoteTypeId_239_Filtered'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_Votes_UserId_VoteTypeId_239_Filtered
    ON dbo.Votes (UserId, VoteTypeId, PostId)
    INCLUDE (BountyAmount, CreationDate)
    WHERE VoteTypeId IN (2,3,8,9)
    WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 95);
END
GO

4) Optional: drop unused PostLinks index

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

Statistics Recommendations

• Keep AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS enabled (already correct).
• Run targeted stats refresh after index changes and for high-read tables (Users, Posts, Votes, Comments, PostHistory).
• Given heavy analytic query CPU/memory pressure, consider FULLSCAN on key stats during maintenance windows.

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

Query Code Changes (High Impact)

• Replace repeated non-SARGable pattern:

  JOIN Posts p ON p.Tags LIKE '%' + t.TagName + '%'

  with a normalized bridge table (e.g., PostTags(PostId, TagName)) populated from Posts.Tags, then:

  JOIN PostTags pt ON pt.PostId = p.Id
  JOIN Tags t ON t.TagName = pt.TagName

• Pre-aggregate Votes and Comments in temp tables/CTEs before joining to Users to reduce row explosion from multiple LEFT JOINs.
• For Query Store items showing memory spills (e.g., plan hash 485B3C5C5D020582), use staged aggregation and avoid wide intermediate rowsets.