如何生成统计信息脚本以在 SQL Server 中创建仅限统计信息的数据库

  • 项目

本文介绍如何使用数据库元数据生成统计信息脚本，以便在 SQL Server 中创建仅限统计信息的数据库。

原始产品版本：SQL Server 2014、SQL Server 2012、SQL Server 2008

原始 KB 编号： 914288

简介

DBCC CLONEDATABASE 是生成数据库仅架构克隆以调查性能问题的首选方法。 仅当无法使用 时，才使用 DBCC CLONEDATABASE本文中的过程。

Microsoft SQL Server 中的查询优化器使用以下类型的信息来确定最佳查询计划：

  • 数据库元数据
  • 硬件环境
  • 数据库会话状态

通常，必须模拟所有这些相同类型的信息，以重现测试系统上查询优化器的行为。

Microsoft 客户支持服务可能会要求你生成数据库元数据的脚本来调查查询优化器问题。 本文介绍生成统计信息脚本的步骤，还介绍了查询优化器如何使用信息。

注意

此数据中保存的密钥可能包含 PII 信息。 例如，如果表包含包含统计信息的 “电话号码” 列，则每个步骤的高键值都将在生成的统计信息脚本中。

编写整个数据库的脚本

生成仅限统计信息的克隆数据库时，为整个数据库编写脚本（而不是编写单个对象的脚本）可能更容易、更可靠。 编写整个数据库脚本时，可以获得以下优势：

  • 可以避免重现问题所需的缺少依赖对象的问题。
  • 只需执行较少的步骤即可选择所需的对象。

请注意，如果为数据库生成脚本，并且数据库的元数据包含数千个对象，则脚本过程会消耗大量 CPU 资源。 建议在非高峰时段生成脚本，也可以使用第二个选项“ 脚本单个对象 ”为单个对象生成脚本。

若要编写查询引用的每个数据库的脚本，请执行以下步骤：

  1. 打开SQL Server Management Studio。

  2. 在对象资源管理器中，展开“数据库”，然后找到要编写脚本的数据库。

  3. 右键单击数据库，指向 “任务”，然后选择“ 生成脚本”。

  4. 在脚本向导中，验证选择了正确的数据库。 单击以选择 “编写整个数据库和所有数据库对象的脚本”，然后选择“ 下一步”。

  5. 在 “选择脚本选项 ”对话框中，选择“ 高级 ”按钮，将以下设置从默认值更改为下表中列出的值。

    脚本选项 要选择的值
    安西填充 True
    出错时继续编写脚本 True
    为依赖对象生成脚本 True
    包括系统约束名称 True
    脚本排序规则 True
    脚本登录名 True
    脚本对象级别权限 True
    脚本统计信息 脚本统计信息和直方图
    脚本索引 True
    脚本触发器 True

    注意

    请注意，除非架构包含 dbo 以外的登录名拥有的对象，否则可能不需要“脚本登录名”选项和“脚本对象级别权限”选项。

  6. 选择 “确定” 保存更改，并关闭“ 高级脚本选项” 页。

  7. 选择“ 保存到文件 ”，然后选择“ 单个文件 ”选项。

  8. 查看所选内容，然后选择“ 下一步”。

  9. 选择“完成”。

编写单个对象的脚本

只能编写由特定查询引用的单个对象的脚本，而不是编写完整数据库的脚本。 但是，除非使用 WITH SCHEMABINDING 子句创建所有数据库对象，否则系统表中的 sys.depends 依赖项信息可能并不总是准确的。 这种不准确可能会导致以下问题之一：

  • 脚本过程不会编写依赖对象的脚本。

  • 脚本过程可能会按不正确的顺序编写对象的脚本。 若要成功运行脚本，必须手动编辑生成的脚本。

