SQL Server 2008 更新语句挂起
在我的 SQL Server 2008 Enterprise 中，我有一个包含多个表的数据库：
Departments- 几行Persons- 300 多行Permissions- 数千行行数
Persons 的主键是 PersonId 与 DepartmentId（它本身就是 FK）的组合。
Permissions 表有一个 FK，具有与 Persons 表的 PK 级联功能（删除、更新）。
当我执行此语句时：
UPDATE Persons SET PersonId = PersonId + 1 WHERE DepartmentId = 789
使用 SSMS、ADO、ADO.NET，我要么超时，要么花费太长的时间（超过 10 分钟）。
我尝试使用 ADO.NET 逐一更新行，并发现了这种奇怪的行为：当我到达特定行时，更新开始挂起。我尝试重新启动、删除并创建、备份和恢复、删除行并重新插入，但尚未解决。
预先感谢您的任何答复。 母鸡
In my SQL Server 2008 Enterprise I have a database that contains several tables:
Departments- few rowsPersons- a 300+- rowsPermissions- thousands of rows
The primary key of Persons is the PersonId combined with the DepartmentId (which is a FK itself).
The Permissions table has a FK with cascade functionality (delete, update) to the PK of the Persons table.
When I execute this statement:
UPDATE Persons SET PersonId = PersonId + 1 WHERE DepartmentId = 789
using SSMS, ADO, ADO.NET I either get a timeout or it takes way too long (more than 10 minutes).
I tried updating the rows one by one with ADO.NET and found out this strange behaviour: when I get to a specific rows the update starts to hang. I tried restarting, drop and create, backup and restore, deleting the rows and reinserting but haven't solved it yet.
Thanks in advance for any answers.
Hen
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
几乎所有过度阻塞的实例都可以通过使用适当的索引来修复（或至少管理）。
此外，您应该确保重建索引并更新统计信息（至少）。
您的聚集索引是否在复合主键（PersonId，DepartmentId）上定义？
有多种方法可以识别哪个进程正在阻塞。其中之一是
sp_who2。在BlkBy列中查找条目。另请参阅：如何查找被锁定的表名（特定于任何事务）
Almost all instances of excessive blocking can be fixed (or at least managed) by using the appropriate indexes.
In addition, you should ensure that your indexes are rebuilt and your statistics are updated (at a minimum).
Is your clustered index defined on the composite primary key (PersonId, DepartmentId) ?
There are several ways to identify which process is blocking. One of which is
sp_who2. Look for an entry in theBlkBycolumn.Also see: How To Find The Table Names Which Are Locked (Specific to any transaction)
如果已添加索引（当然是手动添加，它们不会自动添加 FK），那么另一个主要罪魁祸首是触发器触发。
检查相关表上的触发器。
If indexes have been added (manually of course, they are not automatically added with FKs) then the other major culprit is triggers firing.
Check for triggers on the tables involved.
已经过去很长时间了，但这里是已发现的问题：
依赖项之一（通过 FK 依赖项）由视图引用，该视图是架构绑定的。该视图必须保持架构绑定，因为它正在被全文索引。
解决方案是删除并重新创建视图和 fts 索引。
（任何其他想法将不胜感激）
It has been a long time, but here's the identified problem:
one of the dependencies (via FK dependency) is referenced by a view, which is schema bound. The view must stay schema bound because it is being full text indexed.
The solution is dropping and recreating the view and fts index.
(Any other ideas will be appreciated)
运行 sp_who2 并查看发生了哪些阻止（如果有）。如果没有，请像 gdn 建议的那样考虑查看是否有任何可能导致完成触发时间过长的触发器。
Run sp_who2 and see what blocks are occurring if any. If there are none consider like gdn suggested looking if you have any triggers that may be causing excessive amounts of time to finsih firing.
重新启动数据库服务器后，尝试使用 SQL Server 管理工具执行相同的查询。如果它在那里工作正常，那么您可能会遇到一些锁定问题。
也尝试在 DepartmentId 上创建索引。
编辑：
如果是性能问题，除了 Persons DepartmentId 索引之外，还可以考虑在
PermissionsPersonId上创建一个索引。Try the same query with the SQL Server Management tools just after restarting the database server. If its working fine there then you probably have some locking issues.
Try creating an Index on DepartmentId as well.
EDIT:
If its a performance problem consider creating an Index on the
PermissionsPersonIdin addition to the Persons DepartmentId index.