曲演杂坛--隐式转换

--==================================================================
MSDN如下说：
当两个不同数据类型的表达式用运算符组合后，数据类型优先级规则指定将优先级较低的数据类型转换为优先级较高的数据类型。 如果此转换不是所支持的隐式转换，则返回错误。 当两个操作数表达式具有相同的数据类型时，运算的结果便为该数据类型。
--==================================================================

在数据库操作中，类型转换时无可避免的情况，但是“偷偷摸摸”进行的隐式转换就要引起管理员的高度重视，当隐身转换发生在过滤条件中的列上或者表连接的列上时，可能会导致无法使用索引而造成性能问题。

测试code

USE TestDemo
GO
DROP TABLE TB1

--============================================
--创建测试表
CREATE TABLE TB1
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    C1 VARCHAR(200),
    C2 VARCHAR(200)
)
CREATE INDEX IX_C1 ON TB1(C1)
--插入测试数据
INSERT TB1(C1,C2)
SELECT T.name,T.name FROM sys.all_columns T
GO 10
--执行查询


EXEC SP_Executesql N'SELECT * FROM TB1
WHERE C1 =@C1',N'@C1 VARCHAR(200)',@C1='C1'

EXEC SP_Executesql N'SELECT * FROM TB1
WHERE C1 =@C1',N'@C1 NVARCHAR(200)',@C1='C1'

对比两个执行计划，会发现两者的执行计划有很大区别，后者传入NVARCHAR(200)类型的参数，导致执行计划中出现隐式转换：

注意：上图中，因为隐式转换，将等值运算符变成了区间查询。

请原谅我给你们一个不好的Demo，即使在过滤条件的数据列上发生了执行计划，依然有可能使用到索引。

PS:在我的本地测试机上，发生隐式转换的执行计划开销要远低于没发生隐式转换的,发生隐式转换的执行计划产生的逻辑读也小于没发生隐式转换的，对此问题，我也很纠结，请别问。

 

尽管有上面的示例作为发面教材，我仍坚定地建议：请避免在过滤条件中的列上或者表连接的列上发生隐式转换，如果可以，我们甚至需要避免对传入参数进行隐式转换。

 

对于隐式转换，我们可以通过执行计划XML来查找

查找版本1：

-- =============================================
-- Author:    SQL SERVER DMVS IN ACTION
-- Create date: 2013-04-04
-- Description:    查找有以下问题的查询
-- 1. 缺失索引
-- 2. 列隐式转换
-- 3. 表扫描
-- 4. 缺失统计
-- =============================================
CREATE PROCEDURE  [dbo].[usp_GetTop20MostFrequentlyExecutedAndTroubledQueries]
AS
BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 100 
qs.execution_count AS ExecutionCount
,CAST((qs.total_worker_time)/ 1000.0 AS DECIMAL(28,2)) AS TotalMilliSecondsForCPUTime
,CAST((qs.total_worker_time)/ 1000.0 / qs.execution_count AS DECIMAL(28, 2)) AS AvgMilliSecondsForCPUTime
,CAST((qs.total_elapsed_time- qs.total_worker_time) / 1000.0 AS DECIMAL(28,2)) AS BlockedTotalMilliSeconds
,CAST((qs.total_elapsed_time  - qs.total_worker_time) / 1000.0 / qs.execution_count AS DECIMAL(28, 2)) AS AverageBlockingMilliSeconds
,(qs.total_logical_reads + qs.total_logical_writes) AS TotalIO
,(qs.total_logical_reads+ qs.total_logical_writes) / qs.execution_count AS AvgIO
,qs.total_physical_reads
,qs.total_logical_reads
,qs.total_logical_reads/execution_count AS avg_logical_reads
,qs.total_logical_writes
,qs.total_logical_writes/execution_count AS avg_logical_writes
,qs.last_execution_time
,SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,    
((CASE WHEN qs.statement_end_offset = -1
    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
    ELSE qs.statement_end_offset
    END - qs.statement_start_offset)/2) + 1) AS IndividualQuery
, qt.text AS ParentQuery
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE QS.execution_count>10
AND (qs.total_logical_reads+ qs.total_logical_writes) / qs.execution_count>100
AND(
CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%<ColumnsWithNoStatistics>%'  
OR CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%<MissingIndexes>%' 
OR CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%CONVERT_IMPLICIT%' 
OR CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%<TableScan%'
)                 
ORDER BY QS.execution_count DESC

END

 

查找版本2：

--====================================================================
--查看指定数据库下发生的隐式转换
--来源：http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

DECLARE @dbname SYSNAME 
SET @dbname = QUOTENAME(DB_NAME()); 

WITH XMLNAMESPACES 
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT 
   stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text, 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName, 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName, 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName, 
   ic.DATA_TYPE AS ConvertFrom, 
   ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength, 
   t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo, 
   t.value('(@Length)[1]', 'int') AS ConvertToLength, 
   query_plan 
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t) 
JOIN INFORMATION_SCHEMA.COLUMNS AS ic 
   ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') 
   AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') 
   AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') 
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

 

PS：

1.在生产服务器上，在varchar和nvarchar间发生隐式转换，导致全表扫描，服务器擦点挂掉。

2.在不同服务器上，数据库结构和数据相同，对于相同的语句生成两个性能差距巨大的执行计划。

--===================================================

参考链接：
Finding Implicit Column Conversions in the Plan Cache:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspx

数据类型优先级：http://msdn.microsoft.com/zh-cn/library/ms190309.aspx

数据类型转换：http://msdn.microsoft.com/zh-cn/library/ms191530.aspx

--====================================================

--妹子引狼