通过

Synapse SQL 中的统计信息

  • 项目

本文中提供了使用 Synapse SQL 资源创建和更新查询优化统计信息的建议和示例：专用 SQL 池和无服务器 SQL 池。

专用 SQL 池中的统计信息

为何使用统计信息

专用 SQL 池更了解数据，执行查询的速度就越快。 将数据加载到专用 SQL 池后，收集有关数据的统计信息是可以针对查询优化执行的最重要作之一。

专用 SQL 池查询优化器是基于成本的优化器。 此优化器会对各种查询计划的成本进行比较，并选择成本最低的计划。 在大多数情况下，它会选择将执行最快的计划。

例如，如果优化器预估在针对查询的日期进行筛选后将返回一行，它会选择某个计划。 如果优化器估计选定的日期会返回一百万行数据，则它会返回另一个计划。

自动创建统计信息

当数据库AUTO_CREATE_STATISTICS选项设置为 ON时，专用 SQL 池引擎将分析传入用户查询中缺少的统计信息。 如果缺少统计信息，查询优化器会在查询谓词或联接条件中各个列上创建统计信息。

此函数用于改进查询计划的基数估计。

重要

默认情况下，自动创建统计信息目前处于开启状态。

你可以通过运行以下命令检查数据仓库是否已配置 AUTO_CREATE_STATISTICS：

SELECT name, is_auto_create_stats_on
FROM sys.databases

如果数据仓库未启用AUTO_CREATE_STATISTICS，建议运行以下命令来启用此属性：

ALTER DATABASE <yourdatawarehousename>
SET AUTO_CREATE_STATISTICS ON

这些语句将触发自动创建统计信息：

  • 选择
  • INSERT-SELECT
  • CTAS
  • 更新
  • 删除
  • EXPLAIN

注释

不会在临时表或外部表上自动创建统计信息。

自动创建统计信息是同步完成的。 因此，如果列缺少统计信息，则查询性能可能会略有下降。 为单个列创建统计信息所需的时间取决于表的大小。

为了避免可测量的性能降低，应确保在分析系统之前先通过执行基准检验工作负载来创建统计信息。

注释

在其他用户上下文中，统计信息的创建会记录在 sys.dm_pdw_exec_requests 中。

创建自动统计信息时，将采用以下形式：WA_Sys<8 位十六进制列 ID_><8 位十六进制表 ID>。 可以通过运行 DBCC SHOW_STATISTICS 命令来查看已创建的统计信息：

DBCC SHOW_STATISTICS (<table_name>, <target>)

table_name是包含要显示的统计信息的表的名称，不能是外部表。 目标是要显示统计信息的目标索引、统计信息或列的名称。

更新统计信息

最佳实践之一是每天在添加新日期后，更新有关日期列的统计信息。 每次将新行加载到数据仓库中时，都会添加新的加载日期或事务日期。 这些添加操作会更改数据分布情况并使统计信息过时。

客户表中国家或地区列的统计信息可能永远不需要更新，因为值的分布通常不会更改。 假设客户间的分布固定不变，将新行添加到表变化并不会改变数据分布情况。

但是，如果数据仓库仅包含一个国家/地区，并且从新国家或地区引入数据，则需要更新国家或地区列的统计信息。

下面是关于更新统计信息的建议：

类型 建议
统计信息更新频率 保守：每日
在加载或转换数据之后
采样 如果行数少于 10 亿，则使用默认采样率 (20%)。
如果行数超过 10 亿，则使用 2% 的采样率。

确认上次统计信息更新时间

排查查询问题时要问的第一个问题是 “统计信息是否是最新的？”

此问题不是能够通过数据年龄来回答的。 如果对基础数据未做重大更改，则最新的统计信息对象有可能非常陈旧。 如果行数发生了重大更改，或者列的值分布发生重大更改， 则 是时候更新统计信息了。

