Quick Start & Parameter Reference

Run the Full Suite

EXEC DBA_Utilities.indexing.sp_IndexOptimizationSuite
    -- Common Parameters
    @DatabaseName = 'MyApp_%'         -- NULL = all user databases. Supports CSV list and LIKE wildcards. Passed to every tool.
    ,@RunnableUnder = '1,5e'          -- NULL = all uncommented. '2' = 2GB both editions. '1,5e' = 1GB Standard / 5GB Enterprise.
    --,@LinkedServer = 1              -- When 1, wraps output script in EXEC(...) AT [server] so you can run it from ops via linked server.
    -- Tool Toggles (all default to 1)
    ,@RunDuplicateIndex = 1           -- Duplicate/redundant index detection (sp_DuplicateIndexOptimizationRecommendations)
    ,@RunKeyLookup = 1                -- Key lookup elimination (sp_KeyLookupRecommendations)
    ,@RunUnusedIndex = 1              -- Unused index detection (sp_UnusedIndexRecommendations)
    ,@RunOptimizeIndex = 1            -- Missing index recommendations (sp_CreateIndexOptimizationRecommendations)
    ,@RunExpensiveQuery = 1           -- Expensive query plan analysis (sp_ExpensiveQueryRecommendations)
    ,@RunColumnAccess = 1             -- Column access pattern analysis (sp_ColumnAccessPatternRecommendations)
    -- Tool-Specific Parameters
    ,@MinExecutions = 500             -- (Key Lookup, Expensive Query) Minimum plan execution count to consider. Filters out rarely-executed queries.
    ,@MinImpact = 50.0                -- (Expensive Query) Minimum optimizer impact percentage to flag a missing index hint. Higher = fewer, more impactful findings.
    ,@TopN = 500                      -- (Expensive Query, Column Access) Max plans to analyze from the plan cache. Higher = more thorough but slower.
    ,@Confidence = 'MEDIUM'           -- (Unused Index) Minimum confidence level for active scripts. Indexes below threshold are commented out. Values: HIGH, MEDIUM, LOW.
    ,@Action = 'DISABLE'              -- (Unused Index) What to do with unused indexes. DISABLE = reversible (re-enable with REBUILD). DROP = permanent.
    ,@MinUpdateCount = 1000           -- (Unused Index) Minimum user_updates to flag (default 1000). Filters trivial write overhead.
    ,@MinObservDays = 7               -- (Unused Index) Minimum observation window in days (default 7). Won't recommend with insufficient data.
    --,@CombinedOutput = 1            -- When 1, returns one script organized by database -> table -> tool instead of one row per tool.

Suite Summary

EXEC DBA_Utilities.indexing.sp_IndexingSuiteSummary
    @Days = 7                         -- Number of days to look back (default 30).
    --,@DatabaseName = 'MyApp_%'      -- NULL = all databases. Filter to specific database.
    --,@Detail = 1                    -- BIT (default 0). When 1, returns row-level detail per tool after the summary.

Individual Tools

Duplicate Index

EXEC DBA_Utilities.indexing.sp_DuplicateIndexOptimizationRecommendations
    @RunAnalysis = 1                  -- BIT (default 0). When 1, collects live index data and refreshes results before generating output.
    ,@DatabaseName = 'MyApp_%'        -- NULL = all databases. Supports CSV list and LIKE wildcards.
    ,@RunnableUnder = '1,5e'          -- NULL = all uncommented. '2' = 2GB both. '1,5e' = 1GB Std / 5GB Ent. COMBINE ops >= threshold wrapped in /* */. DROP-only never commented.
    --,@TableName = ''                -- Filter to one table. NULL = all.
    --,@ActionType = ''               -- Filter to 'DUPLICATE', 'REDUNDANT', 'DUPLICATE of PK', or 'COMBINE'. NULL = all.

Key Lookup

EXEC DBA_Utilities.indexing.sp_KeyLookupRecommendations
    @RunAnalysis = 1                  -- BIT (default 0). When 1, runs sp_KeyLookupAnalysis first.
    ,@DatabaseName = 'MyApp_%'        -- NULL = all user databases. Supports CSV list and LIKE wildcards.
    ,@RunnableUnder = '1,5e'          -- NULL = all uncommented. '2' = 2GB both. '1,5e' = 1GB Std / 5GB Ent. Indexes >= threshold wrapped in /* */.
    ,@MinExecutions = 1000            -- Minimum plan execution count to consider (default 10).
    --,@TableName = ''                -- Filter to one table. NULL = all.

