Geek Answers Handbook

Simple update request takes too long

I have a table CurrentStatusin my database (merge replication subscription database) ColumnsStatusID {Primary Key + Clustered Index}, StatusName, StatusDate, UserID,CreatedDate, ModifiedDate, ModifiedBy, AllowDelete,AllowUpdate

CurrentStatus table as 26,000 rows

updates and deletes in this table suddenly take too much time, say, from 1 minute 30 seconds to 5 minutes.

The following is a request to execute a minute.

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

There were 5 indexes in the table earlier (even then the query was executed quickly). Now that update / deletion requests fail, I deleted all indexes and saved only two indexes 1) Cluster pointer to StatusID 2) Replication index in rowguid column, which is a unique non-clustered index created automatically by replication.

As I backup and restore the database, the queries in the same table work fine.

Another thing is that I have a complex query running every 2 minutes from about 20 computers on the server.

What makes these requests consume so much time to complete?

Click here to complete the plan.

+5
source share
3 answers

I recommend that you update your statistics with the UPDATE STATISTICS command , for example:

UPDATE STATISTICS StatusMaster
+6
source

After many inserts, deletions, updates, etc. the table can be fragmented and your indexes cannot be optimized. I would advise you to reindex and update statistics.

, , , .

, , , , .

+2

Last time, when some simple queries took an abnormal amount of time [in our small company], this was caused by a damaged RAID array in the [fortunately unproductive] SQL server.

0
source

More articles:


All Articles