SQL服务器统计资料[英] SQL Server STATISTICS

本文是小编为大家收集整理的关于SQL服务器统计资料的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

因此,对于这个一个项目,我们有很多查询定期执行(每分钟左右.我都使用"分析数据库引擎中的查询"来检查它们.

他们很简单: 从tablex选择 * processed ='0'

处理上有一个索引,每个查询都应在带有1mm记录的表上返回<1000行.

分析仪建议对此创建一些统计数据....所以我的问题是:这些统计数据是什么?他们真的有助于表现吗?像上面的桌子一样,它们的昂贵程度?

请记住,我绝不会称自己为SQL Server经验丰富的用户...这是第一次使用此分析仪.

推荐答案

统计信息是SQL Server用来确定如何获取数据的可行性的方法.

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

现在,我们在COL1上添加了索引,因此您假设SQL Server将使用索引查找匹配行,但这并不总是正确的.假设表具有200,000行,col1只有2个值:1和0.当SQL Server使用索引查找数据时,该索引将指示器包含回到群集索引位置.鉴于索引列中只有两个值,SQL Server认为仅扫描表更有意义,因为使用索引将是更多的工作.

现在,我们将在表中添加另外800,000行数据,但是这次col1中的值差异很大.现在,这是一个有用的索引,因为SQL Server可以通过使用索引来限制其从表中撤出所需的内容. SQL Server会使用索引吗?

这取决于.这取决于统计数据.在某个时间点,使用AUTO UPDATE STATISTICS设置,服务器将更新索引的统计信息,并知道它是一个非常好的有效的索引.但是,在那之前,它将忽略该索引是无关紧要的.

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

.

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

hth

其他推荐答案

在SQL Server 2005中,设置自动创建统计信息和自动更新统计信息.您不必担心创建它们或自己维护它们,因为数据库本身很好地处理了这一点.

本文地址:https://www.itbaoku.cn/post/597597.html

问题描述

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.

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

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 ?

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.

推荐答案

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

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.

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.

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?

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.

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.

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

其他推荐答案

In Sql Server 2005, set auto create statistics and auto update statistics. You won't have to worry about creating them or maintaining them yourself, since the database handles this very well itself.