没有动态管理视图可用于确定自上次更新统计信息以来表中的数据是否已更改。 如果知道统计信息的期限，可以大致猜出更新状态。

可以使用以下查询来确定上次在每个表上更新统计信息的时间。

注释

如果列的值分布有重大变化，则应更新统计信息，而不考虑上次更新统计信息的时间。

SELECT
    sm.[name] AS [schema_name],
    tb.[name] AS [table_name],
    co.[name] AS [stats_column_name],
    st.[name] AS [stats_name],
    STATS_DATE(st.[object_id],st.[stats_id]) AS [stats_last_updated_date]
FROM
    sys.objects ob
    JOIN sys.stats st
        ON  ob.[object_id] = st.[object_id]
    JOIN sys.stats_columns sc
        ON  st.[stats_id] = sc.[stats_id]
        AND st.[object_id] = sc.[object_id]
    JOIN sys.columns co
        ON  sc.[column_id] = co.[column_id]
        AND sc.[object_id] = co.[object_id]
    JOIN sys.types  ty
        ON  co.[user_type_id] = ty.[user_type_id]
    JOIN sys.tables tb
        ON  co.[object_id] = tb.[object_id]
    JOIN sys.schemas sm
        ON  tb.[schema_id] = sm.[schema_id]
WHERE
    st.[user_created] = 1;

例如，数据仓库中的日期列通常需要频繁的统计信息更新。 每次将新行加载到数据仓库中时，都会添加新的加载日期或事务日期。 这些添加操作会更改数据分布情况并使统计信息过时。

客户表中性别列的统计信息可能永远不需要更新。 假设客户间的分布固定不变，将新行添加到表变化并不会改变数据分布情况。

但是，如果数据仓库仅包含一种性别，并且新的要求会导致多个性别，则需要更新性别列的统计信息。

有关详细信息，请查看 统计信息 文章。

实施统计信息管理

扩展数据加载过程通常是个不错的想法，可确保在加载结束时更新统计信息。 数据加载发生在表最为频繁地更改其大小和/或其值分布的时候。 因此，加载过程是实施某些管理过程的合理位置。

下面提供了有关在加载过程中更新统计信息的一些指导原则：

  • 确保加载的每个表至少包含一个更新的统计信息对象。 此过程会在统计信息更新过程中更新表大小（行计数和页计数）信息。
  • 将重点放在参与 JOIN、GROUP BY、ORDER BY 和 DISTINCT 子句的列上。
  • 考虑更频繁地更新“递增键”列（例如事务日期），因为这些值不包含在统计信息直方图中。
  • 考虑较不经常更新静态分布列。
  • 请记住，每个统计信息对象是按顺序更新的。 仅实现 UPDATE STATISTICS <TABLE_NAME> 不一定总很理想 - 尤其是对包含许多统计信息对象的宽型表而言。

有关详细信息，请参阅基数估计。

示例：创建统计信息

以下示例演示如何使用各种选项来创建统计信息。 用于每个列的选项取决于数据的特征以及列在查询中的使用方式。

使用默认选项创建单列统计信息

若要基于某个列创建统计信息，需要提供统计信息对象的名称和列的名称。 此语法使用所有默认选项。 默认情况下，专用 SQL 池在创建统计信息时对 20% 的表采样。

CREATE STATISTICS [statistics_name]
    ON [schema_name].[table_name]([column_name]);

例如：

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1);

通过检查每个行创建单列统计信息

20% 的默认采样率足以应付大多数情况。 不过，可以调整采样率。 若要采样整个表，请使用此语法：

CREATE STATISTICS [statistics_name]
    ON [schema_name].[table_name]([column_name])
    WITH FULLSCAN;

例如：

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1)
    WITH FULLSCAN;

通过指定样本大小创建单列统计信息

另一种选择是将样本大小指定为百分比：

CREATE STATISTICS col1_stats
    ON dbo.table1 (col1)
    WITH SAMPLE 50 PERCENT;

