CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Kamran
Kamran

Reputation: 4100

Sql Server 2008 Update query is taking so much time

I have a table name Companies with 372370 records.

And there is only one row which has CustomerNo = 'YP20324'.

I an running following query and its taking so much time I waited 5 minutes and it was still running. I couldn't figure out where is the problem. 

UPDATE Companies SET UserDefined3 = 'Unzustellbar 13.08.2012' WHERE CustomerNo = 'YP20324'

Upvotes: 2

Views: 18326

Answers (8)

Dan Mihai Patrascu
Dan Mihai Patrascu

Reputation: 192

In my case, there was a process that was blocking the update; Run: 'EXEC sp_who;' Find the process that is blocked by inspecting the 'blk' column; Let's say that we find a process that is blocked by '73'; Inspect the record with column 'spid' = '73' and if it's not important, run 'kill 73';

Upvotes: 0

Paresh Varde
Paresh Varde

Reputation: 1144

I would suggest to rebuild your indexes. This should surely help you.

If you do not have index on CustomerNo field you must add one.

Upvotes: 0

vasin1987
vasin1987

Reputation: 2012

Try this SQL and see what is running:

SELECT TOP 20
        R.session_id, R.status, R.start_time, R.command, Q.text
FROM
        sys.dm_exec_requests R
        CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) Q
WHERE R.status in ('runnable')
ORDER BY R.start_time

More details: List the queries running on SQL Server or http://sqlhint.com/sqlserver/scripts/tsql/list-long-running-queries

Once I found someone shrinking database and blocking all other people.

Upvotes: 2

ARA
ARA

Reputation: 1316

5mn is way too long for 370k rows, even without any indexes, someone else is locking your update. use sp_who2 (or activity monitor) and check for BlockedBy Column to find who is locking your update

Upvotes: 0

SAS
SAS

Reputation: 4035

Try adding an index on the field you are using in your WHERE clause:

CREATE INDEX ix_CompaniesCustomerNo ON Companies(CustomerNo);

Also check if there are other active queries which might block the update.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294287

More likely than not your UPDATE is not doing anything, is just waiting, blocked by some other statement. Use Activity Monitor to investigate what is causing the blocking. Most likely you have another statement that started a transaction and you forgot to close it.

There could be other causes too, eg. database/log growth. Only you can do the investigation. An index on CustomerNo is required, true, but lack of an index is unlikely to explain 5 minutes on 370k records. Blocking is more likely.

There are more advanced tools out there like sp_whoisactive.

Upvotes: 1

Mathese F
Mathese F

Reputation: 559

You don't have triggers on update on that table? Do you have a cascade foreign key based on that column?

Are you sure of the performance of your server? try to take a look of the memory, cpu first when you execute the query (for example on a 386 with 640mb i could understand it's slow :p)

And for the locks, you can right click the database and on the report you can see the blocking transactions. Sometimes it helps for concurrent access.

Upvotes: 4

semao
semao

Reputation: 1757

370k records is nothing for sql erver. You should check indexes on this table. Each index makes update operation longer.

Upvotes: -1

Related Questions