Microsoft SQL Server 2000 中查询优化器利用的统计(2)[MSSQL防范]

请注意,当 sp_helpindex 输出只显示城市统计 (City) 的一个列时,show_statistics 的输出也会显示 City,Customer_id 列组合的全部密度值.这是因为表中 Customer_id 列上有一个聚集的索引,并且每个帮助索引也包含聚集的关键字列.普通,这一事实关于用户而言是透明的,但查询优化器会理解聚集的列,并且假如履行查询只要求这些列的值在帮助索引的顶部,它会避免帮助获得.统计也包含聚集的关键字列.-Aio2i7L

利用 sql server 2000 成立统计

SQL Server 2000 中有两种基本语句会生成上述统计信息:CREATE INDEX 首先会生成声明的索引,然后,作为副产品,它会为构成索引的列组合成立一个统计集;CREATE STATISTICS 只为给定列或列的组合生成统计. -Aio2i7L

别的,还有多种办法可以成立统计或索引,但归根结底,每种办法城市发出上述两个号令之一. -Aio2i7L

• 利用 sp_createstats 为当前数据库中全部用户表的全部合格列（不包含图象和文本数据）成立统计.假如某个列已经有了直方图,则不会为其成立新的直方图.
  
  
• 利用 dbcc dbreindex 为指定数据库中的表重新生成一个或多个索引.
  
  
• 在查询解析器中,键入一个查询,挑选“显示履行筹划”(Show Execution Plan),然后履行查询.在显示的肆企图标上单击右键,并挑选“管理索引” (Manage Index)或“成立/更新统计信息”(Create/Update Statistics).
  
  
• 利用 Create Index 向导（在其他文章中阐明）.

下面是对 pubs..authors 表履行 CREATE STATISTICS 号令的一个示例:-Aio2i7L

CREATE STATISTICS s1 ON authors (state, au_lname) WITH SAMPLE 50 PERCENT

普通,按默许抽样方法生成的统计最为抱负.但有时,利用更大的样本大小来生成统计（抱负情况下可以利用 fullscan）有助于查询优化,比方给定列中的值分布不均匀时（某些值频繁呈现,而其他值较少呈现）.利用较大的样本大小来生成统计,必须以成立统计时所需时间的延伸为代价.-Aio2i7L

上面的号令成立一个两列统计.在本例中,因为表太小,所以会忽视 SAMPLE 50 PERCENT 并履行完好扫描.抽样主要用于避免过量扫描数据,并且只影响具有 1024 或更多页面 (8 MB) 的表和索引.-Aio2i7L

在 SQL Server 2000 中,成立索引的同时会为全部索引成立统计.SQL Server 在编译查询时自动成立单列统计.这些统计是为优化器必须预算密度或分发的列而成立的.这一法则有两种例外情形:首先,当直接对表履行操作所需的代价小于成立统计所需的代价时,不能为该表成立统计;其次,当服务器过于繁忙（有大量正在举行的重要操作）时,也不能成立统计.-Aio2i7L

为避免长时间保护未利用过的统计,SQL Server 2000 会记录那些自动成立的统计（仅包含那些不是成立索引的副产品的统计信息）的利用时间.几次自动更新之后,列统计会被放弃而不是被更新.假如将来需求,可以重新成立这些统计.更新统计与成立统计在代价方面并没有实质性的差别.记录利用时间的操作也不会影响用户成立的统计.-Aio2i7L

通过履行 sp_dboption dbname, 'auto create statistics', 'OFF' 可以在数据库级禁用自动成立统计的功效.-Aio2i7L

默许情形下,统计是在履行 CREATE STATISTICS 号令或自动成立统计时,通过对数据集举行抽样而成立的.CREATE INDEX 老是会扫描整个数据集,因此最始成立的索引统计并不举行抽样.CREATE STATISTICS 号令答应您通过在 WITH 子句中指定 FULLSCAN 或要扫描的数据百分比来设置样本大小.后者被认为是一个近似值.在 UPDATE STATISTICS 号令上指定 WITH RESAMPLE 时也可以担当上一个样本大小.当既存在索引（通过 fullscan 统计方法成立）,其他列（通过 sample 统计方法成立）上又有统计时,该办法特别有效.随后在 UPDATE STATISTICS 上利用 RESAMPLE 选项,将保持索引的 fullscan 统计和其他列的 sample 统计.-Aio2i7L

dbcc show_statistics 号令在 Rows Sampled 标题下显示样本大小.自动成立或更新的统计老是用默许的抽样方法生成.默许的抽样方法是按表大小的对数函数举行抽样,这种抽样方法的样本大小增长迟钝.-Aio2i7L

SQL Server 查询解析器也会监督自动成立统计的操作.AutoStats 事件位于 Object 跟踪事件组中.定义该跟踪时,也会挑选 Integer Data、Success 和 Object ID 列.一旦捕捉 AutoStats 事件,Integer Data 列将包含为给定表更新的统计数,Object ID 成为该表的 ID,而 TextData 列（默许情形下包含在跟踪定义中）则包含列的名称以及 Updated: 或 Created: 两个前缀之一.Success 列包含潜在的 Failure 指导信息.在某些情形下,您大概会发现 AutoStats 事件不带有任何成立或更新的统计.当 auto update statistics 处于关闭状况时,会生成这类事件;大概,当表实际已经变更,而因为引用该变更表的查询被优化而招致当时表中不存在任何索引或统计时,也会生成这类事件.-Aio2i7L

