kthanigaivel (1/17/2011)
Hi,I am facing deadlock in update query. i found the reason is Clustered index scan takes more time.
the query likes below example.
update tbl1 set b=10 where c=x and d=y
here a,b,c,d are columns and a is the primary key of tbl1.
any solution to reduce clustered index scan without introducing non-clustered index?
Since A isn't listed in your WHERE clause anywhere, it's forced to scan.
If C/D is the most common way into tbl1, you should set your primary key to non-clustered, and make C/D your clustered index.
Without real ddl and evaluation of the actual database though, I can't tell you if this is your best idea or not. It would fix *this* query. I have no idea if it will foul the rest of your optimization.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA