Finding Index Scans due to Implicit Conversions
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.
This content is published under the Attribution-Share Alike 3.0 Unported license.
Comments
My RSS
-
My LifeStream
My Presence
