位置: 编程技术 - 正文

SQL Server中关于基数估计计算预估行数的一些方法探讨(关于sql的问题)

编辑:rootadmin

推荐整理分享SQL Server中关于基数估计计算预估行数的一些方法探讨(关于sql的问题),希望有所帮助,仅作参考,欢迎阅读内容。

文章相关热门搜索词:在sql server中关于数据库的说法正确的是,关于sql的问题,关于sql server数据库服务器登录账户的说法错误的是,关于sql server常用的数据类型,以下,关于sql server中的存储过程,下列说法中正确的是( ),在sql server中关于数据库的说法正确的是,关于sql server常用的数据类型,以下,SQL server中关于实例的描述,内容如对您有帮助,希望把文章链接给更多的朋友!

关于SQL Server 中的基数估计,官方文档Optimizing Your Query Plans with the SQL Server Cardinality Estimator里有大量细节介绍,但是全部是英文,估计也没有几个人仔细阅读。那么SQL Server 中基数估计的预估行数到底是怎么计算的呢? 有哪一些规律呢?我们下面通过一些例子来初略了解一下,下面测试案例仅供参考,如有不足或肤浅的地方,敬请指教!

下面实验测试的环境主要为SQL Server SP2 (Standard Edition (-bit)) 具体版本号为.0..0 ,如有在其它版本测试,后面会做具体说明。如下所示,我们先创建一个测试表并插入一些测试数据后,方便后面的测试工作。

我们来看看这个表的统计信息以及直方图内容。

SQL Server中有两种谓词:过滤谓词和连接谓词 。 我们先来看看过滤谓词的基数估计(预估行数),测试过程,如果要保持测试的公正性或不被其他因素影响,你可以使用下面的DBCC命令来排除干扰,如下例子所示:

(注意,执行时请勾选包含实际执行计划按钮)如上所示,预估行数(Estimated Number of Rows)为,跟实际行数一致。当然你换其他值,例如, , ,,其预估行数(Estimated Number of Rows)跟实际行数都是正确的(SQL SERVER 中测试结果也相同)。那么如果我换一个不存在的值呢?预估行数会是多少呢?

如上所示,预估行数(Estimated Number of Rows)为1. 你换其他任何不存在的值,预估行数(Estimated Number of Rows)都为1。这个跟沿用了老的基数评估:超出统计信息范围,那么老的基数评估就认为不存在,评估行数为1。很显然,对于没有超出统计信息范畴的,但是确实不存在的记录,其预估行数(Estimated Number of Rows)也是1,这个基数估计确实是合理,也是正确的。那么如果我使用变量呢?这个预估行数(Estimated Number of Rows)又会是什么值呢?

如上截图所示,实际执行计划的预估行数(Estimated Number of Rows)是, 那么如何计算来的呢? 其实你可以根据公式来计算, 如果不相信,你可以构造各种案例测试验证一下就能得到答案了.

[Row Sampled ]* [ALL density ] = * 0.2 = 也就是统计信息中抽样总行数*All Density(统计信息对象中各列的每个前缀的密度)

如果你加上OPTION(RECOMPILE), 那么预估行数(Estimated Number of Rows)又会变成1

如果你赋予@SID值为,并加上OPTION(RECOMPILE)时,那么预估行数(Estimated Number of Rows)就会变成EQ_ROWS的值了

接下来,我们修改一下SQL语句,将查询条件从等于符号改为大于符号,如下所示:

如上所示,预估行数(Estimated Number of Rows)变为了,那么这个值是怎么计算得来的呢?

计算公式是: [Row Sampled ] * 0.3(%)

*0.3=

肯定会有人问,你怎么知道是 [Row Sampled ] * 0.3 呢? 不会是你逆推的吧。 不错,这里是一个推测(网上也有不少资料都确认是0.3,权且当做计算公式中的一个常量),而且也做了不少测试,确实就是%。例如你将@SID赋值为,预估行数(Estimated Number of Rows)依然为,如果你怀疑是缓存的执行计划缘故,你可以先清空缓存的执行计划,结果依然如此。根据我的测试,不管你给@SID赋予任何值,预估行数(Estimated Number of Rows)全部为

