In our Production server, we have created a SQL Server Agent job which runs every 1 minute and monitors if there is any blocking for more than 10 seconds. In...

Is this sql server 2005? Then run a profiler and capture the deadlock graph. Deadlock can occur because of a lot of reasons and the deadlock graph will give you...

Have you tried this same query with hard-coding the value and not using a variable? In that case, is it still doing a Non-clustered Index Scan? Also, are the...