统计信息概念
统计信息是一些对象，这些对象包含在表或索引视图中一列或多列中的数据分布有关的统计信息。数据库查询优化器使用这些统计信息来估计查询结果中的基数或行
数。 通过这些基数估计，查询优化器可以生成高质量的执行计划。
例如，查询优化器可以使用基数估计选择索引查找运算符而不是耗费更多资源的索引扫描运算符，从而提高查询性能。[参考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，因为该索引不是统计信息集合。