Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 optimizatin issue
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Flowing Fount of Yak Knowledge

1410 Posts

Posted - 11/22/2012 :  07:41:46  Show Profile  Reply with Quote
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

Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/22/2012 :  10:30:48  Show Profile  Reply with Quote
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

Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts

Posted - 11/22/2012 :  14:49:51  Show Profile  Visit jackv's Homepage  Reply with Quote
Is there a shared lock on the row at the time and the update statement is trying to get an Exclusive Lock?

Jack Vamvas
Go to Top of Page

Starting Member

16 Posts

Posted - 11/30/2012 :  06:49:44  Show Profile  Reply with Quote
Any update on this issue, resolved? Just curious to know the cause of the issue if its resolved.

Go to Top of Page

Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 11/30/2012 :  07:00:30  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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

Flowing Fount of Yak Knowledge

1410 Posts

Posted - 12/02/2012 :  01:34:25  Show Profile  Reply with Quote
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


United Kingdom
22859 Posts

Posted - 12/03/2012 :  07:48:49  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000