只对某些行创建单列统计信息

还可以在表中的一部分行上创建统计信息，这称为筛选统计信息。

例如，在计划查询大型分区表的特定分区时，可以使用筛选的统计信息。 通过仅基于分区值创建统计信息，统计信息的准确性将提高。 你还将体验到查询性能的改进。

此示例会基于一系列的值创建统计信息。 可以轻松定义这些值以匹配分区中的值范围。

CREATE STATISTICS stats_col1
    ON table1(col1)
    WHERE col1 > '2000101' AND col1 < '20001231';

注释

若要让查询优化器在选择分布式查询计划时考虑使用筛选的统计信息，查询必须符合统计信息对象的定义。 使用上述示例，查询的 WHERE 子句需要指定介于 2000101 和 20001231 之间的 col1 值。

使用所有选项创建单列统计信息

也可以将选项组合在一起。 以下示例使用自定义样本大小创建筛选的统计信息对象：

CREATE STATISTICS stats_col1
    ON table1 (col1)
    WHERE col1 > '2000101' AND col1 < '20001231'
    WITH SAMPLE 50 PERCENT;

有关完整参考，请参阅 CREATE STATISTICS。

创建多列统计信息

若要创建多列统计信息对象，请使用上述示例，但要指定更多的列。

注释

用于估计查询结果中行数的直方图只适用于统计信息对象定义中所列的第一个列。

在此示例中，直方图位于 product_category。 跨列统计信息基于 product_category 和 product_sub_category计算：

CREATE STATISTICS stats_2cols
    ON table1 (product_category, product_sub_category)
    WHERE product_category > '2000101' AND product_category < '20001231'
    WITH SAMPLE 50 PERCENT;

由于 product_category 和 product_sub_category之间存在关联，因此如果同时访问这些列，多列统计信息对象可能很有用。 查询此表时，多列统计信息将改善联接、GROUP BY 聚合、去重计数和 WHERE 筛选器的基数预测（只要主要统计列是筛选器的一部分）。

基于表中的所有列创建统计信息

创建统计信息的一种方法是在创建表后发出 CREATE STATISTICS 命令：

CREATE TABLE dbo.table1
(
   col1 int
,  col2 int
,  col3 int
)
WITH
  (
    CLUSTERED COLUMNSTORE INDEX
  )
;

CREATE STATISTICS stats_col1 on dbo.table1 (col1);
CREATE STATISTICS stats_col2 on dbo.table2 (col2);
CREATE STATISTICS stats_col3 on dbo.table3 (col3);

使用存储过程创建数据库中所有列的统计信息

SQL 池不提供相当于 SQL Server 中 sp_create_stats 的系统存储过程。 此存储过程会为数据库中尚未具有统计信息的每个列创建一个列统计信息对象。

以下示例将帮助你开始使用数据库设计。 请随意根据需要进行调整。

CREATE PROCEDURE    [dbo].[prc_sqldw_create_stats]
(   @create_type    tinyint -- 1 default, 2 Fullscan, 3 Sample
,   @sample_pct     tinyint
)
AS

IF @create_type IS NULL
BEGIN
    SET @create_type = 1;
END;

IF @create_type NOT IN (1,2,3)
BEGIN
    THROW 151000,'Invalid value for @stats_type parameter. Valid range 1 (default), 2 (fullscan) or 3 (sample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN;
    DROP TABLE #stats_ddl;
END;

CREATE TABLE #stats_ddl
WITH    (   DISTRIBUTION    = HASH([seq_nmbr])
        ,   LOCATION        = USER_DB
        )
