You have the following stored procedure.
The stored procedure takes much longer to execute than expected.
While reviewing the execution plan of the stored procedure, you discover the following predicate for a Clustered
Index Scan operator.
You need to resolve the performance issue.
What should you do?
A.
Change the @FileName parameter from nvarchar(512) to varchar(512).
B.
Change the FileName column from varchar(512) to nvarchar(512).
C.
Add a NOLOCK query hint to the SELECT statement.
D.
Convert the table to a memory-optimized table.
E.
Add a FORCESEEK query hint to the SELECT statement.
Explanation:
When using a variable, make sure that the datatype matches the column’s datatype. We suspect that the issue
is that the variable is NVARCHAR (512) whilst the table column is VARCHAR (512). This is indicated by the
CONVERT_IMPLICIT operator in the execution plan.
https://sqlserverperformance.wordpress.com/2009/02/02/beware-of-convert_implicit-in-a-sqlexecution-plan/