精选国内外互联网行业最新文章及报告,让网友获得最新的海内外互联网动态
当前位置:主页 > 网络编程 > 数据库技术 >

Microsoft SQL Server 2000 中查询优化器使用的统计

2015-01-21 13:36 来源: 编辑:admin
SQLServer2000
  目录
  Microsoft SQL Server 2000 中查询优化器使用的统计
  SQL Server 2000 中的统计数据
  SQL Server 2000 收集的统计信息
  关于 INDEX ''PK_Order_Details'' 的统计信息
  用 SQL Server 2000 创建统计
  在 SQL Server 2000 中维护统计
  统计和索引视图
  总结
  
  摘要:本文说明了收集的数据类型、数据的存储位置以及用于创建、更新与删除统计的命令。这些统计信息与存储在 Microsoft SQL Server 2000 数据库中的索引和列数据相关,SQL Server 查询优化器用它们来选择用于检索数据和执行 INSERT、SELECT、DELETE 或 UPDATE 查询的最有效方案。本文同时也概括介绍了如何在不同的级别(索引、表和数据库)上更改 SQL Server 创建与维护统计的默认设置。
  
  Microsoft SQL Server 2000 中查询优化器使用的统计
  Microsoft® SQL Server™ 2000 收集关于存储在数据库中的索引和列数据的统计信息。SQL Server 查询优化器使用这些统计信息来选择用于执行 INSERT、SELECT、DELETE 或 UPDATE 查询的最有效方案。本文说明了收集的数据类型、数据的存储位置以及用于创建、更新与删除统计的命令。默认情况下,SQL Server 2000 会自动创建和更新统计(当此类操作有用时)。本文也概括介绍了如何在不同的级别(索引、表和数据库)上更改这些默认值。
  
  SQL Server 2000 中的统计数据
  SQL Server 2000 既收集关于单个列的统计信息(单列统计),也收集关于成组的列的统计信息(多列统计)。关于一个统计对象的所有信息存储在 SYSINDEXES 表中一行的多个列中。计算列以及数据类型为 ntext、text 或 image 的列不能被指定为统计列。组成一个统计集的所有列的总宽度不能超过 900 字节。
  
  SQL Server 2000 收集的统计信息
  上次收集统计信息的时间(在 STATBLOB 中)。
  
  
  表或索引中的行数(SYSINDEXES 中的 rows 列)。
  
  
  表或索引所占用的页数(SYSINDEXES 中的 dpages 列)。
  
  
  用于生成直方图和密度信息的行数(在 STATBLOB 中,将在下面讲解)。
  
  
  平均键长(在 STATBLOB 中)。
  
  
  单列直方图,包括步数(在 STATBLOB 中)。
  注意:  直方图是给定列的最多 200 个值的集合。给定列中的所有值(如果通过抽样来收集统计信息,则为选定的值)会被排序;排序后序列最多会划分为 199 个间隔,以便得到最有效的统计。一般,这些间距的大小并不相等。以下数值与直方图的每个步长存储在一起。
  
  表 1:直方图的值
   
  使用 dbcc show_statistics 命令时显示的是两个导出值而不是 DENSITY 信息。
  
  表 2:用两个导出值显示 dbcc show_statistics 的直方图
   
  SQL Server 2000 中的直方图只用于单个列、多列统计中的第一列或者索引。
  
  SQL Server 2000 按照三个步骤从排序后的列值集合生成直方图。第一步,最多收集 200 个 RANGE_HI_KEY、EQ_ROWS、RANGE_ROWS 和 DISTINC_RANGE_ROWS 的值。第二步,对每个其他的列值进行处理:该值或者被添加到上一个区间中(对值进行排序),或者创建一个新区间。如果是创建一个新区间,则两个现有的相邻区间会合并到一个区间中。SQL Server 2000 通过密度信息来选择要合并的区间,使密度最接近的两个相邻区间被合并,从而将信息的损失降到最低程度。第三步,合并更多的密度接近的区间。因此,即使列中的唯一值个数超过 200,直方图的步数也可能会小于 200。
  
  如果通过抽样来生成直方图,那么 RANGE_ROWS、EQ_ROWS、DISTINCT_RANGE_ROWS 和 AVG_RANGE_ROWS 的值将为估计值,因此它们不必都是整数。
  
  密度是关于给定列或列的组合中重复项数目的信息,其计算公式为 1/(互不相同的值的数目)。如果在相等条件判断表达式中使用了某个列,则会使用从直方图导出的密度来估算符合条件的行数。直方图通常用于估算不等条件判断表达式。
  
  注意:  dbcc show_statistics 的第一行中会显示一个单独的密度值,但 SQL Server 2000 中的优化器不使用该值。
  
  一个列集合的多列统计包括以下信息:统计定义中第一个列的直方图,第一个列的密度值,以及每个列的前缀组合(单独包括第一个列)的密度值。每个统计集(一个直方图加上两个或多个密度值)都存储在 SYSINDEXES 表的一行中,同一行中还包括上次更新统计的时间戳、用于生成统计信息的抽样行数、直方图的步数和平均键数。SQL Server 2000 只为编号为 0 或 1 的索引(堆索引或群集索引)维护行数的值(rowcnt 列),并在表中的所有索引中复制该值。同样,SQL Server 2000 也为每个表和索引维护 dpages。如果收集统计信息时表中没有任何行,则该表的统计信息为空。
  
  使用 sp_helpindex 和 sp_helpstats 可以显示给定表中所有可用统计的列表,sp_helpindex 列出了表中的所有索引,而 sp_helpstats 则列出了表中的所有统计。每个索引都带有其中列的统计信息。在相同的列中,使用 CREATE STATISTICS 命令创建的统计信息与使用 CREATE INDEX 命令生成的统计信息等价。唯一的差别在于,CREATE STATISTICS 命令默认采用抽样方式,而 CREATE INDEX 命令则由于必须处理索引的所有行,因而使用 fullscan 收集统计信息。
  
  下面是罗斯文数据库中 Order Details 表的所有索引和统计的示例。因为最初无索引的列上没有任何统计信息,所以在连接到罗斯文数据库后,请先运行 sp_createstats。
  
  表 3:罗斯文数据库中的 Order Details 表
  
  sp_helpindex [Order Details]
  
   
  也可以象下面这样使用 dbcc show_statistics 命令来显示统计信息:
  
  dbcc show_statistics ([Order Details],PK_Order_Details)
  
  如果没有返回任何信息,则表明上次已经更新了统计,或在表中没有任何行时就创建了索引。要更新 Order Details 表的统计,请运行 UPDATE STATISTICS [Order Details];要更新罗斯文数据库中所有表的统计,请运行 sp_updatestats。
  
  注意:  该命令的输出已经增强了可读性。
  
  关于 INDEX ''PK_Order_Details'' 的统计信息
  表 4:关于 INDEX ''PK_Order_Details'' 的统计信息
   
   
  请注意,当 sp_helpindex 输出只显示城市统计 (City) 的一个列时,show_statistics 的输出也会显示 City,Customer_id 列组合的所有密度值。这是因为表中 Customer_id 列上有一个群集的索引,并且每个辅助索引也包含群集的关键字列。通常,这一事实对于用户而言是透明的,但查询优化器会了解群集的列,并且如果执行查询只要求这些列的值在辅助索引的顶部,它会避免辅助获取。统计也包含群集的关键字列。
  
  使用 SQL Server 2000 创建统计
  SQL Server 2000 中有两种基本语句会生成上述统计信息:CREATE INDEX 首先会生成声明的索引,然后,作为副产品,它会为组成索引的列组合创建一个统计集;CREATE STATISTICS 只为给定列或列的组合生成统计。
  
  另外,还有多种方法可以创建统计或索引,但归根结底,每种方法都会发出上述两个命令之一。
  
  使用 sp_createstats 为当前数据库中所有用户表的全部合格列(不包括图像和文本数据)创建统计。如果某个列已经有了直方图,则不会为其创建新的直方图。
  
  
  使用 dbcc dbreindex 为指定数据库中的表重新生成一个或多个索引。
  
  
  在查询分析器中,键入一个查询,选择“显示执行计划”(Show Execution Plan),然后执行查询。在显示的任意图标上单击右键,并选择“管理索引” (Manage Index)或“创建/更新统计信息”(Create/Update Statistics)。
  
  
  使用 Create Index 向导(在其他文章中说明)。
  下面是对 pubs..authors 表执行 CREATE STATISTICS 命令的一个示例:
  
  CREATE STATISTICS s1 ON authors (state, au_lname) WITH SAMPLE 50 PERCENT
  通常,按默认抽样方式生成的统计最为理想。但有时,使用更大的样本大小来生成统计(理想状况下可以使用 fullscan)有助于查询优化,例如给定列中的值分布不均匀时(某些值频繁出现,而其他值较少出现)。使用较大的样本大小来生成统计,必须以创建统计时所需时间的延长为代价。
  
  上面的命令创建一个两列统计。在本例中,因为表太小,所以会忽略 SAMPLE 50 PERCENT 并执行完全扫描。抽样主要用于避免过多扫描数据,并且只影响具有 1024 或更多页面 (8 MB) 的表和索引。
  
  在 SQL Server 2000 中,创建索引的同时会为所有索引创建统计。SQL Server 在编译查询时自动创建单列统计。这些统计是为优化器必须估算密度或分发的列而创建的。这一规则有两种例外情况:首先,当直接对表执行操作所需的代价小于创建统计所需的代价时,不能为该表创建统计;其次,当服务器过于繁忙(有大量正在进行的重要操作)时,也不能创建统计。
  
  为避免长时间维护未使用过的统计,SQL Server 2000 会记录那些自动创建的统计(仅包括那些不是创建索引的副产品的统计信息)的使用时间。几次自动更新之后,列统计会被放弃而不是被更新。如果将来需要,可以重新创建这些统计。更新统计与创建统计在代价方面并没有
标签
返回首页
扫描微信
返回顶部