AS
WITH T
AS
(
SELECT      t.[name]                        AS [table_name]
,           s.[name]                        AS [table_schema_name]
,           c.[name]                        AS [column_name]
,           c.[column_id]                   AS [column_id]
,           t.[object_id]                   AS [object_id]
,           ROW_NUMBER()
            OVER(ORDER BY (SELECT NULL))    AS [seq_nmbr]
FROM        sys.[tables] t
JOIN        sys.[schemas] s         ON  t.[schema_id]       = s.[schema_id]
JOIN        sys.[columns] c         ON  t.[object_id]       = c.[object_id]
LEFT JOIN   sys.[stats_columns] l   ON  l.[object_id]       = c.[object_id]
                                    AND l.[column_id]       = c.[column_id]
                                    AND l.[stats_column_id] = 1
LEFT JOIN    sys.[external_tables] e    ON    e.[object_id]        = t.[object_id]
WHERE       l.[object_id] IS NULL
AND            e.[object_id] IS NULL -- not an external table
)
SELECT  [table_schema_name]
,       [table_name]
,       [column_name]
,       [column_id]
,       [object_id]
,       [seq_nmbr]
,       CASE @create_type
        WHEN 1
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+')' AS VARCHAR(8000))
        WHEN 2
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH FULLSCAN' AS VARCHAR(8000))
        WHEN 3
        THEN    CAST('CREATE STATISTICS '+QUOTENAME('stat_'+table_schema_name+ '_' + table_name + '_'+column_name)+' ON '+QUOTENAME(table_schema_name)+'.'+QUOTENAME(table_name)+'('+QUOTENAME(column_name)+') WITH SAMPLE '+CONVERT(varchar(4),@sample_pct)+' PERCENT' AS VARCHAR(8000))
        END AS create_stat_ddl
FROM T
;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''
;

WHILE @i <= @t
BEGIN
    SET @s=(SELECT create_stat_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

若要使用默认设置基于表中的所有列创建统计信息，请执行存储过程。

EXEC [dbo].[prc_sqldw_create_stats] 1, NULL;

若要使用 fullscan 创建表中所有列的统计信息，请调用此过程：

EXEC [dbo].[prc_sqldw_create_stats] 2, NULL;

若要针对表中的所有列创建采样统计信息，请输入 3，并输入样本百分比。 下面的过程使用 20% 的采样率。

EXEC [dbo].[prc_sqldw_create_stats] 3, 20;

示例：更新统计信息

要更新统计信息，可以：

  • 更新一个统计信息对象。 指定要更新的统计信息对象名称。
  • 更新表中的所有统计信息对象。 指定表名称，而不是一个特定的统计信息对象。

更新一个特定的统计信息对象

使用以下语法来更新特定的统计信息对象：

UPDATE STATISTICS [schema_name].[table_name]([stat_name]);

例如：

UPDATE STATISTICS [dbo].[table1] ([stats_col1]);

通过更新特定统计信息对象，可以减少管理统计信息所需的时间和资源。 此作需要一些想法来选择要更新的最佳统计信息对象。

更新表格中的所有统计数据

用于更新表中所有统计信息对象的一个简单方法如下：

UPDATE STATISTICS [schema_name].[table_name];

例如：

UPDATE STATISTICS dbo.table1;

UPDATE STATISTICS 语句易于使用。 只需记住，它会更新表 上的所有 统计信息，从而导致工作量超过必要的范围。

如果性能不是问题，此方法是最简单、最完整的方法来保证统计信息是最新的。

注释

更新表上的所有统计信息时，专用 SQL 池会执行扫描来对每个统计信息对象的表进行采样。 如果表很大、包含许多列和许多统计信息，则根据需要更新各项统计信息可能比较有效率。

有关 UPDATE STATISTICS 过程的实现，请参阅临时表。 实现方法与上述 CREATE STATISTICS 过程略有不同，但最终结果相同。 有关完整语法，请参阅 “更新统计信息”。

统计信息元数据

可以使用多个系统视图和函数来查找有关统计信息的信息。 例如，可以使用 STATS_DATE（） 函数查看统计信息对象是否过期。 STATS_DATE（） 允许查看上次创建或更新统计信息的时间。

统计信息的目录视图

这些系统视图提供有关统计信息的信息：

目录视图 DESCRIPTION
sys.columns 针对每个列提供一行。
sys.objects 针对数据库中的每个对象提供一行。
sys.schemas 针对数据库中的每个架构提供一行。
sys.stats 针对每个统计信息对象提供一行。
sys.stats_columns 针对统计信息对象中的每个列提供一行。 链接回到 sys.columns。
sys.tables 针对每个表（包括外部表）提供一行。
sys.table_types 每种数据类型一行。

统计信息的系统函数

这些系统函数适合用于处理统计信息：

系统函数 DESCRIPTION
STATS_DATE 上次更新统计信息对象的日期。
DBCC SHOW_STATISTICS 有关统计信息对象所理解的值分布的摘要级别和详细信息。

将统计信息列和函数合并成一个视图

此视图将与来自 STATS_DATE（） 函数的统计信息和结果相关的列组合在一起。

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON    co.[object_id]      = tb.[object_id]
JOIN    sys.schemas         AS sm ON    tb.[schema_id]      = sm.[schema_id]
WHERE   1=1
AND     st.[user_created] = 1
;

DBCC SHOW_STATISTICS() 示例

DBCC SHOW_STATISTICS（） 显示统计信息对象中保存的数据。 这些数据包括三个组成部分：

  • 标题
  • 密度矢量
  • 直方图

标头是有关统计信息的元数据。 直方图显示统计信息对象的第一个键列中的值分布。

密度向量可度量跨列相关性。 专用 SQL 池使用统计信息对象中的任何数据来计算基数估计值。

显示标头、密度和直方图

此简单示例显示了统计信息对象的所有三个组成部分：

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)

