[[226440]]

數(shù)據(jù)庫參數(shù)化的模式

數(shù)據(jù)庫的參數(shù)化有兩種方式，簡單（simple）和強制（forced），默認(rèn)的參數(shù)化默認(rèn)是“簡單”，簡單模式下，如果每次發(fā)過來的SQL，除非完全一樣，否則就重編譯它（特殊情況會自動參數(shù)化，正是本文想說的重點）

強制模式就是將adhoc SQL強制參數(shù)化，避免每次運行的時候因為參數(shù)值的不同而重編譯，這里不詳細(xì)說明。

這首先要感謝“瀟湘隱者”大神的提示，當(dāng)時也是遇到一個實際問題，發(fā)現(xiàn)執(zhí)行計劃對數(shù)據(jù)行的預(yù)估，怎么都不對，有觀察到無論怎么改變參數(shù)，SQL語句執(zhí)行前都沒有重編譯，疑惑了好一會，這個問題正是簡單參數(shù)化模式下，對某些SQL自動參數(shù)化造成執(zhí)行計劃重用引起的，也是本文想表達(dá)的重點。

這個問題之前就寫過，當(dāng)時也只是看書上理論上這么說的，沒有想到其帶來的影響，該參數(shù)是一個數(shù)據(jù)級別的選項，設(shè)置情況可以參考下圖

什么情況下會自動參數(shù)化

簡單參數(shù)化模式下，對于有且只有一種執(zhí)行方式的Adhoc SQL語句，SQL Server會自動參數(shù)化它，從而達(dá)到重用執(zhí)行計劃的目的。

究竟哪些類型的SQL會被自動參數(shù)化，后面會舉例說明。

自動參數(shù)化會存在哪些問題

在簡單模式下，SQL對于某些SQL會自動參數(shù)化他，避免每次都重編譯。

SQL Server 自動參數(shù)化SQL語句的行為，能夠避免一些重編譯，原本也是出于“好意”，但是這種“好意”往往不一定總是給我們帶來好處。

舉例說明什么情況下會自動參數(shù)化

先造一個簡單的測試環(huán)境 

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 

之所以自動參數(shù)化了SQL語句，就是因為select * from TestAuotParameter where id=33333 （66666,99999）這句SQL語句，在當(dāng)前的數(shù)據(jù)量下和***索引的特點，決定了有且只有一種高效的執(zhí)行方式（也就是索引查找）這里說有且只有一種方式是表中數(shù)據(jù)量相對較多，又因為idx_id這個索引是unique的。如果不是unique的，那么情況就不同了，下面來解釋什么是有且只有一種高效的執(zhí)行計劃

如下截圖：同樣的測試，我刪除id上的***索引，創(chuàng)建為非***索引，再做同樣的測試，就會發(fā)現(xiàn)執(zhí)行同樣的SQL并沒有被自動參數(shù)化

這里解釋一下原因，索引類型怎么跟執(zhí)行計劃緩存扯上了？

對于非***索引，有可能作做引查找是高效的，有可能做全表掃描是高效的（比如某個ID的數(shù)據(jù)分布的特別多）此時執(zhí)行計劃有可能是多樣的，不僅僅只有一種方式，所以就不會自動參數(shù)化SQL

自動參數(shù)化存在的問題

自動參數(shù)化好處并不用多說，因為可以重用緩存的執(zhí)行計劃，避免了每次參數(shù)值不一樣就重編譯的問題。說到執(zhí)行計劃重用，不得不說的一個話題就是parameter sniff，嘴皮子都磨破的問題了

沒錯，自動參數(shù)化因為不同參數(shù)會重用***次編譯生成的執(zhí)行計劃，很有可能造成parameter sniff問題，以及parameter sniff衍生出來的其他問題

同樣用一個例子來做演示，該問題是最近在觀察執(zhí)行計劃統(tǒng)計信息（statistics）預(yù)估問題時遇到的一個問題，讓我困惑了好一會，這里再次感謝瀟湘隱者。

該問題同樣也是因為自動參數(shù)化了SQL語句，造成執(zhí)行計劃重用，從而造成一個極其簡單的SQL執(zhí)行效率在某些情況下較低的情況，為什么自動化參數(shù)的原因跟上述類似，都是有且只有一種執(zhí)行方式（索引查找）的情況下，不同參數(shù)執(zhí)行計劃重用造成對數(shù)據(jù)行的錯誤預(yù)估。測試之前清空一下緩存執(zhí)行計劃，觀察不同查詢條件下的實際執(zhí)行計劃對數(shù)據(jù)行的預(yù)估

如下查詢條件：

1，初始查詢條件為：CreateDate>’2016-6-1′ and CreateDate

2，將查詢條件更新為：CreateDate>’2016-6-1′ and CreateDate

3，將查詢條件更新為：CreateDate>’2016-6-1′ and CreateDate

發(fā)現(xiàn)沒有，因為查詢時間段有變化，實際行數(shù)也有變化，但是不管實際行數(shù)多少，預(yù)估行數(shù)總是為***次執(zhí)行預(yù)估的行數(shù)。

這肯定不對吧？隨便帶入什么條件，預(yù)估行數(shù)都是37117，當(dāng)時一下子蒙了，怎么每次執(zhí)行SQL對數(shù)據(jù)行的預(yù)估都是一樣的？

其實這個問題跟一開始舉例的一樣，都是SQL語句被自動參數(shù)化了，因此造成了執(zhí)行計劃重用，執(zhí)行計劃重用，導(dǎo)致錯誤地預(yù)估實際查詢的數(shù)據(jù)行數(shù)。

如何解決自動參數(shù)化造成錯誤地重用執(zhí)行計劃的問題

很多問題找到了真正的原因，解決起來往往并不難，這個問題的原因是執(zhí)行計劃重用造成的，那么我們只需要解決執(zhí)行計劃重用的問題即可。也就是不讓他重用執(zhí)行計劃，只需要在SQL語句中加一個提示即可，也即：select COUNT(1) from Test20160810 where CreateDate>’2016-6-1′ and CreateDateOPTION(RECOMPILE)

原因就在于加上OPTION(RECOMPILE)這個查詢提示之后，不緩存SQL的執(zhí)行計劃緩存，沒有了執(zhí)行計劃緩存，也就沒得重用了

比如這個查詢，在查詢語句中加入OPTION(RECOMPILE)查詢提示，讓其執(zhí)行之前重編譯SQL語句，他就可以正確地預(yù)估數(shù)據(jù)行了。

總結(jié)

本文通過一個實際案例說明了什么是簡單參數(shù)模式下的自動化參數(shù)，自動化參數(shù)會帶來哪些問題，以及如何解決，問題本身非常簡單，如果不注意還是會偶爾還是會出現(xiàn)困惑的。

題外話

有一點感受非常深，就是說，越來越多的實際問題，都要有理論知識作支撐，但往往理論上說的情況并沒有頻繁出現(xiàn)或者即使出現(xiàn)了也沒有引起注意，有時間就忽略了一些理論上的知識。 

對于遇到的問題，如果真的要想弄清楚，還是要有一些理論知識做鋪墊的。很多時候，往往是遇到問題之后，回憶起來曾經(jīng)好似乎看過這一方面的理論知識。這也是我們需要堅持看書，了解一些理論知識的原因。