如果SQL加上 OPTION(RECOMPILE) ,然后@SID赋予RANGE_HI_KEY里的值,那么预估行数(Estimated Number of Rows)又是如何计算的呢?

这个 是这样计算的,如下所示,大于的RANGE_HI_KEY有 , , ,他们对应的EQ_ROWS值相加 + + =, 不信你可以测试一下,将@SID赋予,那么预估行数(Estimated Number of Rows)就会变成.

那么我们再修改一下SQL查询语句,例如,我们要做一个区间查询,预估行数(Estimated Number of Rows)又会有什么变化呢?

如上所示,预估行数(Estimated Number of Rows)为. 这个值怎么来的呢?其实它是这样计算的:

那么如果我在SQL Server 中执行该SQL语句或者使用查询跟踪标记来关闭新的基数评估,数据库优化器使用老的基数评估,你会发现预估行数(Estimated Number of Rows)为了。如下所示:

这里的计算公式是

那么现在我们往表TEST_ESTIMATED_ROW里面插入条记录,此时这个数据量是不会触发统计信息更新的,而此时ID=的值超出了直方图中的RANG_HI_KY的最大值,也就是说直方图中没有统计这些新插入的数据,那这种情形称作升序键问题(ascending key problem)。在更统计信息新前就对这些数据运行查询,就会发生此类问题。

那么再来看看下面SQL的预估行数(Estimated Number of Rows),如下所示:

那么预估行数(Estimated Number of Rows)为. 是怎么计算来的呢?其实这个问题就是 Your Query Plans with the SQL Server Cardinality Estimator里面介绍,这种是基数估计的计算公式为 [All density] * [Rows Sampled] 。但是实际测试发现这个例子并不是如此,那么我们先来亲自测试一下白皮书文档里面的例子(注意,数据库实例是SQL Server ,AdventureWorks的兼容级别为),看看文档里面的例子是否正确。

可以看到OrderDate的统计信息为_WA_Sys__4BFF

SQL Server中关于基数估计计算预估行数的一些方法探讨(关于sql的问题)

DBCC SHOW_STATISTICS('Sales.SalesOrderHeader', _WA_Sys__4BFF);

从上可以看到最后统计信息更新时,采集的RANGE_HI_KEY的最大值为-- ::,那么我们插入条记录,此时这个数据量并不会触发统计信息更新。

然后我们开启SQL跟踪标志,你会发现下面SQL的预估行数为1。因为此时优化器采用老的基数估计。

取消SQL跟踪标志时,数据库使用新的基数估计时,预估函数变为了.

白皮书里的例子确实是如此,但是最上面那个例子,不清楚预估行数是如何计算的,尽管做了一些推测,但是在其它例子中始终不能验证。不知是这个白皮书有误还是SQL Server的基数估计做了调整, 还是说基数估计(CE)的算法远远不止这么简单?我在这个问题上纠结了两天,依然没有搞清楚!在测试、推测过程中,我发现一个新的问题:当表里面新增了数据,那么之前的测试列子结果是否还是一样呢?答案是不一样了。如下所示:

预估函数从变为了., 这个是怎么计算来的呢? 个人推测是这样得来的(如下所示)。

也就是说升序键问题(ascending key problem)也会影响预估函数。上面都是简单SQL的预估行数(Estimated Number of Rows)的推演、实际情况中,SQL要比这个复杂得多,那么在复杂情况下,例如多个过滤谓词的情况下,基数估计又是怎样预估行数的呢?由于前面例子构造的比较简单,不适合后面的演示,那么我们就用Optimizing Your Query Plans with the SQL Server Cardinality Estimator里的例子来简单演示一下:

如下所示,过滤谓词[StateProvinceID]、[City]、 [PostalCode]对应的统计信息分别为IX_Address_StateProvinceID、_WA_Sys__B1、_WA_Sys__B1。

从SQL Server 7 ~ SQL Server , 如果查询条件中,两个或多个谓词使用AND联结,那么各个谓词的选择率Si的乘积将作为查询预估函数的选择率

其计算结果为0. ,它低于1行。所以查询优化器使用估计的最小行数 (1)。下面看看SQL Server 中新的基数估计是如何计算预估行数的。

