SQL Server 查找统计信息的相关采样信息

sql server 查找统计信息的相关采样信息

 

有时候我们会遇到，由于统计信息不准确导致优化器生成了一个错误的执行计划（或者这样表达：一个较差的执行计划），从而引起了系统性能问题。那么如果我们怀疑这个错误的执行计划是由于统计信息不准确引起的。那么我们如何判断统计信息不准确呢？当然首先得去查看实际执行计划中，统计信息的相关数据是否与实际情况有较大的出入，下面我们抛开这个大命题，仅仅从统计信息层面去查看统计信息的更新时间，统计信息的采样行数等情况。

 

1：首先，我们要查查统计信息是什么时候更新的。

 

2：其次，我们查看统计信息的采样信息：采样选取的行数、自上次更新统计信息以来前导统计信息列（构建直方图的列）的总修改次数。。。 

 

查看统计信息的最后更新时间。

 

方法1：

 

--查看统计信息的更新时间

declare @tablename nvarchar(128);

set @tablename = 'dbo.pbcutclothcost';

select  @tablename  as table_name,

        name as stats_name ,

        stats_date(object_id, stats_id) as last_stats_update

from    sys.stats

where   object_id = object_id(@tablename)

order by 2 desc;

 

 

 

 

 

如上所示，我们通过这个脚本查看某个表，所有的统计信息最后一次更新时间。如果你需要查看某个具体的统计信息的最后更新时间，那么在这个sql的基础上增加相关查询条件即可。

 

方法2：

 

 

--查看统计信息的更新时间

exec sp_autostats 'dbo.pbcutclothcost';

 

 

方法3：

 

还有一种方法可以通过 sys.dm_db_stats_properties 返回统计信息的更新时间，不过这个dmf只有sql server 2008 r2 sp2这个版本之后的才有。

 

列名	数据类型	description
object_id	int	要返回统计信息对象属性的对象（表或索引视图）的 id。
stats_id	int	统计信息对象的 id。 在表或索引视图中是唯一的。 有关详细信息，请参阅 sys.stats (transact-sql)。
last_updated	datetime2	上次更新统计信息对象的日期和时间。 有关详细信息，请参阅此页中的备注部分。
rows	bigint	上次更新统计信息时表或索引视图中的总行数。 如果筛选统计信息或者统计信息与筛选索引对应，该行数可能小于表中的行数。
rows_sampled	bigint	用于统计信息计算的抽样总行数。
step	int	直方图中的值范围数（步长）（number of steps in the histogram）。 有关详细信息，请参阅 dbcc show_statistics (transact-sql)。
unfiltered_rows	bigint	应用筛选表达式（用于筛选的统计信息）之前表中的总行数。 如果未筛选统计信息，则 unfiltered_rows 等于行列中返回的值。
modification_counter	bigint	自上次更新统计信息以来前导统计信息列（构建直方图的列）的总修改次数。 内存优化表： 正在启动sql server 2016 (13.x)并在azure sql database此列包含： 修改因为最后一个时间统计信息已更新或重新启动数据库的表的总次数。
persisted_sample_percent	float	持久样本百分比用于未显式指定采样百分比的统计信息更新。 如果值为零，则不为此统计信息设置持久样本百分比。 适用范围：sql server 2016 (13.x) sp1 cu4

 

 

 

查看采样的相关信息

 

 

 

select sch.name + '.' + so.name as table_name

      , so.object_id

      , ss.name  as stat_name

      , ds.stats_id

      , ds.last_updated

      , ds.rows

      , ds.rows_sampled

      , ds.steps

      , ds.unfiltered_rows

      , ds.modification_counter 

from sys.stats ss

join sys.objects so on ss.object_id = so.object_id

join sys.schemas sch on so.schema_id = sch.schema_id

cross apply sys.dm_db_stats_properties(ss.object_id,ss.stats_id) ds

where so.name =  n'pbcutclothcost'

    and   left(ss.name, 4) != '_wa_';

 

 

 

 

如上截图，索引ix_cutclothcost的统计信息有更新，是因为在执行上面脚本，我更新了这个索引的统计信息。通过rows与实际记录数对比、 modification_counter信息，我们从而有个大概的判断，这些统计信息是否过时。是否采样的比例太小等。

select sch.name + '.' + so.name as table_name

      , so.object_id

      , ss.name  as stat_name

      , ds.stats_id

      , ds.last_updated

      , ds.rows

      , ds.rows_sampled

      , ds.rows_sampled/ds.rows *100 as sample_rate

      , ds.steps

      , ds.unfiltered_rows

      , ds.modification_counter 

from sys.stats ss

join sys.objects so on ss.object_id = so.object_id

join sys.schemas sch on so.schema_id = sch.schema_id

cross apply sys.dm_db_stats_properties(ss.object_id,ss.stats_id) ds

where so.name =  n'pbcutclothcost'

    and   left(ss.name, 4) != '_wa_';