Even Brent likes AI now

AI SQL Tuner Recommendations

Executive Summary

1. Convert dbo.lineitem to a clustered columnstore index to accelerate scan-heavy analytics and aggregations while dramatically reducing I/O and CPU. Retain a minimal set of targeted nonclustered B-tree indexes for critical join/exists patterns.
2. Create a composite nonclustered index on dbo.lineitem covering (l_orderkey, l_suppkey) INCLUDE (l_commitdate, l_receiptdate) to support the frequent supplier “numwait” EXISTS/NOT EXISTS patterns efficiently.
3. Optimize dbo.partsupp with a composite nonclustered index (ps_suppkey, ps_partkey) INCLUDE (ps_supplycost, ps_availqty) to accelerate joins with lineitem in profit queries and reduce hash join pressure.
4. Rationalize dbo.orders nonclustered indexes: drop low-usage IX_orders_custkey_orderdate; keep and refine a single date-based index to serve date filters and groupings with lower write overhead.
5. Create nonclustered columnstore indexes on dbo.orders and dbo.part to improve large analytical joins and aggregations while preserving existing primary keys and clustered rowstore access paths.
6. Update and add focused statistics (FULLSCAN) on large tables, especially lineitem predicates (shipdate, discount, quantity) and partsupp join keys, to stabilize cardinality and reduce misestimates.
7. Refactor key queries to explicit JOIN syntax and anti/semi-join forms to improve plan quality; pushdown filters and avoid IN with GROUP BY HAVING where possible.

Detailed Prioritized Recommendations

1) Convert dbo.lineitem to Clustered Columnstore + Targeted NCIs

• Rationale:
  • dbo.lineitem is 59,996,066 rows and dominates logical reads and CPU across the plan cache (multiple 1.2M–3.9M logical read queries, heavy aggregations).
  • Current clustered index l_shipdate_ind shows relatively low seeks/scans versus high update count (Updates: 74,809). Workload is predominantly read/analytic; batch mode via columnstore will reduce resource consumption.
  • Existing IX_lineitem_orderkey (Seeks: 30,060) is valuable for joins; retain it or re-create post-CCI if needed.
• Implementation Notes:
  • Create a clustered columnstore index (CCI) on dbo.lineitem. Use ONLINE where feasible.
  • Add a narrow composite NCI on (l_orderkey, l_suppkey) INCLUDE (l_commitdate, l_receiptdate) to accelerate the frequent “numwait” EXISTS/NOT EXISTS supplier queries.
  • Optionally add a date/predicate aid NCI on (l_shipdate, l_discount, l_quantity) INCLUDE (l_extendedprice) if rowstore operators persist in revenue queries; start with CCI alone and the (l_orderkey, l_suppkey) NCI, then evaluate.
  • Use PAGE compression and conservative FILLFACTOR on NCIs (95–96) to mitigate page splits; CCI manages its own compression.

USE [tpch];
SET NOCOUNT ON;

-- 1.1 Create Clustered Columnstore Index on lineitem
-- If an existing clustered index must be dropped, do so explicitly.
-- Name the new CCI for clarity.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.lineitem') AND name = 'CCI_lineitem' AND type_desc = 'CLUSTERED COLUMNSTORE')
BEGIN
    -- Drop existing clustered index if present (rowstore CI).
    DECLARE @ci_name sysname;
    SELECT @ci_name = name FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.lineitem') AND index_id = 1 AND type_desc IN ('CLUSTERED', 'CLUSTERED COLUMNSTORE');
    IF @ci_name IS NOT NULL AND @ci_name <> 'CCI_lineitem'
    BEGIN
        PRINT 'Dropping existing clustered index ' + @ci_name + ' on dbo.lineitem...';
        DROP INDEX [@ci_name] ON dbo.lineitem WITH (ONLINE = ON);
    END

    PRINT 'Creating clustered columnstore index CCI_lineitem on dbo.lineitem...';
    CREATE CLUSTERED COLUMNSTORE INDEX [CCI_lineitem]
        ON dbo.lineitem
        WITH (DROP_EXISTING = OFF, ONLINE = ON);
END

