共計 2561 個字符，預計需要花費 7 分鐘才能閱讀完成。
如何理解 SQL Server SQL 性能優化中的參數化，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面丸趣 TV 小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。
數據庫參數化的模式
數據庫的參數化有兩種方式，簡單（simple）和強制（forced），默認的參數化默認是“簡單”，簡單模式下，如果每次發過來的 SQL，除非完全一樣，否則就重編譯它（特殊情況會自動參數化，正是本文想說的重點）
強制模式就是將 adhoc SQL 強制參數化，避免每次運行的時候因為參數值的不同而重編譯，這里不詳細說明。
這首先要感謝“瀟湘隱者”大神的提示，當時也是遇到一個實際問題，發現執行計劃對數據行的預估，怎么都不對，有觀察到無論怎么改變參數，SQL 語句執行前都沒有重編譯，疑惑了好一會，這個問題正是簡單參數化模式下，對某些 SQL 自動參數化造成執行計劃重用引起的，也是本文想表達的重點。
這個問題之前就寫過，當時也只是看書上理論上這么說的，沒有想到其帶來的影響，該參數是一個數據級別的選項，設置情況可以參考下圖
什么情況下會自動參數化
簡單參數化模式下，對于有且只有一種執行方式的 Adhoc SQL 語句，SQL Server 會自動參數化它，從而達到重用執行計劃的目的。
究竟哪些類型的 SQL 會被自動參數化，后面會舉例說明。
自動參數化會存在哪些問題
在簡單模式下，SQL 對于某些 SQL 會自動參數化他，避免每次都重編譯。
SQL Server 自動參數化 SQL 語句的行為，能夠避免一些重編譯，原本也是出于“好意”，但是這種“好意”往往不一定總是給我們帶來好處。
舉例說明什么情況下會自動參數化
先造一個簡單的測試環境
create table TestAuotParameter ( id int not null, col2 varchar(50) ) GO declare @i int=0 while @i100000 begin insert into TestAuotParameter values (@i, NEWID()) set @i=@i+1 end GO create unique index idx_id on TestAuotParameter(id) GO之所以自動參數化了 SQL 語句，就是因為 select * from TestAuotParameter where id=33333（66666,99999）這句 SQL 語句，在當前的數據量下和 *** 索引的特點，決定了有且只有一種高效的執行方式（也就是索引查找）這里說有且只有一種方式是表中數據量相對較多，又因為 idx_id 這個索引是 unique 的。如果不是 unique 的，那么情況就不同了，下面來解釋什么是有且只有一種高效的執行計劃
如下截圖：同樣的測試，我刪除 id 上的 *** 索引，創建為非 *** 索引，再做同樣的測試，就會發現執行同樣的 SQL 并沒有被自動參數化
這里解釋一下原因，索引類型怎么跟執行計劃緩存扯上了？
對于非 *** 索引，有可能作做引查找是高效的，有可能做全表掃描是高效的（比如某個 ID 的數據分布的特別多）此時執行計劃有可能是多樣的，不僅僅只有一種方式，所以就不會自動參數化 SQL
自動參數化存在的問題
自動參數化好處并不用多說，因為可以重用緩存的執行計劃，避免了每次參數值不一樣就重編譯的問題。說到執行計劃重用，不得不說的一個話題就是 parameter sniff，嘴皮子都磨破的問題了
沒錯，自動參數化因為不同參數會重用 *** 次編譯生成的執行計劃，很有可能造成 parameter sniff 問題，以及 parameter sniff 衍生出來的其他問題
同樣用一個例子來做演示，該問題是最近在觀察執行計劃統計信息（statistics）預估問題時遇到的一個問題，讓我困惑了好一會，這里再次感謝瀟湘隱者。
該問題同樣也是因為自動參數化了 SQL 語句，造成執行計劃重用，從而造成一個極其簡單的 SQL 執行效率在某些情況下較低的情況，為什么自動化參數的原因跟上述類似，都是有且只有一種執行方式（索引查找）的情況下，不同參數執行計劃重用造成對數據行的錯誤預估。測試之前清空一下緩存執行計劃，觀察不同查詢條件下的實際執行計劃對數據行的預估
如下查詢條件：
1，初始查詢條件為：CreateDate rsquo;2016-6-1 prime; and CreateDate
2，將查詢條件更新為：CreateDate rsquo;2016-6-1 prime; and CreateDate
3，將查詢條件更新為：CreateDate rsquo;2016-6-1 prime; and CreateDate
發現沒有，因為查詢時間段有變化，實際行數也有變化，但是不管實際行數多少，預估行數總是為 *** 次執行預估的行數。
這肯定不對吧？隨便帶入什么條件，預估行數都是 37117，當時一下子蒙了，怎么每次執行 SQL 對數據行的預估都是一樣的？
其實這個問題跟一開始舉例的一樣，都是 SQL 語句被自動參數化了，因此造成了執行計劃重用，執行計劃重用，導致錯誤地預估實際查詢的數據行數。
如何解決自動參數化造成錯誤地重用執行計劃的問題
很多問題找到了真正的原因，解決起來往往并不難，這個問題的原因是執行計劃重用造成的，那么我們只需要解決執行計劃重用的問題即可。也就是不讓他重用執行計劃，只需要在 SQL 語句中加一個提示即可，也即：select COUNT(1) from Test20160810 where CreateDate rsquo;2016-6-1 prime; and CreateDateOPTION(RECOMPILE)
原因就在于加上 OPTION(RECOMPILE) 這個查詢提示之后，不緩存 SQL 的執行計劃緩存，沒有了執行計劃緩存，也就沒得重用了
比如這個查詢，在查詢語句中加入 OPTION(RECOMPILE) 查詢提示，讓其執行之前重編譯 SQL 語句，他就可以正確地預估數據行了。
通過一個實際案例說明了什么是簡單參數模式下的自動化參數，自動化參數會帶來哪些問題，以及如何解決，問題本身非常簡單，如果不注意還是會偶爾還是會出現困惑的。
看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注丸趣 TV 行業資訊頻道，感謝您對丸趣 TV 的支持。
