Why a clustered index scan on this Update SQL?
-
I have an app that someone installed (open source) and it does a lookup against a user table. The table yazdUser, column userID is Char(8). There's a clustered, unique Primary Key Index on userID. I'm seeing this code running quite often through Verity I3 monitors:
(
@P1 nvarchar (4000),
@P2 nvarchar (4000),
@P3 nvarchar (4000),
@P4 nvarchar (4000))
UPDATE yazdUser
SET name = @P1,
email = @P2,
username = @P3,
passwordHash = ''
WHERE userID = @P4
The execution plan displayed by I3 says 94% of the execution is in a Clustered Index Scan of PK_yazdUser. It is scanning all 33K rows. I would have thought that executing this SQL with a valid userid would result in a direct read of the appropriate entry. Could the difference in data types (nvarchar(4000) v char(8)) cause the scan? If not, then what?
-
I already posted something like that. Look carefully at the where in the clustered scan. You'll most likely find the condition to be something like this : where Cast(userID as nvarchar(4000)) = @P4 which forces a scan.
The change of the 4th parameter to char(8) will most certainly resolve this situation.
-
In addition to Remi's sage advice,
Is the index fragmented? Is there proper fill factor, How often is index maintenance performed to ensure NON-Fragmented indexes?
I know this is more maintenance-wise but, is something to consider for questions like this as well..
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Ya, 33k records can cause a lot of page reads if it's fragmented... but not as much as an useless scan
. -
No fragmentation. The index was built after the table was populated and there haven't been any adds or deletes since building the index. I double-checked anyway with DBCC and it says no. Must be the bad query.
Now if I can just get the developer to change the code...
-
Certainly having develper change code will help.
But if the app passes in nvarchar(4000)
Evaluate it in the procedure prior to the update
Declare @p5 char(8)
set @p5 = left(@p4,8)
UPDATE yazdUser
SET name = @P1,
email = @P2,
username = @P3,
passwordHash = ''
WHERE userID = @P5
-
That's where you see who's the boss on the server
.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply