SQL Server 2008处理隐式数据类型转换在执行计划中的增强

作者 | 邹建,资深数据库专家,精通各项 SQL Server 技术,具有丰富的管理、维护、优化能力以及业务应用经验。他一直热心于技术知识的分享、传播,持续活跃在 CSDN 和 MSDN 社区,曾多年蝉联 CSDN 论坛积分榜首。

此外,邹建还是 2004~2010、2013年度 MVP(微软最有价值专家) 获得者。著有《深入浅出 SQL Server 2005开发、管理与应用实例》《SQL Server 2000开发与管理应用实例》等畅销书。

在 SQL Server 查询中,不经意思的隐匿数据类型转换可能导致极大的查询性能问题,比如一个看起来没有任何问题简单的条件:WHERE c = N’x’ ,如果 c 的数据类型是 varchar,并且表中包含大量的数据,这个查询可能导致极大的性能开销,因为这个操作会导致列 c 的数据类型转换为 nvarchar与常量值匹配,在 SQL Server 2008 及之后的版本中,这种操作做了增强,一定程度上降低了性能开销,参考SQL Server 2008 处理隐式数据类型转换在执行计划中的增强 。

不过在实际应用中发现,这种增强有时候似乎没有起到作用,还是会存在很大的性能问题。

最近找时间做了一个测试,找出了一种可能的问题。

1. 创建一个测试表

USE tempdb GOCREATE TABLE _t( c varchar(50) );CREATE INDEX IX_c ON _t( c );GO-- 加入 10000 条数据INSERT _tSELECT (9999 + id) FROM( SELECT TOP 10000 id = ROW_NUMBER() OVER( ORDER BY GETDATE() ) FROM sys.all_columns a, sys.all_columns )ID

2. 通过执行计划看下查询计划

-- Rebuild索引,确保无索引碎片和统计信息准确 ALTER INDEX IX_c ON _t REBUILD;GO SET SHOWPLAN_ALL ON GO SELECT * FROM _t WHERE c = N'10005b'; GO SET SHOWPLAN_ALL OFF;

注意EstimateRows列,该列值为1,表示评估的满足条件的数据是1条,现在看起来一切正常 。

3.把数据变一下,将大量数据变成相同值

-- 将 5000 条数据值变成一样,重建索引之后重新测试 UPDATE _t SET c = '15000' WHERE c >= '15000' ALTER INDEX IX_c ON _t REBUILD; GO SET SHOWPLAN_ALL ON GO SELECT * FROM _t WHERE c = N'10005'; GO SET SHOWPLAN_ALL OFF;

然后我们发现评估的记录数变大了

4. 继续加大相同值的比例

-- 继续加大相同值的比例,重建索引之后重新测试 UPDATE _t SET c = '11000' WHERE c >= '11000' AND c < '15000' ALTER INDEX IX_c ON _t REBUILD; GO SET SHOWPLAN_ALL ON GO SELECT * FROM _t WHERE c = N'10005'; GO SET SHOWPLAN_ALL OFF; GO -- 继续加大相同值的比例,重建索引之后重新测试 UPDATE _t SET c = '10100' WHERE c >= '10100' AND c < '11000' ALTER INDEX IX_c ON _t REBUILD; GO SET SHOWPLAN_ALL ON GO SELECT * FROM _t WHERE c = N'10005'; GO SET SHOWPLAN_ALL OFF;

相应的,预估的行数也在增加

如果我们使用正确的数据类型,WHERE c = ‘10005’,则始终可以得到正确的预估行数。

我不确定 SQL Server是按照什么标准来预估这种情况下的记录数,从执行计划看,它将 nvarchar 值通过 GetRangeThroughConvert 评估出一个范围,实际执行的是一个范围 seek,在试验中,查询的值是一个常量,可以准确评估,难道这个转换之后,把常量当变量评估了,所以是一个泛泛的评估结果值。

这个问题看起来不大,但在实际应用中,如果表的数据量很大,并且不是平均分布的话,这种错误的预估结果带来的性能影响是很大的,比如明明满足条件的很少,可以 seek, 但评估的结果很大,执行计划变 Scan了,在复杂的执行计划中,这个带来的影响更大。

看起来,2008(包括R2)还没有那么省心,这种问题还得控制,特别是程序中,.Net过来的参数通常都是 nvarchar类型,这种导致性能问题的情况遇到N多了 。

最后啰嗦一下的是,在 SQL Server 2014中,没有再发现这个问题(不知道 2012中怎么样)

原创:邹建。

投稿:有投稿意向技术人请在公众号对话框留言。

转载:意向文章下方留言。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-09-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

一条insert语句导致的性能问题分析(一)(r8笔记第40天)

今天早上开发找我看一个问题,说他们通过程序连接去查一个表的数据的时候,只查到了8条记录,这个情况着实比较反常,因为从业务上的数据情况来说,不可能只有8条。 但是...

3195
来自专栏开发与安全

腾讯2014年实习生招聘广州站offer经历(TEG-后台开发)

     过去的一年学的都是linux 系统编程和网络编程方面的东西,比较熟悉的语言也是c/c++,python仅限写一些测试客户端。所以这学期开始投的实习职...

2290
来自专栏java架构学习交流

面试技巧,如何通过索引说数据库优化能力,内容来自Java web轻量级开发面试教程

       如果我们需要招个Java方面的高级程序员,一方面看年限(本科3年),具体到数据库方面的技能要求,包括如下三个方面:        第一,是否会...

2136
来自专栏PPV课数据科学社区

【学习】图解SQL连接语句

SQL 联合语句好像是基于集合的,用韦恩图来解释咋一看是很自然而然的。假设我们有下面两张表。表A在左边,表B在右边。我们给它们各四条记录。 id name ...

3609
来自专栏杨建荣的学习笔记

相差数十倍的SQL性能分析(r11笔记第98天)

今天处理开发同学提交的一个数据查询需求,看起来是一个很常规的SQL,但是有一点不同的是,他们提供了两份文件,一份是一个id列表,大概有3000多个id值,...

3468
来自专栏PingCAP的专栏

TiDB 助力客如云餐饮 SaaS 服务

客如云成立于 2012 年,是全球领先、 国内最大的 SaaS 系统公司。 目前面向餐饮、 零售等服务业商家, 提供软硬一体的新一代智能化前台、收银等 SaaS...

2224
来自专栏Grace development

基于TP的每日签到代码

3043
来自专栏杨建荣的学习笔记

关于MySQL学习大纲(r5笔记第26天)

首先需要自我反省,因为自己圈内朋友中MySQL大牛太多,自己就先班门弄斧了,莫见怪:) 前段时间很荣幸通过了YEP(Young Expert Program)的...

4248
来自专栏杨建荣的学习笔记

一条"简单"的sql语句和小兔子买面包的故事 (r3笔记第25天)

有时候开发人员写sql语句的时候,接触的性能问题越多,可能对sql语句的结构,性能考虑会多一些,这也是一件好事,不过如果考虑不当,本来原本想做的的一些优化却使得...

2745
来自专栏大数据

爬虫无烦恼,大牛教你怎么简单爬36kr网,带源码

抓取36kr网站数据 爬虫代码和数据sql脚本在下方链接,抓取过程主要是抓包找到url递归解析的规律,三个主要的函数 以及 表结构脚本如下: #建表语句(写爬虫...

2439

扫码关注云+社区

领取腾讯云代金券