Optimize your indexes with AI SQL Tuner

Executive Summary

These recommendations prioritize high-impact, low-overhead index additions to accelerate common filters and ranges on read-mostly tables, consolidate overlapping missing index requests, and remove clearly underutilized indexes. All proposed nonclustered indexes use PAGE compression to minimize I/O and storage, and conservative fill factors suitable for the workload characteristics observed.

1. Create consolidated covering index on dbo.part: Nonclustered index on (p_size, p_brand) INCLUDE (p_type, p_mfgr), PAGE compressed. This satisfies two separate missing-index patterns in one structure with minimal write overhead.
2. Create targeted lookup index on dbo.partsupp: Nonclustered index on (ps_suppkey) INCLUDE (ps_supplycost, ps_availqty), PAGE compressed. Supports seeks by supplier over partsupp where the clustered PK begins with ps_partkey.
3. Create range-supporting index on dbo.customer: Nonclustered index on (c_acctbal) INCLUDE (c_phone), PAGE compressed, to accelerate inequality/range queries on account balance while covering phone lookups.
4. Drop unused index on dbo.lineitem: Drop IX_lineitem_l_suppkey_l_partkey_cov (0 seeks, 1 scan, 0 updates), reducing storage and maintenance exposure without observed benefit.
5. Statistics maintenance: Fullscan updates on affected tables post-deployment; ensure auto stats settings are enabled; add explicit column statistics only if auto-create is disabled.
6. Optional compression/fill factor adjustment for write-heavy clustered index on dbo.lineitem: Consider PAGE compression if CPU is available and set moderate fill factor to mitigate page splits. This is optional and should follow index additions above.

Detailed Prioritized Recommendations

1) Create consolidated covering index on dbo.part

• Rationale:
  • Two missing index suggestions target dbo.part:
    • Equality on p_size INCLUDE (p_type, p_mfgr)
    • Inequality on p_size, p_brand INCLUDE (p_type)
  • A single index on (p_size, p_brand) INCLUDE (p_type, p_mfgr) satisfies both patterns, reducing index count and write overhead.
  • Index usage shows dbo.part is read-heavy (3,190 seeks, 510 scans, negligible updates), making PAGE compression advantageous.
• Script:

USE tpch;
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.part')
      AND name = N'IX_part_p_size_brand_inc_type_mfgr'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_part_p_size_brand_inc_type_mfgr
    ON dbo.part (p_size ASC, p_brand ASC)
    INCLUDE (p_type, p_mfgr)
    WITH (
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 100,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON
    );
END
GO

Consider verifying column nullability and selectivity; if p_brand is frequently filtered, its position as second key helps inequality and composite predicates.

2) Create targeted lookup index on dbo.partsupp

• Rationale:
  • Missing index suggests equality on ps_suppkey INCLUDE (ps_supplycost, ps_availqty).
  • Existing clustered key is partsupp_pk (likely (ps_partkey, ps_suppkey)); filtering by suppkey alone benefits from an access path beginning with ps_suppkey.
  • Table is read-heavy with no observed updates; PAGE compression yields significant I/O savings with minimal CPU overhead on this workload.
• Script:

USE tpch;
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.partsupp')
      AND name = N'IX_partsupp_ps_suppkey_inc_cost_qty'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_partsupp_ps_suppkey_inc_cost_qty
    ON dbo.partsupp (ps_suppkey ASC)
    INCLUDE (ps_supplycost, ps_availqty)
    WITH (
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 100,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON
    );
END
GO

3) Create range-supporting index on dbo.customer

• Rationale:
  • Missing index recommends inequality on c_acctbal INCLUDE (c_phone).
  • Current access primarily via clustered PK; no nonclustered exists on c_acctbal. A focused index greatly improves range queries and sorts on c_acctbal.
  • Customer table shows read-heavy access; added maintenance overhead is low, and compression reduces I/O.
• Script:

