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(合并复制中的订阅数据库)列是StatusID {Primary Key + Clustered Index},StatusName,StatusDate,UserID,CreatedDate,ModifiedDate,ModifiedBy,AllowDelete,AllowUpdate
CurrentStatus table as 26000 rows
CurrentStatus表为26000行
updates and deletes on this table are suddenly taking too much time say 1 min 30 sec to even 5 min.
此表上的更新和删除突然花费太多时间,例如1分30秒甚至5分钟。
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.
表上有以前的5个索引(即使后来查询用于快速执行。)现在由于更新/删除查询没有执行,我已经删除了所有索引并且只保留了两个索引1)StatusID上的聚簇索引2)复制索引rowguid列,它是由复制自动创建的唯一非聚簇索引。
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.
还有一件事是我从服务器上的大约20台机器每2分钟运行一次复杂的查询。
What is causing this queries to consume so much time to execute?
是什么导致此查询消耗这么多时间来执行?
单击此处查看执行计划
3 个解决方案
#1
6
I recommend that you update your statistics via the UPDATE STATISTICS command, e.g.:
我建议您通过UPDATE STATISTICS命令更新统计信息,例如:
UPDATE STATISTICS StatusMaster
#2
2
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.
您应该定期安排这样的维护以保持性能。
#3
0
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.
上次当一些简单的查询花了不正常的时间[在我们的小公司]时,这是由于[幸运的是非生产] SQL服务器中的RAID阵列损坏引起的。
#1
6
I recommend that you update your statistics via the UPDATE STATISTICS command, e.g.:
我建议您通过UPDATE STATISTICS命令更新统计信息,例如:
UPDATE STATISTICS StatusMaster
#2
2
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.
您应该定期安排这样的维护以保持性能。
#3
0
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.
上次当一些简单的查询花了不正常的时间[在我们的小公司]时,这是由于[幸运的是非生产] SQL服务器中的RAID阵列损坏引起的。