kthanigaivel (1/17/2011)
any solution to reduce clustered index scan without introducing non-clustered index?
No. Why don't you want a non-clustered index?
Turn traceflag 1222 on. That will write a deadlock graph into the error log when a deadlock is encountered. Post the graph here along with the definition of the table and the indexes on it.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability