From adaptive to intelligent query processing in SQL

From adaptive to intelligent: query processing in SQL Server 2019 Hugo Kornelis hugo@per. Fact. info @Hugo_Kornelis #SQLSat. Madrid

Global Venue Silver Gold Platinum BIG Thanks to SQLSat. Madrid sponsors

Hugo Kornelis SQLServer. Fast. com I make SQL Server Fast • Execution Plan Reference: http: //sqlserverfast. com/epr • Detailed description of all operators, and other relevant information • Other content • Blog: http: //sqlserverfast. com/blog • Articles • Longer, even more detailed in-depth information • Resources • Deck and demo for this session (and others)

Hugo Kornelis I make SQLServer. Fast. com I do other community things I work (consulting, training) Contact details • Email: hugo@per. Fact. info • Twitter: @Hugo_Kornelis https: //sqlserverfast. com/presentations/from-adaptive-to-intelligent-query-processing-in-sql-2019/

Query Processing Query optimization Query

Query Processing Query optimization Query execution Query Cardinality estimates Reality

Adaptive Query Processing SQL Server 2017

Adaptive Query Processing Source: https: //docs. microsoft. com/en-us/sql/relationaldatabases/performance/intelligent-query-processing? view=sql-server-2017

Adaptive Intelligent Query Processing SQL Server 2019

Intelligent Query Processing Source: https: //docs. microsoft. com/en-us/sql/relationaldatabases/performance/intelligent-query-processing? view=sql-server-2017

Memory Grant Feedback Memory Grant: memory required to execute plan • Determined during compile time • • Based on cardinality estimates So … can be wrong

Memory Grant Feedback Memory Grant: memory required to execute plan • Determined during compile time • Under-estimated? Spills to tempdb! • Can be very slow!

Memory Grant Feedback Memory Grant: memory required to execute plan • Determined during compile time • Under-estimated? Spills to tempdb! • Over-estimated? Resources wasted! • Impacts concurrency

Memory Grant Feedback Memory Grant: memory required to execute plan • • Determined during compile time Under-estimated? Spills to tempdb! Over-estimated? Resources wasted! Memory Grant Feedback • Compilation and execution as normal • Cached plan updated after execution

Memory Grant Feedback: Details • Adjusts down if >50% unused • Adjusts up after any spill • Based on last execution only • Can “chase own tail” in alternating patterns • Increases thresholds after X consecutive adaptations • Stops adapting after Y consecutive adaptations

Memory Grant Feedback: Details • • Adjusts down if >50% unused Adjusts up after any spill Stores last execution only Information stored in plan cache • Forgotten when plan recompiles

Memory Grant Feedback: Details • • • Adjusts down if >50% unused Adjusts up after any spill Stores last execution only Information stored in plan cache Extended events available • spilling_report_to_memory_grant_feedback • memory_grant_updated_by_feedback • memory_grant_feedback_loop_disabled (debug channel)

Intelligent Query Processing Source: https: //docs. microsoft. com/en-us/sql/relationaldatabases/performance/intelligent-query-processing? view=sql-server-2017

Table Variable Deferred Compilation

Table Variable Deferred Compilation

Table Variable Deferred Compilation

Table Variable Deferred Compilation -- (inequality)

Table Variable Deferred Compilation -- (equality)

Intelligent Query Processing Source: https: //docs. microsoft. com/en-us/sql/relationaldatabases/performance/intelligent-query-processing? view=sql-server-2017

Batch Mode on Rowstore Traditional processing (row mode) Get. Next() ? � Get. Next()

Batch Mode on Rowstore Batch mode processing Get. Next() � � � ? Get. Next()

Batch Mode on Rowstore Batch mode processing • • • Introduced in SQL Server 2012 Benefits analytic operations (join, aggregation, window functions) Benefits processing of large data collections Overhead / startup cost Requires columnstore index on at least one table in query Trickery to get batch mode without columnstore index

Batch Mode on Rowstore Batch mode on rowstore • New in SQL Server 2019 • Batch mode without columnstore index – no trickery! • “Interesting” tables used? • CTP 2. 2: >= 131, 702 rows • “Interesting” operations used? • CTP 2. 2: join, aggregation, windows aggregate with >= 131, 702 rows • Compare estimated cost • No non-supported features

Batch Mode on Rowstore Batch mode on rowstore • New in SQL Server 2019 • Batch mode without columnstore index – no trickery! • Limitations • In-memory tables and indexes • Can only be read in row mode, rest of plan can still use batch mode • LOB data, XML, spatial, full-text search, cursors • No batch mode at all

Batch Mode on Rowstore Batch mode on rowstore • • New in SQL Server 2019 Batch mode without columnstore index – no trickery! Limitations Sometimes batch mode can actually be slower than row mode • Many of these cases will improve before RTM • But … do test and monitor your workloads!