-- 1.2 Targeted NCI to support supplier "numwait" EXISTS/NOT EXISTS
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.lineitem') AND name = 'IX_lineitem_orderkey_suppkey')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_lineitem_orderkey_suppkey]
    ON dbo.lineitem (l_orderkey, l_suppkey)
    INCLUDE (l_commitdate, l_receiptdate)
    WITH (FILLFACTOR = 95, DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END

-- 1.3 Optional NCI for revenue predicates (start disabled; enable if needed)
-- Uncomment to create if revenue queries still prefer rowstore seeks over batch scans.
/*
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.lineitem') AND name = 'IX_lineitem_shipdate_discount_qty')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_lineitem_shipdate_discount_qty]
    ON dbo.lineitem (l_shipdate, l_discount, l_quantity)
    INCLUDE (l_extendedprice)
    WITH (FILLFACTOR = 96, DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END
*/

2) Optimize dbo.partsupp for Profit/Join Queries

• Rationale:
  • dbo.partsupp (8,000,000 rows) participates in profit subqueries joining on (ps_partkey, ps_suppkey), with heavy reads.
  • Missing index recommendation on ps_suppkey supports these patterns; composite key (ps_suppkey, ps_partkey) with includes will better serve join + calculations.
• Implementation:
  • Create NCI (ps_suppkey, ps_partkey) INCLUDE (ps_supplycost, ps_availqty) with PAGE compression and FILLFACTOR 96.

USE [tpch];
SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.partsupp') AND name = 'IX_partsupp_suppkey_partkey')
BEGIN
    CREATE NONCLUSTERED INDEX [IX_partsupp_suppkey_partkey]
    ON dbo.partsupp (ps_suppkey, ps_partkey)
    INCLUDE (ps_supplycost, ps_availqty)
    WITH (FILLFACTOR = 96, DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END

3) Rationalize dbo.orders Nonclustered Indexes

• Observations:
  • orders_pk (CLUSTERED) has Seeks: 15,040; NCIs IX_orders_orderdate (Seeks: 50) and IX_orders_custkey_orderdate (Seeks: 20, Scans: 21) provide minimal benefit versus Updates: 30,000 (write overhead).
  • Multiple plan cache queries filter by o_orderdate and group/order by o_orderpriority.
• Actions:
  • Drop IX_orders_custkey_orderdate (low utility, high maintenance).
  • Refine IX_orders_orderdate to INCLUDE (o_orderpriority, o_custkey) to cover common patterns without an additional index.

USE [tpch];
SET NOCOUNT ON;

-- 3.1 Drop low-usage composite NCI
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.orders') AND name = 'IX_orders_custkey_orderdate')
BEGIN
    DROP INDEX [IX_orders_custkey_orderdate] ON dbo.orders WITH (ONLINE = ON);
END

-- 3.2 Recreate/refine date index with useful includes
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.orders') AND name = 'IX_orders_orderdate')
BEGIN
    -- Replace with includes (DROP_EXISTING = ON)
    CREATE NONCLUSTERED INDEX [IX_orders_orderdate]
    ON dbo.orders (o_orderdate)
    INCLUDE (o_orderpriority, o_custkey)
    WITH (DROP_EXISTING = ON, FILLFACTOR = 96, DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END
ELSE
BEGIN
    CREATE NONCLUSTERED INDEX [IX_orders_orderdate]
    ON dbo.orders (o_orderdate)
    INCLUDE (o_orderpriority, o_custkey)
    WITH (FILLFACTOR = 96, DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END

4) Add Nonclustered Columnstore on dbo.orders and dbo.part (Hybrid Analytics)

• Rationale:
  • Large tables commonly scanned in analytics: orders (15M rows) and part (2M rows). NCCI enables batch mode and columnar compression while preserving PK/clustered rowstore layout.
  • Many queries aggregate by o_orderdate/o_orderpriority and filter p_name like ‘%…%’.
• Implementation:
  • Create NCCI on orders and part. Keep indexes narrow and avoid redundant B-trees.

USE [tpch];
SET NOCOUNT ON;

-- 4.1 Nonclustered Columnstore on orders
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.orders') AND name = 'NCCI_orders' AND type_desc = 'NONCLUSTERED COLUMNSTORE')
BEGIN
    CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCI_orders]
    ON dbo.orders (o_orderdate, o_orderpriority, o_orderkey, o_custkey)
    WITH (DROP_EXISTING = OFF);