例如：

DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1');

显示 DBCC SHOW_STATISTICS() 的一个或多个组成部分

如果只想要查看特定部分，请使用 WITH 子句并指定要查看哪些部分：

DBCC SHOW_STATISTICS([<schema_name>.<table_name>],<stats_name>)
    WITH stat_header, histogram, density_vector

例如：

DBCC SHOW_STATISTICS ('dbo.table1', 'stats_col1')
    WITH histogram, density_vector

DBCC SHOW_STATISTICS() 差异

与在 SQL Server 中相比，DBCC SHOW_STATISTICS() 在专用 SQL 池中的实现相对更严格：

  • 未阐述的功能不受支持。
  • 无法使用Stats_stream。
  • 不能联接特定统计信息子集的结果。 例如 STAT_HEADER JOIN DENSITY_VECTOR。
  • 无法将NO_INFOMSGS设置为消息抑制。
  • 不能在统计信息名称的前后使用方括号。
  • 不能使用列名来标识统计信息对象。
  • 不支持自定义错误 2767。

无服务器 SQL 池中的统计信息

针对某个数据集（存储路径），为其中的每个特定列创建统计信息。

注释

无法为 LOB 列创建统计信息。

为何使用统计信息

无服务器 SQL 池对数据了解越多，对数据执行查询的速度就越快。 收集有关数据的统计信息是优化查询时可以执行的最重要作之一。

无服务器 SQL 池查询优化器是基于成本的优化器。 此优化器会对各种查询计划的成本进行比较，并选择成本最低的计划。 在大多数情况下，它会选择将执行最快的计划。

例如，如果优化器估计查询筛选的日期会返回一行数据，则它会选择一个计划。 如果它估计所选日期将返回 100 万行，它将选取其他计划。

自动创建统计信息

无服务器 SQL 池分析传入的用户查询中缺少的统计信息。 如果缺少统计信息，查询优化器在查询谓词或联接条件中各个列上创建统计信息，以改进查询计划的基数估计。

SELECT 语句将触发“自动创建统计信息”。

注释

