為什么讀懂執(zhí)行計(jì)劃對(duì)你是那么重要

一條俊俏的 SQL 被一個(gè)懵懂的少年，扔向了深遠(yuǎn)的 TCP 隧道，少年苦苦等待，卻遲遲等不來(lái)那滿載而歸。于是少年氣憤，費(fèi)盡苦心從度娘那邊求來(lái)的一手好代碼，等來(lái)的卻是 timeout…

如果你也正在經(jīng)歷著這些苦澀的等待，那么該了解執(zhí)行計(jì)劃了；如果你自認(rèn)為執(zhí)行計(jì)劃已經(jīng)了如指掌，那么你該讀一讀 SQL Server 2017 新特性， adaptive query processing , 我蹩腳的將其翻譯為 “自適應(yīng)查詢處理”。

在講解概念之前，首先要對(duì)內(nèi)存分配要有清晰的理解。一個(gè)查詢請(qǐng)求在執(zhí)行完畢之后，會(huì)有詳細(xì)的內(nèi)存分配指標(biāo)和統(tǒng)計(jì)值附加在它的執(zhí)行計(jì)劃屬性上。這些內(nèi)存分配指標(biāo)和統(tǒng)計(jì)值，分別是執(zhí)行前預(yù)估的內(nèi)存分配和最佳內(nèi)存大小以及運(yùn)行中被分配的內(nèi)存大小。

如果一開(kāi)始內(nèi)存預(yù)估分配不準(zhǔn)確，在執(zhí)行的時(shí)候，就會(huì)分配不到合理的執(zhí)行內(nèi)存，導(dǎo)致整個(gè)查詢期間，頻繁的往 tempdb 里面去做spill（這個(gè)詞真不好翻譯，有緩存溢出的意思，即內(nèi)存裝不下，暫交給 tempdb 存儲(chǔ)的意思）, 將不能被當(dāng)前內(nèi)存空間容納的數(shù)據(jù)緩存到 tempdb 里面，利用硬盤IO來(lái)緩存數(shù)據(jù)，比起內(nèi)存存儲(chǔ)效率差了很多。因此在執(zhí)行前就需要保證預(yù)估的數(shù)據(jù)量大小和需要的內(nèi)存比較精確。 這里需要對(duì)統(tǒng)計(jì)信息 (statistics)做實(shí)時(shí)更新，以便預(yù)分配內(nèi)存準(zhǔn)確。

Brentozar 有個(gè)實(shí)例可以很好的解釋和解決這個(gè)問(wèn)題:
https://www.brentozar.com/blitzcache/tempdb-spills/

換句話來(lái)說(shuō)，對(duì)即將執(zhí)行的查詢，分配足夠多的內(nèi)存，那么該查詢的執(zhí)行所需的數(shù)據(jù)，就完全可以在內(nèi)存中處理，而不會(huì)溢出到硬盤。從而查詢速度就快。

如果發(fā)生 spill 會(huì)有系統(tǒng)提示，這必須依靠執(zhí)行計(jì)劃才能鋪?zhàn)降?

在 SQL Server 2017 出來(lái)之前，需要掌握 DBA 知識(shí)才能意識(shí)和掌握處理這類事情。但 SQL Server 2017 之后，作為普通的一名開(kāi)發(fā)者，完全可以忽略這類問(wèn)題，因?yàn)?adaptive query processing 已經(jīng)幫我們?cè)谀缓髢?yōu)化這類 SQL.

如上所說(shuō)，SQL 執(zhí)行完畢之后，會(huì)將執(zhí)行計(jì)劃與執(zhí)行環(huán)境( execution context)一起緩存。Adaptive Query Processing 引入了 Batch Mode memory grant
feedback.  執(zhí)行引擎通過(guò)對(duì)執(zhí)行計(jì)劃緩存屬性的校驗(yàn)，可以發(fā)現(xiàn)請(qǐng)求的執(zhí)行過(guò)程中，是否發(fā)生了 spill,對(duì)于發(fā)生 spill 的情況，引擎會(huì)對(duì)這份執(zhí)行計(jì)劃做重估，一旦發(fā)現(xiàn)如統(tǒng)計(jì)信息過(guò)期 等導(dǎo)致的 spill, 就會(huì)用最新的統(tǒng)計(jì)信息去重估執(zhí)行計(jì)劃，更新執(zhí)行計(jì)劃中分配內(nèi)存的策略，要么降低內(nèi)存分配提高并發(fā)中內(nèi)存需要，要么提高內(nèi)存，減少 spill 的發(fā)生概率。這些校驗(yàn)都回反饋給 memory grant feedback, 由 它采用最新的策略，去更新執(zhí)行計(jì)劃緩存。

上述講的是自適應(yīng)查詢處理( Adaptive Query Processing)中 Batch Mode Memory Grant Feedback 的一個(gè)自動(dòng)處理特性，被稱為 memory grant feedback sizing. 除此之外，SQL Server 2017 還帶來(lái)了更多的智能優(yōu)化策略， 自動(dòng)化完成 DBA 的部分工作。