AI SQL Tuner Recommendations

Tuning goal: Index Tuning

Server: RockyPC
Database: tpch
SQL Server: Microsoft SQL Server 2022 (RTM-GDR) 16.0.1160.1 (X64) — Developer Edition / Engine Edition: Enterprise
Key capabilities used in recommendations: Online index ops, columnstore, advanced compression, SQL Server 2022 IQP.

Executive summary Top priorities

Primary bottleneck observed
Large, scan-heavy analytical workloads on dbo.lineitem and dbo.orders (multi-million logical reads per execution).
Most impactful structural change
Fix dbo.orders heap (create clustered index/PK) and add 2–3 targeted nonclustered indexes to support the highest-cost plan-cache query patterns.
Columnstore suitability
dbo.lineitem (60M) and dbo.orders (15M) are strong candidates for nonclustered columnstore to accelerate aggregations and large scans with minimal OLTP overhead (user_updates shown as 0).
  1. Create a clustered index (PK) on dbo.orders (table is currently a heap with scans and lookups). High impactLow maintenance risk (updates=0)
  2. Add a nonclustered columnstore on dbo.lineitem to accelerate GROUP BY/aggregate-heavy TPC-H style queries (e.g., Q1, Q7/8/9-like). High impactExtra storage
  3. Add 2 targeted rowstore nonclustered indexes on dbo.lineitem to eliminate repeated large scans for order-based semi-joins and late-receipt predicates. High impactAdds write overhead if workload becomes OLTP
  4. Add rowstore nonclustered indexes on dbo.orders for date-range filters and customer joins used heavily in cache. Medium-high impact
  5. Update statistics (fullscan on largest tables initially) and enable async stats updates for less blocking on first-run. Medium impact
  6. Query rewrites: replace legacy comma joins + IN(subquery) and correlated EXISTS patterns with JOIN/APPLY forms to improve cardinality/plan shape and reduce memory grants. Medium impact

Detailed prioritized recommendations

  1. Fix dbo.orders heap by creating a clustered primary key on (o_orderkey) Priority 1
    • Why: Index usage shows dbo.orders has a HEAP with 110 scans and 10 lookups; multiple plan-cache queries join on o_orderkey and filter on o_orderdate. Heaps amplify lookups and scanning costs.
    • Overhead: One-time build cost. Ongoing maintenance is low in your captured workload (user_updates=0).
    • Implementation note: Because there is already a PK named orders_pk currently implemented as NONCLUSTERED, the cleanest approach is to drop and recreate as CLUSTERED. If any foreign keys reference it, drop/recreate those as well (your provided constraint list does not show any table referencing orders except dbo.lineitem (lineitem_order_fk) which references dbo.orders.o_orderkey—so we must handle that FK).
    USE [tpch];
GO

/* Drop FK(s) referencing dbo.orders PK */
ALTER TABLE dbo.lineitem DROP CONSTRAINT lineitem_order_fk;
GO

/* Drop existing PK (currently nonclustered per usage stats) */
ALTER TABLE dbo.orders DROP CONSTRAINT orders_pk;
GO

/* Recreate PK as CLUSTERED */
ALTER TABLE dbo.orders
ADD CONSTRAINT orders_pk
PRIMARY KEY CLUSTERED (o_orderkey)
WITH
(
  FILLFACTOR = 98,
  DATA_COMPRESSION = PAGE,
  ONLINE = ON,
  SORT_IN_TEMPDB = ON
);
GO

/* Recreate FK */
ALTER TABLE dbo.lineitem
ADD CONSTRAINT lineitem_order_fk
FOREIGN KEY (l_orderkey) REFERENCES dbo.orders(o_orderkey);
GO
  2. Create a nonclustered columnstore index (NCCI) on dbo.lineitem Priority 2
    • Why: dbo.lineitem is 60M rows and the plan cache shows repeated, scan-heavy aggregations (e.g., Q1-style aggregation by l_returnflag, l_linestatus, and revenue calculations). NCCI provides batch-mode execution and compression benefits for these analytics patterns.
    • Overhead: Adds storage and delta-store maintenance; but your captured user_updates=0 indicates minimal DML, making this a strong fit.
    • Notes: Keep the existing clustered rowstore index (l_shipdate_ind) for selective date predicates; NCCI complements it for broad scans/aggregations.
    USE [tpch];
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCX_lineitem_analytics
ON dbo.lineitem
(
  l_orderkey,
  l_partkey,
  l_suppkey,
  l_shipdate,
  l_commitdate,
  l_receiptdate,
  l_returnflag,
  l_linestatus,
  l_quantity,
  l_extendedprice,
  l_discount,
  l_tax,
  l_shipmode,
  l_shipinstruct
)
WITH (ONLINE = ON);
GO
  3. Create a supporting rowstore nonclustered index for order-based aggregation/semi-join on dbo.lineitem Priority 3
    • Targets: The most expensive cached query pattern repeatedly executes: o_orderkey IN (SELECT l_orderkey FROM lineitem GROUP BY l_orderkey HAVING SUM(l_quantity) > ...) and then joins back to lineitem on l_orderkey.
    • Why: Missing indexes repeatedly suggest (l_orderkey) INCLUDE (l_discount, l_extendedprice, l_suppkey, l_partkey, l_commitdate, l_receiptdate). Consolidate into one index to cover the highest-frequency join + select list needs while keeping width bounded.
    • Overhead: Wider include list increases storage; however user_updates=0 on lineitem implies low maintenance cost in this workload snapshot.
    USE [tpch];
