我发现了很多有关STATISTICS的信息:如何维护它们,如何从查询或索引中手动或自动创建它们,等等。但是,我无法找到有关何时创建它们的任何指导或“最佳实践”信息:在哪种情况下,手动创建的STATISTICS对象比从Index中受益更多。我已经看到了手动创建的筛选统计信息,可帮助对分区表进行查询(因为为索引创建的统计信息涵盖整个表,而不是每个分区-太小了!),但是肯定还有其他情况可以从统计对象中受益不需要索引的细节,也不需要花费维护索引或增加阻塞/死锁的机会的成本。

@JonathanFite在评论中提到了索引和统计数据之间的区别:


索引将通过创建与表本身排序不同的查找来帮助SQL更快地找到数据。统计信息可帮助SQL确定满足查询所需的内存/工作量。


这是非常有用的信息,主要是因为它可以帮助我阐明我的问题:

知道这一点(或与STATISTICS的行为和性质有关的方法和方式的任何其他技术信息)如何帮助确定何时选择CREATE STATISTICS而不是CREATE INDEX,尤其是在创建索引时会创建相关的STATISTICS对象?仅具有统计信息而不具有索引会更好地服务于哪种情况?

如果有一个STATISTICS对象为比INDEX更合适。


由于我是视觉学习者/思想家,所以我认为并排查看STATISTICSINDEX es之间的区别可能会有所帮助一种帮助确定STATISTICS何时是更好选择的可能方法。

 Thingy           PROs                             CONs
-------          ----------                       -------------------
INDEX            * Can help sorts.                * Takes up space.
                 * Contains data (can             * Needs to be maintained (extra I/O).
                   "cover" a query).              * More chances for blocking / dead-locks.

STATISTICS       * Takes up very little space.    * Cannot help sorts.
                 * Lighter maintenance / won't    * Cannot "cover" queries.
                   slow down DML operations.
                 * Does not increase chances
                   of blocking / dead-locks.
 



以下是我在寻找该资源时发现的一些资源,甚至有人问同样的问题,但未得到回答:

SQL Server索引与统计信息

SQL Server统计信息问题我们不敢问

统计信息。是否可以使用多列直方图?

**要清楚,我对此没有答案,实际上是希望从一些人那里得到反馈,以提供一些似乎奇怪的信息。在互连网上。

评论

索引将通过创建与表本身排序不同的查找来帮助SQL更快地找到数据。统计信息可帮助SQL确定满足查询所需的内存/工作量。

@JonathanFite谢谢您的评论。我已将其合并到我的问题中:)。

遵循@JonathanFite的评论,似乎统计数据最适合于提高即席系统/表/查询模式的性能,而索引最适合可预测的查询模式。我的意思是,这不仅仅是陈述,是问题。

#1 楼

您的问题围绕着-创建统计信息与创建索引(创建统计信息)何时是一件好事。

从我的sql server内部笔记(SQLSkills类IE1和IE2)和SQL Server内部这本书对我的理解有限:

SQL Server统计信息只不过是包含有关索引键值和常规列值的重要信息的系统对象。

SQL Server使用一个基于成本的模型,以尽快选择“足够好”的执行计划。可伸缩性估计(估计
查询执行的每个步骤上要处理的行数)是查询优化中最重要的因素,这反过来会影响连接策略,内存授予要求,工作线程选择以及访问数据时的索引选择。

SQL Server估计较大的数字时,它将不使用非聚集索引。 KEY或RID循环操作将是必需的,因此它维护了索引(和列)的统计信息,这将有助于这种估算。

关于统计信息有2个重要的事项:


直方图仅存储有关最左侧统计信息(索引)列的数据分布的信息。它还存储有关键值的多列密度的信息。因此,基本上,
直方图只存储最左侧统计信息列的数据分布。

SQL Server将在直方图中最多保留200个步骤,而与表的大小无关。随着表的增加,每个直方图步骤所覆盖的间隔也会增加,这会导致大型表的统计信息“准确性下降”。

请记住,索引选择性是与密度成反比的度量,即值越独特列具有更高的选择性。