自动创建统计信息时会使用采样，在大多数情况下，采样百分比将小于 100%。 对于每种文件格式，此流都是相同的。 请记住，如果不支持使用分析程序版本 1.0 采样读取 CSV，不会自动创建统计信息，且采样百分比小于 100%。 对于具有估计低基数（行数）的小型表，将触发自动统计信息创建，且采样百分比为 100%。 这基本上意味着会触发全面扫描，并且即便是使用分析程序版本 1.0 的 CSV 也会创建自动统计信息。

自动创建统计信息是同步完成的，因此，如果列缺少统计信息，则查询性能可能会略有下降。 为单个列创建统计信息所耗用的时间取决于目标文件的大小。

手动创建统计信息

无服务器 SQL 池允许手动创建统计信息。 如果将分析器版本 1.0 与 CSV 配合使用，可能需要手动创建统计信息，因为此分析程序版本不支持采样。 除非采样百分比为 100%，否则分析器版本 1.0 不会自动创建统计信息。

有关如何手动创建统计信息的说明，请参阅以下示例。

更新统计信息

对文件中数据的更改、删除和添加文件会导致数据分发更改并使统计信息过期。 在这种情况下，需要更新统计信息。

如果数据发生显著更改，则无服务器 SQL 池会自动重新创建 OPENROWSET 列的统计信息。 每次自动创建统计信息时，会同时保存数据集的当前状态：文件路径、大小、上次修改日期。

统计信息过时时，将创建新统计信息。 该算法浏览数据并将其与数据集的当前状态进行比较。 如果更改的大小大于特定阈值，则会删除旧统计信息，并将在新数据集上重新创建。

手动统计信息永远不会声明过时。

注释

自动重新创建统计信息时会使用采样，在大多数情况下，采样百分比将小于 100%。 对于每种文件格式，此流都是相同的。 请记住，使用解析器版本 1.0 读取 CSV 时不支持采样，因此如果采样百分比小于 100%，则不会自动重新创建统计信息。 在这种情况下，需要手动删除并重新创建统计信息。 查看以下示例，了解如何删除和创建统计信息。 对于具有估计低基数（行数）的小型表，将触发自动统计信息重新创建，且采样百分比为 100%。 这基本上意味着会触发全面扫描，并且即便是使用分析程序版本 1.0 的 CSV 也会创建自动统计信息。

排查查询问题时要问的第一个问题是 “统计信息是否是最新的？”

如果行数有明显变化或给定列的值分布有重大变化，这时就需要更新统计信息。

注释

如果列的值分布有重大变化，则应更新统计信息，而不考虑上次更新统计信息的时间。

实施统计信息管理

你可能需要扩展数据管道，以便在因添加、删除或更改文件而导致数据发生重大更改时能确保更新统计信息。

下面提供了有关更新统计信息的一些指导原则：

  • 确保数据集至少更新了一个统计信息对象。 这将在统计信息更新过程中更新大小（行计数和页计数）信息。
  • 将重点放在参与 WHERE、JOIN、GROUP BY、ORDER BY 和 DISTINCT 子句的列上。
  • 更新“升序键”列（如事务日期）的频率更高，因为这些值不会包含在统计信息直方图中。
  • 更新静态分布列的频率较低。

有关详细信息，请参阅基数估计。

示例：在 OPENROWSET 路径中创建列的统计信息

以下示例演示如何使用各种选项在 Azure Synapse 无服务器 SQL 池中创建统计信息。 用于每个列的选项取决于数据的特征以及列在查询中的使用方式。 有关这些示例中使用的存储过程的详细信息，请查看 sys.sp_create_openrowset_statistics 和 sys.sp_drop_openrowset_statistics，它们仅适用于无服务器 SQL 池。

注释

目前只能创建单列统计信息。

执行 sp_create_openrowset_statistics 和 sp_drop_openrowset_statistics 需要以下权限：ADMINISTER BULK OPERATIONS 或 ADMINISTER DATABASE BULK OPERATIONS。

以下存储过程用于创建统计信息：

sys.sp_create_openrowset_statistics [ @stmt = ] N'statement_text'

