Why should you care?

You get better plans with fewer manual hints as you raise compatibility levels on modern SQL Server.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
-- Example query that may benefit from batch mode / feedback over time
SELECT City, SUM(Amount) AS Revenue
FROM dbo.Orders
GROUP BY City;
SELECT name, compatibility_level
FROM sys.databases WHERE name = N'DataVerse';
-- Aim for recent compat (e.g. 160 on SQL 2022) after testing

What happened?

• Aggregates are common IQP beneficiaries.
• Compatibility level enables many IQP features — raise it only after checking critical query plans.

Practice next

1. Note current compatibility_level.
2. Run a heavy aggregate with actual plan; note mode.
3. Read which IQP features your version includes.
4. Compare plans under compat 140 vs 160 in lab.
5. Watch memory grant feedback on a spilling sort query.

Remember

IQP improves plans automatically. Tied to version + compatibility. Still need solid schema design.

Fewer manual hints

DataVerse removes outdated OPTIMIZE FOR hints after 2022 upgrade.

Outcome: IQP adapts better to changing data volumes.