当特定查询不经常运行时,您可以选择创建列级统计信息而不是索引。列级统计信息帮助
Query Optimizer可以找到更好的执行计划,即使这些执行计划由于所涉及的索引扫描不是最佳选择。同时,统计信息不会在数据修改操作期间增加开销,并且
有助于避免索引维护。此方法仅适用于很少执行的查询。

参考:



列统计信息让Kallen Delaney提供优化器优势

统计,该死的谎言和统计–什么是Statman?注意:像Paul White或Aaron Bertrand这样的人可以发出声音,为您的好问题提供更多色彩。

评论


“当SQL Server估计将需要大量的KEY或RID循环操作时,它将不使用非聚集索引。”使用基于索引的stats对象而不依赖于索引?意思是,如果索引不是最优的,但是在查询中是最前面的列,那么统计仍然是相关的。那么会使用它们吗?还是此信息暗示在某些情况下不太可能使用索引,但是由于统计信息仍然有价值,因此没有真正的理由创建索引,只需统计一下即可?

–所罗门·鲁兹基
15-10-23在20:57

#2 楼

我想说的是,当您需要能够根据字段限制数据量/快速获取正确的数据时,需要一个索引。

需要优化器时就需要统计信息为了理解数据的本质,以便能够以最佳方式执行操作。

我发现,经过过滤的统计信息会在数据中的偏斜严重影响计划时提供帮助,例如堆栈溢出示例中,很少有用户拥有大量帖子,因此仅使用每个用户的平均帖子并不是最佳估计。因此,您可以基于用户名在userId上创建过滤的统计信息,然后SQL Server应该知道,当该用户名出现在查询中时,这就是它将获得的用户ID,并且应该能够弄清楚posts表中的索引字段将具有该id的大量行,因为在那里存在直方图。如果使用平均值,则不可能做到这一点。

评论


嗨,谢谢您的回答。因此,什么时候我需要/希望优化器更好地了解数据的性质,而又不限制该数据或不想更快地获取它,或者需要它“覆盖”查询?与您的过滤索引示例相同。我确实得到了您说的从平均值中打破边缘情况的意思,但是为什么过滤后的统计数据比相同字段上的过滤索引好?这是我想要达到的区别。

–所罗门·鲁兹基
2015年10月23日19:30在

像在示例中一样,您无法在用户名上创建到posts表的过滤索引,因为该索引在该表中不存在。您可以根据用户ID创建它,但这不在where子句中。

–詹姆斯Z
15-10-23在19:31

但是,即使不在WHERE中,UserID也不会处于JOIN条件吗?而且这还不够好以获取筛选出的索引吗?

–所罗门·鲁兹基
2015年10月23日,19:36

@srutzky在最新版本中可能更多,但是一般而言,我不会依赖...在大多数情况下,谓词必须完全匹配。我忘了他们是否解决了这个问题,但是在某个时候,对于简单的查询WHERE BitColumn <> 1来说,不会选择过滤索引WHERE BitColumn =0。(很明显,bit列不可为空。) IntColumn> 10之类的情况与IntColumn> = 11不匹配

–亚伦·伯特兰(Aaron Bertrand)
2015年10月23日在20:11



如果下次有人使用该计划时,将无法使用过滤索引。我认为没有任何可以使用过滤索引的联接。甚至无法使用变量,因为下次该值可能不合适。

–詹姆斯Z
15年10月23日在20:16

#3 楼

从Itzik Ben-Gan撰写的70-461培训手册中

只有少数可能的原因来手动创建统计信息。一个示例是查询谓词包含具有跨列关系的多个列时。多列的统计信息可以帮助改进查询计划。多列统计信息包含跨列密度,这在单列统计信息中不可用。但是,如果列已在同一索引中,则多列统计信息对象已存在,因此您不应手动创建其他列。

评论


感谢您发布此信息。这回答了我的部分问题,但仍然悬而未决:如果我需要多列统计信息,为什么我只创建统计信息而不是索引,索引将包括统计信息以及可以进一步帮助查询的其他信息( ies)?

–所罗门·鲁兹基
15年10月23日在19:40

我认为Kin的解释将进一步解释您的追求。也许经常插入但很少查询的堆?

–健太郎
15-10-23在20:34