隐式转换

发现某一条语句消耗较高，执行比较频繁

数据库版本如下

将TextData语句拷贝到查询窗口执行


将sp_executesql中的主体语句拷贝到查询窗口执行


执行计划的总体流向是一致的，根据token得到LKLoginTokenRecord，然后嵌套循环AccountsInfoSimple。但是sp_executesql语句的执行计划有常量扫描和计算标量的操作，并且在索引查找中有谓词CONVERT_IMPLICIT(nvarchar(32),[DBname].[dbo].[LKLoginTokenRecord].[Token] as [a].[Token],0)=[@0]
常量扫描和计算标量操作的文本计划如下

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1008], [Expr1009], [Expr1007]))
|    |--Compute Scalar(DEFINE:(([Expr1008],[Expr1009],[Expr1007])=GetRangeThroughConvert([@0],[@0],(62))))
|    |    |--Constant Scan
|    |--Index Seek(OBJECT:([DBname].[dbo].[LKLoginTokenRecord].[IX_Token] AS [a]), SEEK:([a].[Token] > [Expr1008] AND [a].[Token] < [Expr1009]),  WHERE:(CONVERT_IMPLICIT(nvarchar(32),[LK78DB].[dbo].[LKLoginTokenRecord].[Token] as [a].[Token],0)=[@0]) ORDERED FORWARD)

最开始一直不明白为什么会多出两个操作，明明很简单的语句，并且LKLoginTokenRecord在Token上有索引。sp_executesql的执行计划怎么就变得那么复杂？
以下段落来自微软亚太区数据库技术支持组 官方博客

我们注意到，这里出现了一个操作叫做GetRangeThroughConvert().在这里，SQL Server由于不能直接对varchar(32)的列用nvarchar(4000)的值进行seek，因此，SQL Server必须将nvarchar转换成varchar。但是由于这个转换可能导致数据丢失，SQL Server采用了另一种做法，首先扩展了一个varchar类型的范围，确保可以转换成我们目标的nvarchar值的varchar数据落在这个范围之内，然后使用这个范围去对index直接做seek。得到了返回的满足范围的少量数据以后，对这个范围内的少量数据进行数据类型转换，然后用来和nvarchar的值比较，最终准确的返回结果集。在这样一个过程中，SQL Server采用了一种迂回的方式使用了index seek而避免了表扫描。

就是说通过常量扫描和计算标量，SQL Server得到一个"缩小"范围的集合，然后针对集合内的数据进行转换并比较。实际就是减少需要隐式转换的数量。
因此问题语句出在隐式转换CONVERT_IMPLICIT，检查LKLoginTokenRecord表，Token字段类型为varchar(32)。sp_executesql语句的参数定义列表将@0定义为nvarchar(4000)，它传递到主体语句时，等效语句

SELECT a.userID,b.accounts AS Account,b.nickName,a.ExpireDate as TokenExpireDate
FROM LKLoginTokenRecord a WITH(NOLOCK)
INNER JOIN AccountsInfoSimple b WITH(NOLOCK)
ON a.UserID=b.userID
WHERE convert(nvarchar(32),token)=convert(nvarchar(4000),'65F2032A36C0477CBA602E7232BD6B49')
ORDER BY a.ExpireDate desc

注意where条件的两边有convert关键字


执行计划是不是似曾相识？它和sp_executesql的执行计划就是"一样"。我们单独查看执行计划是没有任何不同，但它们在缓存中对应不同的plan_handle，可用以下语句验证

SELECT cacheobjtype,objtype,usecounts,plan_handle, st.text  
FROM sys.dm_exec_cached_plans   
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st  
WHERE text LIKE N'%SELECT a.userID,b.accounts%'; 

第2行是等效语句，第9行是sp_executesql对应的执行情况。
如果我们最开始看到的问题语句是WHERE convert(nvarchar(32),token)=convert(nvarchar(4000),'65F2032A36C0477CBA602E7232BD6B49')，我们会毫不犹豫地说这样的查询使用不了Token上的索引，它会以表扫描(或聚集索引扫描)的方式返回满足条件的记录。
那么如何解决这个问题，只需将sp_executesql语句的参数定义列表将@0修改为varchar(4000)类型即可

如果我们使用存储过程

create proc DBA_TroubleShooting
@token varchar(32)
as
SELECT a.userID,b.accounts AS Account,b.nickName,a.ExpireDate as TokenExpireDate
FROM LKLoginTokenRecord a WITH(NOLOCK)
INNER JOIN AccountsInfoSimple b WITH(NOLOCK)
ON a.UserID=b.userID
WHERE (token=@token)
ORDER BY a.ExpireDate desc

使用sp_executesql语句执行存储过程

exec sp_executesql N'exec DBA_TroubleShooting @0',N'@0 nvarchar(4000)',@0=N'65F2032A36C0477CBA602E7232BD6B49'

此时不受sp_executesql语句的参数定义列表@0的数据类型影响，不管@0定义为varchar或nvarchar，只需存储过程的@token参数定义为varchar类型，语句就能直接使用参数Token在索引中查找数据。
sp_executesql使用的注意事项可参考博客请谨慎使用sp_executesql
疑问，GetRangeThroughConvert()是怎样计算得到范围的呢？它所到一个所谓的范围，会降低逻辑读吗(它让扫描变成了查找)？
在生产环境返回LKLoginTokenRecord表的所有记录，进行聚集索引扫描，SQL Server会产生5154个逻辑读

它比sp_executesql使用索引查找的逻辑读还低！很置疑sp_executesql执行计划中的索引查找，感觉查找并没带来太多实质性的改善。它的逻辑读并没比直接整表扫描的逻辑读低。对于非覆盖的非聚集索引尚能考虑Tipping Point问题，但对于GetRangeThroughConvert()目前却解释不了。
总结：很简单的一个问题，由于隐式转换，导致索引使用不上，开销异常。之前没有如此深入去分析这样的问题，最初被sp_executesql执行计划中的索引查找误导，以为此索引查找就是直接使用参数Token在索引中查找，分析后发现是此查找是得到一个"缩小"范围的集合，目的是减少需要隐式转换的数量。