From adaptive to intelligent: query processing in SQL Server 2019

1. Hugo Kornelis From adaptive to intelligent:query processing in SQL Server 2019

2. Hugo Kornelis I make SQLServerFast.com • 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) • I make SQL Server Fast

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

4. Query Processing • Query optimization Query

5. Query Processing • Query optimization • Query execution Query Cardinality estimates Reality

6. Adaptive Query Processing • SQL Server 2017

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

8. Adaptive Intelligent Query Processing • SQL Server 2019

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

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

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

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

13. 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

14. Memory Grant Feedback • 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

15. Memory Grant Feedback • 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

16. Memory Grant Feedback • 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)

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

18. Table Variable Deferred Compilation

19. Table Variable Deferred Compilation

20. Table Variable Deferred Compilation

21. Table Variable Deferred Compilation -- (inequality)

22. Table Variable Deferred Compilation -- (equality)

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

24. Batch Mode on Rowstore • Traditional processing (row mode) GetNext() GetNext() ?  GetNext() ?  GetNext()

25. Batch Mode on Rowstore • Batch mode processing ? GetNext() GetNext()               

26. 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

27. 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

28. 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

29. 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!

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

31. 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

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

33. Scalar UDF Inlining • Example

34. Scalar UDF Inlining • FROID equivalent (simplified!)

35. Scalar UDF Inlining • FROID execution plan

36. Scalar UDF Inlining • FROID performance • Old behavior (compatibility level 140 or lower) • 10 executions took 10.8 seconds • FROID enabled (compatibility level 150) • 10 executions took 2.1 seconds

37. 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

38. 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

39. Scalar UDF Inlining • Limitations of FROID • Limitations on UDF itself • Limitations on how/where UDF is used • Actual list is too long to include here • Check full documentation on MSDN http://tinyurl.com/FROID-dox

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

41. Approximate Count Distinct • Sometimes, a close estimate is “good enough” • Can be very beneficial for somespecific problems • For example “how many uniquevisitors on our web site this month?” • (On a site that logs 10 million pageviewsper day) • We care about trend, not about theexact correct number

42. Approximate Count Distinct • Standard approach (using COUNT DISTINCT)

43. Approximate Count Distinct • New alternative (using APPROX_COUNT_DISTINCT)

44. Approximate Count Distinct • How does it work? • HyperLogLog 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 • (Can also use left-most zeroes, left-most ones, etc. – just a choice) • Track highest number of zeroes in the input • (50% of values end in 0 / 25% in 00 / 12.5% in 000 / etc) • The higher this number, the less likely – so probably more distinct values

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

46. Approximate Count Distinct • How does it work? • HyperLogLog algorithm (based on Flajolet-Martin algorithm) • Flajolet-Martin • Weaknesses of Flajolet-Martin • Flajolet-Martin  HyperLog  HyperLogLog • 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)

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

48. T H E E N D Questions? • Email: hugo@perFact.info • Twitter: @Hugo_Kornelis https://sqlserverfast.com/presentations/from-adaptive-to-intelligent-query-processing-in-sql-2019/