Index Tuning – RockyPC / SQLStorm / dbo.Votes – 2026-05-25

Executive summary

1. Create a nonclustered index on dbo.Votes(PostId, VoteTypeId) including BountyAmount. This is the highest-value change. It aligns with the most frequent missing-index pattern on PostId, matches many Query Store statements that aggregate votes by post, and supports both vote counts and bounty rollups without key lookups. Priority 1 Confidence: 94%
2. Optionally add a filtered nonclustered index on dbo.Votes(PostId) including BountyAmount for VoteTypeId IN (8,9) only if bounty-focused queries remain hot after Recommendation 1. This is narrower and cheaper than a broad index, but it should be considered a second-step optimization rather than a first deployment. Priority 2 Confidence: 76%
3. Do not prioritize a broad UserId-only index at this time. Missing-index DMVs suggest it, but UserId cardinality is low (about 929 distinct values over 926k rows), making selectivity poor. The usage signal is also weaker than the PostId pattern. Priority 3 Confidence: 82%
4. No rebuild/reorganize action is needed for the clustered primary key. Page density is 99.83% and fragmentation is only 0.33%, so maintenance would add work with little gain. Priority 4 Confidence: 98%
5. No columnstore recommendation for dbo.Votes. The table has about 926k rows, below the stated 1,000,000-row threshold, and the current evidence favors targeted rowstore indexing over columnstore for this object. Priority 5 Confidence: 89%

Scope and context

• Object-scoped index analysis is limited to [dbo].[Votes].
• Estimated row count from metadata: approximately 926,084 rows.
• Current indexes on dbo.Votes: only clustered primary key PK__Votes__3214EC07006169CE on Id.
• Clustered index size: 11.8 MB, compression: PAGE.
• Foreign keys present on UserId and VoteTypeId.
• Automatic tuning FORCE_LAST_GOOD_PLAN: desired ON, actual ON.
• Forced plan failures: none detected.

Votes table index overview

dbo.Votes  (~926,084 rows)
│
├─ Current
│  └─ PK__Votes__3214EC07006169CE
│     Type: CLUSTERED, Key: (Id)
│     Compression: PAGE
│     Reads: 804 scans, 0 seeks
│     Density: 99.83%, Fragmentation: 0.33%
│
├─ High-value access patterns seen
│  ├─ JOIN / GROUP BY Votes.PostId
│  ├─ SUM/COUNT by VoteTypeId per PostId
│  └─ SUM(BountyAmount) for VoteTypeId IN (8,9)
│
└─ Recommended additions
   ├─ IX_Votes_PostId_VoteTypeId_INC_BountyAmount
   │  Key: (PostId, VoteTypeId)
   │  Include: (BountyAmount)
   │  Purpose: vote counts + bounty rollups by post
   │
   └─ Optional: IX_Votes_PostId_BountyAmount_VT_8_9
      Key: (PostId)
      Include: (BountyAmount)
      Filter: VoteTypeId IN (8,9)
      Purpose: narrow support for bounty-only post queries

Detailed prioritized recommendations

1. Create IX_Votes_PostId_VoteTypeId_INC_BountyAmount

Priority 1 Confidence: 94%

• Why: This is the best match to both the missing-index evidence and the visible workload. PostId is high-cardinality and highly selective, while VoteTypeId is a common grouping/filtering attribute in the top Query Store statements.
• Benefit: Reduces clustered scans on Votes, improves join efficiency from Posts to Votes, and allows aggregation of upvotes/downvotes/bounties per post from a much narrower structure.
• Why include BountyAmount: Many expensive queries sum BountyAmount after joining on PostId and filtering on bounty vote types. Including it avoids lookups.
• Overhead: Adds one write-maintained rowstore index. Given the table currently has only the clustered PK and no captured update pressure, the tradeoff is favorable.
• Compression/fill factor: Keep PAGE compression to align with the existing clustered index and use fill factor 100 because current density is excellent and there is no evidence of split pressure.

2. Consider a filtered bounty index only if post-bounty queries remain expensive after Recommendation 1

Priority 2 Confidence: 76%

• Why second: The broader (PostId, VoteTypeId) index will already help many bounty queries. A filtered index is worthwhile only if bounty-specific reporting remains a hotspot.
• Pattern addressed: Queries with predicates like V.VoteTypeId IN (8,9) joined on V.PostId = P.Id and aggregating SUM(V.BountyAmount).
• Benefit: Smaller than a full-table index, lower maintenance cost, good for rare bounty rows.
• Risk: Moderate redundancy with Recommendation 1. If both indexes are created, validate that both are actually used; otherwise remove the less useful one.

3. Do not create a general-purpose UserId-only nonclustered index yet

Priority 3 Confidence: 82%

