Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Getzin
Starting Member
6 Posts |
 Posted - 2009-09-11 : 01:35:08
|
| 1) I have an index on C1+C2+C3 columns. C1 integer, C2,C3 varcharWill the following statement use the above index :Update T1 with (rowlock) Set C5 = @C5 where C1 = @C1Or do i need to create an index on C1 ?2) There is only one row in T1 which will satisfy above Update stmt. However instead of rowlock, it looks like Tablelock is being applied. Because deadlock is happening betweenUpdate T1 with (rowlock) Set C5 = @C5 where C1 = @C1andUpdate T1 with (rowlock) Set C5 = @C6 where C1 = @C2Should i index on C1 with something more ? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-09-11 : 03:44:50
|
| Yes, that update can use the index. The predicate (on C1) is a left-based subset of the index key.What percentage of the table is getting updated? If it's a fair portion, SQL probably will choose a table lock, as it's less memory. Also if there are a lot of existing locks it may decide to escalate to table lock.There's no real point in widening the index for the update. It's only on C1 so there's no use for anything else in the index key.Do you have the deadlock graph?--Gail ShawSQL Server MVP |
 |
|
|
|
|
|
Index question