1 / 48

Intelligent Query Processing in SQL Server 2019: Enhancing Performance and Optimization

Learn about intelligent query processing in SQL Server 2019, adaptive to intelligent query processing, memory grants feedback, and batch mode operations. Enhance query performance with detailed information and resources.

katiev
Download Presentation

Intelligent Query Processing in SQL Server 2019: Enhancing Performance and Optimization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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/

More Related