SQL Server Statistics and Cost Estimation
統計資訊(Statistics)會幹擾查詢優化器(Query Optimizer)生成最優的執行計劃。它存儲的是表列或者索引列的數值分布統計，也稱為柱狀統計Histogram。統計資訊的過期或者不充分，都能導緻優化器評估成本模型（Cost-Based Estimation）失效。是以我們就要時刻監控統計資訊的有效性，采取适當的技術手段來保證它的時效性。
先從概念說起，我們看下SQL SERVER是如何啟動Statistics這個特性的。如果沒有啟動我們就要手工啟動：
select name,is_auto_create_stats_incremental_on,is_auto_update_stats_on,is_auto_update_stats_async_on ,is_auto_create_stats_on
from sys.databases
where name = 'lenistest4'
上面的查詢就能判斷特定的資料庫是不是啟動了自動更新統計資訊的特性。那麼如果統計資訊經常自動更新，加上又是資料量極大，頻率又非常快的話，就會使查詢優化器經常被自動更新給拖累，也就是經常等待自動更新的完成而不能很快給查詢做出即時的執行計劃，這個時候就有2種方法可以選了：一是設定一個維護視窗，讓伺服器在這一個視窗内更新統計資訊，而自動更新統計資訊這個開關就可以關閉了；二是開啟is_auto_upadte_stats_async_on，這個選項是可以讓統計資訊的更新在晚點的時刻進行更新，而不是在更新完資料後，馬上就更新。這裡“晚點”的時刻概念是指，query optimizer并不會理會目前的statistics是不是最新的，而是直接按照目前的統計資訊來判斷最優的執行計劃，而由另一根背景線程在目前的優化器在編譯的時候，去更新統計資訊。
如何啟動異步更新統計資訊 is_auto_update_stats_async_on呢？在開啟之前，Auto_update_statistics也需要同時啟動。
alter database lenistest4
set auto_update_statistics on
alter database lenistest4
set auto_update_statistics_async on
is_auto_create_stats_incremental_on 這個選項，是為了更新partition資訊而設定的。如果設定為off，則整個statistics tree就丢棄了，而需要重新計算。通常partition的統計資訊是不需要全表掃描的，隻需掃描需要更新的partition的資料就可以了。
我們可以用dbcc show_statistics來檢視對應的表，試圖，索引的統計資訊：
1 找到對應表的統計對象(statistics object)：
select * from sys.stats where object_id = object_id(N'dbo.cdc_driven')
2 将表 以及對應的統計對象傳給 dbcc show_statistics函數：
dbcc show_statistics ('dbo.cdc_driven','_WA_Sys_00000002_32E0915F')
還可以使用create statistics 來給我們想要的列(column)加上統計資訊:
同樣，我們可以使用
dbcc show_statistics ('dbo.cdc_driven','statsofCDCdriven')
來檢視統計資訊的具體内容。
這裡要注意的是，
dbcc show_statistics(table_view_index_name, column_statisticsobject_name)，
這裡如果沒有為column建立自動統計資訊更新的話，會報錯誤：
dbcc show_statistics('dbo.cdc_driven',[cdcMinLsn])
Msg , Level , State , Line
Could not locate statistics 'cdcMinLsn' in the system catalogs.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
這裡等的自動統計資訊更新，其實指的就是非SQL語句建立的statistics。上面的例子就證明了，隻要一個column有了相應的statistics object，那麼顯示這個column對應的統計資訊，在dbcc show_statistics中就不能用column，而隻能用statistics object了。
DBCC SHOW_Statistics會傳回三個結果集，分别是header, the density vector和the histogram。如果想要傳回三者之一 ，在dbcc show_statistics的時候要加上with option，比如：
dbcc show_statistics ('dbo.cdc_driven','statsofCDCdriven')
dbcc show_statistics ('dbo.cdc_driven','_WA_Sys_00000002_32E0915F') with histogram
dbcc show_statistics ('dbo.cdc_driven','_WA_Sys_00000002_32E0915F') with stat_header
dbcc show_statistics ('dbo.cdc_driven','_WA_Sys_00000002_32E0915F') with density_vector
有意思的是，_WA，這裡是指 Washington, the state of the United Stats where the SQL Server development team is located。 SQL SERVER開發小組所在地。[00000002]在這裡指的是column id, [32E0915F]是object_id：
select object_id(N'dbo.cdc_driven')
select convert(bigint, )
這裡density_vector結果集裡面的[All Density]是所有唯一值的總數的倒數，即 1/countof(all distinct values)。 與header結果集裡面的density不同，header結果集裡面的density已經棄用了。
dbcc show_statistics('dbo.fctdbsize','dt_ty_nm_size') with density_vector
select /count(distinct record_date) from dbo.fctdbsize
這裡4.228509E-05，指的是4.228509*10(-5)。Density就是資料密度，對Group By 是有優化提示作用的。
Histogram又是另一種資料統計資訊表達方式，每個statistics object的第一個column就有一個histogram, 它把資料 壓縮後平均配置設定到 200個subset或者step,bcukets裡面去。如果資料超過 200個bucket,那麼就用算法算出最近頻繁使用的資料。這個算法是maxdiff的一種變形，讓一個range盡量去覆寫最大的數值空間。
Statistics的維護:剛才我們在談到這個屬性 is_auto_update_statistics_asnyc_on時，建議維護statistics的最佳方法是放在一個維護視窗期，更有可能的話應該放在index rebuilt或者碎片化整理之後，這樣對于index statistics的維護更新也更有利，既節約了時間，還能使用full scan 對全表做抽樣：
update statistics是最細粒度的更新文法，任何其他的統計資訊更新方法都要調用到這個方法。像sp_updateStats。當然我們還是要知道update statistics是怎麼工作的。
在更新統計資訊之前，先截圖儲存目前的統計資訊：
select * from sys.stats
where object_id = object_id('dbo.cdc_driven')
dbcc show_statistics ('dbo.cdc_driven',statsofCDCdriven) with stat_header
dbcc show_statistics ('dbo.cdc_driven',_WA_Sys_00000002_32E0915F) with stat_header
dbcc show_statistics ('dbo.cdc_driven',_WA_Sys_00000005_32E0915F) with stat_header
dbcc show_statistics ('dbo.cdc_driven',_WA_Sys_00000007_32E0915F) with stat_header
1 這裡有 4 個統計資訊對象(statistics object)。Sys.stats中的auto_create顧名思義就是系統自動建立的，而user_created則表示是使用者自己建立的。
2 系統自建的這些統計資訊對象，在小資料量下，更新的頻率不好掌握，比如我根據這些自動更新統計資訊的列查詢了一些資料，統計資訊并不自動更新。而且當我更新了或者插入了新的資料，統計資訊依舊不更新，不管是index還是column，或者建立的statistics objects.
insert into dbo.cdc_driven(transactionId,cdcId,cdcStartDT,cdcEndDT,cdcCompleted,cdcMinLsn,cdcMaxLsn)
select transactionId,cdcId,cdcStartDT,cdcEndDT,cdcCompleted,cdcMinLsn,cdcMaxLsn
from dbo.cdc_driven
select * from sys.stats
where object_id = object_id('dbo.cdc_driven')
dbcc show_statistics ('dbo.cdc_driven',statsofCDCdriven) with stat_header
dbcc show_statistics ('dbo.cdc_driven',_WA_Sys_00000002_32E0915F) with stat_header
dbcc show_statistics ('dbo.cdc_driven',_WA_Sys_00000005_32E0915F) with stat_header
dbcc show_statistics ('dbo.cdc_driven',_WA_Sys_00000007_32E0915F) with stat_header
dbcc show_statistics ('dbo.cdc_driven',idx_cdcid) with stat_header
select count(*) from dbo.cdc_driven
3 如果我建立一個index，系統會添加一個index 的統計資訊對象，名稱就是index名字。而且auto_created,user_created都為0。并且都是基于最新的資料作統計 。
create index idx_cdcid on dbo.cdc_driven(cdcId)
go
select * from sys.stats
where object_id = object_id('dbo.cdc_driven')
dbcc show_statistics ('dbo.cdc_driven',statsofCDCdriven) with stat_header
dbcc show_statistics ('dbo.cdc_driven',_WA_Sys_00000002_32E0915F) with stat_header
dbcc show_statistics ('dbo.cdc_driven',_WA_Sys_00000005_32E0915F) with stat_header
dbcc show_statistics ('dbo.cdc_driven',_WA_Sys_00000007_32E0915F) with stat_header
dbcc show_statistics ('dbo.cdc_driven',idx_cdcid) with stat_header
4 是以基于這些統計資訊自動更新不好掌握的情況，我們就手工來執行更新。綜上， 一個表可能會有三種統計資訊對象：statistics object, column, index。
Statistics object的更新:
use lenistest4
go
update statistics dbo.cdc_driven statsofCDCdriven with fullscan
注意table, statistics object的順序，table 在前，statistics object 在後
Column statistics的更新:
Index Statistics的更新:
如果要全部更新，加上 all :
Statistics失靈的地方：函數式列比對。我們先建立一個索引，比如：
create index idx_size on dbo.fctdbsize(size)
go
select count(*) from dbo.fctdbsize where size >
這裡列加了函數就不能走index seek而隻能index scan 了。
這裡和oracle的函數索引就不一樣了。但是我們還是可以讓這個查詢走上索引的道路，用scalar expression column。我們可以将convert(int,size)建立成一個列，加個索引，然後就能用上statistics了和索引了
這裡statistics的真正用法是建立scalar expression column之後，這個column 的statistics就能上了，比如:
沒有建立scalar expression column之前 :
select * from dbo.cdc_driven where datediff(ms,cdcStartDT,cdcEndDT) >
這裡estimated number of rows是6.6, 而actual number of rows是20， 是以并不準确；
然後，我們建立一個computed column也就是scalar expression column，
alter table dbo.cdc_driven add dur_cdc as datediff(ms,cdcStartDT,cdcEndDT)
select * from dbo.cdc_driven where dur_cdc >
或者：
select * from dbo.cdc_driven where datediff(ms,cdcStartDT,cdcEndDT) >
這裡estimated number of rows就和actual number of rows一樣了 。可見統計資訊可以建立在computed column之上，提高優化器的準确性。
這裡的政策其實是以空間換取時間，一種折中的辦法。
還有一種奇特的statistics，叫做filtered statistics,他的文法是這樣的：
察看這個statistics的形态：
dbcc show_statistics ('dbo.cdc_driven',dur_minlsn ) with stat_header。
這裡有意思的是 Unfiltered Rows，它指的是在statistics object建立的時候，表總共有的資料量。
但是filter不能使用computed column,也不能使用scalar expression。
create statistics dur_minlsn on dbo.cdc_driven(cdcMinLsn) where datediff(ms,cdcStartDT,cdcEndDT) >
create statistics dur_minlsn on dbo.cdc_driven(cdcMinLsn) where dur_cdc >
類似的錯誤有 ：
Msg , Level , State , Line
Filtered statistics 'dur_minlsn' cannot be created on table 'dbo.cdc_driven' because the column 'dur_cdc' in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.
Cardinality基數的估計錯誤會導緻優化器不能很好的挑選最優執行計劃，是以這個時候，我們要看是不是statistics沒有正确被更新，或者壓根就沒statistics。怎麼去判斷statistics的錯誤呢? 隻要看estimated number of rows 和actual number of rows是不是對得上，就可以了：
set statistics profile on
select * from dbo.cdc_driven where datediff(ms,cdcStartDT,getutcdate()) >
set statistics profile off
對不上怎麼辦，看情況。像上面這種函數式判斷條件，我們可以建立computed column來解決。
Undocumented Options: ROWCOUNT & PageCount: 引導優化器産生小表或者大表的執行計劃：
select * from sys.stats where object_id = object_id(N'dbo.cdc_driven')
select object_name(object_id) as objectName,index_id,rows from sys.partitions where object_id = object_id(N'dbo.cdc_driven')
select object_name(object_id) as objectName,index_id,partition_id,row_count,used_page_count from sys.dm_db_partition_stats where object_id = object_id(N'dbo.cdc_driven')
成本估計 - cost estimated: 查詢優化器并不會周遊或者窮舉所有的執行計劃，并對所有的執行計劃都做一邊成本估算，進而來選擇最優的執行計劃，而是一旦計算出一個可行的執行計劃并且該計劃的成本相對低廉有效，就立即執行。那麼這裡對成本的定義就很重要了。
1 成本估算有CPU，IO， Memory的估算。我們用table scan的方式來查找一張表的部分資料再觀察這個執行的計劃的成本
這裡顯示的 Estimated I/O Cost 有 740.743， Estimated CPU Cost 有1.10016, Estimated Operator Cost是前面兩部分的總和 741.843. Cost的機關是什麼？将每一個operator的estimated cost相加，成為這個執行計劃的成本估算，一旦合理就執行 。 每個operator的成本如下圖可見：
Execution Engine
1 資料通路操作符号
Scan:并不保證是排過序的，除非使用了order by
Seek: non-heap table才有可能用到seek.
Bookmark Lookup: RID lookup
2 聚合運算符号
Sort:
Hash:
Stream Aggregation and Hash Aggregation (Union: Sum: Count: )
3 一次元操作符号
Scalar Expression:
4 Join操作符
Nested Loop:
Merge join:
Hash Join:對兩者中較小的表建立一個has table。
5 Parallel運算符
The Optimization Process
1. 整個query optimization的程序 : 軟解析和硬解析：軟解析從文法到對象綁定；硬解析，除了進行軟解析之外，還需要進行執行計劃的優化，包括評估計劃模型成本，生成計劃的實體操作。怎麼讓語句隻進行軟解析，将軟解析的錯誤異常先抛出來?
解析文法可以用: set parseonly on
select top * from dbo.cdc_driven
set parseonly on
select Id,LastUpdated from dbo.cdc_driven
這裡不管表裡面有沒有這個字段，都可以解析成功，不會有任何錯誤。
但是如果我們的文法錯誤了，就要報錯了：
這裡多了一個逗号，文法錯誤。
進行name resolution的時候，也就是在binding這個環節，會檢查所有在語句中引用到的對象，是不是都存在，不存在就報錯。這個時候還沒有執行SQL 。
begin try
declare @sqlstatement nvarchar(max) = 'set noexec on
select transactionid,cdcId,cdcStartDT from dbo.cdc_driven'
exec sp_executesql @stat = @sqlstatement
end try
begin catch
select ERROR_MESSAGE()
end catch
這裡我們故意把 transactionId這個字段改為 transactionid，大小寫不比對。結果就捕獲了這個錯誤：
這裡， set noexec on起到了隻編譯而不執行的作用。
2.DBCC TRACEON(3604):這裡的作用是将DBCC的一些結果傳回給用戶端。關于flag 3604有說法：一般的 DBCC PAGE等指令，預設的輸出不是輸入指令的用戶端（通常 是SSMS）。 開啟 3604，就能使得傳回結果輸出到用戶端。
dbcc traceoff()
dbcc page('lenistest4',,)
dbcc traceon()
dbcc page('lenistest4',,)
這裡的DBCC PAGE 就是察看data page的一部分資料。如果拿到某一個表的所有page，我們可以用這個DMV sys.dm_db_database_page_allocations。
文法是這樣的：
sys.dm_db_database_page_allocations
(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)
Parameters :
@DatabaseId :You need to pass the required database ID. This parameter is mandatory and data type of this argument is small integer.
@TableId:You need to pass the required table ID. This parameter is optional and data type of this argument is integer.
@IndexId:You need to pass the required Index ID. This parameter is optional and data type of this argument is integer.
@PartionID:You need to pass the required Partion ID. This parameter is optional and data type of this argument is integer.
@Mode:You need to pass the required Mode. This parameter is mandatory and data type of this argument is nvarchar(). In this argument we must pass only ‘DETAILED’ OR ‘LIMITED’.
select object_id,index_id,partition_id,extent_file_id,extent_page_id,allocated_page_iam_file_id,allocated_page_iam_page_id,allocated_page_file_id,
allocated_page_page_id,is_iam_page,page_type_desc
from sys.dm_db_database_page_allocations( db_id(N'lenistest4'),object_id(N'dbo.cdc_driven'),,,'detailed')
與sys.dm_db_index_physical_stats聯合起來看，更能說明問題，畢竟sys.dm_db_database_page_allocations不是官方document的函數。
select object_id,index_id,partition_id,extent_file_id,extent_page_id,allocated_page_iam_file_id,allocated_page_iam_page_id,allocated_page_file_id,
allocated_page_page_id,is_iam_page,page_type_desc
from sys.dm_db_database_page_allocations( db_id(N'lenistest4'),object_id(N'dbo.cdc_driven'),,,'detailed')
select * from sys.dm_db_index_physical_stats (db_id(N'lenistest4'),object_id(N'dbo.cdc_driven'),,,'detailed')
既然談到了DBCC PAGE,我們把相關的概念都了解下：
這裡的 GAM，SGAM， PFS, IAM 都分别代表了啥意思,要徹底了解這些概念的作用，我們就要知道在配置設定存儲的時候，大概的過程是怎麼樣的，猜想下哪些關鍵流程可能會用到這些概念。
1). 資料頁的配置設定是按照一個Extent來分的，不管需要多少page，首先會先配置設定一個Extent出來，一個Extent能包含多少page呢？8個連續的page成為一個Extent，共64KB.
2). Extent的原資料管理？怎麼知道這些Extent是屬于這個資料庫的，多少個Extent已經申請配置設定了？
GAM(Global Allocation Map)就是用來檢視哪些 extent已經被配置設定了， 哪些還沒有？1 表示還沒配置設定，0表示已經被配置設定了。 Bitmaps就是映射Extent的整體配置設定情況。
SGAP(Shared Global Allocation Map),混合extent的配置設定情況，1表示還沒配置設定，0表示已經配置設定了（并且是mixed extent）.什麼叫 mixed extent？
3). Page有很多種, data page, index page, text or image page, GAM page, SGAM page.是以當一個extent有多種page存在的時候，就叫做 mixed extent.
4).GAM,SGAM,IAM的存儲:這三種都是data page或者index data page.是以理應歸檔在Extent的範疇裡面。當data file的第一個extent配置設定的時候，先産生一個GAM, 或者SGAM，當第一個index建立的時候，IAM 是第一個建立的page。這三種page隻存儲bitmaps而不存儲資料或者 index data。一個page有8K,那麼總共能有64000個Extent能被map到，大概是64000*64KB的資料量，即4GB。當過了4GB 之後，需要建立GAM,SGAM,IAM.
5). PFS: Page Free Space.每個page都會留點空間給修改或者insert。好處就是一個update不至于引起整個page或者一連串page的遷移。
3 SQL Server 有自己的優化步驟，可能soft parsing, hard parsing不适合T-SQL。它的專署流程是這樣的: Parsing->Binding->Transformation->Simplification|Trivial Plan|Full Optimization->Execution. 從輸出角度看，分别對應了 parse tree -> algebrized tree->memo(s)->execution plan->result
上面講到parse和binding了，下面開始讨論transformation。
簡單講，transformation就是将sql源代碼根據transformation rules轉換成各種optimizer可以了解的邏輯和實體操作，這些轉換後的表達式都放在叫做Memo的記憶體裡面。問題來了，Memo究竟占用了多少記憶體，transformation的深度有多少，肯定不會是全部都周遊一邊，那麼規則是怎麼樣定的?最後一個問題，為什麼要有transformation?其實這麼問為什麼要有transformation挺傻的，既然要有最優計劃，當然是要羅列所有可能的執行路徑了，比如join, 實際上可能有merge join, hash join等等， 那麼将這些可能性都羅列出來才有可能進行下一步的比較。Transformation Rules有哪些呢？其實這裡講的就是将邏輯處理單元轉成各種實體算法的轉換規則，有可互換原則(commutative rules)，可互協原則(associative rules)，實作原則(implementation rules)等。Commutative rules就是 A JOIN B可換成B JOIN A； Associative Rules就是 (A JOIN B) JOIN C 可轉成A JOIN (B JOIN C), Implementation Rule就是 A JOIN B轉成 A MER JOIN B or A HASHJOIN B. 有意思的是，我們通過觀察這個DMV， 可以看到很多的Transformation rules被用到了SQL Server裡面:
select * from sys.dm_exec_query_transformation_stats
SQL SERVER 2014中已經到了394種 Transformation rules。如果一條SQL語句要經過這麼多轉換來擷取最優的執行計劃，本身就是耗費性能的一件事，而Memo中還隻是存儲了每個執行計劃的成本，并沒有生成這些執行計劃的成本 。當然在我們非常熟練的情況下，我們可以禁用掉一些transformation rules，dbcc ruleson 和dbcc rulesoff 就是用來開啟和禁用Transformation rules的指令。我們也可以用hint來忽略掉一些rules進而確定執行計劃最優而不增加生成執行計劃的成本。Hint顯然比dbcc rulesoff有效的多，dbcc rulesoff在production環境引用的時候，會幹擾所有執行計劃，不建議使用 。通過截獲SQL時間前後的sys.dm_exec_query_transformation_stats統計，我們可以看到所有transformation rules被用到了這個SQL裡面，但是僅在你一個人用SERVER的情況下才正确。我們還可以使用 dbcc showoffrules可以看到目前SQL Server禁用掉的transformation rules.
dbcc traceon()
dbcc showoffrules
trace flag 3604表示将所有的dbcc 結果從預設的輸出改到傳輸到用戶端。
看下 transformation rules的整個過程，舉一個簡單的例子：
SELECT FirstName, LastName, CustomerType
FROM Person.Contact AS C
JOIN Sales.Individual AS I
ON C.ContactID = I.ContactID
JOIN Sales.Customer AS Cu
ON I.CustomerID = Cu.CustomerID
這個查詢就是join三張表，在Memo中，一個transformation就換到一個group裡面。
上面的幾個表格，就是各個transformation rule被應用到SQL上面的整個過程。第一張圖，産生了一個transformation rule applied之後的邏輯執行樹，Group 1,2,3,4,5分别代表了一個操作，Group 6是root節點，可以看到這裡是從上到下的分層，最小的group是最先執行的。第二，第三個圖是又産生了幾個邏輯執行計劃的結果 。第四張圖很重要，就是最後一個環節，生成各種不同的實體執行計劃，在這個時候，就會加上各個計劃的成本了。實際上，這個時候還不是評估計劃成本的時候，隻不過已經生成了可以附加計劃成本的對象而以，這個對象就是各種存在Memo裡面的邏輯表示樹
經過Transformation rules應用過濾後，就到最後一個優化環節了。 優化還有3個步驟，第一simplification, trivial plan以及full optimize。 Full Optimize還分 Search 0, Search 1, Search 2. Simplification 簡單來講概念就是去重，比如有where條件了，就不用Foreign Key限制，或者join的時候，提前将where條件放到join裡面去做限制以減少資料集；Trivial Plan就是不經曆full optimize 直接産生執行計劃，這裡要注意的執行計劃屬性StatementOptmLevel, 走Trivial Plan，他的值就是Trivial,如果不走Trivial Plan就是 Full(Optimize)。
Full Optimize的原則，從Search 0, Search 1, Search 2來分别做Transformation rule的應用，評估計劃成本，任何一個stage出現最優計劃，就直接使用。Search 0步驟做的主要工作就是更改Join Order來擷取最小的資料集；Search 1接收到Search 0拿到的最優計劃，進行parallel分析以得到最優計劃，同樣也要經過應用transformation rules， 在sys.dm_exec_query_optimizer_info這個DMV裡面，有這麼個Counter ，叫做 gain stageo 0 to stage1，表達的意思是stage1提升了多少成本，比如0.45923就是50%; Search 2叫做Full optimization，最終産生計劃的地方，同樣也有gain stage1 to stage2,表示提升的百分比 。Stage 2 (search 2)裡面有個特别的概念,Time Out 事件。這個time out是通過transformation rules的應用狀況和已經流失時間的總和計算的，如果超過這個時間，我們在執行計劃的Reason For Early Termination Of Statement Optimization屬性裡看到Time Out,還可以在sys.dm_exec_query_optimizer_info DMV裡面看到。這個 Time Out出現的時間也很有講究，Search 0 這個階段還處于改寫邏輯樹階段，沒有真正可用的執行計劃産生，這個階段沒有Time Out事件。
一些有意思的話題，但是并不适合放在優化裡面講的，但是很重要也很有意思：
1 Query Parameter: 一種是SQL語句hard code參數，一種是參數探嗅(parameter sniffing).
Hard code 參數的示例:
declare @ptableName varchar() = 'fctdbsize'
select * from sys.tables where name = @ptableName
另一種參數探嗅的示例:
貌似上面一種情況是針對oracle優化器的，sql server優化器還沒那麼智能可以識别出這種參數探嗅的模式，隻能對語句一模一樣的SQL進行軟解析。這裡有特殊，除非是明顯不會影響查詢計劃的參數化，比如用主鍵來做等于限制，無論怎麼樣都是做cluster index seek，是以SQL SERVER優化器就自動參數化了。
dbo.dimstatisticscounters這個表以row_id作為主鍵，而且用了相等比對模式 row_id = 2890，是以隻走cluster index seek而且隻傳回一條資料 。這樣的查詢模式就會被自動參數化。
察看執行計劃，這裡有 where (row_Id) = @1 的提示 。
而且理論上， SQL TEXT也會被改寫，但是這裡并沒有。
select text
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle)
where text like '%dimstatisticscounters WHERE row_id%'
[@ int ]select * from dbo.dimstatisticscounters WHERE row_id = @
期望是這個 SQL TEXT， 結果并沒有 。
當我們使用非主鍵作搜尋時，就不會被參數化（這裡貌似也可以做參數化，例子不好還是SQL SERVER 2014更改了?）
由于這麼多不确定性 ，我們還是使用sp_executesql來強制化走參數化以便降低解析成本 。
declare @stat nvarchar(max) = N'select * from dbo.dimstatisticscounters where row_id = @rowid'
exec sp_executesql @stmt = @stat, @params = N'@rowid int' , @rowid =
(@rowid int)select * from dbo.dimstatisticscounters where row_id = @rowid
可見，省事兒多了。直接就根據參數編譯了語句，生成的執行計劃就再也不變了。
更多的是針對同一條帶參數的查詢 ，根據 column statistics的統計資訊(histogram)來判斷是走index scan還是index seek。為什麼會有這兩種執行方案呢? 還是要看index的存儲資訊來确定。假如index的Key存儲了很多RID或者KEY，一邊掃描就能找到很多記錄(sequential read)，而不象index seek，用的是random read就會慢很多。
create procedure dbo.gettopdbsize
(@dbsize int )
as
begin
select top(10) * from dbo.fctdbsize
where size = @dbsize
end
go
根據 @dbsize的傳入值，執行計劃會有不同，seek與scan的差別。如果想要執行計劃一直按照seek或者scan計算 ，我們可以用option(optimize for(@dbsize = 1000)) :
alter procedure dbo.gettopdbsize
(@dbsize int )
as
begin
select top(10) * from dbo.fctdbsize
where size = @dbsize
option(optimize for (@dbsize = 100))
end
go
用本地變量來hard code參數，其實不會産生不同的執行計劃，舉個例子，我們将上面的存儲過程改寫，可以使得它隻産生唯一計劃：
alter procedure dbo.gettopdbsize
(@dbsize int )
as
begin
declare @dbsize_local int = @dbsize
select top() * from dbo.fctdbsize
where size = @dbsize_local
end
go
用optimize for unknown也可以得到類似地功效：
alter procedure dbo.gettopdbsize
(@dbsize int )
as
begin
select top() * from dbo.fctdbsize
where size = @dbsize
option(optimize for unknown)
end
go
Hints, 唯一可以改變query optimizer産生執行計劃的方法：
1 query hint : 用option來分隔SQL語句與hint語句
Select xxx from table_name option(table tableName index(indexName)):
select object_id,column_id ,column_name
from siebeldbTableSchema
where table_name = 'S_CONTACT' and column_id = and object_id =
option( table hint (siebeldbTableSchema,index(idx_colstr_sts)))
這裡還有這麼一個坑，就是exposed object name必須和 from的表對象引用一緻，否則出現類似這個錯誤：
select object_id,column_id ,column_name
from siebeldbTableSchema
where table_name = 'S_CONTACT' and column_id = and object_id =
option( table hint (dbo.siebeldbTableSchema,index(idx_colstr_sts)))
Msg , Level , State , Line
Cannot execute query. Object 'dbo.siebeldbTableSchema' is specified in the TABLE HINT clause, but is not used in the query or does not match the alias specified in the query. Table references in the TABLE HINT clause must match the WITH clause.
2 Join hint: 指定采取join的方式
Left|Right|Full{Loop|merge|hash} join:
select top f.record_date, d.object_name, d.counter_name, d.instance_name , f.cntr_value from dimstatisticscounters d
inner merge join fctstatisticscollection f on f.row_id = d.row_id
3。Table hint :針對單張表作hint ，一般是指定某個具體的索引
With(index(index_name)):
select object_id,column_id ,column_name
from siebeldbTableSchema with(index(idx_obj_col_id))
where column_id = and object_id =
這三種索引地寫法除了第一種是獨立于SQL之外的，其他都是要嵌入到SQL内部的，寫法比較複雜。
每種hint的寫法都會有自己适用的場合， 比如想要單個join實作某一特定join type，我們隻要用join hint就可以了，但是如果全局的join都要使用一種或者兩種join type,那麼用 join hint就比較麻煩了，用Query hint就比較友善:
select top f.record_date, d.object_name, d.counter_name, d.instance_name , f.cntr_value
from dimstatisticscounters d
inner merge join fctstatisticscollection f on f.row_id = d.row_id
inner merge join fctdbsize dbs on dbs.record_date = f.record_date
這裡每個join都用了join hint,不友善的地方就是有多少join就要寫多少merge join，是以不友善。但是我們要換了query join，就簡單了：
select top f.record_date, d.object_name, d.counter_name, d.instance_name , f.cntr_value
from dimstatisticscounters d
inner join fctstatisticscollection f on f.row_id = d.row_id
inner join fctdbsize dbs on dbs.record_date = f.record_date
option(merge join)
還可以指定使用三種join方式的其中兩種，具體哪一種由優化器判斷:
select top f.record_date, d.object_name, d.counter_name, d.instance_name , f.cntr_value
from dimstatisticscounters d
inner join fctstatisticscollection f on f.row_id = d.row_id
inner join fctdbsize dbs on dbs.record_date = f.record_date
option(merge join, hash join)
當然，得在适合的場合用join，比如merge join, hash join就得用在相等的Join條件下:
select top f.record_date, d.object_name, d.counter_name, d.instance_name , f.cntr_value
from dimstatisticscounters d
inner merge join fctstatisticscollection f on f.row_id > d.row_id
Msg , Level , State , Line
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
這裡的join條件是f.row_Id>d.row_id，是以不是merge join, hash join适用的場景，query optimizer保證不會産生無效的計劃。
Force Order ：option(Force Order)
select top d.*,sc.*,db.*
from dbo.dimstatisticscounters d
inner join dbo.fctstatisticscollection sc
on d.row_id = sc.row_id
inner join dbo.fctdbsize db
on db.record_date = sc.record_date
option(Force Order)
select top d.*,sc.*,db.*
from dbo.dimstatisticscounters d
inner join dbo.fctstatisticscollection sc
inner join dbo.fctdbsize db
on db.record_date = sc.record_date
on d.row_id = sc.row_id
option(Force Order)
先看沒有option(Force Order)的處理 ：
select top d.*,sc.*,db.*
from dbo.dimstatisticscounters d
inner join dbo.fctstatisticscollection sc
on d.row_id = sc.row_id
inner join dbo.fctdbsize db
on db.record_date = sc.record_date
執行計劃并沒有按照從上到下的join order來産生執行計劃。而是第二和第三個表先做了join. 最後才和第一個表作join.
比較下option(Force Order)的執行計劃：
select top d.*,sc.*,db.*
from dbo.dimstatisticscounters d
inner join dbo.fctstatisticscollection sc
on d.row_id = sc.row_id
inner join dbo.fctdbsize db
on db.record_date = sc.record_date
option(Force Order)
從上到下，依次join，速度上也快了很多。
有意思的是，這裡有這麼個寫法 :
SELECT A.*,B.*,C。*
FROM xxx A
Join xxx B
Join xxx C on B.xx = C.xx
On A.xxx = B.xxx
Join原來是可以嵌套寫的。 嵌套的Join，兩表的on必須緊挨着寫，再寫外層的Join On條件。是以下面的寫法是錯的：
SELECT A.*,B.*,C。*
FROM xxx A
Join xxx B
Join xxx C
on A.xx = C.xx
On C.xxx = B.xxx
Force Order 對aggregation的影響：
select d.row_id, count(f.record_date) as instances
from dbo.dimstatisticscounters d
inner join dbo.fctstatisticscollection f on d.row_id = f.row_id
group by d.row_id
先join之前做了aggregation。
select d.row_id, count(f.record_date) as instances
from dbo.dimstatisticscounters d
inner join dbo.fctstatisticscollection f on d.row_id = f.row_id
group by d.row_id
option(force order)
這一部分，先完成join, 再完成aggregation。這樣先篩選出要做aggregation的記錄 ，再完成計算就省去一部分不需要做計算的記錄，在這個case裡面，省去2/3的時間。
綜上， Force Order 可以改變多表join的次序，還可以更改aggregation在join的位置，是在join之前還是join之後。
Index 與 Force Seek
select * from dbo.fctdbsize
where record_date= ‘2016-02-22’
select * from dbo.fctdbsize
with(index(0))
where record_date= ‘2016-02-22’
上面的例子，第一個沒有用index hint,結果執行計劃走了bookmark lookup.第二個例子用了index hint,是以沒有走bookmark lookup. 在沒有主鍵和聚集索引的情況下，走table scan，加上資料量大，耗時比較多。
select distinct record_date from dbo.fctdbsize with(index(idx_recordDate),forceseek)
where record_date= '2016-02-22'
這裡還可以配合forceseek一起用。
歡迎關注個人微信公衆号