Index Tuning Report – RockyPC / SQLStorm / dbo.Votes – 2026-05-12 03:04:35 UTC

Executive Summary

Environment & Scope

• Analyzed table: [dbo].[Votes]
• Observed row count from index physical stats: 926,084
• Current index footprint reviewed: clustered PK only
• Missing index DMV: no recommendations returned
• Query Store forced plan failures: none detected
• Automatic tuning: FORCE_LAST_GOOD_PLAN enabled and aligned with desired state

Detailed Prioritized Recommendations

1. Priority 1 — No index create recommendation for [dbo].[Votes]

   • Recommendation: Do not add nonclustered indexes now.
   • Why:
     • No missing index recommendations were produced.
     • Plan cache and Query Store do not show user queries filtering, joining, grouping, or ordering on dbo.Votes.
     • The current clustered PK has only 2 scans, 0 seeks, 0 lookups, and 0 updates; this is not evidence of unmet indexing demand.
     • Adding indexes on PostId, UserId, VoteTypeId, or composites purely from cardinality would be speculative and would increase write, storage, and maintenance cost.
   • Cardinality observations:
     • PostId is high cardinality (~242,634 distinct) and would be a strong candidate only if recurring seeks/joins on PostId appear.
     • CreationDate is moderately selective (~5,405 distinct).
     • UserId (~929 distinct) is modestly selective.
     • VoteTypeId (~13 distinct) and BountyAmount (~12 distinct) are low-cardinality standalone columns and are not good standalone index leaders.
   • Assessment: Wait for workload evidence before creating indexes.

2. Priority 2 — Keep the clustered primary key unchanged

   • Recommendation: Retain PK__Votes__3214EC07006169CE on Id exactly as implemented.
   • Why:
     • Compression is already optimal for this observed state: PAGE.
     • Physical health is excellent: 99.83% avg page density, 0.33% fragmentation.
     • No evidence of page split pressure or low page utilization.
     • No evidence supports changing fill factor from its effective current state.
   • Assessment: No rebuild/reorg needed now.

3. Priority 3 — Do not add foreign-key support indexes without workload evidence

   • Reviewed constraints:
     • FK__Votes__UserId__5EBF139D on UserId → dbo.Users(Id)
     • FK__Votes__VoteTypeI__5DCAEF64 on VoteTypeId → dbo.VoteTypes(Id)
   • Recommendation: Do not create FK helper indexes at this time.
   • Why:
     • There is no observed delete/update pressure on parent tables causing RI-check scans against dbo.Votes.
     • No query text in the provided workload shows joins on these columns.
     • VoteTypeId is low cardinality and would likely have weak standalone usefulness.
   • Conditional future candidate only if workload appears: (PostId, VoteTypeId) or (PostId) would be more promising than standalone VoteTypeId, based on higher combined distinctness.

4. Priority 4 — No columnstore recommendation

   • Recommendation: Do not add clustered or nonclustered columnstore.
   • Why:
     • Table size is 926,084 rows, below the stated threshold for considering columnstore.
     • No analytic aggregation workload on dbo.Votes was captured.
     • A columnstore index would add storage and DML complexity without demonstrated benefit.

5. Priority 5 — Low-priority statistics maintenance on dbo.Votes

   • Recommendation: Run targeted statistics refresh on dbo.Votes during a maintenance window if recent data changes were significant.
   • Why:
     • Auto Create Stats and Auto Update Stats are already enabled and appropriate.
     • No stale-stats symptom is directly visible in the supplied workload, but a large table with changing skew can still benefit from periodic manual refresh.
   • Recommendation: Keep AUTO_UPDATE_STATISTICS_ASYNC = OFF unless compile blocking due to stats refresh is confirmed elsewhere; nothing in this evidence set requires a change.

6. Priority 6 — No action required for forced plans or automatic tuning

   • Forced plan failures: none detected.
   • Automatic tuning: FORCE_LAST_GOOD_PLAN is ON and matches desired state.
   • Recommendation: No remediation needed.

Workload Evidence

Observed plan cache statements

• The top captured plan cache statements are metadata/reporting queries against DMVs and catalog views, not business workload against dbo.Votes.
• Examples include:
  • Query Hash 51643155D72AC80E — query against sys.dm_db_missing_index_%
  • Query Hash E90AC64A1205B68D — query reading sys.index_columns, sys.columns, and catalog metadata
  • Query Hash 21D4DDCF73AF10BE — environment introspection query using @@SERVERNAME and @@VERSION
• Conclusion: no captured statement can be tied to a new dbo.Votes index recommendation with confidence.

Query Store workload insights

• The highest-cost Query Store statements are also metadata discovery queries, for example:
  • SELECT db_id() AS database_id, o.[type] as ModuleType, COUNT_BIG(*) ... FROM sys.objects ...
  • SELECT TOP 50 ... FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ...
  • SELECT AVG(cells_per_object) ... FROM sys.spatial_index_tessellations ...
• No Query Store item in the supplied data shows a recurring predicate on dbo.Votes.
• Result: index creation on dbo.Votes would be unsupported by observed workload.

Index Health Review

• No drop recommendation: the clustered PK is required and healthy.
• No modify recommendation: there is no evidence supporting key changes, includes, compression changes, or fill factor changes.
• No rebuild recommendation: fragmentation and density are already excellent.

Statistics & Automatic Tuning

Statistics

• Auto Create Stats: True
• Auto Update Stats: True
• Auto Update Stats Async: False

No statistics drops or custom statistics creation are recommended from the supplied evidence. A targeted update of existing statistics on dbo.Votes is the only low-risk maintenance action worth considering.

Automatic tuning and plan forcing

• FORCE_LAST_GOOD_PLAN: Desired ON, Actual ON
• Forced plan failures: none

No corrective action is needed here.

Scripts

1. Recommended maintenance script — targeted statistics refresh on dbo.Votes

USE [SQLStorm];
GO

UPDATE STATISTICS [dbo].[Votes] WITH FULLSCAN;
GO

2. Optional verification script — confirm current index and stats health before any future changes

USE [SQLStorm];
GO

SELECT
    s.name AS SchemaName,
    o.name AS TableName,
    i.name AS IndexName,
    i.type_desc,
    i.fill_factor,
    i.is_padded,
    p.rows AS RowCount,
    ips.avg_fragmentation_in_percent,
    ips.avg_page_space_used_in_percent,
    ips.page_count
FROM sys.objects o
JOIN sys.schemas s
    ON o.schema_id = s.schema_id
JOIN sys.indexes i
    ON o.object_id = i.object_id
JOIN sys.partitions p
    ON i.object_id = p.object_id
   AND i.index_id = p.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), o.object_id, i.index_id, NULL, 'SAMPLED') ips
WHERE s.name = N'dbo'
  AND o.name = N'Votes'
  AND o.type = 'U';

SELECT
    st.name AS StatsName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats st
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp
WHERE st.object_id = OBJECT_ID(N'dbo.Votes')
ORDER BY st.name;
GO

3. Explicit no-op on index creation

No CREATE INDEX, DROP INDEX, or ALTER INDEX statement is recommended for dbo.Votes from the supplied evidence set.

Confidence Levels