RootCauseunvalidated
When run from code, the query parameters are passed as NVARCHAR, causing an implicit conversion of the indexed VARCHAR column due to type-precedence rules. This prevents use of the nonclustered index, leading to clustered index scans (often parallel), which in turn increases and changes lock acquisition. The raw SQL uses a literal VARCHAR, allowing an index seek and resulting in range/key locks instead.
907a172b-971d-46cb-bf54-91878b12125b
When run from code, the query parameters are passed as NVARCHAR, causing an implicit conversion of the indexed VARCHAR column due to type-precedence rules. This prevents use of the nonclustered index, leading to clustered index scans (often parallel), which in turn increases and changes lock acquisition. The raw SQL uses a literal VARCHAR, allowing an index seek and resulting in range/key locks instead.