谓词的直接价值产生不太好的计划 -- sql-server 领域 和 sql-server-2012 领域 和 performance 领域 和 optimization 领域 和 cardinality-estimates 领域 和 query-performance 领域 dba 相关 的问题

Direct values on predicate produce not-so-good plan


3
vote

问题

中文

我正在使用stackoverflow转储来运行一些测试。

特别是,我正在查询此表:

posts表

我已创建此索引:

创建索引

我正在运行以下查询(只是强制索引来测试替代方案)

查询直接值

我得到以下exec计划的高成本(66.63)。

不如此良好的计划

这些是运行此查询后的IO统计信息:

输入图像描述

然后,我通过提供变量而不是直接值来运行相同的查询

查询与变量

我得到一个更好的计划(成本为0.4385)。

更好的计划

统计也更好:

更好的统计

首先......我认为SQL Server没有识别直接值作为int,但既没有型号不匹配也没有隐式转换警告。

我也尝试避免并行性,但是当在谓词中传递直接值时,我仍然使用MaxDop 1获得高成本计划(和更高的IO统计)。

在比较两个计划时,存在不同的估计值:

在此处输入图像说明

直接值作为谓词的一部分传递出什么问题?

英文原文

I'm using StackOverflow dump to run some tests.

In particular, I'm querying this table:

Posts Table

I have created this index:

Created index

I'm running the following query (just forcing an index to test alternatives)

Query with direct values

I get the following exec plan high a cost (66.63).

Not-so-good plan

These are the IO statistics after running this query:

enter image description here

Then, I run the same query by providing variables instead of direct values

Query with variables

I get a better plan (Cost is 0.4385).

Better plan

Statistics are also better:

Better statistics

At first...I thought SQL Server was not recognizing direct values as INT, but there are neither type mismatch nor implicit conversion warnings.

I also tried avoiding parallelism, but I still get a high-cost plan (and higher IO statistics) with MAXDOP 1 when passing direct values in the predicate.

When comparing both plans, there are different estimates:

enter image description here

What's wrong with direct values being passed as part of predicate?

                 

回答列表

4
 
vote
vote
最佳答案
 

直接值作为谓词的一部分传递出什么问题?

变量和参数之间的差异

在计算变量估计时,优化器使用统计密度矢量。

当直接在查询中嵌入"直接" 或"静态" 值时,使用统计数据图。这就是你得到不同估计的原因,因此不同的计划。

这是我的估计计划: https://www.brentozar.com/pastetheplan/? id = sjcdutukn

在我的2010副本的SO数据库中,OWNERSERID列的密度为.000003807058。将3,744,192行乘以= 14.2544行。这正是估计来自IX_POSTS_OWNERUSERID的行数。

屏幕截图索引估计

您可以通过运行此DBCC命令获取有关该索引的统计信息的信息:

  DBCC SHOW_STATISTICS('dbo.Posts', 'IX_Posts_OwnerUserId');   

这是(缩写)输出:

  Name                    Updated             Rows IX_Posts_OwnerUserId    Apr  8 2019  8:33AM 3744192  All density     Average Length  Columns 3.807058E-06    4               OwnerUserId   

由于PostTypeID也是Where子句的一部分,因此也会为该列自动生成统计信息。密度矢量出现在.25 x 3,744,192行= 936,048行。

  DBCC SHOW_STATISTICS('dbo.Posts', '_WA_Sys_00000010_0519C6AF');   

和输出:

  Name                        Updated             Rows _WA_Sys_00000010_0519C6AF   Apr  8 2019  9:04AM 3744192  All density Average Length  Columns 0.25        4               PostTypeId   

因为这是一个"和" 谓词,估计使用两者的较低。

当您使用静态值而不是变量时,它使用统计器直方图。这是该show_statistics命令的第三结果。对于您使用的关键,这里是直方图条目:

  RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS 22656           13040       11371   305                 42.7541   

这是11,371的估计来自"静态值" 计划。

直方图可以更好地估计很多时间,因为它涉及边缘案例有点更好 - 因为它通常会在这样的大表中经常出现一些异常值。

成本差异

在该特定情况下,直方图产生估计,该估计是恰好右。产生的计划的成本是(正确)高于使用密度向量的成本,因为它必须处理更多的行。

"较低的成本" 计划认为,当实际上产生11,371行时,将产生14行。

逻辑读取

由于嵌套回路预取。似乎在我的机器上没有巨大差异,查询的经过时间在彼此的10毫秒内。

并行性实际上没有任何帮助,因为所有行都在一个线程上结束(无论如何在我的机器上)。添加 OPTION (MAXDOP 1) 帮助执行时间,但不会删除额外的逻辑读取。

