SQL Server 2019 Expert

Key Features

Intelligent Query Processing (IQP)

IQP is an umbrella for multiple features that make the query optimizer self-correcting. All require compat level 150 unless noted.

1. Batch Mode on Rowstore: Batch mode execution (processing ~900 rows at a time) is no longer limited to columnstore indexes. Any query with sufficient cost can use batch mode on pure rowstore tables.

-- Check if a query is using batch mode on rowstore:
-- Look for BatchModeOnRowstore="true" in the execution plan XML
-- Or check for batch mode operators in the graphical plan

-- Disable for a specific query if it causes regression:
SELECT ... OPTION (USE HINT ('DISALLOW_BATCH_MODE'));

Key benefits: 30-50% improvement for analytical queries (aggregations, window functions, sorts) even without columnstore indexes.

2. Scalar UDF Inlining: Scalar UDFs that meet certain criteria are inlined into the calling query, eliminating the per-row function call overhead.

-- Check if a UDF is inlineable:
SELECT OBJECT_NAME(object_id) AS udf_name, is_inlineable
FROM sys.sql_modules
WHERE definition IS NOT NULL;

A UDF is NOT inlineable if it:

• References table variables or TVFs
• Uses TRY/CATCH, @variable assignments in SELECT, WHILE loops, cursors
• Calls non-inlineable functions
• References computed columns or check constraints with UDFs

Force disable for a specific function:

ALTER FUNCTION dbo.MyUDF(...) ... WITH INLINE = OFF;

3. Table Variable Deferred Compilation: Table variables now get cardinality estimates at first compilation (deferred until the table variable is populated), not the hardcoded 1-row estimate.

-- Before 2019: Table variables always estimated at 1 row
-- After 2019 (compat 150): Actual row count at first compilation
-- No syntax change needed -- automatic

-- Check estimated vs actual rows for table variable operators in execution plans

4. Memory Grant Feedback (Row Mode): Extends the batch mode memory grant feedback from 2017 to row mode queries. Also persists feedback in Query Store (percentile-based).

-- Monitor memory grant feedback adjustments in the plan:
-- IsMemoryGrantFeedbackAdjusted = "Yes" in execution plan XML

-- Disable for a query:
SELECT ... OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

5. APPROX_COUNT_DISTINCT: HyperLogLog-based approximate distinct count. ~2% error rate, significantly faster than exact COUNT(DISTINCT) on large datasets.

SELECT APPROX_COUNT_DISTINCT(CustomerID) AS approx_customers
FROM dbo.Orders;
-- Much faster than: SELECT COUNT(DISTINCT CustomerID) FROM dbo.Orders
-- for tables with millions+ rows

Accelerated Database Recovery (ADR)

ADR fundamentally redesigns the recovery process using a persistent version store (PVS) in the user database.

Benefits:

• Instant transaction rollback -- Long-running transactions roll back in constant time
• Fast crash recovery -- Recovery time is constant regardless of longest active transaction
• Aggressive log truncation -- Log truncation no longer blocked by active transactions

-- Enable ADR
ALTER DATABASE [MyDB] SET ACCELERATED_DATABASE_RECOVERY = ON;

-- Monitor PVS size
SELECT pvs_off_row_page_count_in_db,
       current_aborted_transaction_count,
       aborted_version_cleaner_start_time
FROM sys.dm_db_persisted_sku_features;  -- check ADR status

-- Monitor version store space in the database
SELECT * FROM sys.dm_tran_persistent_version_store_stats;

Trade-offs:

• PVS consumes space in the user database (not tempdb)
• Additional I/O for version store maintenance
• Some workloads see slight throughput decrease for write-heavy operations
• Best for databases with long-running transactions or AG secondaries

Big Data Clusters (Deprecated)

Big Data Clusters integrated SQL Server with Apache Spark and HDFS in Kubernetes.

Important: Big Data Clusters were deprecated in SQL Server 2019 CU28 and removed in future versions. Do NOT build new solutions on this feature. Use Microsoft Fabric or Synapse for big data scenarios.

Data Virtualization with PolyBase

SQL Server 2019 expanded PolyBase to query many external sources without moving data:

-- Connect to Oracle
CREATE EXTERNAL DATA SOURCE OracleServer
WITH (LOCATION = 'oracle://oracle-host:1521',
      CREDENTIAL = OracleCredential);

CREATE EXTERNAL TABLE dbo.OracleOrders (...)
WITH (DATA_SOURCE = OracleServer, LOCATION = 'SCHEMA.ORDERS');

-- Query across SQL Server and Oracle
SELECT s.CustomerName, o.OrderTotal
FROM dbo.Customers s
JOIN dbo.OracleOrders o ON s.CustomerID = o.CustomerID;

Supported sources in 2019: SQL Server, Oracle, Teradata, MongoDB, ODBC generic, S3-compatible storage (CSV/Parquet), Hadoop, Azure Blob.

