exec spSubscribe

tblMashedUp

SQL Blogs on MSDN

SQL Blogs on Technet

SQL Server 2008 @ Facebook

SQL YouTube Videos

sp_help 'jmassie'

tblTagCloud

Home»query optimizer

�

MyStream

Finding Index Scans due to Implicit Conversions

Posted by Jason on Monday, January 12, 2009 to SQL Server 2008, SQL Server 2005, query optimizer, SQL performance tuning, tsql, Indexes, Procedure Cache, DMVs
2452 Views | 8 Comments |

It is fairly widely known that implicit conversions of data types can cause scans. However, you may not be able to control what goes in your database, you may have inherited an app and we just are not perfect.

On top of that some can be tricky. For example, did you know that scope_identity and @@identity both return numeric data types. An AVG of a SMALLINT column returns an INT.

Here is a simple repro which the same query does an index scan instead of an index seek

CREATE TABLE t1
( 
id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, c1 VARCHAR(100) 
) 

GO

INSERT INTO t1(c1) 
SELECT 1
GO 10000

INSERT INTO t1(c1) 
SELECT 0
GO 10

CREATE INDEX ix ON t1 (c1) 

CREATE PROC p1
@blah NVARCHAR(100) 
AS
SELECT id FROM t1
WHERE c1 = @blah

EXEC p1 N'0'

CREATE PROC p2 
@blah VARCHAR(100) 
AS
SELECT id FROM t1
WHERE c1 = @blah

EXEC p2 '0'

DROP TABLE t1
DROP PROC p1
DROP PROC p2

I found a sample query from Umachandar Jayachandran and made some mod’s to it. It basically scans your procedure cache for index scans containing implicit conversions.

I have posted it at the SQLServerPedia Wiki here. It is the 2nd query listed.

Note: Implicit conversions may not always result in a scan.

email it! | |

Permalink

8 Comments

Rate this Post: 12345
COMMENTS:

Hi
I got Index Seek for both stored procedures. Yes, fo the first one ,SQL Server addes Compute and Constraint scan operato bit nothing more

posted @ Monday, January 12, 2009 11:55 PM by Uri

Using the same technique , we can solve lot of issues outlined in the post I did earlier.
http://sankarreddy.spaces.live.com/blog/cns!1F1B61765691B5CD!312.entry

posted @ Tuesday, January 13, 2009 1:21 PM by Sankar Reddy

Using the same technique , we can solve lot of issues outlined in the post I did earlier.
http://sankarreddy.spaces.live.com/blog/cns!1F1B61765691B5CD!312.entry

posted @ Tuesday, January 13, 2009 1:34 PM by Sankar Reddy

Good stuff Sankar.

posted @ Tuesday, January 13, 2009 1:42 PM by JasonMassie

Comments from the following blog entry: The evils of implicit conversions, located at: http://sqlblogcasts.com/blogs/acalvett/archive/2009/01/18/the-evils-of-implicit-conversions.aspx

posted @ Sunday, January 18, 2009 11:25 AM by Andrew Calvett

You have syntax error in Wiki (btw, in the blog its OK) - XQuery is case-sensitive, so "value" has to be in lower case - in Wiki it is in upper case, the query fails validation.
Anyway - great stuff!

posted @ Wednesday, January 21, 2009 1:16 AM by Michael Zilberstein

Comments from the following blog entry: http://www.brentozar.com/archive/2009/01/more-sqlserverpedia-syndicated-bloggers/

posted @ Friday, January 30, 2009 7:33 AM

Whether this particular conversion results in an scan depends
on your collation. If you have a Windows collation, you will
still get a seek, but a less efficient one. If you have an SQL
collation, then you get a scan.

posted @ Monday, September 14, 2009 1:41 PM by Erland Sommarskog

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]

Home|Upcoming Events|Feeds | Privacy Statement | Terms Of Use