存储过程间歇性超时！
我有许多使用 ExecuteNonQuery 从代码中调用的存储过程。
一切都很好，但是我的 2 个存储过程今天开始间歇性超时：
超时已过。 超时时间 完成前已过去 操作或服务器不 回应。 该声明已 终止。
如果我从管理工作室手动执行 sp，它仍然很好。
我的数据库最近没有发生任何变化 - 我的命令超时是默认的。
有什么线索吗？
编辑
针对 SP 的表正在运行，它非常大 --> 15 场演出。 重新启动盒子 - 同样的问题，但这次也无法从 Management Studio 运行 sp。
谢谢！
I have a number of stored procedures I call from code with ExecuteNonQuery.
It was all good but 2 of my stored procedures started timing out intermittently today with:
Timeout expired. The timeout period
elapsed prior to completion of the
operation or the server is not
responding. The statement has been
terminated.
If I execute the sp manually from management studio it's still all good.
Nothing recently changed in my db - my command timeout is the default one.
Any clue?
EDIT
the table against the SPs are running it's huge --> 15 Gigs.
Rebooted the box - same issue but this time can't get the sp to run from Management Studio either.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
尝试重新编译这些程序。 我遇到过几次这样的问题，但没有找到问题的原因，但重新编译总是有帮助的。
编辑：
要重新编译 proc，请转到 Management Studio，打开要修改的过程并按 F5 或执行： EXEC sp_recompile 'proc_name'
Try to recompile these procedures. I've such problems few times and didn't find the cause of problem, but recompiling always helps.
EDIT:
To recompile proc, you go to management studio, open procedure to modify and hit F5 or execute: EXEC sp_recompile 'proc_name'
Management studio 对其运行的查询/命令设置无限超时。 代码中的数据库连接将有一个默认超时，您可以在 命令对象。
Management studio sets an infinite timeout on queries/commands it runs. Your database connection from code will have a default timeout which you can change on the command object.
这通常可能与以下原因有关：
SET选项可能导致某些索引类型不可用（索引例如，在持久计算列上 - 包括“升级的”xml/udf 查询）
This can often relate to:
The SET options can lead to certain index types not being usable (indexes on persisted calculated columns, for example - including "promoted" xml/udf queries)
您是否设置了命令超时？ 您的数据库中最近是否发生了某些更改导致此过程花费更长的时间？
如果必须诊断锁定问题，则需要使用 sp_lock 之类的工具。
你能分享一下你的其中一件道具的来源吗？
http://msdn.microsoft.com/ en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
Is you command timeout set? Has something in your db recently changed that is causing this proc to take longer?
If you are have to diagnose locking issues, you will need to use something like sp_lock.
Can you share the source of one of your procs?
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
好的 - 这就是我最终修复它的方法。
包含 4500 万条记录的表上的聚集索引正在杀死我的 SQL 服务器 - 每次代码插入都会导致答案中描述的令人讨厌的超时。 增加超时容忍度并不能解决我的可扩展性问题，因此我尝试使用索引并使主键上的聚集索引非聚集解锁了这种情况。
我希望对此发表评论，以便更好地理解这是如何解决问题的。
Ok - this is how I fixed it in the end.
A clustered index on a table with 45 million records was killing my SQL server - every insert from code was resulting in the nasty timeouts described in the answer. Increasing the timeout tolerance wasn't gonna solve my scalability issues so I played around with indexes and making the clustered index on the primary key nonclustered unlocked the situation.
I'd appreciate comments on this to better understand how this fixed the problem.
您可能需要更新数据库的统计信息。 最近表上的索引是否发生了变化？
检查sp的执行计划，看能否找到瓶颈。 即使以前运行良好，也可能可以对其进行调整以提高运行效率。
另外你要返回多少数据？ 过去，我们遇到过 SQL 设计不当的问题，直到累积报告开始在结果集中包含更多数据时才出现这些问题。 不知道你的 sps 做什么，很难说这是否有可能，但值得一提的是你需要进行调查。
You might need to update statistics on the database. Also has indexing on the table changed recently?
Check the execution plan of the sp to see if you can find the bottleneck. Even if it ran ok before, it can probably be tuned to run more efficiently.
Also how much data are you returning? We have had issues with poorly designed SQL in the past that didn't show up until the cumulative report starting having more data in the result set. Not knowing wht your sps do, it is hard to say if this is a possibilty, but it is worth mentioning for you to investigate.
SQL Server 在返回给用户之前将无限期地等待。 很可能设置了客户端超时属性。 例如，您可以为 ADO 命令对象设置 超时属性。
SQL Server will wait indefinitely before returning to the user. More than likely there was a client side timeout property set. For example you can set a timeout property for the ADO command object.
获取 SQL 探查器，比较在 Management studio 中运行和通过应用程序运行的结果。
Get the SQL profiler on it, compare results between running it in Management studio and via your app.
就我而言，我只是重新组织了操作表的簇索引，超时问题就解决了。 此外，
select * from table查询时间减少到 2 秒，而重组索引之前几乎是 30 秒+In my case I just reorganized my cluster index of the operation table, the timeout problem resolved. Also the
select * from tablequery time reduced to 2 sec, where before reorganize index was almost 30 sec +