GO

CREATE INDEX IX_lineitem_orderkey_cover
ON dbo.lineitem (l_orderkey)
INCLUDE (l_quantity, l_extendedprice, l_discount, l_suppkey, l_partkey, l_commitdate, l_receiptdate, l_returnflag)
WITH
(
  FILLFACTOR = 98,
  DATA_COMPRESSION = PAGE,
  ONLINE = ON,
  SORT_IN_TEMPDB = ON
);
GO
  4. Create a selective rowstore nonclustered index for late receipt/commit predicates on dbo.lineitem Priority 4
    • Targets: Supplier “numwait” query pattern uses: l_receiptdate > l_commitdate, joins on l_orderkey and l_suppkey, and probes for existence of other suppliers per order.
    • Why: A composite index starting with l_orderkey then l_suppkey supports both the main join and the correlated existence checks. Including the date columns supports the predicate evaluation without extra lookups.
    • Overhead: Additional nonclustered index on a 60M row table; justified by very high logical reads (~3.9M) and CPU (~26–35s) per execution for this pattern.
    USE [tpch];
GO

CREATE INDEX IX_lineitem_order_supp_receipt_commit
ON dbo.lineitem (l_orderkey, l_suppkey)
INCLUDE (l_receiptdate, l_commitdate)
WITH
(
  FILLFACTOR = 98,
  DATA_COMPRESSION = PAGE,
  ONLINE = ON,
  SORT_IN_TEMPDB = ON
);
GO
  5. Create date-range and join-supporting nonclustered indexes on dbo.orders Priority 5
    • Targets:
      • Revenue/top-N and region/nation revenue queries filter on o_orderdate range and join on o_custkey, project o_orderkey.
      • Orderpriority counts filter on o_orderdate and group by o_orderpriority.
      • Top-10 revenue query projects o_shippriority and o_orderdate grouped by l_orderkey/o_orderkey.
    • Why: Missing index hints point to o_custkey + o_orderdate patterns and pure o_orderdate range patterns.
    • Overhead: Moderate; orders is 15M rows. Still acceptable given user_updates=0 in captured stats.
    USE [tpch];
GO

/* For customer + date range patterns */
CREATE INDEX IX_orders_cust_orderdate
ON dbo.orders (o_custkey, o_orderdate)
INCLUDE (o_orderkey, o_totalprice)
WITH
(
  FILLFACTOR = 98,
  DATA_COMPRESSION = PAGE,
  ONLINE = ON,
  SORT_IN_TEMPDB = ON
);
GO

/* For pure date-range analytics and grouping/projection */
CREATE INDEX IX_orders_orderdate
ON dbo.orders (o_orderdate)
INCLUDE (o_orderkey, o_custkey, o_shippriority, o_orderpriority, o_orderstatus)
WITH
(
  FILLFACTOR = 98,
  DATA_COMPRESSION = PAGE,
  ONLINE = ON,
  SORT_IN_TEMPDB = ON
);
GO
  6. Create a nonclustered index on dbo.partsupp for ps_suppkey lookups Priority 6
    • Why: Missing index recommends (ps_suppkey) INCLUDE (ps_supplycost, ps_availqty). While partsupp has a clustered PK (likely on (ps_partkey, ps_suppkey)), supplier-driven access will not efficiently seek unless ps_suppkey is leading.
    • Overhead: Adds one NC index to an 8M row table; acceptable in analytics-oriented workloads.
    • Guardrail: If supplier-driven access is rare, deprioritize; but the missing index shows 10 seeks/scans with meaningful impact.
    USE [tpch];
