- [Instructor] Starting next chapter is all about some of the optimizer enhancements that come in in SQL Server 2017. I'd like to first step back and talk a little bit about the changes in the way Microsoft has developed SQL Server. If you're not familiar, Microsoft has an Azure SQL database, which is the same code as the on-premises SQL Server and Azure Amazon VM that you would run. The difference being that they manage the service fully, so they're able to roll out new features to it sooner, and it's a slightly different version of the code in that it's ahead of the code that you're probably running on-prem.
This allows them to make changes. Additionally, it allows them to collect a lot of telemetry. Also, they're collecting telemetry on on-prem SQL Server. Before you worry about telemetry and security, Microsoft has extensively cleared this with their legal department and meets all sorts of audit specifications, so they're not looking at your data. They're looking at your metadata, however, so they're able to see where performance problems occur, what sorts of wait types are happening, where performance problems occur, and this has let them focus on how they can improve the query processor.
If you've seen the query store feature in SQL Server 2016 and 17, that was some of the inspiration for that, to help identify those problems. But in looking at that telemetry, they've identified some other specific problems that they wanted to work on, and they can't always just fix the optimizer, 'cause the problem with just fixing the optimizer with a blunt force means a lot of queries are going to get faster, but some percentage of queries may get slower. A smaller percentage of queries may get a lot slower. So if we think about the way the query optimizer works, after SQL Server parses your query to ensure that it has valid syntax, your query is then optimized.
And what does that mean? It means SQL Server is generating an execution plan for your query, and during that optimization process, there's a process that's known as the cardinality estimator that comes in. This is a feature that was updated for SQL Server 2014. Prior to SQL Server 2014, SQL Server used the same cardinality estimation model since all the way back to SQL Server 7. And what this does is it estimates the number of rows processed in each step in a given execution plan. Cardinality estimation uses a combination of some statistical modeling techniques, and it uses your statistics, which is one of the reasons why it's so important that your statistics are up to date in your database.
And it also makes some assumptions, and when estimates are accurate enough, SQL Server makes informed decisions around the order of the operations that it wants to choose and what sort of operators it wants to choose, and that's how your execution plans get generated. So what happens when those estimates are inaccurate? So maybe you didn't have up to date stats or SQL Server made a bad decision. So this means you can make poor decisions regarding algorithm selection and the order of operations performed in your execution plan.
So you can get slow query response times due to bad plans. You can get extensive resource utilization, so things like CPU, memory, and IO can be really bad. You can spill to disk. One of the things that happens in an execution plan is that when you have a sort operation or you're joining two tables together, SQL Server asks for a certain amount of memory, and if it asks for too much or too little you can have a spill or inefficient use of resources. And when you spill, that means SQL Server's writing to tempDB, which is kind of expensive in terms of performance.
Even if you're on a really fast disk, it still orders a magnitude slower than writing directly to memory. Overall, this just gives us reduced throughput and concurrency, all of these things combined. So there are several reasons why these problems occur, and some of them are missing or stale statistics or bad parameter sniffing, which you'll learn about in this chapter. And there are some places where SQL Server simply makes fixed-estimate guesses, and these would be multi-statement table value functions, table variables, and x query references. So SQL Sever 2017 introduces something known as adaptive query processing, and these are processes that allow for a SQL server to defer optimization.
For certain query operators, there's three specific scenarios that we're going to go through until it has more information about that operation. This is currently only supported in BATCH execution mode, so you have to be using, at least in SQL Server 2017, a column store index somewhere in your query for this to happen. And these are enabled by turning on compatibility mode 140. So this is the compatibility mode for SQL 2017. Our three types that we have are batch mode memory grant feedback, and what happens is a query's post execution plan includes the minimum amount of memory required that it needs to execute.
So the minimum it needs to complete, and then the ideal grant that it would need to have all of those rows fit in memory. So performance suffers when memory is too large or too small, and what happens here for excessive grants, so if SQL Server sees that the granted memory is more than two times the size of the actual used memory, memory grant feedback will recalculate the memory grant and update that cached execution plan. So it's doing this with a plan in the plan cache, it's not going to recompile. And plans with memory grants that are under one meg will be skipped calculation.
For plans that don't have enough memory and result in a spill to tempDB, memory grant feedback will trigger a recalculation of the memory grant, and the spill events are reported to memory grant feedback and you can see them via an extended event called spilling report to memory grant feedback. And what SQL Server will do is change the amount of memory, up or down, that's granted to the query in an incremental fashion until it gets to the just right number. The next feature I want to talk about is batch mode adaptive joins, and this enables the choice of a nested loop or hash join operator to be deferred until after the first input has been scanned.
This means the adaptive join uses a threshold to decide when it should switch from a hash join to a nested loop, and vice versa. This allows for a better input and a smarter join decision. Where this will really benefit you is if you have workloads that frequently shift from having large or small input joins, and they do have a little bit higher overhead than a traditional nested loop plan in terms of memory, but the benefit should outweigh the cost here. Finally, the last piece we have is interleaved execution for multi-statement table-valued functions.
What does this mean? If you're using multi-statement table-valued functions, SQL Server has always introduced a cardinality of one, which means one row, and if you're in SQL Server 2014 or 2016, they have a fixed cardinality guess of 100. So if you have a lot more rows than that, you're not going to get a good execution plan. So what SQL Server 2017 does is if you encounter a candidate for interleaved execution, it will pause optimization, execute the first part of the plan, and then get the actual number of rows in the multi-statement table-value function, and then create the rest of the downstream plans.
So it's creating the downstream plan in a smarter fashion. That's adaptive query processing. We'll walk through each of these functionalities in detail in their own videos.
- Querying with SQL Graph
- String functions in SQL 2017
- Adaptive query processing
- Docker containers and SQL
- Deploying CLR code with SQL Server on Linux
- Working with SQL Operations Studio
- Using snippets
- Code deployment using GIT