Intelligent Query Processing
Self-Optimizing SQL Server

-- SQL Server 2022: Enable IQP features at database level
ALTER DATABASE YourDatabase
SET COMPATIBILITY_LEVEL = 160;  -- SQL Server 2022

-- Verify IQP features are active
SELECT name, value_in_use
FROM sys.database_scoped_configurations
WHERE name IN (
    'BATCH_MODE_ADAPTIVE_JOINS',
    'BATCH_MODE_MEMORY_GRANT_FEEDBACK',
    'INTERLEAVED_EXECUTION_TVF',
    'APPROXIMATE_QP'
);

-- Example: Approximate COUNT DISTINCT (100x faster on large tables)
-- BEFORE (exact, slow on 500M rows):
SELECT COUNT(DISTINCT CustomerID) FROM Orders;  -- 45 seconds

-- AFTER (approximate, 2% error margin):
SELECT APPROX_COUNT_DISTINCT(CustomerID) FROM Orders;  -- 0.3 seconds

-- Memory Grant Feedback: SQL Server learns optimal memory
-- First execution: over-allocated 2GB, spilled to disk
-- After feedback: correctly allocates 128MB, no spill
SELECT o.CustomerID, SUM(o.Total) as Revenue,
       COUNT(*) as OrderCount
FROM Orders o
JOIN OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY o.CustomerID
ORDER BY Revenue DESC;