GO

CREATE INDEX IX_partsupp_suppkey
ON dbo.partsupp (ps_suppkey)
INCLUDE (ps_supplycost, ps_availqty, ps_partkey)
WITH
(
  FILLFACTOR = 98,
  DATA_COMPRESSION = PAGE,
  ONLINE = ON,
  SORT_IN_TEMPDB = ON
);
GO
  7. Consider nonclustered columnstore on dbo.orders (optional, analytics-heavy workloads) Priority 7
    • Why: 15M-row table with frequent date-range scans and groupings; NCCI can improve batch-mode joins/aggregations with lineitem and customer/nation/region.
    • Overhead: Extra storage; delta store overhead on DML (currently appears negligible).
    • When to skip: If orders is frequently inserted/updated (not reflected in your usage snapshot), prefer rowstore only.
    USE [tpch];
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCX_orders_analytics
ON dbo.orders
(
  o_orderkey,
  o_custkey,
  o_orderdate,
  o_totalprice,
  o_orderpriority,
  o_shippriority,
  o_orderstatus
)
WITH (ONLINE = ON);
GO
  8. Drop/avoid redundant indexes (none recommended to drop from provided list) Priority 8
    • dbo.lineitem: Existing clustered index l_shipdate_ind shows seeks (70) and scans (130) with 0 updates; keep it. It supports Q1-style l_shipdate <= ... predicates.
    • dbo.orders: Current orders_pk is used (10 seeks). After converting PK to clustered, no separate nonclustered PK should remain.
    • No additional drops are recommended because updates are 0 and existing indexes appear either essential (PKs) or actively used.
  9. Statistics recommendations Priority 9
    • Why: Analytical queries are sensitive to cardinality. After large index changes and on large tables, proactively updating stats improves plan quality and reduces memory grant over-allocation.
    • Settings: Auto-create/update stats are enabled; consider enabling async stats updates to reduce blocking on first compile after stale stats.
    USE [tpch];
GO

/* Enable async stats updates to reduce compile-time waits on large tables */
ALTER DATABASE [tpch] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
GO

/* One-time post-change stats refresh (target the largest 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;
GO
  10. Query code changes (high ROI patterns from plan cache) Priority 10
    • Rewrite IN (SELECT ... GROUP BY ... HAVING ...) to a derived-table join to improve optimization and reduce redundant scans; use explicit JOIN syntax (avoid old-style comma joins).
    • Rewrite correlated EXISTS/NOT EXISTS in the supplier “numwait” query to pre-aggregate per order to reduce repeated probes. 
    /* Pattern 1: Replace IN-subquery with JOIN to aggregated lineitem */
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) > 314
) AS x ON x.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 4);

/* Pattern 2: Reduce repeated EXISTS probes by summarizing per order */
WITH order_supp AS
(
    SELECT
        l_orderkey,
        l_suppkey,
        MAX(CASE WHEN l_receiptdate > l_commitdate THEN 1 ELSE 0 END) AS has_late
    FROM dbo.lineitem
    GROUP BY l_orderkey, l_suppkey
),
order_late_stats AS
(
    SELECT
        l_orderkey,
        SUM(has_late) AS late_supp_cnt,
        COUNT(*)      AS supp_cnt
    FROM order_supp
    GROUP BY l_orderkey
)
SELECT TOP (100)
    s.s_name,
    COUNT_BIG(*) AS numwait
FROM dbo.orders AS o
JOIN dbo.lineitem AS l1
  ON l1.l_orderkey = o.o_orderkey
JOIN dbo.supplier AS s
  ON s.s_suppkey = l1.l_suppkey
JOIN dbo.nation AS n
  ON n.n_nationkey = s.s_nationkey
JOIN order_late_stats AS z
  ON z.l_orderkey = o.o_orderkey
WHERE
    o.o_orderstatus = 'F'
    AND n.n_name = 'PERU'
    AND l1.l_receiptdate > l1.l_commitdate
    AND z.supp_cnt > 1
    AND z.late_supp_cnt = 1
GROUP BY s.s_name
ORDER BY numwait DESC, s.s_name
OPTION (MAXDOP 4);
    These rewrites align with the recommended indexes on dbo.lineitem (l_orderkey, l_suppkey) and dbo.orders date/customer keys and typically reduce logical reads by avoiding repeated correlated probes.

Deployment script (ordered, safe, online where possible)

USE [tpch];
GO

/* =========================
   1) dbo.orders: heap fix
   ========================= */
