SQL Server统计中究竟保存了什么？什么时候更新？ SQL Server本身是否正在处理它们？

I have been working with SQL Server as a Developer a while.

我一直在使用SQL Server作为开发人员。

One thing I learnt is SQL Server manages Statistics which help Engine to create optimized execution plan.

我学到的一件事是SQL Server管理统计数据，帮助Engine创建优化的执行计划。

I could not figure out what exactly is stores in Statistics? (I read it saves Vector, but what Vector?)

我无法弄清楚统计中究竟是什么商店？（我读它保存Vector，但是什么Vector？）

When/In which scenario SQL Server updates Statistics?

何时/在哪种情况下SQL Server更新统计信息？

How/why some time they go out of sync (old Statistics)

如何/为什么有些时候他们不同步（旧统计）

In case of old Statistics is a manual DBA/Developer intervention is required or SQL Server Will get them updated.

如果是旧的统计信息是需要手动DBA / Developer干预或SQL Server将更新它们。

As a DBA/Developer how to find out if Statistics OLD? What should we do?

作为DBA / Developer如何查出统计OLD？我们应该做什么？

2 个解决方案

#1

Statistics in this context refers to a sampling that the RDBMS takes of the values of a given index. Roughly speaking, this gives the engine an idea of the distribution of values, and helps it to plan efficient queries. You can see the actual contents of a statistics set using DBCC SHOW_STATISTICS.

此上下文中的统计信息是指RDBMS采用给定索引值的抽样。粗略地说，这使引擎了解了值的分布，并帮助它规划有效的查询。您可以使用DBCC SHOW_STATISTICS查看统计信息集的实际内容。

DBCC SHOW_STATISTICS (table_name, index_name)

Statistics on an index can become outdated over time as the data in the table -- and therefore the distribution of the index values -- changes. This can result in less than optimal query execution plans, which is why you should aim to keep statistics up-to-date.

随着时间的推移，索引的统计信息可能会过时，因为表中的数据 - 以及索引值的分布 - 会发生变化。这可能导致查询执行计划不够理想，这就是为什么您应该将统计数据保持最新。

You can update statistics manually, or set them to update automatically, using the UPDATE STATISTICS T-SQL command. From the first MSDN link:

您可以使用UPDATE STATISTICS T-SQL命令手动更新统计信息，或将其设置为自动更新。从第一个MSDN链接：

When the automatic update statistics option, AUTO_UPDATE_STATISTICS, is on, the query optimizer determines when statistics might be out-of-date and then updates them when they are used by a query.

当启用自动更新统计信息选项AUTO_UPDATE_STATISTICS时，查询优化器会确定统计信息何时可能已过期，然后在查询使用它们时更新它们。

#2

You can see if your statistics are up-to-date with:

您可以查看您的统计信息是否与以下内容保持同步：

select  object_name(ind.object_id) as TableName
,       ind.name as IndexName
,       stats_date(ind.object_id, ind.index_id) as StatisticsDate
FROM    sys.indexes ind
order by 
        stats_date(ind.object_id, ind.index_id) desc

#1

DBCC SHOW_STATISTICS (table_name, index_name)