UTF-8 Support

SQL Server 2019 supports UTF-8 collations, reducing storage for Unicode data by up to 50% for Latin-heavy text:

-- Use UTF-8 collation
CREATE DATABASE [MyDB] COLLATE Latin1_General_100_CI_AS_SC_UTF8;

-- Or per column
ALTER TABLE dbo.MyTable
ALTER COLUMN TextCol VARCHAR(200) COLLATE Latin1_General_100_CI_AS_SC_UTF8;

With UTF-8 collations, VARCHAR stores UTF-8 encoded text (including non-Latin characters) without needing NVARCHAR. This saves storage for predominantly ASCII data.

Additional 2019 Features

• Resumable online CREATE INDEX -- Not just rebuild (2017), now also initial index creation
• OPTIMIZE_FOR_SEQUENTIAL_KEY -- Reduces last-page insert contention on identity columns:

  CREATE INDEX IX_Col ON dbo.MyTable(IdentityCol) WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);
  

• Memory-optimized tempdb metadata -- Eliminates tempdb metadata contention:

  ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON; -- requires restart
  

• Graph enhancements -- SHORTEST_PATH, edge constraints
• Machine Learning Services on Linux -- R and Python now available on Linux
• TDE on Standard Edition -- No longer Enterprise-only
• sys.dm_exec_query_plan_stats -- Lightweight query profiling (last actual plan)
• Verbose truncation warnings -- Error messages now tell you which column and what the value was

Version Boundaries

• This agent covers SQL Server 2019 (compat level 150) specifically
• Features NOT available in 2019 (introduced later):
  • Parameter Sensitive Plan optimization (2022)
  • DOP feedback (2022)
  • CE feedback (2022)
  • Optimized plan forcing (2022)
  • Query Store hints (2022)
  • Ledger tables (2022)
  • Contained Availability Groups (2022)
  • GREATEST/LEAST/DATETRUNC/GENERATE_SERIES (2022)
  • IS DISTINCT FROM (2022)
  • XML compression (2022)
  • Native vector data type (2025)
  • RegEx functions (2025)
  • JSON index / native JSON type (2025)
  • Optimized locking (2025)

Common Pitfalls

1. Scalar UDF inlining breaking changes -- Some UDFs that worked as black boxes may change plan behavior when inlined. Test carefully and use WITH INLINE = OFF for problematic functions.
2. Batch mode on rowstore overhead -- For very simple OLTP queries, batch mode can add unnecessary overhead. The optimizer should avoid it, but monitor for unexpected batch mode usage.
3. ADR PVS growth -- The persistent version store can grow substantially with heavy update workloads. Monitor PVS size and ensure sufficient disk space:

   SELECT DB_NAME(database_id), persistent_version_store_size_kb / 1024 AS pvs_mb
   FROM sys.dm_tran_persistent_version_store_stats;
   

4. Table variable deferred compilation plan cache bloat -- Each distinct table variable row count can produce a different plan. Monitor plan cache growth.
5. Big Data Clusters investment -- Feature is deprecated. Migrate workloads to Microsoft Fabric or Synapse.
6. UTF-8 collation compatibility -- Some legacy applications assume VARCHAR = single-byte. UTF-8 VARCHAR can be multi-byte, breaking length assumptions.
7. Memory grant feedback oscillation -- For queries with highly variable data distributions, feedback can oscillate. Use DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK hint if needed.

Migration from SQL Server 2017

When upgrading from SQL Server 2017 (compat level 140) to 2019 (compat level 150):

1. Enable Query Store at compat 140 -- Capture baseline performance
2. Upgrade engine -- Keep compat level at 140 initially
3. Change compat level to 150 -- This unlocks ALL Intelligent Query Processing features at once
4. Monitor IQP impact:
   • Watch for scalar UDF inlining changing plans (use sys.sql_modules.is_inlineable to identify candidates)
   • Monitor batch mode on rowstore for unexpected usage
   • Track table variable execution plan accuracy
5. Enable ADR if you have long-running transactions or AG secondaries
6. Enable memory-optimized tempdb metadata if you have tempdb contention
7. Evaluate TDE -- Now available on Standard Edition

Known Behavioral Changes at Compat 150

• Scalar UDFs may be inlined, changing execution plans
• Table variables get actual cardinality estimates
• Batch mode may activate on rowstore queries
• Row mode memory grant feedback begins adjusting
• APPROX_COUNT_DISTINCT function available
• Verbose truncation warnings include column name and value
• STRING_AGG with WITHIN GROUP ordering supported

Reference Files

Load these for deep knowledge:

• ../references/architecture.md -- Storage engine, buffer pool, query processing
• ../references/diagnostics.md -- Wait stats, DMVs, Query Store usage, Extended Events
• ../references/best-practices.md -- Instance configuration, backup strategy, security