IF EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'lineitem_order_fk')
BEGIN
  ALTER TABLE dbo.lineitem DROP CONSTRAINT lineitem_order_fk;
END
GO

IF EXISTS (SELECT 1 FROM sys.key_constraints WHERE name = 'orders_pk' AND parent_object_id = OBJECT_ID('dbo.orders'))
BEGIN
  ALTER TABLE dbo.orders DROP CONSTRAINT orders_pk;
END
GO

ALTER TABLE dbo.orders
ADD CONSTRAINT orders_pk
PRIMARY KEY CLUSTERED (o_orderkey)
WITH
(
  FILLFACTOR = 98,
  DATA_COMPRESSION = PAGE,
  ONLINE = ON,
  SORT_IN_TEMPDB = ON
);
GO

ALTER TABLE dbo.lineitem
ADD CONSTRAINT lineitem_order_fk
FOREIGN KEY (l_orderkey) REFERENCES dbo.orders(o_orderkey);
GO

/* =========================
   2) dbo.lineitem: NCCI
   ========================= */
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NCCX_lineitem_analytics' AND object_id = OBJECT_ID('dbo.lineitem'))
BEGIN
  CREATE NONCLUSTERED COLUMNSTORE INDEX NCCX_lineitem_analytics
  ON dbo.lineitem
  (
    l_orderkey, l_partkey, l_suppkey,
    l_shipdate, l_commitdate, l_receiptdate,
    l_returnflag, l_linestatus,
    l_quantity, l_extendedprice, l_discount, l_tax,
    l_shipmode, l_shipinstruct
  )
  WITH (ONLINE = ON);
END
GO

/* =========================
   3) dbo.lineitem: targeted rowstore NCIs
   ========================= */
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_lineitem_orderkey_cover' AND object_id = OBJECT_ID('dbo.lineitem'))
BEGIN
  CREATE INDEX IX_lineitem_orderkey_cover
  ON dbo.lineitem (l_orderkey)
  INCLUDE (l_quantity, l_extendedprice, l_discount, l_suppkey, l_partkey, l_commitdate, l_receiptdate, l_returnflag)
  WITH (FILLFACTOR = 98, DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_lineitem_order_supp_receipt_commit' AND object_id = OBJECT_ID('dbo.lineitem'))
BEGIN
  CREATE INDEX IX_lineitem_order_supp_receipt_commit
  ON dbo.lineitem (l_orderkey, l_suppkey)
  INCLUDE (l_receiptdate, l_commitdate)
  WITH (FILLFACTOR = 98, DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END
GO

/* =========================
   4) dbo.orders: supporting rowstore NCIs
   ========================= */
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_orders_cust_orderdate' AND object_id = OBJECT_ID('dbo.orders'))
BEGIN
  CREATE INDEX IX_orders_cust_orderdate
  ON dbo.orders (o_custkey, o_orderdate)
  INCLUDE (o_orderkey, o_totalprice)
  WITH (FILLFACTOR = 98, DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_orders_orderdate' AND object_id = OBJECT_ID('dbo.orders'))
BEGIN
  CREATE INDEX IX_orders_orderdate
  ON dbo.orders (o_orderdate)
  INCLUDE (o_orderkey, o_custkey, o_shippriority, o_orderpriority, o_orderstatus)
  WITH (FILLFACTOR = 98, DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END
GO

/* =========================
   5) dbo.partsupp: supplier lookup NCI
   ========================= */
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'IX_partsupp_suppkey' AND object_id = OBJECT_ID('dbo.partsupp'))
BEGIN
  CREATE INDEX IX_partsupp_suppkey
  ON dbo.partsupp (ps_suppkey)
  INCLUDE (ps_supplycost, ps_availqty, ps_partkey)
  WITH (FILLFACTOR = 98, DATA_COMPRESSION = PAGE, ONLINE = ON, SORT_IN_TEMPDB = ON);
END
GO

/* =========================
   6) Optional: dbo.orders NCCI (comment out if DML-heavy)
   ========================= */
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'NCCX_orders_analytics' AND object_id = OBJECT_ID('dbo.orders'))
BEGIN
  CREATE NONCLUSTERED COLUMNSTORE INDEX NCCX_orders_analytics
  ON dbo.orders (o_orderkey, o_custkey, o_orderdate, o_totalprice, o_orderpriority, o_shippriority, o_orderstatus)
  WITH (ONLINE = ON);
END
GO

/* =========================
   7) Stats
   ========================= */
ALTER DATABASE [tpch] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
GO

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;
GO