Why GIIP AI Harness Excludes Query Hints

Applicability of This Document

Ready-to-Run Commands

# Target: Application and stored-procedure source code repositories
# Permission: Repository read access
# Change: None (search only)
# Production: N/A (run locally or in CI)

# Search for common execution-plan-forcing patterns across the codebase
# WITH (INDEX(...)) / FORCESEEK / FORCESCAN / explicit join hints (LOOP|HASH|MERGE) / OPTION(FORCE ORDER)
grep -RniE "with[[:space:]]*\([[:space:]]*index[[:space:]]*\(|forceseek|forcescan|force[[:space:]]*order|loop[[:space:]]+join|hash[[:space:]]+join|merge[[:space:]]+join" --include=*.sql --include=*.cs --include=*.ts .

-- Target: SQL Server 2008 or later / Amazon RDS for SQL Server / Azure SQL
-- Permission: VIEW DEFINITION
-- Change: None (read-only)
-- Production: Permitted
SELECT
    o.name       AS object_name,
    o.type_desc  AS object_type
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
        ON o.object_id = m.object_id
WHERE m.definition LIKE '%INDEX(%'
   OR m.definition LIKE '%FORCESEEK%'
   OR m.definition LIKE '%FORCESCAN%'
   OR m.definition LIKE '%FORCE ORDER%'
ORDER BY o.name;

-- Target: SQL Server 2008 or later / Amazon RDS for SQL Server / Azure SQL Managed Instance
-- Permission: VIEW SERVER STATE (VIEW DATABASE STATE for Azure SQL Database)
-- Change: None (read-only)
-- Production: Permitted
SELECT
    OBJECT_NAME(i.object_id)   AS table_name,
    i.name                      AS index_name,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan,
    ius.last_user_lookup
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS ius
       ON ius.object_id   = i.object_id
      AND ius.index_id    = i.index_id
      AND ius.database_id = DB_ID()
WHERE i.object_id = OBJECT_ID('dbo.SampleTable')
ORDER BY i.name;

How to Read the Results

When to Use This

• The same base AI model produces different tuning proposals for the same slow query depending on the tool used.
• CPU or I/O spikes occur months after applying an AI-proposed FORCE INDEX or join hint, even though performance improved immediately.
• Deleting an index flagged as unused based solely on usage metrics caused a specific query or stored procedure to throw an exception immediately afterward.
• Application-code coordination is required every time an index is redesigned or a DDL change is made, causing delays.

Possible Causes (Most Likely First)

1. 01

   Selectivity reversal caused by data-distribution changes

   Even with the same schema, data volume and cardinality vary by country, service characteristics, and traffic patterns. An Index Seek + Key Lookup that was optimal when the table was small can generate large random I/O at tens of millions of rows or with skewed data distribution. Past a certain tipping point, an Index Scan or Clustered Index Scan is often faster and more stable. Hints prevent the CBO from making this adjustment.

2. 02

   Dangerous coupling between the index lifecycle and application code

   Large databases continuously create new composite indexes and consolidate or remove duplicate or unused ones. When an index reference is hard-coded in source code or stored procedures, changing or deleting that index causes an immediate runtime exception in production. In large-scale services, it is not uncommon for a team to delete an index based on usage metrics, only to have a specific query or procedure force-referencing that index fail immediately.

3. 03

   Difference in Harness, not in the model

   A general-purpose base model looks only at the query string in front of it and proposes local optimizations. GIIP AI Harness is designed to leave room for the CBO to decide — it explicitly excludes hints that force specific indexes or require static data assumptions, and it treats the index lifecycle as separate from application code.

Diagnostic Steps

1. 1

   Search the codebase for execution-plan-forcing hints

   Read-only

   Use the grep pattern above to search both application code and stored procedures for WITH (INDEX(...)), FORCESEEK/FORCESCAN, explicit join hints, and FORCE ORDER.

2. 2

   Check real index usage statistics

   Read-only

   Use sys.dm_db_index_usage_stats to review the count of read and write operations and the last time each index was used.

3. 3

   Check whether DMV counters have been reset

   Read-only

   Confirm the dates of recent service restarts, failovers, or instance recreations. If the observation period is short, "0% usage" is not a reliable basis for decisions.

4. 4

   Compare estimated vs. actual row counts in execution plans

   Read-only

   Look for selectivity reversal caused by data-distribution skew between the estimated and actual rows in execution plans.

5. 5

   Check for Sargability blockers

   Read-only

   Look for WHERE-clause expressions that apply functions to columns or cause implicit type conversions, preventing the index from being used.

Solutions

If This Doesn't Resolve It

• Did you check whether the target index remains in the execution-plan cache?

  Use sys.dm_exec_query_stats and sys.dm_exec_sql_text to find recent execution plans that reference the index.

• Did you check that the same hint patterns are not present in other repositories or stored-procedure groups?

  A hint may exist in a different repository, batch process, or a procedure maintained by another team.

• Did you check that statistics are not stale?

  The CBO relies on sampled statistics. If statistics are outdated, removing hints will not allow the CBO to make accurate decisions.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• Why do different AI tools produce different SQL tuning proposals?
• What are the risks of using query hints?
• What should I check before deleting an index?

What the Risk Labels Mean

• Read-onlyDoes not change data or settings.
• LowImpact is limited, but permissions and load should be checked.
• MediumMay affect performance, locking, or cost.
• HighMay cause an outage, data loss, or require recovery work.
• Expert Review RequiredRequires a separate review before applying to production.

GIIP's Scope of Support

GIIP AI Harness is designed to exclude FORCE INDEX and explicit join-order hints from all SQL it generates or proposes, and limits performance proposals to measures that do not interfere with CBO decisions — such as index-design review, statistics maintenance, and Sargability improvement. As part of the index lifecycle (create, consolidate, delete), it is an operational prerequisite to combine usage statistics with a code-base search confirming no force-reference exists, rather than relying on a single point-in-time snapshot of usage metrics.

Related Services

• データベース性能チューニング
• AIインフラ運用