曲演雜壇--隱式轉換

2024-07-21 02:50:38

字體：大中小

來源：轉載

供稿：網友

曲演雜壇--隱式轉換

--==================================================================MSDN如下說：當兩個不同數據類型的表達式用運算符組合后，數據類型優先級規則指定將優先級較低的數據類型轉換為優先級較高的數據類型。如果此轉換不是所支持的隱式轉換，則返回錯誤。當兩個操作數表達式具有相同的數據類型時，運算的結果便為該數據類型。--==================================================================

在數據庫操作中，類型轉換時無可避免的情況，但是“偷偷摸摸”進行的隱式轉換就要引起管理員的高度重視，當隱身轉換發生在過濾條件中的列上或者表連接的列上時，可能會導致無法使用索引而造成性能問題。

測試code

USE TestDemoGODROP 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 TGO 10--執行查詢EXEC SP_Executesql N'SELECT * FROM TB1WHERE C1 =@C1',N'@C1 VARCHAR(200)',@C1='C1'EXEC SP_Executesql N'SELECT * FROM TB1WHERE 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]ASBEGINSET NOCOUNT ON;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDSELECT 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_planFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qtCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpWHERE QS.execution_count>10AND (qs.total_logical_reads+ qs.total_logical_writes) / qs.execution_count>100AND(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 DESCEND

查找版本2：

--====================================================================--查看指定數據庫下發生的隱式轉換--來源：http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/08/finding-implicit-column-conversions-in-the-plan-cache.aspxSET 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

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

--妹子引狼