Shulou(Shulou.com)06/01 Report--

A handsome SQL was thrown into the deep TCP tunnel by an ignorant teenager. The teenager waited hard, but he didn't come back with a full load. Therefore, the teenager was angry and painstakingly asked for a good code from Du Niang, but what came was timeout…

If you're experiencing these painful waits, it's time to learn about execution planning; if you think you know execution planning, it's time to read about SQL Server 2017 's new feature, adaptive query processing , which I poorly translate as "adaptive query processing."

Before explaining the concept, it is important to have a clear understanding of memory allocation. After a query is executed, detailed memory allocation metrics and statistics are appended to its execution plan attribute. These memory allocation metrics and statistics are estimated memory allocation and optimal memory size before execution and allocated memory size during operation, respectively.

If the initial memory allocation estimate is inaccurate, during execution, reasonable execution memory will not be allocated, resulting in frequent spill to tempdb during the entire query period, caching data that cannot be accommodated by the current memory space into tempdb, using hard disk IO to cache data, which is much less efficient than memory storage. Therefore, it is necessary to ensure that the estimated data size and memory requirements are accurate before execution. Statistics need to be updated in real time so that pre-allocated memory is accurate.

Brentozar has an example that explains and solves this problem:

https://www.brentozar.com/blitzcache/tempdb-spills/

In other words, allocate enough memory for the query to be executed so that the data needed to execute the query can be processed in memory without spilling to disk. So the query speed is fast.

If a spill occurs, there will be a system prompt, which must rely on the implementation plan to spread the catch.

Before SQL Server 2017 came out, DBAs needed knowledge to be aware of and master how to handle such things. But after SQL Server 2017, as an ordinary developer, you can completely ignore this kind of problem, because adaptive query processing has helped us optimize this kind of SQL.

As mentioned above, after SQL execution is complete, the execution plan is cached along with the execution context. Adaptive Query Processing introduces Batch Mode memory grant

feedback. Through checking the cache attributes of the execution plan, the execution engine can find out whether a spill occurred during the execution of the request. In case of a spill, the engine will reevaluate the execution plan. Once a spill caused by the expiration of statistical information is found, the execution plan will be reevaluated with the latest statistical information, and the memory allocation strategy in the execution plan will be updated. Either reduce memory allocation and increase memory requirements in concurrency, or increase memory. Reduce the probability of spillage. These checks are fed back to the memory grant feedback, which updates the execution plan cache with the latest policies.

The above is an automatic processing feature of Batch Mode Memory Grant Feedback in Adaptive Query Processing, which is called memory grant feedback sizing. In addition, SQL Server 2017 brings more intelligent optimization strategies to automate some of the work of DBAs.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.