Installation

Download

Script	Description
INSTALL_IndexOptimizationSuite.sql	Core suite — all 6 optimization tools + orchestrator + summary
index-maintenance.sql	Index defragmentation (REORG/REBUILD)
statistics-maintenance.sql	Stale statistics detection and update
query-plan-analysis.sql	One-off query plan diagnostics
help-index-usage.sql	Enhanced sp_helpindex with usage stats

Or clone the full repo: git clone https://github.com/mja1175/SQL-Server-Optimization.git

Prerequisites

SQL Server 2016 or later (uses STRING_SPLIT, STRING_AGG, DROP TABLE IF EXISTS)
A DBA_Utilities database on each server (the suite creates the indexing schema automatically)
sysadmin or db_owner on DBA_Utilities to create objects

Install the Suite

The entire suite ships as a single SQL file. Download INSTALL_IndexOptimizationSuite.sql and run it against DBA_Utilities:

USE DBA_Utilities;
GO
-- Run INSTALL_IndexOptimizationSuite.sql
-- Creates the indexing schema, all tables, functions, and procedures

The script is safe to re-run. Tables are created with IF NOT EXISTS (existing data is preserved). Procedures use CREATE OR ALTER (always updated to the latest version).

Maintenance Tools (recommended)

Index Maintenance

Automated defragmentation with edition-aware REORG/REBUILD, ONLINE fallback, and time limits:

USE DBA_Utilities;
GO
-- Run index-maintenance.sql

Statistics Maintenance

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

USE DBA_Utilities;
GO
-- Run statistics-maintenance.sql

Standalone Diagnostics (optional)

Query Plan Analysis

sp_AnalyzeQueryPlan is a standalone diagnostic tool for investigating slow queries. Not part of the suite orchestrator:

USE DBA_Utilities;
GO
-- Run query-plan-analysis.sql

sp_helpindexUsage

Installs in the master database (not DBA_Utilities) and is marked as a system object so it can be called from any database context:

USE master;
GO
-- Run help-index-usage.sql

Verify Installation

-- Check all suite objects are present
SELECT SCHEMA_NAME(schema_id) + '.' + name AS ObjectName, type_desc
FROM DBA_Utilities.sys.objects
WHERE schema_id = SCHEMA_ID('indexing')
ORDER BY type_desc, name;

Upgrading

To upgrade to a new version, simply re-run the install script. All procedures are replaced with the latest version. Tables are not dropped — your historical data (recommendations, resolved dates, maintenance history) is preserved.