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?
Advertisement
Answer
I recommend that you update your statistics via the UPDATE STATISTICS command, e.g.:
UPDATE STATISTICS StatusMaster