Implicit conversion causing index scan

If when you are looking at a query plan and you see it doing an index scan along with an implicit conversion then the chances
are that if you fix the implicit conversion it will change to an index seek and result in much better performance.

You may not notice much of a difference if the table is only small, however on a large table with millions of rows
the issue will certainly start to cause problems.

Here is a query that you can run to check the plan cache for implicit conversions.

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)'), 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)'), 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)'), 
   t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)'), 
   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

I found this query here at sqlblog.com

How to reproduce Implicit Conversion example

Here is a small test you can run, check the execution plan to see the results of the implicit conversion.


USE DBAFire_DB

CREATE TABLE #Implicit_Test (id INT, WebsiteAddress VARCHAR (95))

INSERT INTO #Implicit_Test 
SELECT 1,'www.dbafire.com'
INSERT INTO #Implicit_Test
SELECT 2,'www.Microsoft.com'
INSERT INTO #Implicit_Test
SELECT 3,'www.Google.com'

DECLARE @SearchText NVARCHAR(95)

SET @SearchText = 'www.dbafire.com'

SELECT * FROM #Implicit_Test WHERE WebsiteAddress = @SearchText

Implicit-Conversion

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.