此查询的"额外读取" 问题的一个潜在解决方案是为了避免完全通过将PostTypeID添加为包含的列:

  CREATE INDEX IX_Posts_OwnerUserId ON dbo.Posts (OwnerUserId)  INCLUDE (PostTypeId)  WITH (DROP_EXISTING = ON);   
 

What's wrong with direct values being passed as part of predicate?

The Difference Between Variables and Parameters

The optimizer uses the statistics density vector when calculating estimates for variables.

When "direct" or "static" values are embedded in the query directly, the statistics histogram is used instead. This is why you get different estimates, and thus different plans.

Here's my estimated plan: https://www.brentozar.com/pastetheplan/?id=SJCduTuKN

On my 2010 copy of the SO database, the density of the OwnerUserId column is .000003807058. Multiplying that by 3,744,192 rows = 14.2544 rows. Which is exactly the number of rows estimated to come out of IX_Posts_OwnerUserId.

screenshot of the index seek estimate

You can get this information about the statistics for that index by running this DBCC command:

DBCC SHOW_STATISTICS('dbo.Posts', 'IX_Posts_OwnerUserId'); 

Here's the (abbreviated) output:

Name                    Updated             Rows IX_Posts_OwnerUserId    Apr  8 2019  8:33AM 3744192  All density     Average Length  Columns 3.807058E-06    4               OwnerUserId 

Since PostTypeId is also part of the WHERE clause, statistics are automatically generated for that column as well. That density vector comes out to be .25 x 3,744,192 rows = 936,048 rows.

DBCC SHOW_STATISTICS('dbo.Posts', '_WA_Sys_00000010_0519C6AF'); 

And the output:

Name                        Updated             Rows _WA_Sys_00000010_0519C6AF   Apr  8 2019  9:04AM 3744192  All density Average Length  Columns 0.25        4               PostTypeId 

Since this is an "AND" predicate, the estimate uses the lower of the two.

When you use static values instead of variables, it uses the statistics histogram. This is in the third resultset of that SHOW_STATISTICS command. For the key you're using, here is the histogram entry:

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS 22656           13040       11371   305                 42.7541 

This is where the estimate of 11,371 comes from in the "static values" plan.

The histogram can be a better estimate a lot of the time, as it deals with edge cases a bit better - since there will often be some outliers in a big table like this.

Costing Differences

In this specific case, the histogram produces an estimate that is exactly right. The cost of the plan produced is (correctly) higher than the one that uses the density vector, because it has to process many more rows.

The "lower cost" plan thinks that 14 rows will be produced by that seek, when in fact 11,371 rows are produced.

Logical Reads

Logical reads are slightly higher in the parallel plan because of nested loops prefetching. It doesn't seem to make a big difference on my machine - elapsed time for the queries was within 10 ms of each other.

The parallelism doesn't actually help anything, since all of the rows end up on one thread (on my machine anyway). Adding OPTION (MAXDOP 1) helps with the execution time, but doesn't remove the extra logical reads.

One potential solution to the "extra reads" problem for this query would be to avoid the key lookup entirely by adding PostTypeId as an included column:

CREATE INDEX IX_Posts_OwnerUserId ON dbo.Posts (OwnerUserId)  INCLUDE (PostTypeId)  WITH (DROP_EXISTING = ON); 
 
 

相关问题

3  如何将本地DB服务器之间的数据推向云DB服务器?  ( How to push data between a local db server to cloud db server ) 
我有关于SQL Server 2014数据库服务器(从本地房屋SQL Server复制到云DB服务器的数据)之间的复制数据的疑问,这些数据在同一域或VPN中)。 可以请您提出建议解决方案,从本地DB服务器实现此数据传输推送到云DB服务器。 我会详细说明这里的场景,我们正在尝试实现的目标。 场景 我的客户在.N...

1  SSMS - 数据加载选项  ( Ssms data load option ) 
我最近在我的系统上安装了更新版本的SSM。但是加载Excel文件的选项是灰色的。我无法加载批量加载 我试图运行dtswizard.exe,即使它没有给我从Excel表中加载数据的选项。 请找到附件 此处是我在此系统上安装的实例 ...

1  如何重建/重新安装SSRS(ReportServer,ReportServerveMPDB)数据库?  ( How to rebuild reinstall ssrs reportserver reportservertempdb databases ) 
我们的服务器崩溃了。我们备份并运行,但是,所提到的数据库已被损坏。 是否有程序化/自动重建或重新安装SSRS数据库的方法? 如果不是: 由于我们仍然可以 select * ,从数据库中,我如何在不同的报告上获取权限和角色? 如何检索订阅信息? ...

0  SQL Server服务只有在Windows Shutdown后自动启动,我如何在没有批处理文件的情况下自动重启? [复制]  ( Sql server service start automatically only after windows shutdown how can i ma ) 
这个问题已经在这里有答案: 自动重启SQL Server 2008 (2个答案) ...