参数：[ @stmt = ] N'statement_text' - 指定一个 Transact-SQL 语句，该语句将返回用于统计的列值。 可以使用 TABLESAMPLE 指定要使用的数据示例。 如果未指定 TABLESAMPLE，将使用 FULLSCAN。

<tablesample_clause> ::= TABLESAMPLE ( sample_number PERCENT )

注释

如果使用分析程序版本 1.0，CSV 采样不起作用，分析程序版本 1.0 的 CSV 仅支持全面扫描。

通过检查每个行创建单列统计信息

要创建某个列的统计信息，请提供一个查询，该查询返回需要统计信息的列。

默认情况下，如果手动创建统计信息时未另外指定，则无服务器 SQL 池在创建统计信息时会使用数据集中提供的全部数据。

例如，若要使用默认选项 (FULLSCAN) 为基于 us_population.csv 文件的数据集的人口列创建统计信息，请执行以下操作：


EXEC sys.sp_create_openrowset_statistics N'SELECT 
    population
FROM OPENROWSET(
    BULK ''https://azureopendatastorage.blob.core.windows.net/censusdatacontainer/raw_us_population_county/us_population.csv'',
    FORMAT = ''CSV'',
    PARSER_VERSION = ''2.0'',
    HEADER_ROW = TRUE)
AS [r]'

通过指定样本大小创建单列统计信息

可以将样本大小指定为百分比：

/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO

CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = ''
GO
*/

EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'

示例：更新统计信息

若要更新统计信息，您需要先删除并重新创建统计信息。 有关详细信息，请查看 sys.sp_create_openrowset_statistics 和 sys.sp_drop_openrowset_statistics。

sys.sp_drop_openrowset_statistics 存储过程用于删除统计信息：

sys.sp_drop_openrowset_statistics [ @stmt = ] N'statement_text'

注释

执行 sp_create_openrowset_statistics 和 sp_drop_openrowset_statistics 需要以下权限：ADMINISTER BULK OPERATIONS 或 ADMINISTER DATABASE BULK OPERATIONS。

参数：[ @stmt = ] N'statement_text' - 指定创建统计信息时使用的相同 Transact-SQL 语句。

若要更新数据集中的年份列统计信息，该列基于population.csv文件，您需要删除并重新创建统计信息。

EXEC sys.sp_drop_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'
GO

/* make sure you have credentials for storage account access created
IF EXISTS (SELECT * FROM sys.credentials WHERE name = 'https://azureopendatastorage.blob.core.windows.net/censusdatacontainer')
DROP CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]
GO

CREATE CREDENTIAL [https://azureopendatastorage.blob.core.windows.net/censusdatacontainer]  
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = ''
GO
*/

EXEC sys.sp_create_openrowset_statistics N'SELECT payment_type
FROM OPENROWSET(
        BULK ''https://sqlondemandstorage.blob.core.windows.net/parquet/taxi/year=2018/month=6/*.parquet'',
         FORMAT = ''PARQUET''
    ) AS [nyc]
    TABLESAMPLE(5 PERCENT)
'

示例：为外部表列创建统计信息

以下示例演示如何使用各种选项来创建统计信息。 用于每个列的选项取决于数据的特征以及列在查询中的使用方式。

注释

目前只能创建单列统计信息。

若要基于某个列创建统计信息，需要提供统计信息对象的名称和列的名称。

CREATE STATISTICS statistics_name
ON { external_table } ( column )
    WITH
        { FULLSCAN
          | [ SAMPLE number PERCENT ] }
        , { NORECOMPUTE }

参数：external_table指定应创建统计信息的外部表。

FULLSCAN 计算统计信息通过扫描所有行。 FULLSCAN 和 SAMPLE 100 PERCENT 的结果相同。 FULLSCAN 不能与 SAMPLE 选项一起使用。

