最近由於SQL2008R2即將EOS，所以陸陸續續將資料庫升級到SQL2017的版本。而升級完成後有部分功能居然查詢時間太久導致Web TimeOut，經一番查詢後發現是新版SQL Server已在SQL2014採用新的基數估計(Cardinality Estimation簡稱CE)。而某些查詢會因為採用新版CE來估計統計反而誤用效能不好的執行計畫，官網分析有下列特性的查詢就可能發生查詢變慢的狀況。
既然是新版CE造成的效能問題，那是不是有合適的解決方案來解決這樣的問題呢? 下列是微軟提供解決的幾種解決方式。
-
調低資料庫相容性層級，只要將相容性層級調降到110(含)以下，SQL就會改採舊版的CE來運作。
-
直接設定資料庫採用舊版CE。
-
在有效能問題的Query中加入Option Hint，強制該Query採用舊版CE。
-
利用SQL2016新功能Query Store，來強制指定Query的執行計畫。
下面這句語法是我實際遇到因CE問題而導致效能變差，由下圖紅色圈選處可以看見Tempdb有大量的IO(workfile有3336個Page讀取)，整個執行耗時也需要4秒半左右。
接下來我們採用第一種方式，調降資料庫相容性層級到100，讓資料庫會自動採用舊的CE來產出執行計畫。由下圖可以看出當我們調降相容性層級到100後，該查詢在workfile沒有任何的邏輯讀取，且執行耗時只要272ms。
接下來我們採用第二種方式，直接設定資料庫採用舊版CE來產出執行計畫。由下圖可以看出當我們設定資料庫採用舊版CE後，該查詢在workfile也沒有任何的邏輯讀取，且執行耗時只要252ms。
第三種方式我們在該Query加入Option(Querytraceon 9481)讓該句查詢採用舊版CE(下圖藍色圈選處)，其結果也是workfile也沒有任何的邏輯讀取，且執行耗時只要291ms
SQL2017後該Query的Hint可改為OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))這樣。
上述的第一及第二種方式都要犧牲資料庫無法採用新版CE的優點，這兩個方式的解決方法最快，但須在新舊間取捨。而第三種方式就得改到Code，因此也需要付出相對成本。
第四種方式則是利用Query Store的功能針對該句語法在不同相容性層級產出不同執行計畫後，強制指定該Query用效能較好的執行計畫，作法如下。
我們調降資料庫相容性層級到100，然後開啟該資料庫Query Store的功能，開啟後我直接執行該句Query，如下圖所示。
如下圖所示，我們來開啟Query Store的圖表。
我們可以在該圖表左上長條圖中找到該句語法，找到後點選該長條圖，然後右上圖表中的點點(方案識別碼為1)就是這句語法相對的執行計畫。
接下來我將相容性層級調回到130後，再次執行該語法，如下圖所示。
然後我們更新剛剛那張圖表會發現該Query又產生了另一個執行計畫，兩個執行計畫的執行所需耗時可以直接在該圖表看見，方案識別碼1(約300ms內)比方案識別碼10(需4秒多)好很多。
接下來我就點選方案識別碼1的圈圈後再去點選 [強制執行計畫]，這樣一來日後只要執行該Query就會直接跳過SQL的最佳化引擎而直接用我們剛剛強制指定的執行計畫來撈取資料。
完成上述設定後，我們可以在相容性層級130且沒開啟資料庫採用舊CE這功能的狀況下解掉剛剛因新CE導致查詢效能不佳的狀況(workfile沒有任何的邏輯讀取，且執行耗時只要146ms)。
好友Alexis有提到微軟建議升級後先不要調升相容性層級，而是先開啟Query Store功能，等到AP所有功能測試過一輪再調高相容性層級。調高後如遇到因為升級而導致的效能問題的語法，就可以馬上用指定強制執行計畫的方式來解決。但這樣一來就等於跳過SQL最佳化引擎，且日後升級或搬移資料庫到別台主機，其相同的狀況又會浮現。
我近期遇到會因為新舊CE而造成效能問題的情況都是因為語法執行過程中有隱含轉換，例如varchar跟nvarchar做join或是查詢的Where兩邊資料型態不一樣導致。修正掉這些原因後都可以讓效能恢復，只是不知道以前同樣情境為何在舊CE模式下不會有效能緩慢的問題。因此找出問題解決根本的原因才是王道，上述做法應該僅適用解決燃眉之急吧。
我是ROCK
rockchang@mails.fju.edu.tw
