为什么 SQL 在执行更新时会非常慢？
突然间，对几个表进行更新的速度比以前慢了 10 倍。有哪些好的建议可以确定根本原因并进行优化？是否对某些列建立索引导致更新缓慢？还有其他建议吗？
我想比猜测更重要的是有助于识别根本原因或绩效指标。
Fluent NHibernate 中是否有任何内容可以帮助您确定性能问题的根本原因？
Suddenly doing updates into a few tables have gotten 10 times slower than they used to be. What are some good recommendations to determine root cause and optimization? Could it be that indexing certain columns are causing updates to be slow? Any other recommendations?
I guess more important than guesses would be help on the process of identifying the root cause or metrics around performance.
Is there anything in Fluent NHibernate that you can use to help identify the root cause of performance issues?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
首先尝试更新您的统计数据。
然后，检查您的索引，并确保您只有您需要的内容。额外的索引肯定会减慢插入速度。
然后，尝试重建索引。
在不知道架构、查询或数据量的情况下，很难说更多。
First try updating your statistics.
Then, look into your indexing, and make sure you have only what you need. Additional indexes can most definitely slow down inserts.
Then, try rebuilding the indexes.
Without knowing the schema, query, or amount of data, it is hard to say more than that.
与以往一样，在 SQL 服务器上运行 SQL 探查器将使您清楚地了解瓶颈到底在哪里。
And as ever, running up SQL profiler on your SQL server will give you a good idea of exactly at what point your bottleneck is.
1- 首先检查表是否没有锁
2- 最近实现的触发器
3- 数据库刷新可能需要重建索引。
4- 一些预定义作业正在进行中。
5-任何未知的原因
使用一些查询分析工具来查看实际发生的情况。
1- First check if the table does not have locks
2- Recently implemented triggers
3- Database refresh may need index rebuilt.
4- Some predefind jobs is in process.
5- ANY UNKNOWN REASONS
Use some query analzer tools to see what is actually happening.
原因...
索引太多
记录数量不断增加
更新查询的where子句的索引协变不好
由于多次更新而导致的锁定问题（表锁）
硬件问题（磁盘控制器） 、网络等）
Causes...
Too many indexes
Growing number of records
Bad index covarage of the where-clause of the update query
Locking issues due to multiple updates (table-lock)
Hardware issues (disk controller, network, etc.)
值得检查数据文件上的增长设置，如果您正在进行大量插入，这会强制常规文件增长，则可能会表现为速度减慢。
Its worth checking your growth settings on the data files, if you're doing a lot of inserts which forces regular file grows it can manifest as slowdown.
另请检查您是否以无序方式将数据插入聚集索引。
聚集索引按字段顺序存储数据，这意味着如果它是唯一标识符，您将在每次插入时重新排列数据。
聚集索引更适合顺序数据。
Also check if you're inserting data into a clustered index in an out-of-order way.
A clustered index stored the data in order of the field, which means if it's a uniqueidentifer, you'll be re-arranging the data on each insert.
Clustered indexes work better with sequential data.