SQL Server Optimization Suite

Comprehensive SQL Server index optimization toolkit. All objects in the indexing schema in DBA_Utilities. Seven tools for finding what's missing, removing what's redundant, and diagnosing what the optimizer doesn't tell you.

Tools

Duplicate Index

Detects duplicate, redundant, and combinable indexes. Generates DROP and COMBINE scripts.

Key Lookup Elimination

Finds key lookups in the plan cache and recommends INCLUDE columns to eliminate them.

Unused Index Detection

Identifies indexes with zero reads and active writes. DISABLE or DROP with confidence levels.

Missing Index

DMV-based recommendations with multi-stage consolidation. Detects extend/rebuild opportunities.

Expensive Query

Plan cache analysis for table scans, missing index hints, and query regressions.

Column Access Patterns

Discovers frequently filtered/joined/sorted columns for composite index opportunities.

Query Plan Analysis

One-off diagnostic: stale stats, missing indexes, implicit conversions, cardinality issues.

sp_helpindexUsage

Enhanced index info combining structure, usage stats, and sizes in one result set.

Suite

Quick Start

-- Run ALL optimization tools in one call
EXEC indexing.sp_IndexOptimizationSuite @DatabaseName = 'MyApp_%', @RunnableUnder = '1,5e';

-- Combined output with linked server support
EXEC indexing.sp_IndexOptimizationSuite @DatabaseName = 'MyApp_%', @RunnableUnder = '1,5e',
    @CombinedOutput = 1, @LinkedServer = 1;

-- Diagnose a specific slow query
EXEC indexing.sp_AnalyzeQueryPlan @SQL = 'SELECT ...', @DatabaseName = 'MyDatabase';

-- Fleet-wide summary
EXEC indexing.sp_IndexingSuiteSummary @Days = 7, @Detail = 1;

See Quick Start for the full parameter reference.

Maintenance

Index Maintenance

Automated defragmentation with edition-aware REORG/REBUILD, ONLINE fallback, time limits, and full history logging.

Statistics Maintenance

Stale statistics detection and targeted updates based on modification rates and table size.

Reference