CODEWITHSUNDEEP
sqlsql-servert-sql
Thakur
Thakur

Reputation: 2020

Simple update query is taking too long

I have a table CurrentStatus in my database (subscription database in a merge replication) Columns are StatusID {Primary Key + Clustered Index}, StatusName, StatusDate, UserID,CreatedDate, ModifiedDate, ModifiedBy, AllowDelete,AllowUpdate

CurrentStatus table as 26000 rows

updates and deletes on this table are suddenly taking too much time say 1 min 30 sec to even 5 min.

Below query is taking over a minute to execute.

update StatusMaster set StatusName='OK' where StatusID = 22

There were previously 5 indexes on table (even then the query used to execute fast.) Now as the update/delete queries are not executing, i have deleted all indexes and kept only two indexes 1) Clustered Index on StatusID 2) Replication index on rowguid column which is an unique nonclustered index created by replication automatically.

Whn i take backup and restore the database, the queries on same table run fine.

One more thing is that i have complex query running every 2 mins from around 20 machines on server.

What is causing this queries to consume so much time to execute?

Click here for Execution plan

Upvotes: 7

Views: 28972

Answers (4)

Jan Macháček
Jan Macháček

Reputation: 621

I know it's an old question, but i may help someone. We had similar problem right now, found out there was trigger "after update" causing simple update to take too long.

Upvotes: 0

Arvo
Arvo

Reputation: 10570

Last time when some simple queries took abnormal amount of time [in our little company], this was caused by RAID array corruption in [fortunately non-production] SQL server.

Upvotes: 0

Sean Lane Fuller
Sean Lane Fuller

Reputation: 31

After a lot of inserts, deletes, updates, etc., you table can get fragmented and your indexes may not be optimized. I would suggest that you re-index and update statistics.

When you take a backup and restore it, I believe you are accomplishing the same thing as the re-index and update of statistics would do.

You should probably schedule maintenance like this on a recurring basis to maintain performance.

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

I recommend that you update your statistics via the UPDATE STATISTICS command, e.g.:

UPDATE STATISTICS StatusMaster

Upvotes: 8

Related Questions