2

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'
Kamran
  • 4,010
  • 14
  • 60
  • 112

8 Answers8

4

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.

Mathese F
  • 559
  • 4
  • 9
2

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.

Tony
  • 9,672
  • 3
  • 47
  • 75
SAS
  • 3,943
  • 2
  • 27
  • 48
2

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.

Community
  • 1
  • 1
vasin1987
  • 1,962
  • 20
  • 26
1

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

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

ARA
  • 1,296
  • 10
  • 18
0

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.

Paresh Varde
  • 1,084
  • 3
  • 16
  • 39
0

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';

Dan Mihai Patrascu
  • 182
  • 1
  • 10
-1

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

semao
  • 1,757
  • 12
  • 12
  • You should clarify what you mean by "Each index makes update operation longer"; In this case an index on the field being used in the `WHERE` clause would greatly speed up the update. If you have many indexes to update because of the fields which have changed, that _might_ slow down an `INSERT` or `UPDATE` but not much, and only when bulk loading many rows. – Tony Nov 29 '13 at 11:41