END

-- 4.2 Nonclustered Columnstore on part (accelerate LIKE scans and joins)
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.part') AND name = 'NCCI_part' AND type_desc = 'NONCLUSTERED COLUMNSTORE')
BEGIN
    CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCI_part]
    ON dbo.part (p_partkey, p_name)
    WITH (DROP_EXISTING = OFF);
END

5) Consolidate Missing Index Requests into Fewer High-Impact Indexes (Avoid Over-Indexing)

• Lineitem suggestions repeated around l_suppkey, l_partkey, l_shipinstruct, l_shipmode, l_quantity. Over-indexing large write tables increases maintenance and memory footprint.
• Chosen strategy:
  • Use CCI for broad analytics and one targeted NCI for the supplier “numwait” pattern.
  • Defer additional NCIs unless post-CCI plans show persistent rowstore seeks on specific predicates. This keeps Updates: 74,809 overhead minimal.

6) Statistics Maintenance (Large Tables)

• Rationale:
  • Heavy aggregations on lineitem often misestimate without robust stats on correlated predicates (shipdate, discount, quantity).
  • Columnstore segments use different metadata; still maintain auxiliary stats for NCIs and key columns in rowstore tables.
• Actions:
  • Update statistics with FULLSCAN on large tables post-index changes.
  • Create targeted multi-column stats aligned with predicate patterns to improve selectivity estimation.

USE [tpch];
SET NOCOUNT ON;

-- 6.1 Update statistics with FULLSCAN on large tables
UPDATE STATISTICS dbo.lineitem WITH FULLSCAN;
UPDATE STATISTICS dbo.orders WITH FULLSCAN;
UPDATE STATISTICS dbo.partsupp WITH FULLSCAN;
UPDATE STATISTICS dbo.part WITH FULLSCAN;
UPDATE STATISTICS dbo.customer WITH FULLSCAN;

-- 6.2 Targeted stats on lineitem predicates (helps when NCIs are used)
IF NOT EXISTS (SELECT 1 FROM sys.stats WHERE object_id = OBJECT_ID('dbo.lineitem') AND name = 'ST_lineitem_shipdate_discount_qty')
BEGIN
    CREATE STATISTICS [ST_lineitem_shipdate_discount_qty]
    ON dbo.lineitem (l_shipdate, l_discount, l_quantity)
    WITH FULLSCAN;
END

-- 6.3 Targeted stats on partsupp join keys
IF NOT EXISTS (SELECT 1 FROM sys.stats WHERE object_id = OBJECT_ID('dbo.partsupp') AND name = 'ST_partsupp_suppkey_partkey')
BEGIN
    CREATE STATISTICS [ST_partsupp_suppkey_partkey]
    ON dbo.partsupp (ps_suppkey, ps_partkey)
    WITH FULLSCAN;
END

7) Query Code Improvements (Plan Quality)

• General guidance:
  • Replace comma joins with explicit JOIN syntax to improve readability and optimizer transformations.
  • Replace IN (SELECT … GROUP BY HAVING) with semi-join to allow better join ordering.
  • Pushdown predicates early; avoid scalar conversions; keep sargable expressions.
