SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

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

esthera
Flowing Fount of Yak Knowledge

1340 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

sunitabeck
Flowing Fount of Yak Knowledge

5152 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

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1765 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
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

johnson_ef
Starting Member

India
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.

-Johnson
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 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

esthera
Flowing Fount of Yak Knowledge

1340 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

Kristen
Test

United Kingdom
22191 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  
 New 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.05 seconds. Powered By: Snitz Forums 2000