Intelligent Query Processing Source: https: //docs. microsoft. com/en-us/sql/relationaldatabases/performance/intelligent-query-processing? view=sql-server-2017

Scalar UDF Inlining Scalar user-defined functions • Good for development • • Code encapsulation Code reuse • Bad for performance • • • Executed once per row Optimizer crippled No parallelism

Scalar UDF Inlining The solution: FROID • Runs at parse time • Tries to convert UDF into equivalent query expression • (In internal represenatation) • Does some optimization • (constant folding, dead code elimination, and others) • Injects this with APPLY in query • Result goes to Query Optimizer

Scalar UDF Inlining Example

Scalar UDF Inlining FROID equivalent (simplified!)

Scalar UDF Inlining FROID execution plan (CTP 2. 2)

Scalar UDF Inlining FROID execution plan (CTP 3. 0)

Scalar UDF Inlining FROID performance (measured on RC 1) • Old behavior (compatibility level 140 or lower) • 10 executions took 8. 6 seconds • FROID enabled (compatibility level 150) • 10 executions took 1. 7 seconds

Scalar UDF Inlining Limitations of FROID • Limitations on UDF itself • No functions that can change between calls • Time-related, e. g. GETDATE(), CURRENT_TIMESTAMP, … • Affects state for future call, e. g. NEWSEQUENTIALID(), RAND() , … • No loops (WHILE) in UDF code • No table variables used • Check sys. sql_modules. is_inlineable to verify if UDF qualifies

Scalar UDF Inlining Limitations of FROID • Limitations on UDF itself • Limitations on how/where UDF is used • • UDF itself not used in GROUP BY Nesting and recursion are partly supported • Replacement stops at certain level

Scalar UDF Inlining x o d Limitations of FROID D I • Limitations on UDF itself O R • Limitations on how/where UDF is used /F m o c. l r u y n i t / / : p t ht • Actual list is too long to include here • Check full documentation on MSDN

Intelligent Query Processing Source: https: //docs. microsoft. com/en-us/sql/relationaldatabases/performance/intelligent-query-processing? view=sql-server-2017

Approximate Count Distinct Sometimes, a close estimate is “good enough” • Can be very beneficial for some specific problems • For example “how many unique visitors on our web site this month? ” • • (On a site that logs 10 million pageviews per day) We care about trend, not about the exact correct number

Approximate Count Distinct Standard approach (using COUNT DISTINCT)

Approximate Count Distinct New alternative (using APPROX_COUNT_DISTINCT)

Approximate Count Distinct How does it work? • Hyper. Log algorithm (based on Flajolet-Martin algorithm) • Flajolet-Martin: • • Hash to get “pseudo-random” value for each input value Find number of zeroes after last one in the binary representation of the hash • • Track highest number of zeroes in the input • • (Can also use left-most zeroes, left-most ones, etc. – just a choice) (50% of values end in 1 / 25% in 10 / 12. 5% in 100 / 6. 25% in 1000 / etc) The higher this number, the less likely – so probably more distinct values

Approximate Count Distinct How does it work? • Hyper. Log algorithm (based on Flajolet-Martin algorithm) • Flajolet-Martin • Weaknesses of Flajolet-Martin • • High variance (even single row can have value that hashes to 0 x 100000) Hash collisions can also cause incorrect results

Approximate Count Distinct How does it work? • • Hyper. Log algorithm (based on Flajolet-Martin algorithm) Flajolet-Martin Weaknesses of Flajolet-Martin Hyper. Log • • Divides input in separate subsets (based on first bits in binary hash) Computes Flajolet-Martin for each subset Final result based on harmonic mean of the results per subset Error margin: within 2% for at least 97% of all use cases • (In other words: 3% chance to be more than 2% wrong)

Intelligent Query Processing Source: https: //docs. microsoft. com/en-us/sql/relationaldatabases/performance/intelligent-query-processing? view=sql-server-2017

THE END Questions? Email: hugo@per. Fact. info Twitter: @Hugo_Kornelis https: //sqlserverfast. com/presentations/from-adaptive-to-intelligent-query-processing-in-sql-2019/ https: //sqlserverfast. com/presentations/deep-dive-into-adaptive-query-processing/

Global Venue Silver Gold Platinum BIG Thanks to SQLSat. Madrid sponsors

THE END Questions? Email: hugo@per. Fact. info Twitter: @Hugo_Kornelis https: //sqlserverfast. com/presentations/from-adaptive-to-intelligent-query-processing-in-sql-2019/ https: //sqlserverfast. com/presentations/deep-dive-into-adaptive-query-processing/