Scanning on different Index

Question

Scanning on different Index

VastSQL

SSC-Dedicated

Points: 36069
More actions
July 20, 2022 at 7:43 am

#4065767

Hi Experts,
Below query is scanning index on column 'created' where I was expecting it to scan on CID which is the 'where' clause and PK?
declare @P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000),
@P6 varbinary(max),@P7 nvarchar(4000),@P8 nvarchar(4000)
UPDATE cache_render SET cid=@P1, expire=@P2, created=@P3, tags=@P4, checksum=@P5, [data]=@P6, serialized=@P7
WHERE cid = @P8
https://www.brentozar.com/pastetheplan/?id=BkX-MVHh9
Can you help me understand why its scanning different index?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

kaj SSCommitted Points: 1580 More actions · Answer 1

It does look strange. I notice that there are some implicit conversions involved - the parameter @p8 is converted/cast to nvarchar(4000) and the cid column is likewise implicitly converted/cast to nvarchar(255), presumately to allow the to values to be compared. I don't know if that fact influences the priority of the indexes to use.

The question on my mind now is: how are cid and @p8 defined?

ratbak Ten Centuries Points: 1334 More actions · Answer 2

@P8 -- like all your improbably large/generic parameters -- is an nvarchar(4000). cid can't possibly be nvarchar(4000) and have an index/primary key -- it's too large to be indexed. The incorrect data types are forcing implicit conversion, making index useless.

Is this code generated by an ORM (e.g., Entity Framework)? You can make EF (and probably other ORMs) use the right data type.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

What is the datatype of @P8? If it's not NVARCHAR(), it will ALWAYS do an index SCAN because it first has to convert all of the rows in the entire column to NVARCHAR() to do the comparison. This is a VERY common problem for ORMs of any brand. Implicit data conversions like this are one of the reasons that people thing databases are slow. It's also a good reason to use stored procedures instead of directly generated code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100950 More actions · Answer 4

VastSQL wrote:

Hi Experts,
Below query is scanning index on column 'created' where I was expecting it to scan on CID which is the 'where' clause and PK?
Can you help me understand why its scanning different index?

You would expect, and hope, that SQL would be able to do a seek on CID rather than a scan.

The most likely reason is what Jeff said: that the cid column is not nvarchar but varchar (or char).

SQL is scanning the 'created' index because it's the smallest index that has all the data that SQL needs to determine matching rows and SQL "thinks" that doing that scan will give the lowest overall cost for the query.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

VastSQL SSC-Dedicated Points: 36069 More actions · Answer 5

VastSQL

SSC-Dedicated

Points: 36069

August 2, 2022 at 8:54 am

#4071250

Thanks everyone

durai nagarajan SSCoach Points: 15775 More actions · Answer 6

is the above comments was the solution? what is the difference is execution plan post changes?

Regards
Durai Nagarajan