• Example rewrites:
  • Customer-Orders-Lineitem with HAVING SUM(l_quantity):

    -- Original (uses IN + GROUP BY HAVING)
    -- select ... from customer, orders, lineitem where o_orderkey in (select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 312) ...
    
    -- Rewrite with explicit JOIN and semi-join
    SELECT TOP (100)
           c.c_name, c.c_custkey, o.o_orderkey, o.o_orderdate, o.o_totalprice, SUM(l.l_quantity) AS sum_qty
    FROM dbo.customer AS c
    JOIN dbo.orders   AS o ON o.o_custkey = c.c_custkey
    JOIN dbo.lineitem AS l ON l.l_orderkey = o.o_orderkey
    JOIN (
        SELECT l_orderkey
        FROM dbo.lineitem
        GROUP BY l_orderkey
        HAVING SUM(l_quantity) > 312
    ) AS lk ON lk.l_orderkey = o.o_orderkey
    GROUP BY c.c_name, c.c_custkey, o.o_orderkey, o.o_orderdate, o.o_totalprice
    ORDER BY o.o_totalprice DESC, o.o_orderdate
    OPTION (MAXDOP 8);
    

  • Revenue queries: ensure sargability and avoid unnecessary casts:

    SELECT SUM(l_extendedprice * l_discount) AS revenue
    FROM dbo.lineitem
    WHERE l_shipdate >= '1995-01-01'
      AND l_shipdate < '1996-01-01'
      AND l_discount BETWEEN 0.08 AND 0.10  -- 0.09 ± 0.01
      AND l_quantity < 25
    OPTION (MAXDOP 8);
    

  • Supplier “numwait” exists patterns: benefit from (l_orderkey, l_suppkey) NCI:

    SELECT TOP (100)
           s.s_name,
           COUNT_BIG(*) AS numwait
    FROM dbo.supplier AS s
    JOIN dbo.lineitem AS l1 ON l1.l_suppkey = s.s_suppkey
    JOIN dbo.orders   AS o  ON o.o_orderkey = l1.l_orderkey
    JOIN dbo.nation   AS n  ON n.n_nationkey = s.s_nationkey
    WHERE o.o_orderstatus = 'F'
      AND l1.l_receiptdate > l1.l_commitdate
      AND EXISTS (
            SELECT 1
            FROM dbo.lineitem AS l2
            WHERE l2.l_orderkey = l1.l_orderkey
              AND l2.l_suppkey <> l1.l_suppkey
      )
      AND NOT EXISTS (
            SELECT 1
            FROM dbo.lineitem AS l3
            WHERE l3.l_orderkey = l1.l_orderkey
              AND l3.l_suppkey <> l1.l_suppkey
              AND l3.l_receiptdate > l3.l_commitdate
      )
      AND n.n_name = N'JAPAN'
    GROUP BY s.s_name
    ORDER BY numwait DESC, s.s_name
    OPTION (MAXDOP 8);
    

8) Compression, Fillfactor, and Maintenance Considerations

• Use PAGE compression on large NCIs to reduce I/O; validate CPU impact (typically favorable for analytic reads).
• FILLFACTOR 95–96 on NCIs to reduce page splits; set SORT_IN_TEMPDB = ON for large builds to offload temp sort I/O.
• Prefer fewer, higher-impact NCIs over many narrow ones on dbo.lineitem to avoid excessive write amplification (user_updates currently sizable relative to seeks/scans on the clustered index).
• Columnstore indexes (CCI/NCCI) bring batch mode benefits and reduce memory grants, aligning with high Max Grant KB queries observed.

Index/Constraint Integrity Notes

• No foreign keys reference dbo.lineitem; converting to CCI does not require FK reconstruct. Primary keys on other tables remain unchanged.
• If any future change requires dropping and recreating a primary key, ensure dependent foreign keys are dropped and recreated in transactions to maintain referential integrity.

9) Optional Scripts for PK/FK Rebuild Safety (Template)

• Use this template if a future decision requires changing a PK (not currently recommended for tpch):

-- TEMPLATE ONLY: Not applied. Use for PK changes with FKs.
BEGIN TRAN;

-- Drop dependent FKs
-- ALTER TABLE <child_table> DROP CONSTRAINT <fk_name>;

-- Drop & recreate PK (with ONLINE where supported)
-- ALTER TABLE <parent_table> DROP CONSTRAINT <pk_name>;
-- ALTER TABLE <parent_table> ADD CONSTRAINT <pk_name> PRIMARY KEY CLUSTERED (<key_columns>) WITH (FILLFACTOR = 98, DATA_COMPRESSION = PAGE);

-- Recreate FKs
-- ALTER TABLE <child_table> WITH CHECK ADD CONSTRAINT <fk_name> FOREIGN KEY (<child_cols>) REFERENCES <parent_table> (<parent_cols>);

COMMIT TRAN;

Post-Implementation Validation

• Capture before/after metrics: logical reads, CPU time, elapsed time for the top 10 queries in the plan cache.
• Verify index usage DMV (sys.dm_db_index_usage_stats) trends after at least one workload cycle to confirm reduced scans and improved seeks where intended.
• Ensure auto-update stats remains enabled and schedule periodic FULLSCAN updates on large static tables.