SQL Server SQL性能優(yōu)化之參數(shù)化
數(shù)據(jù)庫參數(shù)化的模式
數(shù)據(jù)庫的參數(shù)化有兩種方式，簡單（simple）和強(qiáng)制（forced），默認(rèn)的參數(shù)化默認(rèn)是“簡單”，簡單模式下，如果每次發(fā)過來的SQL，除非完全一樣，否則就重編譯它（特殊情況會(huì)自動(dòng)參數(shù)化，正是本文想說的重點(diǎn)）
強(qiáng)制模式就是將adhoc SQL強(qiáng)制參數(shù)化，避免每次運(yùn)行的時(shí)候因?yàn)閰?shù)值的不同而重編譯，這里不詳細(xì)說明。
這首先要感謝“瀟湘隱者”大神的提示，當(dāng)時(shí)也是遇到一個(gè)實(shí)際問題，發(fā)現(xiàn)執(zhí)行計(jì)劃對(duì)數(shù)據(jù)行的預(yù)估，怎么都不對(duì)，有觀察到無論怎么改變參數(shù)，SQL語句執(zhí)行前都沒有重編譯，疑惑了好一會(huì)，這個(gè)問題正是簡單參數(shù)化模式下，對(duì)某些SQL自動(dòng)參數(shù)化造成執(zhí)行計(jì)劃重用引起的，也是本文想表達(dá)的重點(diǎn)。
這個(gè)問題之前就寫過，當(dāng)時(shí)也只是看書上理論上這么說的，沒有想到其帶來的影響，該參數(shù)是一個(gè)數(shù)據(jù)級(jí)別的選項(xiàng)，設(shè)置情況可以參考下圖
什么情況下會(huì)自動(dòng)參數(shù)化
簡單參數(shù)化模式下，對(duì)于有且只有一種執(zhí)行方式的Adhoc SQL語句，SQL Server會(huì)自動(dòng)參數(shù)化它，從而達(dá)到重用執(zhí)行計(jì)劃的目的。
究竟哪些類型的SQL會(huì)被自動(dòng)參數(shù)化，后面會(huì)舉例說明。
自動(dòng)參數(shù)化會(huì)存在哪些問題
在簡單模式下，SQL對(duì)于某些SQL會(huì)自動(dòng)參數(shù)化他，避免每次都重編譯。
SQL Server 自動(dòng)參數(shù)化SQL語句的行為，能夠避免一些重編譯，原本也是出于“好意”，但是這種“好意”往往不一定總是給我們帶來好處。
舉例說明什么情況下會(huì)自動(dòng)參數(shù)化
先造一個(gè)簡單的測試環(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
之所以自動(dòng)參數(shù)化了SQL語句，就是因?yàn)閟elect * from TestAuotParameter where id=33333 （66666,99999）這句SQL語句，在當(dāng)前的數(shù)據(jù)量下和***索引的特點(diǎn)，決定了有且只有一種高效的執(zhí)行方式（也就是索引查找）這里說有且只有一種方式是表中數(shù)據(jù)量相對(duì)較多，又因?yàn)閕dx_id這個(gè)索引是unique的。如果不是unique的，那么情況就不同了，下面來解釋什么是有且只有一種高效的執(zhí)行計(jì)劃
如下截圖：同樣的測試，我刪除id上的***索引，創(chuàng)建為非***索引，再做同樣的測試，就會(huì)發(fā)現(xiàn)執(zhí)行同樣的SQL并沒有被自動(dòng)參數(shù)化
這里解釋一下原因，索引類型怎么跟執(zhí)行計(jì)劃緩存扯上了？
對(duì)于非***索引，有可能作做引查找是高效的，有可能做全表掃描是高效的（比如某個(gè)ID的數(shù)據(jù)分布的特別多）此時(shí)執(zhí)行計(jì)劃有可能是多樣的，不僅僅只有一種方式，所以就不會(huì)自動(dòng)參數(shù)化SQL
自動(dòng)參數(shù)化存在的問題
自動(dòng)參數(shù)化好處并不用多說，因?yàn)榭梢灾赜镁彺娴膱?zhí)行計(jì)劃，避免了每次參數(shù)值不一樣就重編譯的問題。說到執(zhí)行計(jì)劃重用，不得不說的一個(gè)話題就是parameter sniff，嘴皮子都磨破的問題了
沒錯(cuò)，自動(dòng)參數(shù)化因?yàn)椴煌瑓?shù)會(huì)重用***次編譯生成的執(zhí)行計(jì)劃，很有可能造成parameter sniff問題，以及parameter sniff衍生出來的其他問題
同樣用一個(gè)例子來做演示，該問題是最近在觀察執(zhí)行計(jì)劃統(tǒng)計(jì)信息（statistics）預(yù)估問題時(shí)遇到的一個(gè)問題，讓我困惑了好一會(huì)，這里再次感謝瀟湘隱者。
該問題同樣也是因?yàn)樽詣?dòng)參數(shù)化了SQL語句，造成執(zhí)行計(jì)劃重用，從而造成一個(gè)極其簡單的SQL執(zhí)行效率在某些情況下較低的情況，為什么自動(dòng)化參數(shù)的原因跟上述類似，都是有且只有一種執(zhí)行方式（索引查找）的情況下，不同參數(shù)執(zhí)行計(jì)劃重用造成對(duì)數(shù)據(jù)行的錯(cuò)誤預(yù)估。測試之前清空一下緩存執(zhí)行計(jì)劃，觀察不同查詢條件下的實(shí)際執(zhí)行計(jì)劃對(duì)數(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)沒有，因?yàn)椴樵儠r(shí)間段有變化，實(shí)際行數(shù)也有變化，但是不管實(shí)際行數(shù)多少，預(yù)估行數(shù)總是為***次執(zhí)行預(yù)估的行數(shù)。
這肯定不對(duì)吧？隨便帶入什么條件，預(yù)估行數(shù)都是37117，當(dāng)時(shí)一下子蒙了，怎么每次執(zhí)行SQL對(duì)數(shù)據(jù)行的預(yù)估都是一樣的？
其實(shí)這個(gè)問題跟一開始舉例的一樣，都是SQL語句被自動(dòng)參數(shù)化了，因此造成了執(zhí)行計(jì)劃重用，執(zhí)行計(jì)劃重用，導(dǎo)致錯(cuò)誤地預(yù)估實(shí)際查詢的數(shù)據(jù)行數(shù)。
如何解決自動(dòng)參數(shù)化造成錯(cuò)誤地重用執(zhí)行計(jì)劃的問題
很多問題找到了真正的原因，解決起來往往并不難，這個(gè)問題的原因是執(zhí)行計(jì)劃重用造成的，那么我們只需要解決執(zhí)行計(jì)劃重用的問題即可。也就是不讓他重用執(zhí)行計(jì)劃，只需要在SQL語句中加一個(gè)提示即可，也即：select COUNT(1) from Test20160810 where CreateDate>’2016-6-1′ and CreateDateOPTION(RECOMPILE)
原因就在于加上OPTION(RECOMPILE)這個(gè)查詢提示之后，不緩存SQL的執(zhí)行計(jì)劃緩存，沒有了執(zhí)行計(jì)劃緩存，也就沒得重用了
比如這個(gè)查詢，在查詢語句中加入OPTION(RECOMPILE)查詢提示，讓其執(zhí)行之前重編譯SQL語句，他就可以正確地預(yù)估數(shù)據(jù)行了。
總結(jié)
本文通過一個(gè)實(shí)際案例說明了什么是簡單參數(shù)模式下的自動(dòng)化參數(shù)，自動(dòng)化參數(shù)會(huì)帶來哪些問題，以及如何解決，問題本身非常簡單，如果不注意還是會(huì)偶爾還是會(huì)出現(xiàn)困惑的。
題外話
有一點(diǎn)感受非常深，就是說，越來越多的實(shí)際問題，都要有理論知識(shí)作支撐，但往往理論上說的情況并沒有頻繁出現(xiàn)或者即使出現(xiàn)了也沒有引起注意，有時(shí)間就忽略了一些理論上的知識(shí)。
對(duì)于遇到的問題，如果真的要想弄清楚，還是要有一些理論知識(shí)做鋪墊的。很多時(shí)候，往往是遇到問題之后，回憶起來曾經(jīng)好似乎看過這一方面的理論知識(shí)。這也是我們需要堅(jiān)持看書，了解一些理論知識(shí)的原因。