DROP STATISTICS 号令用于放弃统计,但不能放弃成立索引时附带生成的统计.只有将索引放弃后,才能删除其附带生成的统计.-Aio2i7L

在 SQL Server 2000 中保护统计

在表中履行了一系列 INSERT、DELETE 和/或 UPDATE 查询后,统计大概不反映给定列或索引中数据分布的真实情形.假如某个表在上次成立或更新统计之后举行了大量的更新活动,那么当 SQL Server 查询优化器需求该表中某个特定列的统计时,SQL Server 会（利用 auto update statistics）通过对列中的值举行抽样来自动更新统计.统计的自动更新由查询优化操作触发,并且只触及查询中所引用列的一个子集.SYSINDEXES 值中的 rowmodctr 列显示了自上次成立或更新统计之后对表履行的更改次数.关于每个索引和统计集,SYSINDEXES 中城市有一个对应的行（假如表中没有聚集的索引,则会有一个与堆相对应的行）,SQL Server 2000 将辨别保护每个索引和统计的更改次数（但在 SQL Server 7.0 中并非这样）.在表中履行 INSERT、UPDATE 和 DELETE 查询时,只会为 ID 为 0 或 1 的索引（普通,每个表中只有一个）递增 rowmodctr 值.关于其他的统计和索引,它只显示一个必须加到索引 0 或 1 的 rowmodctr 上的相对值,通过将这两个值相加来得到该索引中已变更的行的真实数目.-Aio2i7L

这一逻辑使得在 INSERT、UPDATE 和 DELETE 查询历程中,对每个表只需求保护一行中的 rowmodctr;同时,又可以辨别跟踪每个索引中已变更的行.因此,当 auto update statistics 处于翻开状况时,只更新与给定查询所必须的索引和列对应的行.-Aio2i7L

查询示例

在表 t1 中插入 506 行,然后成立非聚集的索引 i1 和 i2.举行了这些操作之后,SYSINDEXES 表中会呈现风趣的条目:-Aio2i7L

插入了别的 213 行后,计数为:-Aio2i7L

利用 UPDATE STATISTICS t1 (i1) 革新了索引 i1 的统计之后:-Aio2i7L

可以在差别的级别上将上面所说的 auto update statistics 特点关闭. -Aio2i7L

• 在数据库级别利用 sp_dboption dbname, 'auto create statistics', 'OFF'.也可以利用该 sp 来显示特定数据库的当前设置.
  
  
• 对表、索引或统计利用 UPDATE STATISTICS 号令的 NORECOMPUTE 选项.
  
  
• 对表或统计利用 CREATE STATISTICS 号令的 NORECOMPUTE 选项.
  
  
• 用 sp_autostats 来显示和更改表、索引或统计的设置.

也可以利用 sp_dboption、UPDATE STATISTICS 或 sp_autostats 重新启用自动更新统计,其办法与上述操作类似.-Aio2i7L

SQL Server 2000 在每个数据库、每个索引和表的级别上辨别保存自动更新统计的设置.固然您可以利用一条 CREATE STATISTICS 号令来翻开一个表中的全部统计,但必须通过更改给定表中全部统计和索引的设置来完成该操作.表 6 显示了差别的数据库、表和索引设置的组合效果.-Aio2i7L

表 6:差别的数据库、表和索引设置的组合效果-Aio2i7L

自动更新统计普通通过对索引或表抽样来履行.您可以通过手动运行 CREATE 和 UPDATE 统计来改变样本大小.在这样成立统计时,统计更新会被相同的 SQL 解析器事件替换.-Aio2i7L

统计和索引的视图

普通,索引的视图上不需求统计,这是因为:只有在将底子表和索引的全部统计都附加到查询操作之后,才会考虑替换查询操作中的索引视图.但是,有一点例外:假如利用 NOEXPAND 提醒在 FROM 子句中直接引用视图,就会利用统计.假如在不包含索引的视图上利用 NOEXPAND 提醒,则会生成错误并没有法履行操作.-Aio2i7L

因为索引视图上的统计的用处非常有限,所以不利用 sp_createstats 在索引的视图上成立索引,也不利用 sp_updatestats 对其举行更新.auto update 和 auto create statistics 可以用于索引的视图.但正如前面所说,仅当在查询中通过 NOEXPAND 提醒利用索引的视图,并且 AUTO UPDATE 或 CREATE STATISTICS 选项处于翻开状况时,优化器才需求这些统计,这些统计也才会被真正成立.您也可以在索引的视图列上手动履行 CREATE STATISTICS,或在索引的视图上利用 UPDATE STATISTICS 来更新列或索引统计.-Aio2i7L

总结

关于查询优化器,数据库中的数据统计是重要的输入.默许情形下,SQL Server 自动成立和保护统计,而不需求用户的参与.大大都 SQL Server 用户没必要更改默许值,便可以得到最佳性能.SQL Server 供应了多个接口,用于更改默许的统计成立和保护的法则,但只有在默许法则不能到达最佳性能的特别环境下,您才需求利用这些接口.-Aio2i7L