Unused Index

EXEC DBA_Utilities.indexing.sp_UnusedIndexRecommendations
    @RunAnalysis = 1                  -- BIT (default 0). When 1, runs sp_UnusedIndexAnalysis first.
    ,@DatabaseName = 'MyApp_%'        -- NULL = all user databases. Supports CSV list and LIKE wildcards.
    ,@Action = 'DISABLE'              -- DISABLE (default) = ALTER INDEX ... DISABLE (reversible). DROP = DROP INDEX IF EXISTS (permanent).
    ,@Confidence = 'HIGH'             -- Minimum confidence for active scripts (default HIGH). Indexes below threshold are commented out. Values: HIGH, MEDIUM, LOW.
    ,@MinUpdateCount = 1000           -- Minimum user_updates to flag (default 100). Filters trivial write overhead.
    ,@MinObservDays = 7               -- Minimum observation window in days (default 7). Won't recommend with insufficient data.
    --,@WithQueryStore = 1            -- BIT (default 0). When 1, enables Query Store plan scanning for HIGH confidence. Significantly slower.
    --,@TableName = ''                -- Filter to one table. NULL = all.

Missing Index (per-finding view with ResolvedDate tracking)

EXEC DBA_Utilities.indexing.sp_MissingIndexRecommendations
    @RunAnalysis = 1                  -- BIT (default 0). When 1, runs Load + Analyze + Generate first.
    ,@DatabaseName = 'MyApp_%'        -- NULL = all user databases. Supports CSV list and LIKE wildcards.
    ,@RunnableUnder = '1,5e'          -- NULL = all uncommented. '2' = 2GB both. '1,5e' = 1GB Std / 5GB Ent. Indexes >= threshold wrapped in /* */.
    --,@MinIndexAdvantage = 2500.0    -- Minimum DMV index advantage score (default 2500.0).
    --,@MinAvgUserImpact = 99.0       -- Minimum average user impact percentage (default 99.0).
    --,@TableName = ''                -- Filter to one table. NULL = all.

Missing Index (standalone wrapper)

EXEC DBA_Utilities.indexing.sp_CreateIndexOptimizationRecommendations
    @DatabaseName = 'MyApp_%'         -- NULL = all user databases. Supports comma-delimited list.
    ,@RunnableUnder = '1,5e'          -- NULL = all uncommented. '2' = 2GB both. '1,5e' = 1GB Std / 5GB Ent. Indexes >= threshold wrapped in /* */.
    --,@MinIndexAdvantage = 2500.0    -- Minimum DMV index advantage score (default 2500.0).
    --,@MinAvgUserImpact = 99.0       -- Minimum average user impact percentage (default 99.0).
    --,@Debug = 1                     -- 0 = silent (default). 1 = verbose output with step timing.

Expensive Query

EXEC DBA_Utilities.indexing.sp_ExpensiveQueryRecommendations
    @RunAnalysis = 1                  -- BIT (default 0). When 1, runs sp_ExpensiveQueryAnalysis first.
    ,@DatabaseName = 'MyApp_%'        -- NULL = all user databases. Supports CSV list and LIKE wildcards.
    ,@TopN = 1000                     -- Max top queries to analyze from the plan cache (default 50).
    ,@MinExecutions = 500             -- Minimum execution count to consider (default 10). Filters one-off queries.
    ,@MinImpact = 90                  -- Minimum optimizer impact percentage to flag a missing index hint (default 50.0).
    ,@RunnableUnder = '1,5e'          -- NULL = all uncommented. '2' = 2GB both. '1,5e' = 1GB Std / 5GB Ent. Indexes >= threshold wrapped in /* */.
    --,@SortBy = 'CPU'                -- Rank queries by READS (default), CPU, DURATION, or EXECUTIONS.
    --,@FindingType = ''              -- Filter to TABLE_SCAN, MISSING_INDEX, or REGRESSED_QUERY. NULL = all.
    --,@WithQueryStore = 1            -- BIT (default 0). When 1, enables Query Store regression detection per database.
    --,@LookbackDays = 7              -- Days back for Query Store regression comparison (default 7).
    --,@TableName = ''                -- Filter to one table. NULL = all.