5  创建数据库,因为副本不适用于Azure  ( Create database as copy does not work with azure ) 
我是新的t-sql和mssql,但需要将Azure SQL数据库从一个服务器复制到另一个服务器。 我googled - 它可以使用 CREATE DATABASE Database1_copy AS COPY OF server1.Database1; 查询来完成,但在我的ubuntu linux上的Vusia...

3  使用过于子句的函数的正式名称  ( Formal name for functions that use the over clause ) 
是否有正式/学术名称在SQL Server等SQL Server等函数中的名称,如row_number(),lag,ligefefghijklmnabcdefghijklmn2 语法? ANSI标准涵盖了多少? 我可以在msdn上看到一些那些被列为分析函数,但其​​他一些是排名函数。 我要使用SQL Server名称...

2  如何将Azure混合连接指向可用性组侦听器?或重定向到辅助副本?  ( How to point azure hybrid connection to availability group listener or redirec ) 
| video_category_id | name | |------------------------------ | 4 | music | | 8 | tv | | 5 | black ...

0  解决高盗窃内存  ( Solve high stolen memory ) 
我是一个SQL Server 2012 SP2,最大内存设置为13GB,但有8GB被盗内存( SQLServer:Memory Manager - 被盗服务器内存(KB))。我知道被盗的内存是从缓冲池取消的内存来进行排序和散列操作。 但是如何确定使用所有被盗内存的过程/请求,或者查询正在做大量散列/排序? 更新...

1  ADFS部署的SQL恢复模型  ( Sql recovery model for adfs deployment ) 
我们有"adfsconfiguration" 和"adfsartifactstore" 数据库,用于我们的ADFS部署。由于服务器上拍摄的完整和事务日志备份,我将继续运行磁盘空间问题。 现在,这两个数据库处于完整恢复模型 - 但我想知道我是否可以切换到简单。是在这两个数据库中的事务日志备份将重要吗? 我已经搜索了很多...

0  需要帮助弄清一个雏菊链问题[关闭]  ( Need help figuring out a daisy chain issue ) 
关闭。这个问题需要详细信息或清晰度。它目前不接受答案。 想要改进这个问题?添加详细信息并阐明编辑此帖的问题。 关闭 2年前。 ...

5  可以使用比db_ddladmin更少的权限设置identity_insert?  ( Can set identity insert be allowed with less privileges than db ddladmin ) 
我继承了应用程序在 sysadmin 帐户下运行的系统。 我将此帐户限制为 db_datareader + db_datawriter + EXECUTE 在所有数据库上设置 extended events 会话以捕获<代码>服务器上的ABCDEFGHIJKLMNABCDEFGHIJKLMN5 。 我非常令人...

4  数据库备份VS服务器备份  ( Database backups vs server backups ) 
在我当前的工作中,我最近发现我们的SQL数据库服务器没有为它们设置备份设备。当我向其他IT成员询问此问题时,响应是定期备份服务器本身,包括数据库文件,因此拍摄SQL Server备份将是冗余的。 我确定这是一个非常基本的问题,但它让我思考:为什么MS SQL Server以及其他RDBMS系统有自己的备份系统?大多数...

12  为什么索引视图不允许非唯一群集索引?  ( Why do indexed views not permit non unique clustered indexes ) 
我一直在研究使用索引的观点来提高一些最常用的视图的性能。 然而,索引视图不支持非唯一群集索引,这对数据库结构的其余部分设置的优先级略微反对。 例如,这里是一对表的简化版本。 -Groups- Group ID GroupName -Users- UserKey UserName FullNam...

0  始终on实例服务代理/数据库镜像传输连接端点错误  ( Always on instance service broker database mirroring transport connection endpoi ) 
我有一个有2个aog的生产实例。两个组都没有配置镜像。两组也处于"健康" 状态。 在检查SQL Server上的错误日志时,我注意到从12/19/20开始,这是PM 9:03开始的这个错误。我进一步挖了一个挖出挖掘,看看有一种模式。每天晚上9:03-9:04PM发生同样的错误。在正常工作时间内不会发生这种情况。 错误...

1  将数据从Oracle移动到SQL Server:表大小优化  ( Moving data from oracle to sql server table size optimization ) 
我正在使用ssis从oracle加载数据到SQL Server数据库。一个表我在Oracle下移动的尺寸为4 MB,但加载到SQL Server时,大小会增长到7.96 MB! 我分析了大小(以kB为单位),具体取决于插入的行并得到以下结果: Rows SQL Server Oracle 10 ...




© 2021 it.wenda123.org All Rights Reserved. 问答之家 版权所有


Licensed under cc by-sa 3.0 with attribution required.