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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 optimizatin issue

Author  Topic 

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2012-11-22 : 07:41:46
I have a table with 85961118 rows

Now it has an ID as primary key and 2 more indexes on other fields

Now my problem is if I run an update statement on one record where id=85961118 it takes 10 minutes to run

what 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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2012-12-02 : 01:34:25
I fixed it by adding a key

I thought identity fields automatically have keys - I never new it was possible not to but adding the key fixed it
Go to Top of Page

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)
Go to Top of Page
   

- Advertisement -