那么新的基数估计(SQL Server )的预估行数(Estimated Number of Rows).是怎么计算来的呢? 其实它们是选择率使用下面这样一个公式,其中p0 < p1 < p2 < p3 < p4

计算结果为. ~= . 是否还是有一些差别呢?你使用下面SQL对比,就会发现,其实原因是小数点后精确位数和四舍五入导致的。具体我也不知道计算估计精确位数。

那么OR Selectivity又是如何计算的,我们先来看看老的基数估计是是如何计算的,如下例子所示:

计算公式:(S1 + S2) ? (S1 * S2) ,那么(S1 + S2) ? (S1 * S2) 计算的值为

然后和AND操作,我们执行SQL Server 以前的AND的选择性是这样计算的S1 * S2

最后的计算结果如下:

那么我们再来看看SQL Server 下OR Selectivity的计算公式

那么这个预估行数(Estimated Number of Rows)是怎么算出来的呢? Paul White 的博客介绍,是通过下面这样计算来的。

A OR B = NOT (( NOT A) AND (NOT B)) 就是说A OR B 和 NOT (( NOT A) AND (NOT B)) 是等价的。

那么就可以这么推算,最后的预估行数(Estimated Number of Rows)计算结果为., 跟结果.有细微差别(这个是因为浮点数精度和四舍五入造成的)

上面是关于SQL Server中的基数估计(CE)如何计算预估行数的一些初步的探讨和认识,纠结我的问题到目前还没有弄清楚。虽然有点遗憾,但是在测试过程,发现去探究这些规律是一件非常有意思的事情.

以上所述是小编给大家介绍的SQL Server中关于基数估计计算预估行数的一些方法探讨,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对积木网网站的支持!

Sql Server数据库各版本功能对比 背景今天举办的DataAmp大会上,微软向开发者们强调了数据如何影响他们的应用和服务,顺道还宣布了几个小新闻。这个免费的线上研讨会不仅展示了未

SQLServer中使用扩展事件获取Session级别的等待信息及SQLServer 中Session级别等待信息的增强 什么是等待简单说明一下什么是等待:当应用程序对SQLServer发起一个Session请求的时候,这个Session请求在数据库中执行的过程中会申请其所需要的资源,

