SQL Server STATISTICS [英] SQL Server STATISTICS

问题描述

所以对于这个项目，我们有一堆定期执行的查询（每分钟左右，我使用数据库引擎中的分析查询来检查它们。）

So for this one project, we have a bunch of queries that are executed on a regular basis (every minute or so. I used the "Analyze Query in Database Engine " to check on them.

它们很简单：
select * from tablex where processed ='0'

They are pretty simple: select * from tablex where processed='0'

每个查询应在一个包含1MM记录的表上返回< 1000行。

There is an index on processed, and each query should return <1000 rows on a table with 1MM records.

分析师建议在此....上创建一些统计数据...因此我的问题是：统计？他们真的帮助性能吗？他们对上面的表有多大的成本？

The Analyzer recommended creating some STATISTICS on this.... So my question is: What are those statistics ? do they really help performance ? how costly are they for a table like above ?

请记住，我不会自称SQL Server经验丰富的用户...这是第一次使用此分析器。

Please bear in mind that by no means I would call myself a SQL Server experienced user ... And this is the first time using this Analyzer.

推荐答案

统计信息是SQL Server用来确定如何

Statistics are what SQL Server uses to determine the viability of how to get data.

例如，假设您有一个在主键上只有一个聚集索引的表，当执行 SELECT * FROM tablename WHERE col1 = value ，SQL Server只有一个选项，可以扫描表中的每一行以找到匹配的行。

Let's say, for instance, that you have a table that only has a clustered index on the primary key. When you execute SELECT * FROM tablename WHERE col1=value, SQL Server only has one option, to scan every row in the table to find the matching rows.

现在我们在col1上添加一个索引，所以你假设SQL Server将使用索引来查找匹配的行，但这不总是真的。假设表有20万行，而 col1 只有2个值：1和0.当SQL Server使用索引查找数据时，索引包含指向集群索引位置。考虑到索引列中只有两个值，SQL Server认为只扫描表格更有意义，因为使用索引会更有效。

Now we add an index on col1 so you assume that SQL Server will use the index to find the matching rows, but that's not always true. Let's say that the table has 200,000 rows and col1 only has 2 values: 1 and 0. When SQL Server uses an index to find data, the index contains pointers back to the clustered index position. Given there's only two values in the indexed column, SQL Server decides it makes more sense to just scan the table because using the index would be more work.

现在我们向表中添加另外800,000行数据，但是此时 col1 中的值差异很大。现在它是一个有用的索引，因为SQL Server可以可行地使用索引来限制需要从表中拉出。 SQL Server会使用索引吗？

Now we'll add another 800,000 rows of data to the table, but this time the values in col1 are widely varied. Now it's a useful index because SQL Server can viably use the index to limit what it needs to pull out of the table. Will SQL Server use the index?

这取决于。而它所依赖的是统计。在某些时间点，使用 AUTO UPDATE STATISTICS 设置，服务器将更新索引的统计信息，并且知道它是一个非常好的和有效的索引使用。在这之前，它将忽略该索引是不相关的。

It depends. And what it depends on are the Statistics. At some point in time, with AUTO UPDATE STATISTICS set on, the server will update the statistics for the index and know it's a very good and valid index to use. Until that point, however, it will ignore the index as being irrelevant.

这是统计的一个用途。但是还有另一个用途，那就是与索引无关。 SQL Server保存有关表中所有列的基本统计信息。如果有足够的不同数据使其值得，SQL Server将实际上在列上创建一个临时索引，并使用它来过滤。虽然这比使用现有索引花费更多的时间，但比全表扫描花费的时间更少。

That's one use of statistics. But there is another use and that isn't related to indices. SQL Server keeps basic statistics about all of the columns in a table. If there's enough different data to make it worthwhile, SQL Server will actually create a temporary index on a column and use that to filter. While this takes more time than using an existing index, it takes less time than a full table scan.

有时您会得到建议，以便为列创建特定的统计信息有用的。这些不是索引，但是跟踪列中数据的统计抽样，因此SQL Server可以确定是否有意义创建一个临时索引以返回数据。

Sometimes you will get recommendations to create specific statistics on columns that would be useful for that. These aren't indices, but the do keep track of the statistical sampling of data in the column so SQL Server can determine whether it makes sense to create a temporary index to return data.

HTH

这篇关于SQL Server STATISTICS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！