Column Access Pattern Analysis

The discovery tool in the suite. While the other tools react to what SQL Server tells you — missing index DMVs, unused indexes, expensive scans — this tool proactively analyzes the workload to find what indexes should exist based on how the data is actually accessed.

How It Differs from Missing Index DMVs

Data Flow

sp_ColumnAccessPatternRecommendations(@RunAnalysis = 1)
  |
  +--> sp_ColumnAccessPatternAnalysis
  |      1. Collect top N plans by total reads from plan cache
  |      2. Extract predicate columns from scan/seek ops via XQuery
  |      3. Extract columns from missing index hints (equality/inequality/include)
  |      4. Filter to target databases
  |      5. Aggregate by database + table + column + usage type
  |         - Count distinct queries referencing each column
  |         - Sum total reads/executions
  |      6. Check existing index coverage per column (per-database loop)
  |         - LEADING_KEY, KEY, INCLUDED, or NULL (not indexed)
  |      7. Set suggested action based on coverage gap
  |      -> writes indexing.ColumnAccessPatternResults
  |
  +--> Build consolidated per-table recommendations:
  |      - Aggregate key columns (EQUALITY/INEQUALITY/SEEK/SCAN predicates)
  |      - Aggregate include columns
  |      - Check existing indexes for near-matches (EXTEND vs CREATE)
  |      - Generate CREATE INDEX or DROP_EXISTING for EXTEND
  |      - Estimate size via sp_GetHybridIndexSizeEstimate
  |
  +--> Generate ScriptText output grouped by database and table

Column Classification

The analysis extracts column references from the plan cache XML using XQuery, categorizing each by how the query uses it:

Columns used as EQUALITY or SEEK_PREDICATE across many queries become key columns. Columns used as INCLUDE stay as includes. The tool orders keys by frequency — the most commonly filtered column becomes the leading key.

Existing Index Coverage Check

Before recommending anything, each discovered column is checked against the server's existing indexes:

By default, columns with LEADING_KEY coverage are hidden from output. Use @ShowCovered = 1 to see the full picture.

Near-Match Detection & Consolidation

When multiple columns on the same table need indexing, the tool consolidates them into a single recommendation rather than suggesting separate indexes for each column. It then checks existing indexes for near-matches:

• EXTEND: An existing index has the right leading key(s) but is missing some columns. The tool generates CREATE INDEX ... WITH (DROP_EXISTING = ON) to atomically replace it with a wider version.
• CREATE: No existing index is close enough. A new index is generated with a proper name following the IX_Table_Keys[_INCLUDES] convention.

This prevents index proliferation — instead of 5 new indexes on one table, you might get 1 new index and 2 extensions of existing ones.

Results Table

indexing.ColumnAccessPatternResults — one row per database + table + column + usage type:

Parameters

sp_ColumnAccessPatternRecommendations (reporting)

sp_ColumnAccessPatternAnalysis (engine)

Script Output Features

• Column access summary: Top 10 most-accessed columns per table with coverage tags
• Consolidated recommendation: Key columns + includes derived from workload patterns
• Near-match detection: EXTEND existing index with DROP_EXISTING = ON, or CREATE new
• Estimated size: Via hybrid size estimator (displayed in MB)
• Enterprise detection: WITH (ONLINE = ON) on Enterprise/Developer editions
• @RunnableUnder: Large indexes wrapped in /* */ with edition-aware thresholds
• ResolvedDate tracking: UPDATE statement per table for history
• Smart filtering: Tables with no actionable recommendations are hidden; empty database headers are suppressed

Usage Examples

-- Default: analyze top 200 plans, show columns in 3+ queries
EXEC indexing.sp_ColumnAccessPatternRecommendations @RunAnalysis = 1;

-- Broader analysis: top 1000 plans across all databases
EXEC indexing.sp_ColumnAccessPatternRecommendations
    @RunAnalysis = 1,
    @DatabaseName = 'MyApp_%',
    @RunnableUnder = '1,5e',
    @TopN = 1000;

-- Stricter: only columns in 10+ queries (high-confidence patterns)
EXEC indexing.sp_ColumnAccessPatternRecommendations
    @RunAnalysis = 1,
    @MinQueryCount = 10;

-- Show everything including well-indexed columns (audit view)
EXEC indexing.sp_ColumnAccessPatternRecommendations
    @RunAnalysis = 1,
    @ShowCovered = 1;

-- Focus on a specific table
EXEC indexing.sp_ColumnAccessPatternRecommendations
    @TableName = 'audit_trail';

-- View raw analysis data
SELECT DatabaseName, TableName, ColumnName, UsageType,
       QueryCount, TotalLogicalReads, ExistingIndexCoverage
FROM indexing.ColumnAccessPatternResults
WHERE ResolvedDate IS NULL
ORDER BY TotalLogicalReads DESC;