SQL 存储过程间歇性超时！

Stored procedures are timing out intermittently!

提问by JohnIdol

I have a number of stored procedures I call from code with ExecuteNonQuery.

我有许多从代码中调用的存储过程ExecuteNonQuery。

It was all good but 2 of my stored procedures started timing out intermittently today with:

一切都很好，但我的 2 个存储过程今天开始间歇性超时：

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.

如果我从管理工作室手动执行 sp，它仍然很好。

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.

反对 SP 的表正在运行它是巨大的 --> 15 Gigs。重新启动盒子 - 同样的问题，但这次也无法从 Management Studio 运行 sp。

Thanks!

谢谢！

采纳答案by JohnIdol

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 nonclusteredunlocked the situation.

一个包含 4500 万条记录的表上的聚集索引正在杀死我的 SQL 服务器 - 代码中的每个插入都会导致答案中描述的令人讨厌的超时。增加超时容限并不能解决我的可扩展性问题，所以我尝试使用索引并使主键上的聚集索引非聚集解锁了这种情况。

I'd appreciate comments on this to better understand how this fixed the problem.

我很感激对此发表评论，以更好地了解这是如何解决问题的。

回答by Sam Meldrum

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.

管理工作室对其运行的查询/命令设置无限超时。来自代码的数据库连接将有一个默认超时，您可以在命令对象上更改该超时。

回答by Michal Dymel

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'

要重新编译 proc，请转到管理工作室，打开程序进行修改并按 F5 或执行：EXEC sp_recompile 'proc_name'

回答by Marc Gravell

This can often relate to:

这通常与：

• bad query plans due to over-eager plan-reuse (parameter sniffing)
• different SET options - in particular ANSI_NULLS and CONCAT_NULL_YIELDS_NULL
• locking (you might have a higher isolation level)
• indexing needs to be rebuilt / stats updated / etc

• 由于过于急切的计划重用（参数嗅探）导致的错误查询计划
• 不同的 SET 选项 - 特别是 ANSI_NULLS 和 CONCAT_NULL_YIELDS_NULL
• 锁定（您可能有更高的隔离级别）
• 索引需要重建/统计更新/等

The SET options can lead to certain index types not being usable (indexes on persisted calculated columns, for example - including "promoted" xml/udf queries)

SET 选项可能导致某些索引类型不可用（例如，持久计算列上的索引 - 包括“提升”的 xml/udf 查询）

回答by Sam Saffron

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.

如果您必须诊断锁定问题，则需要使用 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

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

回答by HLGEM

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.

查看sp的执行计划，看是否能找到瓶颈。即使它之前运行正常，也可以对其进行调整以更有效地运行。

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 问题，这些问题直到累积报告开始在结果集中包含更多数据时才出现。不知道你的 sps 是做什么的，很难说这是否有可能，但值得你去调查。

回答by Andy Jones

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 Server 在返回给用户之前将无限期地等待。很可能设置了客户端超时属性。例如，您可以为 ADO 命令对象设置超时属性。

回答by AndySw

Get the SQL profiler on it, compare results between running it in Management studio and via your app.

获取 SQL 分析器，比较在 Management Studio 中和通过您的应用程序运行它的结果。

回答by Md. Sabbir Ahamed

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 +

就我而言，我只是重新组织了操作表的集群索引，超时问题解决了。另外，select * from table查询时间减少到2秒，在那里重新组织索引之前几乎30秒+