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 |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2012-11-22 : 07:41:46
|
| I have a table with 85961118 rowsNow it has an ID as primary key and 2 more indexes on other fieldsNow my problem is if I run an update statement on one record where id=85961118 it takes 10 minutes to runwhat could be the problem here?I imagine sql is supposed to work even on very large tables |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-22 : 10:30:48
|
| I can't imagine that it should take that long unless something is blocking the update or for some reason, it is not using the primary key to find the row to be updated. Run sp_who2 to see if there is anything blocking. Also, turn on the execution plan (control-m) before you run the query and take a look at the execution plan to see if it is using the index. You might also consider posting the query to the forum which may elicit more thoughts from people on the forum. |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-11-22 : 14:49:51
|
| Is there a shared lock on the row at the time and the update statement is trying to get an Exclusive Lock?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
johnson_ef
Starting Member
16 Posts |
Posted - 2012-11-30 : 06:49:44
|
| Any update on this issue, resolved? Just curious to know the cause of the issue if its resolved.-Johnson |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-11-30 : 07:00:30
|
| Foreign keys referencing it or triggers?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2012-12-02 : 01:34:25
|
| I fixed it by adding a keyI thought identity fields automatically have keys - I never new it was possible not to but adding the key fixed it |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-03 : 07:48:49
|
| You said the IDENTITY column was [part of] the primary key?Primary Key is automatically indexed. (Might not have been the clustered index though) |
|
|
|
|
|
|
optimizatin issue