統計資訊概念
統計資訊是一些對象，這些對象包含在表或索引視圖中一列或多列中的資料分布有關的統計資訊。資料庫查詢優化器使用這些統計資訊來估計查詢結果中的基數或行
數。 通過這些基數估計，查詢優化器可以生成高品質的執行計劃。
例如，查詢優化器可以使用基數估計選擇索引查找運算符而不是耗費更多資源的索引掃描運算符，進而提高查詢性能。[參考msdn]
其實如果你以前沒有接觸過統計資訊，你可以将其看做是資料庫為了得到最優的執行計劃，統計資料庫裡面表、索引等對象的一些資料，例如表的記錄數、所有列的
平均長度、直方圖....等一些優化器需要用到的資料資訊。sql查詢優化器是一個基于成本的優化器，類似于oracle裡面的cbo，那麼優化器如果要
得到成本最低的執行計劃，就需要收集擷取其生成執行計劃的參考依據（統計資訊資料）
如果你對這些概念性的東西比較模糊的話，那麼為了讓你形象的認識一下統計資訊，請看下圖：
統計資訊參數
資料庫的統計資訊相關參數有三個: 自動建立統計資訊（auto
create statistics）、自動更新統計資訊（auto update statistics）、自動異步更新統計資訊（auto
update statistics asynchronously），它們都是資料庫級别的。
自動建立統計資訊（auto create statistics）
該參數指定資料庫是否自動建立缺少的優化統計資訊。如果設定為true，
則将在優化過程中自動生成優化查詢需要但缺少的所有統計資訊。當開啟自動建立統計資訊（auto create
statistics）選項時，查詢優化器會對在謂詞中使用到的列，如果這些列的統計資訊不可用或缺少時，則會單獨對每列建立統計資訊。這些統計資訊對創
建一個查詢計劃非常必要。它們建立于那些現有統計對象中不存在直方圖的列上，名字包括列名和對象id的十六進制格
式：_wa_sys_<column_name>_<xxxx>。這些統計資訊用于查詢優化器決定使用何種優化後的執行計劃。
自動更新統計資訊（auto update statistics）
該參數指定資料庫是否自動更新過期的優化統計資訊。如果設定為true，則将在優化過程中自動生成優化查詢需要但已過期的所有統計資訊。否則不自動更新統計資訊。
自動異步更新統計資訊（auto update statistics asynchronously）
如果設定為 true，則啟動過期統計資訊的自動更新，查詢在編譯前不會等待統計資訊被更新。後續查詢将使用可用的已更新統計資訊。如果設定為 false，則啟動過期統計資訊的自動更新的查詢将等待，直到更新的統計資訊可在查詢優化計劃中使用。将該選項設定為 true 不會産生任何影響，除非“自動更新統計資訊”也設定為 true
關于這三個參數，一般建議開啟自動建立統計資訊、自動更新統計資訊選項，關閉自動異步更新統計資訊。
1：對于自動建立統計資訊選項，因為統計資訊對查詢優化器至關重要，沒有統計資訊，也就失去了它基于成本的優化器的意義，成為無土之木、無源之水。
2：對于自動更新統計資訊選項，有時候過期的統計資訊會導緻嚴重的性能問題，我都碰到過好幾起因為過時統計資訊導緻sql查詢性能問題的案例，就像錯誤的
地圖、錯誤的導航、錯誤的指路會讓你偏離目的地那樣，過時的統計資訊往往導緻查詢優化器選擇了次優的執行計劃，産生糟糕的性能問題，是以這個參數必須開
啟。
3：對于自動異步更新統計資訊選項，這個選項在oltp環境下很有用，但在資料倉庫中有負面影響。至于是否開啟，我建議是關閉。預設也是關閉的。不知道這種觀念是否正确。
下面是msdn給予的使用同步更新統計資訊\異步更新統計資訊的參考意見：
------------------------------------------------------------------------------------------------------------------------
在以下情況下應考慮使用同步統計資訊：
· 您執行會更改資料分布的操作，例如截斷表或對很大百分比的行執行大容量更新。如果您在完成該操作後未更新統計資訊，則使用同步統計資訊将確定對更改的資料執行查詢前統計資訊是最新的。
在以下情況下，考慮使用異步統計資訊來實作可預測性更高的查詢響應時間：
·
您的應用程式頻繁執行相同的查詢、類似的查詢或類似的緩存查詢計劃。與同步統計資訊更新相比，使用異步統計資訊更新時您的查詢響應時間可能具有更高的可預
· 您的應用程式遇到了用戶端請求逾時，這些逾時是由于一個或多個查詢正在等待更新後的統計資訊所導緻的。在某些情況下，等待同步統計資訊可能會導緻應用程式因過長逾時而失敗。
那麼先看一下如何通過sql來檢視這三個參數的設定值：
code snippet
select name ,
is_auto_create_stats_on ,
is_auto_update_stats_async_on ,
is_auto_close_on
from sys.databases ;
select case when databasepropertyex('dbmonitor', 'isautocreatestatistics') = 1
then 'yes'
else 'no'
end as 'isautocreatestatistics' ,
case when databasepropertyex('dbmonitor', 'isautoupdatestatistics') = 1
end as 'isautoupdatestatistics' ,
case when databasepropertyex('dbmonitor', 'is_auto_update_stats_async_on') = 1
end as 'isautoupdatestatsaayncon'
go
那麼這三個參數的值儲存在哪裡呢？其實隻要你稍微花一點心思去研究一下，就會發現
其實它是儲存在系統表[sys].[sysdbreg]裡面，[sys].[sysdbreg]是内部表，預設情況下不可檢視，一般你可以通過系統視圖
sys.database檢視和研究其值的出處。
set quoted_identifier on
set ansi_nulls on
create view sys.databases as
select d.name, d.id as database_id,
r.indepid as source_database_id,
d.sid as owner_sid,
d.crdate as create_date,
d.cmptlevel as compatibility_level,
convert(sysname, collationpropertyfromid(p.cid, 'name')) as collation_name,
p.user_access, ua.name as user_access_desc,
sysconv(bit, d.status & 0x400) as is_read_only, -- dbr_rdonly
sysconv(bit, d.status & 1) as is_auto_close_on, -- dbr_close_on_exit
sysconv(bit, d.status & 0x400000) as is_auto_shrink_on,-- dbr_autoshrink
p.state, st.name as state_desc,
sysconv(bit, d.status & 0x200000) as is_in_standby, -- dbr_standby
sysconv(bit, d.status & 0x40000000) as is_cleanly_shutdown, -- dbr_cleanly_shutdown
sysconv(bit, d.status & 0x80000000) as is_supplemental_logging_enabled,-- dbr_supplement_log
p.snapshot_isolation_state, si.name as snapshot_isolation_state_desc,
sysconv(bit, d.status & 0x800000) as is_read_committed_snapshot_on,-- dbr_readcommitted_snapshot
p.recovery_model, ro.name as recovery_model_desc,
p.page_verify_option, pv.name as page_verify_option_desc,
sysconv(bit, d.status2 & 0x1000000) as is_auto_create_stats_on, -- dbr_autocrtstats
sysconv(bit, d.status2 & 0x40000000) as is_auto_update_stats_on, -- dbr_autoupdstats
sysconv(bit, d.status2 & 0x80000000) as is_auto_update_stats_async_on, -- dbr_autoupdstatsasync
sysconv(bit, d.status2 & 0x4000) as is_ansi_null_default_on, -- dbr_ansinulldflt
sysconv(bit, d.status2 & 0x4000000) as is_ansi_nulls_on, -- dbr_ansinulls
sysconv(bit, d.status2 & 0x2000) as is_ansi_padding_on, -- dbr_ansipadding
sysconv(bit, d.status2 & 0x10000000) as is_ansi_warnings_on, -- dbr_ansiwarnings
sysconv(bit, d.status2 & 0x1000) as is_arithabort_on, -- dbr_arithabort
sysconv(bit, d.status2 & 0x10000) as is_concat_null_yields_null_on, -- dbr_catnull
sysconv(bit, d.status2 & 0x800) as is_numeric_roundabort_on, -- dbr_numeabort
sysconv(bit, d.status2 & 0x800000) as is_quoted_identifier_on, -- dbr_quotedident
sysconv(bit, d.status2 & 0x20000) as is_recursive_triggers_on, -- dbr_recurtrig
sysconv(bit, d.status2 & 0x2000000) as is_cursor_close_on_commit_on, -- dbr_curscloseoncom
sysconv(bit, d.status2 & 0x100000) as is_local_cursor_default, -- dbr_deflocalcurs
sysconv(bit, d.status2 & 0x20000000) as is_fulltext_enabled, -- dbr_ftenabled
sysconv(bit, d.status2 & 0x200) as is_trustworthy_on, -- dbr_trustworthy
sysconv(bit, d.status2 & 0x400) as is_db_chaining_on, -- dbr_dbchaining
sysconv(bit, d.status2 & 0x08000000) as is_parameterization_forced, -- dbr_universalautoparam
sysconv(bit, d.status2 & 64) as is_master_key_encrypted_by_server, -- dbr_mastkey
sysconv(bit, d.category & 1) as is_published,
sysconv(bit, d.category & 2) as is_subscribed,
sysconv(bit, d.category & 4) as is_merge_published,
sysconv(bit, d.category & 16) as is_distributor,
sysconv(bit, d.category & 32) as is_sync_with_backup,
d.svcbrkrguid as service_broker_guid,
sysconv(bit, case when d.scope = 0 then 1 else 0 end) as is_broker_enabled,
p.log_reuse_wait, lr.name as log_reuse_wait_desc,
sysconv(bit, d.status2 & 4) as is_date_correlation_on, -- dbr_datecorrelationopt
sysconv(bit, d.category & 64) as is_cdc_enabled,
sysconv(bit, d.status2 & 0x100) as is_encrypted, -- dbr_encryption
sysconv(bit, d.status2 & 0x8) as is_honor_broker_priority_on -- dbr_honorbrkpri
from master.sys.sysdbreg d outer apply openrowset(table dbprop, d.id) p
left join sys.syssingleobjrefs r on r.depid = d.id and r.class = 96 and r.depsubid = 0-- src_viewpointdb
left join sys.syspalvalues st on st.class = 'dbst' and st.value = p.state
left join sys.syspalvalues ua on ua.class = 'dbua' and ua.value = p.user_access
left join sys.syspalvalues si on si.class = 'dbsi' and si.value = p.snapshot_isolation_state
left join sys.syspalvalues ro on ro.class = 'dbro' and ro.value = p.recovery_model
left join sys.syspalvalues pv on pv.class = 'dbpv' and pv.value = p.page_verify_option
left join sys.syspalvalues lr on lr.class = 'lrwt' and lr.value = p.log_reuse_wait
where d.id < 0x7fff
and has_access('db', d.id) = 1
create table [sys].[sysdbreg]
(
[id] [int] not null ,
[name] [sys].[sysname] not null ,
[sid] [varbinary](85) null ,
[status] [int] not null ,
[status2] [int] not null ,
[category] [int] not null ,
[crdate] [datetime] not null ,
[modified] [datetime] not null ,
[svcbrkrguid] [uniqueidentifier] not null ,
[scope] [int] not null ,
[cmptlevel] [tinyint] not null
)
on[primary]
修改統計資訊參數
方法1：
----關閉資料庫dbmonitor自動建立統計資訊功能
use [master]
alter database [dbmonitor] set auto_create_statistics off with no_wait
--開啟資料庫dbmonitor自動建立統計資訊功能
alter database [dbmonitor] set auto_create_statistics on with no_wait
--關閉資料庫dbmonitor自動更新統計資訊功能
alter database [dbmonitor] set auto_update_statistics off with no_wait
--啟用資料庫dbmonitor自動更新統計資訊功能
alter database [dbmonitor] set auto_update_statistics on with no_wait
--關閉資料庫dbmonitor自動異步更新統計資訊功能
alter database [dbmonitor] set auto_update_statistics_async off with no_wait
--啟用資料庫dbmonitor自動異步更新統計資訊功能
alter database [dbmonitor] set auto_update_statistics_async on with no_wait
方法2：使用sp_dboption來啟用或禁用。
sp_dboption dbmonitor, 'auto update statistics', 'on';
sp_dboption dbmonitor, 'auto update statistics', 'off;
方法3：圖形化方法啟用或禁用
對應的圖像化操作：選擇所要修改的資料庫，單擊右鍵選項”屬性“，選擇左側的”選項“，則能看到這三個參數
在 auto_update_statistics 為 on
時，您可以覆寫資料庫範圍的統計資訊更新行為，并且根據您的應用程式的要求為單獨的表、索引或列将自動統計資訊更新設為關閉。在
auto_update_statistics 為 on 時，您可以通過以下方式為表、索引或列禁用和重新啟用自動統計資訊更新：
· 使用 sp_autostats 系統存儲過程。這可以禁用或重新啟用表或索引的統計資訊更新。
· norecompute 選項。
· 在
auto_update_statistics 為 off
時，不能為單獨的表、索引或列将自動更新設為打開。重新啟用自動統計資訊更新将還原auto_update_statistics 選項指定的行為。如果
auto_update_statistics 選項為off，統計資訊更新将不會發生。
建立統計資訊
如何建立統計資訊呢，如果選項自動建立統計資訊（auto create statistics）開啟了，那麼資料庫會自動建立某些統計資訊，另外你也可以通過create statistics 語句手動建立某些統計資訊。先看下面的例子：
use [dbmonitor]
if exists (select * from sys.objects where object_id = object_id(n'[dbo].[test]') and type in (n'u'))
drop table [dbo].[test]
select * into test from sys.objects;
此時你會看到表test根本沒有統計資訊，那麼我們在表test上建立一個索引idx_test_object_id
create index idx_test_object_id on dbo.test(object_id);
此時，你重新整理一下表test的統計資訊，你會發現多了一個名為“idx_test_object_id”的統計資訊,你建立多少條索引，在統計資訊下就會建立與索引同名的統計資訊。
select distinct type from dbo.test ;
執行上面面腳本後，我重新整理統計資訊，會發現多了一個名為“_wa_sys_00000006_023d5a04”的統計資訊，截圖如下：
那麼自動建立統計資訊的規律或規則是啥呢？或者了解為：什麼時候資料庫建立統計資訊，其實建立統計資訊的規則如下：
1：在索引建立時，sql server 會自動地在索引所在的列上建立統計資訊
2：當sql server想要使用某些列上的統計資訊，發現沒有時，sql server會自動建立統計資訊（前提是要開啟自動建立統計資訊）。例如上面統計distinct type。
3：手工使用create statistics之類的語句手工建立需要的統計資訊。
關于create statistics的文法，大家可以參考msdn，這裡不做闡述了。需要注意的是統計資訊可以通過全表掃描或随機抽樣應讀取的資料百分比或指定的資料行數，收集統計資訊
更新統計資訊
随着資料庫的dml操作，資料的變更會導緻統計資訊過期，那麼這時就需要更新統計資訊。通常資料庫通過兩種方式更新統計資訊：
1：如果開啟了自動更新統計資訊（auto update statistics）或自動異步更新統計資訊選項，那麼資料庫會自動更新統計資訊。
2：手動更新統計資訊
更新統計資訊確定查詢使用最新的統計資訊編譯。不過，更新統計資訊會導緻查詢重新編譯。我們建議不要太頻繁地更新統計資訊，因為需要在改進查詢計劃和重新編譯查詢所用時間之間權衡性能。此類特定的性能權衡取決于您的應用程式。
那麼資料庫什麼時候、什麼條件下才會更新統計資訊呢？
1、 在一個空表中有資料的改動。
2、 當統計資訊建立時，表的行數隻有500或以下，且後來統計對象中的引導列的更改次數大于500.
3、 當表的統計資訊收集時，超過了500行，且統計對象的引導列後來更改次數超過500+表總行數的20%時。
4、 在tempdb中的表，少于6行且最少有6行被更改
注意：資料庫不會為表變量收集統計資訊，是以資料量比較大時盡量不要使用表變量。
一般建議不要太頻繁地更新統計資訊，因為需要在改進查詢計劃和重新編譯查詢所用時間之間權衡性能。 這種特定的性能權衡取決于您的應用程式。
sp_autostats：顯示或更改特定索引或統計資訊的自動 update statistics 設定，或者顯示和更改目前資料庫中指定表或索引視圖的所有索引和統計資訊的自動 update statistics 設定
顯示表的所有索引的目前狀态
use dbmonitor;
go
exec sp_autostats 'dbo.test';
啟用表的所有索引的自動統計資訊
exec sp_autostats 'dbo.test','on'
禁用特定索引的自動統計資訊
exec sp_autostats 'dbo.test','off', 'idx_test_object_id';
檢視統計資訊
如果要了解具體的統計資訊内容，那麼我們首先要知道如何檢視具體的統計資訊，統計資訊儲存在那些系統視圖裡面，如果能很好的回答這兩個問題，那麼我想你也就能知道統計資訊的具體内容是那些了。關于第二個問題，後面章節部分再做探讨。
檢視統計資訊，我們先由淺入深，由簡單到複雜。
sp_helpstats ：傳回指定表中列和索引的統計資訊。
use dbmonitor;
exec sp_helpstats 'test' ,'all'
下面我們看看sys.sp_helpstats的腳本
2：檢視統計資訊一般用dbcc show_statistics指令，如下所示
雖然檢視統計資訊很容易，但是要讀懂并能讀取一些資訊那們就不是那麼簡單的了。
列名
描述說明
name
統計資訊對象名稱
update
上一次更新統計資訊的日期和時間
rows
在目标索引、統計資訊或列中的總行數。如果篩選索引或統計資訊，此行數可能小于表的行數。
rows sampled
用于統計資訊計算的抽樣總行數。
steps
統計資訊對象第一個鍵列的直方圖中的值範圍數。每個步驟包括在直方圖結果中定義的 range_rows 和 eq_rows。
density
查詢優化器不使用此值。顯示此值的唯一目的是為了向後相容。密度的計算公式為 1 / distinct rows，其中 distinct rows 是直方圖輸出中所有步驟的 distinct_range_rows 之和。如果對行進行抽樣，distinct rows 則基于抽樣行的直方圖值。
average key length
統計資訊對象的鍵列中，所有抽樣值中的每個值的平均位元組數
string index
如果為“是”，則統計資訊中包含字元串摘要索引，以支援為 like 條件估算結果集大小。僅當第一個鍵列的資料類型為char、varchar、nchar、nvarchar、varchar(max)、nvarchar(max)、text 或 ntext 時，才會對此鍵列建立字元串索引。
filter expression
包含在統計資訊對象中的表行子集的表達式。null = 未篩選的統計資訊。有關詳細資訊，請參閱篩選統計資訊。
unfiltered rows
應用篩選器表達式前表中的總行數。如果 filter expression 為 null，unfiltered rows 等于行标題值。
下表對指定 density_vector 時結果集中所傳回的列進行了說明。
說明
all density
針對統計資訊對象中的列的每個字首計算密度（1/ distinct_rows）。
密度包含所有抽樣行中的非重複行，包括帶有直方圖邊界點的行。結果為每個密度顯示一行。例如，如果統計資訊對象包含鍵列 (a, b, c)，結果将報告
(a)、(a,b) 以及 (a, b, c) 的密度。非重複行具有一個不同的列值向量。對于列 (a,b,c)，兩個不同的向量值的示例為
(4,5,6) 和 (4,5,7)。對于 (a,b)，相同的兩行具有一個不同的向量值 (4,5)。對于 (a)，存在一個不同的值 (4)。
average length
每個列字首的列值向量的平均長度（按位元組計）。例如，如果列字首為列 a 和 b，則長度為列 a 和列 b 的位元組之和。
columns
為其顯示 all density 和 average length 的字首中的列的名稱。
下表對指定 histogram 選項時結果集中所傳回的列進行了說明。
range_hi_key
直方圖步驟的上限值。
range_rows
表中位于直方圖步驟内（不包括上限）的行的估算數目。
eq_rows
表中值與直方圖步驟的上限值相等的行的估算數目。
distinct_range_rows
直方圖步驟内（不包括上限）非重複值的估算數目。
avg_range_rows
直方圖步驟内（不包括上限）重複值的頻率或平均數目（如果 distinct_range_rows > 0，則為 range_rows / distinct_range_rows）。
統計直方圖用作在查詢執行計劃中查詢優化器的選擇依據
圖形化檢視
删除統計資訊
drop statistics 删除目前資料庫的指定表中的多個集合的統計資訊。
drop statistics test._wa_sys_00000006_023d5a04;
需要注意的是不能用drop statistics 删除有關索引的統計資訊。統計資訊的保留時間與索引存在的時間相同，當你删除索引時，對應的統計資訊也自動删除。如下所示：
drop statistics test.idx_test_object_id
消息 3739，級别 11，狀态 1，第 1 行
無法對索引 'test.idx_test_object_id' 執行 drop，因為該索引不是統計資訊集合。