因此，建议不要编写单个对象的脚本，除非数据库包含许多对象，否则脚本编写会花费太长的时间。 如果必须使用单个对象的脚本，请执行以下步骤：

  1. 在SQL Server Management Studio中，展开“数据库”，然后找到要编写脚本的数据库。

  2. 右键单击数据库，指向 “将数据库编写为脚本”，然后指向 “创建到”，然后选择“ 文件”。

  3. 输入文件名，然后选择“ 保存”。

    将编写核心数据库容器的脚本。 此容器包括文件、文件组、数据库和属性。

  4. 右键单击数据库，指向 “任务”，然后选择“ 生成脚本”。

  5. 确保选择了正确的数据库，然后选择“ 下一步”。

  6. 在 “选择对象类型 ”对话框中，选择 “选择特定数据库对象”，然后选择有问题的查询引用的所有数据库对象类型。

    例如，如果查询仅引用表，请选择“ 表”。 如果查询引用视图，请选择“ 视图和表”。 如果有问题的查询使用用户定义的函数，请选择“ 函数”。

  7. 选择查询引用的所有对象类型后，选择“ 下一步”。

  8. 在“ 设置脚本选项 ”对话框中，选择“ 高级 ”按钮，并将以下设置从默认值更改为“ 高级脚本选项” 页上下表中列出的值。

    脚本选项 要选择的值
    Ansi Padding True
    出错时继续编写脚本 True
    包括系统约束名称 True
    为依赖对象生成脚本 True
    脚本排序规则 True
    脚本登录名 True
    脚本对象级别权限 True
    脚本统计信息 脚本统计信息和直方图
    脚本 USE DATABASE True
    脚本索引 True
    脚本触发器 True

    注意

    请注意，除非架构包含 dbo 以外的登录名拥有的对象，否则可能不需要“脚本登录名”和“脚本对象级别权限”选项。

  9. 选择 “确定” 以保存并关闭“ 高级脚本选项” 页。

    对于在步骤 7 中选择的每个数据库对象类型，将显示一个对话框。

  10. 在每个对话框中，选择特定的表、视图、函数或其他数据库对象，然后选择“ 下一步”。

  11. 选择 “脚本到文件” 选项，然后指定在步骤 3 中输入的相同文件名。

  12. 选择“ 完成 ”以开始编写脚本。

    脚本编写完成后，将脚本文件发送给Microsoft 支持部门工程师。 Microsoft 支持部门工程师可能还会请求以下信息：

    • 硬件配置，包括处理器数量和物理内存量。

    • 运行查询时处于活动状态的 SET 选项。

    请注意，你可能已通过发送 SQLDiag 报表或 SQL Profiler 跟踪来提供此信息。 你可能还使用了另一种方法来提供此信息。

如何使用信息

下表有助于说明查询优化器如何使用此信息来选择查询计划。

元数据

选项 解释
约束 查询优化器经常使用约束来检测查询与基础架构之间的矛盾。 例如，如果查询包含 WHERE col = 5 子句，并且 CHECK (col < 5) 基础表上存在约束，则查询优化器知道没有行匹配。 查询优化器对可为空性进行类似类型的推导。 例如， WHERE col IS NULL 子句已知为 true 或 false，具体取决于列的可为空性以及列是否来自外部联接的外部表。 存在 FOREIGN KEY 约束有助于确定基数和适当的联接顺序。 查询优化器可以使用约束信息来消除联接或简化谓词。 这些更改可能会消除访问基表的要求。
统计信息 统计信息包含密度和直方图，显示索引和统计信息键的前导列的分布情况。 根据谓词的性质，查询优化器可能会使用密度和/或直方图来估计谓词的基数。 准确的基数估计需要最新的统计信息。 基数估计用作估计运算符成本的输入。 因此，必须具有良好的基数估计值才能获得最佳查询计划。
表大小 (行数和页数) 查询优化器使用直方图和密度来计算给定谓词为 true 或 false 的概率。 通过将概率乘以子运算符返回的行数来计算最终基数估计值。 表或索引中的页数是估算 IO 成本的一个因素。 表大小用于计算扫描成本，在估计索引查找期间将访问的页数时，该大小非常有用。
数据库选项 多个数据库选项可能会影响优化。 AUTO_CREATE_STATISTICS和 AUTO_UPDATE_STATISTICS 选项会影响查询优化器是创建新的统计信息还是更新过期的统计信息。 参数化级别会影响在将输入查询传递给查询优化器之前输入查询的参数化方式。 参数化可能会影响基数估计，还可能会阻止与索引视图和其他类型的优化匹配。 设置 DATE_CORRELATION_OPTIMIZATION 会导致优化器搜索列之间的相关性。 此设置会影响基数和成本估算。

环境

选项 解释
会话 SET 选项 设置 ANSI_NULLS 会影响表达式的 NULL = NULL 计算结果是否为 true。 外部联接的基数估计可能会根据当前设置而更改。 此外，不明确的表达式也可能更改。 例如， col = NULL 表达式根据设置以不同的方式计算。 但是，表达式的 col IS NULL 计算方式始终相同。
硬件资源 排序和哈希运算符的成本取决于可用于SQL Server的相对内存量。 例如，如果数据的大小大于缓存，查询优化器知道数据必须始终假脱机到磁盘。 但是，如果数据大小远远小于缓存，则操作很可能在内存中完成。 如果服务器有多个处理器，并且尚未通过使用MAXDOP提示或最大并行度配置选项禁用并行度，SQL Server还会考虑不同的优化。

另请参阅