USE tpch;
GO
IF NOT EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.customer')
      AND name = N'IX_customer_c_acctbal_inc_phone'
)
BEGIN
    CREATE NONCLUSTERED INDEX IX_customer_c_acctbal_inc_phone
    ON dbo.customer (c_acctbal ASC)
    INCLUDE (c_phone)
    WITH (
        DATA_COMPRESSION = PAGE,
        FILLFACTOR = 100,
        SORT_IN_TEMPDB = ON,
        ONLINE = ON
    );
END
GO

4) Drop unused index on dbo.lineitem

• Rationale:
  • IX_lineitem_l_suppkey_l_partkey_cov shows 0 seeks, 1 scan, 0 updates — effectively unused in observed workload.
  • While current updates are 0, keeping unused indexes increases storage, rebuild/reorganize time, and potential future write overhead.
• Script (safe drop with existence check):

USE tpch;
GO
IF EXISTS (
    SELECT 1
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(N'dbo.lineitem')
      AND name = N'IX_lineitem_l_suppkey_l_partkey_cov'
)
BEGIN
    DROP INDEX IX_lineitem_l_suppkey_l_partkey_cov ON dbo.lineitem WITH (ONLINE = ON);
END
GO

Reassess after a representative workload period. If plans begin using a similar access path, consider recreating a better-targeted covering index based on observed predicates.

5) Statistics recommendations

• Rationale:
  • New indexes will auto-create index stats; update them after creation to optimize cardinality estimates.
  • Ensure AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS are enabled at the database level.
  • Given read-heavy nature, a fullscan update on key tables is appropriate post-deployment.
• Script:

USE tpch;
GO
-- Ensure auto stats settings are enabled (adjust only if currently disabled)
ALTER DATABASE tpch SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE tpch SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE tpch SET AUTO_UPDATE_STATISTICS_ASYNC ON;

-- Update statistics on impacted tables and large fact tables
UPDATE STATISTICS dbo.part WITH FULLSCAN;
UPDATE STATISTICS dbo.partsupp WITH FULLSCAN;
UPDATE STATISTICS dbo.customer WITH FULLSCAN;
-- Optionally refresh lineitem and orders after index changes
UPDATE STATISTICS dbo.lineitem WITH FULLSCAN;
UPDATE STATISTICS dbo.orders WITH FULLSCAN;
GO

6) Optional: Adjust compression and fill factor for write-heavy clustered index on dbo.lineitem

• Rationale:
  • dbo.lineitem clustered index shows relatively high user_updates (3,125) versus seeks/scans, suggesting meaningful write activity.
  • A moderate fill factor can mitigate page splits; PAGE compression can substantially reduce I/O on scans at the cost of CPU. Consider workload CPU headroom.
  • This change is optional and should be evaluated in a performance window.
• Script (no key changes; only storage attributes). Note: This is an online operation in Developer/Enterprise editions.

USE tpch;
GO
-- Rebuild clustered index with PAGE compression and a moderate fill factor
ALTER INDEX [l_shipdate_ind] ON dbo.lineitem
REBUILD WITH (
    DATA_COMPRESSION = PAGE,
    FILLFACTOR = 95,
    SORT_IN_TEMPDB = ON,
    ONLINE = ON,
    MAXDOP = 0
);
GO

If CPU pressure increases, consider ROW compression instead of PAGE: replace DATA_COMPRESSION = PAGE with ROW.

Additional Considerations

• Foreign keys and primary keys: No recommendation to change clustered keys or primary keys; therefore, no FK/PK drop-recreate actions are required.
• Maintenance impact: The proposed additional nonclustered indexes (on part, partsupp, customer) target read-mostly tables with effectively zero observed updates, minimizing DML overhead while delivering measurable query benefits.
• Index hygiene: After deployment, monitor sys.dm_db_index_usage_stats for 1–2 maintenance cycles. Any new indexes with near-zero seeks/scans and nontrivial updates should be reconsidered.