• Why not now: UserId has estimated distinct values of only 929 over about 926k rows, which is low selectivity.
• Missing-index caveat: DMV suggestions on UserId exist, but they are not automatically actionable. The stronger signal is the much higher PostId recommendation count and better cardinality profile.
• Maintenance consideration: A low-selectivity index can consume space and write budget while still being bypassed for large joins or aggregates.
• Exception: If the application later shows frequent selective predicates such as WHERE V.UserId = @UserId returning small row counts with high execution frequency, revisit this.

4. Keep the current clustered primary key as-is

Priority 4 Confidence: 98%

• No evidence supports dropping or recreating the PK.
• Physical health is excellent: page density 99.83%, fragmentation 0.33%.
• No foreign-key-driven PK change is needed.

5. No columnstore recommendation on this table

Priority 5 Confidence: 89%

• The table is below the stated 1,000,000-row threshold for columnstore consideration.
• The visible bottleneck is lack of targeted rowstore access paths, not a broad analytic compression/scan design issue on this object.
• A nonclustered or clustered columnstore here would likely be heavier than necessary for the current scope.

6. Statistics settings are acceptable; no immediate stats-only change recommended

Priority 6 Confidence: 87%

• AUTO_CREATE_STATISTICS = ON and AUTO_UPDATE_STATISTICS = ON are appropriate.
• AUTO_UPDATE_STATISTICS_ASYNC = OFF is acceptable for this scope.
• Creating the new index will also create supporting index statistics automatically.

Query patterns driving recommendations

The following statements from Query Store are the clearest beneficiaries of the recommended PostId-based index strategy:

• Vote counts by post:
  ... LEFT JOIN Votes vb ON p.Id = vb.PostId ... SUM(CASE WHEN vb.VoteTypeId = 2 THEN 1 ELSE 0 END) ... SUM(CASE WHEN vb.VoteTypeId = 3 THEN 1 ELSE 0 END) ...
  Why it benefits: seeks/ordered reads on (PostId, VoteTypeId) replace broad clustered scans.
• Bounty by post:
  ... LEFT JOIN Votes v ON p.Id = v.PostId AND v.VoteTypeId IN (8, 9) ... SUM(v.BountyAmount) ...
  Why it benefits: the proposed index covers both the join key and the aggregation columns.
• Grouped vote summaries:
  SELECT PostId, COUNT(CASE WHEN VoteTypeId = 2 THEN 1 END), COUNT(CASE WHEN VoteTypeId = 3 THEN 1 END) FROM Votes GROUP BY PostId
  Why it benefits: reduces sort/hash burden by reading rows pre-organized on PostId and then VoteTypeId.

Changes not recommended

• No drop of existing indexes: there is only the clustered PK, and it is required.
• No PK redesign: no evidence justifies dropping/recreating the primary key or any referencing foreign keys.
• No fragmentation maintenance: current page density and fragmentation are already excellent.
• No broad UserId rowstore index right now: low cardinality and weaker signal relative to PostId.
• No columnstore: row count and workload pattern do not justify it for this table.

Automatic tuning and forced plans

• FORCE_LAST_GOOD_PLAN: desired state ON, actual state ON. No corrective action needed.
• Query Store forced-plan failures: none detected.
• Immediate action: none required for forced plans; focus on the index changes above.

Scripts

Create the primary recommended index on dbo.Votes for PostId/VoteTypeId aggregation and bounty coverage (Recommendation 1)

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_INC_BountyAmount'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Votes_PostId_VoteTypeId_INC_BountyAmount]
    ON [dbo].[Votes] ([PostId], [VoteTypeId])
    INCLUDE ([BountyAmount])
    WITH
    (
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 100,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON
    );
END
GO

Create an optional filtered bounty index for VoteTypeId 8 and 9 by PostId if bounty-only queries remain hot after Recommendation 1 (Recommendation 2)

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_BountyAmount_VT_8_9'
)
BEGIN
    CREATE NONCLUSTERED INDEX [IX_Votes_PostId_BountyAmount_VT_8_9]
    ON [dbo].[Votes] ([PostId])
    INCLUDE ([BountyAmount])
    WHERE [VoteTypeId] IN (8, 9)
    WITH
    (
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 100,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON
    );
END
GO

Validate whether the new Votes indexes are being used and whether either index should be retained or removed after observation window (supports Recommendations 1 and 2)

USE [SQLStorm];
GO

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_updates,
    us.last_user_seek,
    us.last_user_scan,
    us.last_user_update
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
WHERE i.object_id = OBJECT_ID(N'[dbo].[Votes]')
ORDER BY i.index_id;
GO

Drop the optional filtered bounty index if it proves redundant after monitoring (rollback for Recommendation 2)

USE [SQLStorm];
GO

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

Drop the primary recommended PostId/VoteTypeId index if rollback is required (rollback for Recommendation 1)

USE [SQLStorm];
GO

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