Sql Server临时表和游标的使用小结 1.临时表临时表与永久表相似,但临时表存储在tempdb中,当不再使用时会自动删除。临时表有局部和全局两种类型2者比较:局部临时表的名称以符号(#)打

标签: 关于sql的问题

本文链接地址:https://www.jiuchutong.com/biancheng/324393.html 转载请保留说明!

上一篇:SQL Server 常用函数使用方法小结(sqlserver函数语法)

下一篇:Sql Server数据库各版本功能对比(sql server数据库恢复)

  • 深入分析SqlServer查询计划(深入分析的成语)

    还是来看看这个【执行过程表格】吧。我来挑几个重要的说一下。【Rows】:表示在一个执行步骤中,所产生的记录条数。(真实数据,非预期)【Executes】:表示某个执行步骤被执行的次数。(真实数据,非预期)【Stmt Text】:表示要执行的步骤的描述。【EstimateRows】:表示要预期返回多少行数据。

    在这个【执行过程表格】中,对于优化查询来说,我认为前三列是比较重要的。对于前二列,我上面也解释了,意思也很清楚。 前二列的数字也大致反映了那些步骤所花的成本,对于比较慢的查询中,应该留意它们。 【Stmt Text】会告诉你每个步骤做了什么事情。对于这种表格,它所要表达的其实是一种树型信息(一行就表示在图形方式下的一个节点), 所以,我建议从最内层开始去读它们。做为示例,我来解释一下这张表格它所表达的执行过程。

    第5行:【Clustered Index Seek(OBJECT:([MyNorthwind].[dbo].[Customers].[PK_Customers]), SEEK:([MyNorthwind].[dbo].[Customers].[CustomerID]=[MyNorthwind].[dbo].[Orders].[CustomerID]) ORDERED FORWARD)】, 意思是说,SQL Server在对表Customers做Seek操作,而且是按照【Clustered Index Seek】的方式,对应的索引是【PK_Customers】,seek的值来源于[Orders].[CustomerID]

    第4行:【Clustered Index Scan(OBJECT:([MyNorthwind].[dbo].[Orders].[PK_Orders]), WHERE:([MyNorthwind].[dbo].[Orders].[OrderDate]>='-- ::." class="img-responsive" alt="深入分析SqlServer查询计划(深入分析的成语)">

    深入分析SqlServer查询计划(深入分析的成语)

  • centos 联合编译动态连接库详解(centos编译环境)

    centos 联合编译动态连接库详解(centos编译环境)

  • win8 打开图片或视频 弹出COM Surrogate已停止工作(win8图片查看器无法打开图片内存不足)

    win8 打开图片或视频 弹出COM Surrogate已停止工作(win8图片查看器无法打开图片内存不足)

  • Android 设计模式-单例模式(android设计模式的应用场景)

    Android 设计模式-单例模式(android设计模式的应用场景)

  • 收到退个税手续费要交增值税吗?
  • 计提附加税金额
  • 预收账款确认收入
  • 作废的增值税普通发票还可以重新开吗?
  • 预提费用在资产负债表哪个科目
  • 预付账款收不回发票怎么核销
  • 累计所得税前净现金流量计算公式为
  • 应交税费怎么做分录
  • 其他综合收益转入留存收益还是投资收益
  • 预付账款年底账务怎么做账?
  • 事业单位结转资金和结余资金区别
  • 建筑业暂估成本表
  • 购买原材料产生的运输费计入什么科目
  • 公司先注册实收资本后付账该如何做会计处理呢?
  • 企业用银行存款购买原材料
  • 固定资产增值税可以一次性抵扣吗
  • 没有单价数量只有金额的发票可以用吗
  • 企业境外收入税率
  • 工业企业哪些房子要交房产税
  • 汽车修理费增值税税率
  • 城镇土地使用税减免税政策
  • 哪些违约金可以退回
  • 电子税务局里的利润表,本月金额是填累计数吗
  • 商业折扣销售会计分录
  • 少交的增值税如何记账
  • 如何设置自动登录账号
  • 国库年终总结
  • 个税手续费返还属于政府补助吗
  • 企业间借款增值税怎么算
  • 违约金条款的特点
  • win7如何打开管理员权限
  • 房地产销售代理是什么意思
  • 怎么确认旧城改造完成
  • 期货手续费是双向收取吗
  • 公司增资怎么需要什么资料
  • php $_files
  • 成本结转的科目
  • 一般纳税人辅导期最新规定
  • 总公司给分公司调货
  • 企业固定资产账户2019年6月初借方余额5000000元
  • 应交增值税明细科目怎么看
  • java多线程线程数控制在多少
  • php支付宝现实支付要收费吗
  • 帝国cms更换编辑器
  • 价税合计公式分配
  • 出租无形资产取得的收益计入什么科目
  • 技术安装工人包食宿
  • 经营成本包括五项内容
  • 本年度发票一定要收回吗
  • 年终奖政策,提成怎么算
  • 业务招待费可以开专票抵扣吗
  • 厂房装修费用账务处理
  • 关联企业是如何运作的
  • 工会会计有工资么
  • 预提业务
  • mysql 一键安装
  • windows media player在播放文件时遇到问题怎么解决
  • windows xp.
  • macbookpro鼠标触控板
  • CentOS安装scp命令详解
  • linux bash sh
  • linux目录结构创建
  • linux网络聚合
  • win10取消uac
  • 适者生存作文800字议论文
  • node.js创建服务
  • css中渐变
  • 批处理call命令
  • dos常用命令与批处理文件
  • stick function
  • jquery jwt
  • 用python编写
  • 安卓初始化
  • javascript基础教程教材答案
  • jquery如何做登录
  • HTTP状态代码以及定义(解释)
  • android反编译smali
  • 个人所得税缴纳标准2024年
  • 城市维护建设税税基是什么
  • 法制观念的问题
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

    网站地图: 企业信息 工商信息 财税知识 网络常识 编程技术

    友情链接: 武汉网站建设