什么是執(zhí)行計(jì)劃
執(zhí)行計(jì)劃:是查詢優(yōu)化器分析語(yǔ)句后，生成的一種確定性訪問(wèn)所需數(shù)據(jù)的最高效方式裸卫。
執(zhí)行計(jì)劃的輸入：
- 查詢語(yǔ)句文本
- 相關(guān)表, 約束, 索引定義
- 數(shù)據(jù)庫(kù)的統(tǒng)計(jì)信息
- 提示
- 數(shù)據(jù)庫(kù)的優(yōu)化器參數(shù)設(shè)置
執(zhí)行計(jì)劃定義了：
訪問(wèn)源表的順序
數(shù)據(jù)庫(kù)服務(wù)器一般可以按許多不同的序列訪問(wèn)基表以生成結(jié)果集仿贬。用于從每個(gè)表提取數(shù)據(jù)的方法
訪問(wèn)每個(gè)表中的數(shù)據(jù)一般也有不同的方法。 如果只需要有特定鍵值的幾行墓贿，數(shù)據(jù)庫(kù)服務(wù)器可以使用索引瞧捌。 如果需要表中的所有行附较，數(shù)據(jù)庫(kù)服務(wù)器則可以忽略索引并執(zhí)行表掃描。 如果需要表中的所有行葵礼，而有一個(gè)索引的鍵列在 ORDER BY中柱告，則執(zhí)行索引掃描而非表掃描可能會(huì)省去對(duì)結(jié)果集的單獨(dú)排序苛萎。 如果表很小硅确，則對(duì)該表的幾乎所有訪問(wèn)來(lái)說(shuō)烧颖，表掃描可能都是最有效的方法。用于計(jì)算的方法代嗤，以及如何對(duì)每個(gè)表中的數(shù)據(jù)進(jìn)行篩選、聚合和排序的方法
從表訪問(wèn)數(shù)據(jù)時(shí)缠借，可以使用不同的方法對(duì)數(shù)據(jù)進(jìn)行計(jì)算干毅，例如，計(jì)算標(biāo)量值泼返，以及對(duì)查詢文本中定義的數(shù)據(jù)進(jìn)行聚合和排序（例如硝逢，使用 GROUP BY 或 ORDER BY 子句時(shí)），以及如何篩選數(shù)據(jù)（例如在使用 WHERE 或 HAVING 子句時(shí)）绅喉。
執(zhí)行計(jì)劃生成
從潛在的多個(gè)可能的計(jì)劃中選擇一個(gè)執(zhí)行計(jì)劃的過(guò)程稱為“優(yōu)化”渠鸽。
SQL Server 查詢優(yōu)化器是基于成本的優(yōu)化器。 就所使用的計(jì)算資源量而言柴罐，每個(gè)可能的執(zhí)行計(jì)劃都具有相關(guān)成本徽缚。 查詢優(yōu)化器必須分析可能的計(jì)劃并選擇一個(gè)預(yù)計(jì)成本最低的計(jì)劃。
查詢優(yōu)化器不會(huì)分析所有的可能組合革屠， 而是使用復(fù)雜的算法查找一個(gè)執(zhí)行計(jì)劃：其成本合理地接近最低可能成本凿试。
SQL Server 查詢優(yōu)化器不只選擇資源成本最低的執(zhí)行計(jì)劃，還選擇能將結(jié)果最快地返回給用戶且資源成本合理的計(jì)劃似芝。
SQL Server 查詢優(yōu)化器在估計(jì)用于從表或索引中提取信息的不同方法所需的資源成本時(shí)那婉，依賴于分發(fā)內(nèi)容統(tǒng)計(jì)信息。如果索引統(tǒng)計(jì)信息不是當(dāng)前的党瓮，則查詢優(yōu)化器可能無(wú)法對(duì)表的當(dāng)前狀態(tài)做出最佳選擇详炬。
查看執(zhí)行計(jì)劃
- 估計(jì)的執(zhí)行計(jì)劃
不實(shí)際運(yùn)行SQL語(yǔ)句，不包含任何運(yùn)行時(shí)信息
- 實(shí)際執(zhí)行計(jì)劃
實(shí)際運(yùn)行SQL語(yǔ)句寞奸，包含處理的行數(shù)呛谜、經(jīng)過(guò)的時(shí)間在跳、CPU、內(nèi)存 和 I/O 使用情況等
查看執(zhí)行計(jì)劃
顯示分析呻率、編譯和執(zhí)行各語(yǔ)句所需的毫秒數(shù)
SET STATISTICS TIME ON
當(dāng) SET STATISTICS TIME 為 ON 時(shí)硬毕，會(huì)顯示語(yǔ)句的時(shí)間統(tǒng)計(jì)信息。為 OFF 時(shí)礼仗，不顯示時(shí)間統(tǒng)計(jì)信息吐咳。
SET STATISTICS TIME 的設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置，而不是在分析時(shí)設(shè)置元践。
若要使用 SET STATISTICS TIME韭脊，用戶必須具有執(zhí)行 Transact-SQL 語(yǔ)句的相應(yīng)權(quán)限。但不需要 SHOWPLAN 權(quán)限单旁。
顯示語(yǔ)句生成的磁盤(pán)活動(dòng)量的信息
SET STATISTICS IO ON
掃描計(jì)數(shù)：索引或表掃描次數(shù)
邏輯讀然Ω帷：數(shù)據(jù)緩存中讀取的頁(yè)數(shù)
物理讀取：從磁盤(pán)中讀取的頁(yè)數(shù)
預(yù)讀： 為進(jìn)行查詢而放入緩存的頁(yè)數(shù)
lob邏輯讀认蠡搿：從數(shù)據(jù)緩存讀取的頁(yè)數(shù)蔫饰。 包括 text、ntext愉豺、image篓吁、varchar(max)、 nvarchar(max)蚪拦、varbinary(max) 或列存儲(chǔ)索引頁(yè)
lob物理讀日燃簟：從磁盤(pán)讀取的頁(yè)數(shù)。 包括 text驰贷、ntext盛嘿、image、varchar(max)括袒、 nvarchar(max)次兆、varbinary(max) 或列存儲(chǔ)索引頁(yè)
lob預(yù)讀：為進(jìn)行查詢而放入緩存的頁(yè)數(shù)。 包括 text锹锰、ntext类垦、image、varchar(max)城须、 nvarchar(max)蚤认、varbinary(max) 或列存儲(chǔ)索引頁(yè)
- 按照從右到左、從上到下的方式閱讀執(zhí)行計(jì)劃糕伐。
- 箭頭寬度與箭頭數(shù)據(jù)量成正比砰琢。
- 最左側(cè)的運(yùn)算符中可以查看該執(zhí)行計(jì)劃的緩存大小。
- 表運(yùn)算符中估計(jì)行數(shù)與實(shí)際行數(shù)是否一致（統(tǒng)計(jì)信息是否過(guò)期）
- 執(zhí)行 Transact-SQL 語(yǔ)句的適當(dāng)權(quán)限。
- 對(duì)包含 Transact-SQL 語(yǔ)句所引用對(duì)象的所有數(shù)據(jù)庫(kù)的 SHOWPLAN 權(quán)限陪汽。
運(yùn)算符解釋：
https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms191158(v=sql.105)
看懂執(zhí)行計(jì)劃
嵌套循環(huán)聯(lián)接（Nested Loops Join）
嵌套循環(huán)聯(lián)接使用一個(gè)聯(lián)接輸入作為外部輸入表训唱，另一個(gè)作為內(nèi)部輸入表。外部輸入表是執(zhí)行計(jì)劃中上面的輸入挚冤，而內(nèi)部輸入表是下面的輸入表况增。外部循環(huán)逐行消費(fèi)外部輸入表。內(nèi)部循環(huán)為每個(gè)外部行執(zhí)行一次训挡，搜索內(nèi)部輸入表的匹配行澳骤。
哈希聯(lián)接（Hash Join）
哈希聯(lián)接運(yùn)算符通過(guò)計(jì)算其生成輸入中每行的哈希值生成哈希表。對(duì)于聯(lián)接澜薄，使用第一個(gè)（頂端）輸入生成哈希表为肮，使用第二個(gè)（底端）輸入探測(cè)哈希表。按聯(lián)接類型規(guī)定的模式輸出匹配項(xiàng)（或不匹配項(xiàng)）肤京。
合并聯(lián)接（Merge Join）
合并聯(lián)接要求兩個(gè)聯(lián)接輸入在合并列上排序颊艳，這將在聯(lián)接條件中定義。如果兩個(gè)聯(lián)接上有索引忘分，那么聯(lián)接輸入由該索引排序棋枕。因?yàn)槊總€(gè)聯(lián)接輸入都被排序，合并排序從每個(gè)輸入得到一行比較是否相等妒峦，如果它們相等戒悠，匹配的行被生成。這個(gè)過(guò)程重復(fù)直到所有行都被處理舟山。
分析執(zhí)行計(jì)劃及優(yōu)化
隱式轉(zhuǎn)換
SELECT TOP 10000 u.UserCode,u.UserName,o.HostIP,o.FunctionName
FROM myUser u
INNER JOIN myOperLog o ON o.BUGUID = u.BUGUID
WHERE o.HostIP='192.168.0.34'
AND u.UserName='售樓部'
書(shū)簽查找（鍵查找）
臨界點(diǎn)是查詢計(jì)劃“提示”從尋找不覆蓋的非聚集索引到掃描聚集索引或堆的閾值。
基本公式并不是硬性的規(guī)則卤恳，因?yàn)檫€有其他各種影響因素累盗，它是這樣的：
- 當(dāng)估計(jì)行數(shù)超過(guò)表中頁(yè)數(shù)的33%時(shí)，通常會(huì)出現(xiàn)聚集索引(或表)掃描突琳。
- 當(dāng)估計(jì)行數(shù)低于表中頁(yè)的25%時(shí)若债，通常會(huì)出現(xiàn)非群集查找加鍵查找。
- 在25%到33%之間拆融，它可以走任何一條路蠢琳。
參數(shù)嗅探
重編譯
根據(jù)數(shù)據(jù)庫(kù)新?tīng)顟B(tài)的不同，數(shù)據(jù)庫(kù)中的某些更改可能導(dǎo)致執(zhí)行計(jì)劃效率降低或無(wú)效镜豹。
SQL Server 將檢測(cè)到使執(zhí)行計(jì)劃無(wú)效的更改傲须，并將計(jì)劃標(biāo)記為無(wú)效。
此后趟脂，必須為執(zhí)行查詢的下一個(gè)連接重新編譯新的計(jì)劃泰讽。
導(dǎo)致計(jì)劃無(wú)效的情況包括：
- 對(duì)查詢所引用的表或視圖進(jìn)行更改（ALTER TABLE 和 ALTER VIEW）。
- 對(duì)單個(gè)過(guò)程進(jìn)行更改，這將從緩存中刪除該過(guò)程的所有計(jì)劃 (ALTER PROCEDURE)已卸。
- 對(duì)執(zhí)行計(jì)劃所使用的任何索引進(jìn)行更改佛玄。
- 對(duì)執(zhí)行計(jì)劃所使用的統(tǒng)計(jì)信息進(jìn)行更新，這些更新可能是從語(yǔ)句（如 UPDATE STATISTICS）顯式生成累澡，也可能是自動(dòng)生成的梦抢。
- 刪除執(zhí)行計(jì)劃所使用的索引。
- 顯式調(diào)用 sp_recompile愧哟。
- 對(duì)鍵進(jìn)行大量更改（這些更改是對(duì)查詢所引用的表進(jìn)行修改的其他用戶執(zhí)行 INSERT 或 DELETE 語(yǔ)句所產(chǎn)生的）奥吩。
- 對(duì)于帶觸發(fā)器的表，如果插入的或刪除的表內(nèi)的行數(shù)顯著增長(zhǎng)翅雏。
- 使用 WITH RECOMPILE 選項(xiàng)執(zhí)行存儲(chǔ)過(guò)程圈驼。
執(zhí)行計(jì)劃分析優(yōu)化步驟
1、找到邏輯讀取次數(shù)最多的表望几、執(zhí)行計(jì)劃中開(kāi)銷最大的查詢
2绩脆、在圖形執(zhí)行計(jì)劃中找到對(duì)應(yīng)的表的運(yùn)算符，不是聚集索引查找或索引查找的橄抹，如堆表靴迫、聚集索引掃描、鍵查找楼誓、一個(gè)表使用了2個(gè)索引關(guān)聯(lián)取數(shù)等玉锌，結(jié)合表的數(shù)據(jù)量和現(xiàn)有索引情況嘗試優(yōu)化，如新建缺失索引（執(zhí)行計(jì)劃中建議的缺失索引不應(yīng)照搬疟羹，應(yīng)分析后再創(chuàng)建或修改現(xiàn)有索引主守，同時(shí)應(yīng)刪除缺失索引中的聚集鍵），修改索引為包含索引等（表數(shù)據(jù)量小的情況下榄融，索引掃描可能更優(yōu)于索引查找）
3参淫、索引無(wú)法提高性能的情況下，檢查估計(jì)行數(shù)與實(shí)際行數(shù)的差別愧杯，更新統(tǒng)計(jì)信息涎才。還應(yīng)分析執(zhí)行時(shí)間長(zhǎng)的SQL語(yǔ)句，修改SQL語(yǔ)句的寫(xiě)法力九，將SELECT子句中的子查詢耍铜、自定義標(biāo)量函數(shù)（串行）、APPLY運(yùn)算符等改寫(xiě)為JOIN聯(lián)接跌前，盡量減少中間結(jié)果的數(shù)據(jù)量棕兼，如使用CTE、臨時(shí)表等抵乓，盡量少用表變量程储，表變量不創(chuàng)建統(tǒng)計(jì)信息蹭沛，會(huì)導(dǎo)致執(zhí)行計(jì)劃不準(zhǔn)確
4、重復(fù)步驟1-3章鲤，直至圖形執(zhí)行計(jì)劃中每個(gè)表對(duì)應(yīng)的運(yùn)算符基本為聚集索引查找或索引查找
