主页 / dba / 问题 / 186193
Accepted
user1716729
Asked: 2017-09-19 01:48:40 +0800 CST

统计和行估计

  • 772

希望有人能帮助我了解SQL Server 如何使用stats 来估计记录数。

测试脚本

USE [tempdb]

GO

CREATE TABLE t1
(
a INT NOT NULL, 
b INT NOT NULL, 
c INT CHECK (c between 1 and 50), 
CONSTRAINT pk_a primary key(a)
);

GO

INSERT INTO t1(a,b,c) 
SELECT number, number%1000+1, number%50+1 
FROM master..spt_values 
WHERE type = 'P' AND number BETWEEN 1 and 1000;

GO

CREATE STATISTICS s_b ON t1(b);
CREATE STATISTICS s_c ON t1(c);

GO

示例查询

DECLARE @c INT=300 
SELECT * FROM t1 WHERE b>@c 
SELECT * FROM t1 WHERE b>300

查询 1

估计行数 300

查询 2

估计行数 700

问题

  1. 为什么第一次和第二次查询行估计之间存在如此巨大的差异。
  2. 2nd 查询 SQL 如何从 Stats 估计为 700
  3. 学习这些有什么好文章。
sql-server sql-server-2016

2 个回答

  1. Best Answer

    查询给出不同的估计，因为第一个查询没有从参数嵌入优化中受益。从优化器的角度来看，查询也可能是：

    SELECT * FROM t1 WHERE b>???;

    为了了解基数估计如何用于简单查询，有时我会考虑如果我必须对查询优化器进行编程以生成估计，我会怎么做。通常这些想法与 SQL Server 所做的并不完全匹配，但我发现记住优化器几乎总是处理不完美或有限的信息是有帮助的。让我们回到您的第一个查询：

    DECLARE @c INT=300; 
SELECT * FROM t1 WHERE b>@c;

    没有RECOMPILE提示，优化器不知道@c局部变量的值。这里没有足够的信息来进行正确的估计，但优化器需要为所有查询创建一个估计。那么优化器的程序员可以做什么呢？我认为可以实现一种基于列中值的分布进行猜测的复杂算法，但 Microsoft 使用硬编码猜测 30% 来表示未知的不等式。您可以通过更改局部变量的值来观察这一点：

    DECLARE @c INT=999999999; 
SELECT * FROM t1 WHERE b>@c;

    估计还是300行。请注意，估计值不需要在查询之间保持一致。以下查询也估计有 300 行，而不是 1000 - 300 = 700：

    DECLARE @c INT=300; 
SELECT * FROM t1 WHERE NOT (b>@c);

    从概念上讲，它可以重写为

    DECLARE @c INT=300; 
SELECT * FROM t1 WHERE b<=@c;

    这将具有相同的硬编码 30% 估计值。

    您的第二个查询不使用局部变量，因此优化器可以直接使用您在列上创建的统计对象来帮助进行估计。直方图最多只能有 201 个步骤，并且您的表有 1000 个不同的值，因此优化器在某些过滤器值中没有完整的信息。在这些情况下，它需要进行更多的猜测。查看b列的直方图的方法如下：

    DBCC SHOW_STATISTICS ('t1', 's_b') WITH HISTOGRAM;

    在我的机器上我很幸运，因为 300 是直方图步骤之一的高值：

    直方图

    您可以在文档中找到对不同列的解释。展望未来，我将假设您知道它们的含义。查询请求带有 的行b > 300，因此 a 为 300 的直方图RANGE_HI_KEY是不相关的，所有具有较低 的步骤也是如此RANGE_HI_KEY。对于此查询，我将对优化器进行编程，使其对直方图剩余 133 个步骤的所有RANGE_ROWS和值进行简单求和。EQ_ROWS这些列加起来为 700，这是 SQL Server 的估计值。

    其他过滤器值可能不会给出准确的结果。例如，以下两个查询都具有 704 行的基数估计：

    SELECT * FROM t1 WHERE b>293; -- returns 707 rows
SELECT * FROM t1 WHERE b>299; -- returns 701 rows

    两种估计都非常接近，但并不完全正确。直方图不包含这些值的足够信息以提供准确的估计。

    • 5

  2. 社区维基答案：

    第一种情况参见问答：SQL server How generates a Query plan with Auto Create Statistics set to OFF。您使用了一个变量，因此它被视为未知输入，并且估计为表基数的 30%（1000 行的 30%）。

    在第二种情况下，它使用统计直方图。您可以在 SQL Server 中了解它们：第 2 部分：所有关于 SQL Server 统计信息： Nelson John 的直方图。

    如果您强制根据 的当前值编译新计划，您还将获得参数化查询的 700 行估计值@c1。例如：

    SELECT * FROM t1 WHERE b>@c OPTION (RECOMPILE);
    • 1

相关问题