{"id":838,"date":"2021-02-08T14:48:39","date_gmt":"2021-02-08T22:48:39","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=838"},"modified":"2021-02-08T14:48:39","modified_gmt":"2021-02-08T22:48:39","slug":"the-positive-impact-of-intelligent-query-processing","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/the-positive-impact-of-intelligent-query-processing\/","title":{"rendered":"The Positive Impact of Intelligent Query Processing"},"content":{"rendered":"

\"Image<\/h2>\n

What happens when you fall off a cliff…<\/h2>\n

It\u2019s 2am.\u00a0 Your lead developer\u2019s phone buzzes\u2026again.\u00a0 She groggily gets out of bed and fires up her laptop.\u00a0 It appears your web platform has become unresponsive. Requests to the backend database are all timing out.\u00a0 Customers across the globe are angry.<\/p>\n

It appears that the database is over-consuming memory.\u00a0 A workload that, just yesterday, operated smoothly is now consuming too many resources, limiting the database bandwidth, and timing out.\u00a0 Your lead developer opens up a tool that allows her to see\u00a0regressed queries<\/a>.\u00a0 Two of them have experienced changes in the way they execute in the past 4 hours, and those two queries are now consuming massively more memory than they once were.\u00a0 This memory consumption is bogging down the system \u2013 not only are the queries much slower, but they are also consuming far more memory and CPU than they had been.\u00a0 This means that the other queries can\u2019t get through.\u00a0 The system is at a complete standstill, even though the database is working overtime.\u00a0 Fortunately, she is able to\u00a0request that the future executions<\/a>\u00a0of these two queries go back to the strategy of 6 hours ago, at least until she and the team can do more investigation tomorrow. She trudges back to bed, hoping to get a few more hours of shuteye before her morning commute.<\/p><\/blockquote>\n

The scenario above describes what I call \u201cFalling off a cliff\u201d.\u00a0 Your database is doing fine.\u00a0 Great even.\u00a0 Then something changes, and performance, response time, everything, changes in a blink of an eye.\u00a0 All the time, databases are changing \u2013 data is being inserted, loaded, archived, updated\u2026etc.\u00a0 Most often, the number of rows in an active database is steadily increasing.\u00a0 Sometimes, the number of rows passes a threshold \u2013 and the way the queries are executed changes.\u00a0 In the general case, this is good.\u00a0 You want your database to adapt to changing data loads and query loads.\u00a0 Unfortunately for the developer in the story above, the change was not smooth, and did not yield better overall workload performance, in fact, things got very bad very quickly.<\/p>\n

In this blog, I will attempt to explain, at a high level, why Query Processing (QP) matters, how it works, what can go wrong when QP makes a mistake, and what exciting features are present in the Azure SQL and SQL Server QP that will improve end-user experience, and ultimately, allow you to use resources smarter.\u00a0 Your developers will be able to get work done faster and with fewer middle of the night emergencies.<\/p>\n

Why Good QP Matters<\/h2>\n

Typically, QP is silent and unseen.\u00a0 Not thinking about QP means things are going well.\u00a0 It\u2019s only when you see a sudden degradation in performance, or when queries stop completing, that you must think about QP.\u00a0 While every database requires some planning and setup, once that is complete you want business to move forward without investing substantial time fixing issues or making tweaks.\u00a0 To this goal, having a good quality QP is essential.<\/p>\n

A good QP:<\/p>\n

    \n
  1. Expands the capability of the database<\/li>\n
  2. Uses resources effectively<\/li>\n
  3. Reduces manual intervention<\/li>\n
  4. Adapts to changing conditions<\/li>\n<\/ol>\n

    Expands the capability of the database:\u00a0<\/strong>When a query doesn\u2019t undergo a good optimization process, it may never complete (or will take too long to complete to be reasonable), but, with good optimization, the same query can complete quickly.\u00a0 This means that a good optimizer allows queries that may be impossible, or require extensive re-writing, or even need multiple execution steps in an un-optimized environment.<\/p>\n

    Uses resources effectively:\u00a0<\/strong>When an optimizer is able to grant the correct amount of resources to a query, parallelization can occur.\u00a0 If a query is given too many resources, it could block other queries from executing simultaneously.\u00a0 If given too few, it could execute too slowly.<\/p>\n

    Reduces manual intervention:\u00a0<\/strong>Oftentimes, when a query runs too slowly (or doesn\u2019t complete), someone need to go do some work, or tweaks, etc. to fix the problem.\u00a0 Not only is manual intervention tedious, it can be error prone and may not adapt well to changing conditions inside the database.\u00a0 Further, sometimes these tweaks can slow down other operations.<\/p>\n

    Adapts<\/strong>\u00a0to changing conditions:<\/strong>\u00a0As table sizes change, or as plan parameters change, the query plan will often automatically adjust to these new conditions.\u00a0 These changes reflect the most optimal plan for the query given the database as it is now.\u00a0 If QP only used the very first plan it created for a query, it would be blind to the changes in data or plan parameters, and thus may executions would be far from optimal.<\/p>\n

    \"Plan<\/a>
    Figure 1: Plan space diagram showing different plans<\/figcaption><\/figure><\/p>\n

     <\/p>\n

    You can see in Figure 1 a diagram called a \u201cplan space\u201d diagram.\u00a0 As the number of rows selected by a query from various tables increases, the plan changes.\u00a0 The diagram in figure 1 is just a cartoon, with the intent of showing what the\u00a0Plan Space<\/em>\u00a0for a given query might be, as the number of rows being processed increases.\u00a0 In this diagram, each colored area represents a different plan for the same query, as the number of rows being processed changes. \u00a0The plan space for this imaginary query and tables contains 7 plans, each of which is ideal based on the number of rows being processed.<\/p>\n

    The general trend with plan spaces is that there are more plans when the number of rows is smaller, and fewer plans when the number of rows gets very large. \u00a0The reason for this trend is that QP has a limited set of tools \u2013 some work better for large data sets, some for smaller.\u00a0 When the number of rows is small, tools can be combined and balanced to create different plans.\u00a0 However, when the data size becomes large enough, the QP chooses all of the best operators for large data.\u00a0 For example, nested loops join is a join technique that is well-suited to smaller data sets.\u00a0 Hash join is preferred for processing larger numbers of rows.\u00a0 It is easy to keep making the data size larger, but once the QP is choosing hash join (and other operational equivalents for large data), the options for further improvement are limited.<\/p>\n

    What a good QP does<\/h2>\n

    But how does a good QP even find this \u201cplan space\u201d and execute queries?\u00a0 First, the QP needs to determine\u00a0how<\/em>\u00a0it is going to execute a query, then it needs to\u00a0execute efficiently<\/em>.<\/p>\n

    Upon receipt of a query, the QP first determines\u00a0how<\/em>\u00a0the query will be executed.\u00a0 The result of this step is a query plan.\u00a0 \u00a0A few of the steps involved are:<\/p>\n

    Reorganizing the query:\u00a0<\/strong>\u00a0Some operators can be moved around to occur at different times during execution for faster results, but with no difference in the result set (semantically equivalent).<\/p>\n

    Choosing specific execution strategies:\u00a0<\/strong>With knowledge about the various operators and by using statistics about the data, the optimizer can choose, for example, the way a join will be executed, considering memory usage and the estimated size of the data.<\/p>\n

    To do these things, may underlying pieces play a part, the details of which I will skip here.\u00a0 A few examples would be: selecting good indexes to operate on and estimating the data size for each step of execution.\u00a0\u00a0 In Azure SQL and SQL Server, you can optimize a query without executing it.\u00a0 This way you can see the query plan without waiting for execution to complete.\u00a0 Once the QP creates a plan for a query, that plan can be executed multiple times.\u00a0 Since these plans adjust based on the estimated size of the data, it makes sense that there are a number of different plans that could be selected for the same query.<\/p>\n

    Query execution involves manipulating the rows from the source tables through the query and into the result set.\u00a0 Query execution accesses the indexes, performs the joins, filters the rows according to the query plan, and ultimately provides as output the qualifying rows from the query.<\/p>\n

    What can go wrong?<\/h2>\n

    Sometimes, however, query processing makes reasonable, understandable choices that simply don\u2019t work once the query starts to execute.\u00a0 This is typically due to lack of complete information, or due to the necessary assumptions that the must be made: finding a plan works well in the general case, even if it doesn\u2019t work well in a specific case.<\/p>\n

    At the highest level, either query processing has chosen one or more operators incorrectly based on the actual size of the data (plan generation relies on estimates only), or it has requested the wrong amount of memory for the plan to execute with.\u00a0 Both problems come from the generalizations and assumptions that are necessary for planning a query without having executed it<\/p>\n

    \"Plan<\/a>
    Figure 2: Plan space diagram showing transitions between plans<\/figcaption><\/figure><\/p>\n

    Another thing that can go wrong is when plans change gracelessly.\u00a0 As you can see if Figure 2, we should take note of the transition points between plans.\u00a0 In some cases, users experience a performance change at those points.\u00a0 I often refer to it as going off a cliff.\u00a0 You are chugging along with a decent plan, the data size changes, and boom! Now your query takes a lot longer, or a lot more memory to complete.<\/p>\n

    Intelligent QP to the Rescue<\/h2>\n

    Azure SQL and SQL Server have released a number of improvements that improve upon this standard workflow in an attempt to lessen or correct common errors.<\/p>\n

    Briefly,\u00a0Intelligent QP<\/a>\u00a0can:<\/p>\n

      \n
    1. Remember past query execution\u2019s memory consumption and allocate the proper amount in future executions using memory grant feedback\u00a0(<\/em>1<\/em><\/a>,\u00a0<\/em>2<\/em><\/a>).<\/em><\/li>\n
    2. Adjust on the fly when a join operator turns out to be the wrong one for the job using\u00a0Adaptive Join.<\/em><\/a><\/li>\n
    3. Postpone decision making during optimization while it gathers more information by using both\u00a0Interleaved execution<\/em><\/a>\u00a0and\u00a0Table variable deferred computation<\/em><\/a>.<\/em><\/li>\n
    4. Invest\u00a0less\u00a0<\/em>time and\u00a0fewer\u00a0<\/em>resources optimizing when the exact answer is not needed with\u00a0Approximate QP<\/em><\/a>.<\/em><\/li>\n
    5. Use\u00a0Scalar UDF inlining<\/em><\/a>\u00a0replace function calls with the body of the function, thus allowing for more optimization.<\/li>\n
    6. Execute things in batches, even when data is not compressed, using\u00a0Batch mode on row store<\/em><\/a>.<\/em><\/li>\n<\/ol>\n

      Another way of looking at these elements of Intelligent QP is that they are designed to:<\/p>\n