写点什么

Sqlserver2008 参数化踩的坑

用户头像
风翱
关注
发布于: 2021 年 04 月 18 日
Sqlserver2008参数化踩的坑

线上出现 SqlServer 服务器 CPU 占用过高的情况,从 SqlServer 的活动监视器中可以查看到对应的消耗语句:select top 1 name,sex,address from info with(nolock) where name = @P0 (info 数据量接近 1000 万),

SqlServer Profiler 具体的执行语句是:select top 1 name,sex,address from info with(nolock) where name = N’陈名’


使用语句查询统计数据:

set statistics io onset statistics time onselect top 1 name,sex,address from consult_info with(nolock) where name = N’陈名’


结果:

扫描计数 1,逻辑读取 33957 次,物理读取 62 次,预读 29330 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

SQL Server 执行时间:CPU 时间 = 500 毫秒,占用时间 = 8194 毫秒。

SQL Server 分析和编译时间:CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

SQL Server 执行时间:CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。


使用参数化的查询方式,不管是 java、c#,语句都会自动在字符前面增加 N,并把类型定义为 nvachar(name 数据库中是定义为 varchar)。

exec sp_executesql N’ select top 1 name,sex,address from info with(nolock) where name =@name ‘,N’@name nvarchar(20)’,@name =N’陈名’


验证后的情况:

当只有在查询中包含中文字符,才会出现逻辑读取次数较多,CPU 占用时间较长的情况出现,加 N 和不加 N 执行计划是一样的。当查询中不包含中文字符,逻辑读取次数,CPU 占用时间,加 N 和不加 N 是一样的。还有不确定的一点,和索引是否有关系。

(执行计划中会多出图中标红的部分)


解决的方法:

1:参数化查询的方式,程序上修改为直接采用字符串拼接的方式。(注意加上校验,防止 sql 注入风险)

结果如下:扫描计数 1,逻辑读取 8 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

SQL Server 执行时间:CPU 时间 = 0 毫秒,占用时间 = 97 毫秒。

SQL Server 分析和编译时间:CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

SQL Server 执行时间:CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。


2、在数据库层面,把 name 的 varchar 修改为 nvarchar 类型。

发布于: 2021 年 04 月 18 日阅读数: 15
用户头像

风翱

关注

还未添加个人签名 2017.11.24 加入

勇于尝试,持续成长

评论

发布
暂无评论
Sqlserver2008参数化踩的坑