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

原创 2015年07月02日 17:49:32

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

USE tempdb
GO
CREATE TABLE _t(
    c varchar(50)
);
CREATE INDEX IX_c ON _t( c );
GO

-- 加入 10000 条数据
INSERT _t
SELECT (9999 + id) FROM(
    SELECT TOP 10000 id = ROW_NUMBER() OVER( ORDER BY GETDATE() )
    FROM sys.all_columns a, sys.all_columns
)ID

然后通过执行计划看下查询计划

-- 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条,现在看起来一切正常

然后我们把据变一下,将大量数据变成相同值

-- 将 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;

然后我们发现评估的记录数变大了
这里写图片描述

继续加大相同值的比例

-- 继续加大相同值的比例,重建索引之后重新测试
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中怎么样)

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

在 SQL Server 查询中,不经意思的隐匿数据类型转换可能导致极大的查询性能问题,在 SQL Server 2008 及之后的版本中,这种操作做了增强,但还是经常遇到有问题的时候...
  • zjcxc
  • zjcxc
  • 2015年07月02日 17:49
  • 6039

MS SQL Server存储过程参数的隐式类型转换

Microsoft SQL Server 2000数据表的列和存储过程的参数都是有类型的,但我发现在写存储过程时很多参数类型是可以“混用”的,比如,下面的存储过程: /**//*  Name :   ...
  • langwolf
  • langwolf
  • 2007年08月25日 15:48
  • 858

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

在上一篇文章也谈SQL Server 2008 处理隐式数据类型转换在执行计划中的增强 中,我提到了隐式数据类型转换增加对于数据分布很不平均的表,评估的数据行数与实际值有很大出入的问题,进一步测试之后...
  • zjcxc
  • zjcxc
  • 2015年07月03日 16:58
  • 5804

如何看MS SQLSERVER数据库的执行计划

1.输入一个查询语句看看SQL Server是如何显示查询计划的吧。 select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v...
  • luoyanqing119
  • luoyanqing119
  • 2013年11月29日 12:09
  • 7777

com.microsoft.sqlserver.jdbc.SQLServerException: 不允许从数据类型 varbinary 到 date 的隐式转换。请使用 CONVERT 函数来运行此

在利用String进行数据格式判断时 if("".equals(date) || null==date) return null;
  • u014370239
  • u014370239
  • 2014年04月24日 11:22
  • 1947

Sql Server函数全解(三)数据类型转换函数和文本图像函数

阅读目录 一:数据类型转换函数二:文本和图像函数 回到顶部 一:数据类型转换函数   在同时处理不同数据类型的值时,SQL Server一般会自动进行隐士类型转换。对于数据...
  • yangyisen0713
  • yangyisen0713
  • 2015年05月14日 09:35
  • 723

Sql Server的执行计划

如何启动执行计划执行计划结果要看什么Sql Server的五种查询方式查看更具体的执行过程参考资料   前一篇总结了Sql Server Profiler,它主要用来监控数据库,并跟踪生成的...
  • wangzhen209
  • wangzhen209
  • 2016年07月20日 17:08
  • 458

Microsoft SQL Server 2008性能分析之执行计划

一直想找一些关于SQL语句性能调试的权威参考,但是有参考未必就能够做好调试的工作。我深信实践中得到的经验是最珍贵的,书本知识只是一个引导。本篇来源于《Inside Microsoft SQL Serv...
  • lxl743p
  • lxl743p
  • 2014年12月15日 10:56
  • 372

SQL Server 性能调优(cpu)

研究cpu压力工具 perfom SQL跟踪 性能视图 cpu相关的wait event Signal wait time SOS_SCHEDULER_YIELD等待 CXPACKET等...
  • dragon_ton
  • dragon_ton
  • 2015年10月22日 09:07
  • 2432

SQL Server 性能调优(cpu)

SQL Server 性能调优(cpu)   研究cpu压力工具 perfom SQL跟踪 性能视图 cpu相关的waitevent Signal wait time SOS_SC...
  • miyatang
  • miyatang
  • 2013年02月25日 15:48
  • 10433
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:也谈SQL Server 2008 处理隐式数据类型转换在执行计划中的增强
举报原因:
原因补充:

(最多只允许输入30个字)