示例数字 PERCENT 指定在创建统计信息时要使用的查询优化器表或索引视图中的近似百分比或行数。 数字可以是 0 到 100。

SAMPLE 不能与 FULLSCAN 选项一起使用。

注释

如果使用分析程序版本 1.0，CSV 采样不起作用，分析程序版本 1.0 的 CSV 仅支持全面扫描。

通过检查每个行创建单列统计信息

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

通过指定样本大小创建单列统计信息

-- following sample creates statistics with sampling 5%
CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH SAMPLE 5 percent, NORECOMPUTE

示例：更新统计信息

若要更新统计信息，您需要先删除并重新创建统计信息。 首先删除统计信息：

DROP STATISTICS census_external_table.sState

创建统计信息：

CREATE STATISTICS sState
    on census_external_table (STATENAME)
    WITH FULLSCAN, NORECOMPUTE

统计信息元数据

可以使用多个系统视图和函数来查找有关统计信息的信息。 例如，可以使用 STATS_DATE（） 函数查看统计信息对象是否过期。 STATS_DATE（） 允许查看上次创建或更新统计信息的时间。

注释

统计信息元数据仅适用于外部表列。 统计信息元数据不适用于 OPENROWSET 列。

统计信息的目录视图

这些系统视图提供有关统计信息的信息：

目录视图 DESCRIPTION
sys.columns 针对每个列提供一行。
sys.objects 针对数据库中的每个对象提供一行。
sys.schemas 针对数据库中的每个架构提供一行。
sys.stats 针对每个统计信息对象提供一行。
sys.stats_columns 针对统计信息对象中的每个列提供一行。 链接回到 sys.columns。
sys.tables 针对每个表（包括外部表）提供一行。
sys.table_types 每种数据类型一行。

统计信息的系统函数

这些系统函数适合用于处理统计信息：

系统函数 DESCRIPTION
STATS_DATE 上次更新统计信息对象的日期。

将统计信息列和函数合并成一个视图

此视图将与来自 STATS_DATE（） 函数的统计信息和结果相关的列组合在一起。

CREATE VIEW dbo.vstats_columns
AS
SELECT
        sm.[name]                           AS [schema_name]
,       tb.[name]                           AS [table_name]
,       st.[name]                           AS [stats_name]
,       st.[filter_definition]              AS [stats_filter_definition]
,       st.[has_filter]                     AS [stats_is_filtered]
,       STATS_DATE(st.[object_id],st.[stats_id])
                                            AS [stats_last_updated_date]
,       co.[name]                           AS [stats_column_name]
,       ty.[name]                           AS [column_type]
,       co.[max_length]                     AS [column_max_length]
,       co.[precision]                      AS [column_precision]
,       co.[scale]                          AS [column_scale]
,       co.[is_nullable]                    AS [column_is_nullable]
,       co.[collation_name]                 AS [column_collation_name]
,       QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS two_part_name
,       QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])
                                            AS three_part_name
FROM    sys.objects                         AS ob
JOIN    sys.stats           AS st ON    ob.[object_id]      = st.[object_id]
JOIN    sys.stats_columns   AS sc ON    st.[stats_id]       = sc.[stats_id]
                            AND         st.[object_id]      = sc.[object_id]
JOIN    sys.columns         AS co ON    sc.[column_id]      = co.[column_id]
                            AND         sc.[object_id]      = co.[object_id]
JOIN    sys.types           AS ty ON    co.[user_type_id]   = ty.[user_type_id]
JOIN    sys.tables          AS tb ON    co.[object_id]      = tb.[object_id]
JOIN    sys.schemas         AS sm ON    tb.[schema_id]      = sm.[schema_id]
WHERE   st.[user_created] = 1
;

后续步骤

若要进一步提高专用 SQL 池的查询性能，请参阅 监视工作负荷 和 专用 SQL 池的最佳做法。

若要进一步提高无服务器 SQL 池的查询性能，请参阅 无服务器 SQL 池的最佳做法。