Column Access

EXEC DBA_Utilities.indexing.sp_ColumnAccessPatternRecommendations
    @RunAnalysis = 1                  -- BIT (default 0). When 1, runs sp_ColumnAccessPatternAnalysis first.
    ,@DatabaseName = 'MyApp_%'        -- NULL = all user databases. Supports CSV list and LIKE wildcards.
    ,@RunnableUnder = '1,5e'          -- NULL = all uncommented. '2' = 2GB both. '1,5e' = 1GB Std / 5GB Ent. Indexes >= threshold wrapped in /* */.
    ,@TopN = 1000                     -- Max top plans to analyze from the plan cache (default 200).
    --,@MinQueryCount = 3             -- Minimum distinct queries referencing a column to include it (default 3).
    --,@ShowCovered = 1               -- BIT (default 0). When 1, includes columns already well-indexed (LEADING_KEY).
    --,@TableName = ''                -- Filter to one table. NULL = all.

Query Plan Analysis

-- Pass a query (executes it, grabs plan from cache)
EXEC DBA_Utilities.indexing.sp_AnalyzeQueryPlan
    @SQL = 'SELECT ... FROM ... WHERE ...'  -- Query to analyze (executes it, grabs plan from cache).
    ,@DatabaseName = 'MyDatabase'     -- Database context for @SQL (NULL = current database).
    --,@Debug = 1                     -- When 1, returns the captured plan XML as an additional result set.

-- Or pass plan XML directly (no execution)
DECLARE @plan XML = (SELECT BulkColumn FROM OPENROWSET(
    BULK 'D:\path\to\file.sqlplan', SINGLE_BLOB) AS x);
EXEC DBA_Utilities.indexing.sp_AnalyzeQueryPlan
    @PlanXML = @plan                  -- Plan XML to analyze directly (skip execution).
    --,@Debug = 1

Maintenance

Index Maintenance

EXEC DBA_Utilities.indexing.sp_IndexMaintenance
    @Execute = 0                      -- 0 = dry run (default, show what would happen). 1 = execute maintenance.
    --,@DatabaseName = 'MyApp_%'      -- NULL = all user databases. Supports CSV list and LIKE wildcards.
    --,@TableName = ''                -- NULL = all tables. CSV with optional schema prefix (dbo.MyTable).
    --,@ReorgThreshold = 10.0         -- Reorganize at or above this fragmentation % (default 10.0).
    --,@RebuildThreshold = NULL       -- NULL = auto (Enterprise: 10%, Standard: 30%). Rebuild at or above this %.
    --,@MinPageCount = 1000           -- Skip indexes smaller than this (default 1000, ~8 MB).
    --,@MaxIndexSizeMB = NULL         -- NULL = no limit. Skip indexes larger than this MB.
    --,@MaxDOP = NULL                 -- NULL = server default. Override MAXDOP for rebuild operations.
    --,@TimeLimit = 18000             -- Seconds (default 18000 = 5 hours). 0 = no limit.
    --,@LOBCompaction = 1             -- LOB_COMPACTION for REORGANIZE (default 1). 0 = skip (faster).
    --,@SortBy = 'FRAGMENTATION'      -- Process order: FRAGMENTATION (default, most fragmented first) or SIZE (largest first).

Statistics Maintenance

EXEC DBA_Utilities.dbo.sp_StatisticsMaintenance
    @Run = 1                          -- 0 = display only (default), 1 = execute updates
    ,@StatisticsSample = 100          -- NULL = no WITH clause (default). 100 = FULLSCAN. 1-99.99 = SAMPLE X PERCENT.
    --,@DatabaseName = 'MyApp_%'      -- NULL = all user databases. Supports comma-delimited list and LIKE wildcards.
    --,@TableName = ''                -- NULL = all tables. Comma-separated with optional schema prefix (dbo.MyTable).
    --,@DetailOutput = 1              -- 0 = minimal output (default). 1 = detailed results per statistic.