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:
这一切都很好但是我的存储过程中的两个今天间歇性地开始计时：
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。重新启动该框 - 同样的问题，但这次无法让sp从Management Studio运行。
Thanks!
谢谢！
8 个解决方案
#1
7
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.
Management studio在运行的查询/命令上设置无限超时。来自代码的数据库连接将具有默认超时，您可以在命令对象上更改该超时。
#2
6
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'
#3
5
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
- 不同的SET选项 - 特别是ANSI_NULLS和CONCAT_NULL_YIELDS_NULL
- locking (you might have a higher isolation level)
- 锁定（您可能具有更高的隔离级别）
- indexing needs to be rebuilt / stats updated / etc
- 索引需要重建/统计更新/等
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查询）
#4
3
Is you command timeout set? Has something in your db recently changed that is causing this proc to take longer?
你命令超时设置？你的数据库中最近有什么东西改变了导致这个proc需要更长的时间？
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
#5
2
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.
一个包含4500万条记录的表上的聚簇索引正在杀死我的SQL服务器 - 代码中的每个插入都导致了答案中描述的令人讨厌的超时。增加超时容差并不能解决我的可扩展性问题，所以我玩了索引并使主键非聚簇上的聚簇索引解锁了这种情况。
I'd appreciate comments on this to better understand how this fixed the problem.
我很感激这方面的评论，以便更好地理解这是如何解决问题的。
#6
1
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做什么，很难说这是否可能，但值得一提的是你要调查。
#7
1
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命令对象设置超时属性。
#8
0
Get the SQL profiler on it, compare results between running it in Management studio and via your app.
获取SQL分析器，比较在Management Studio中运行它和通过您的应用程序之间的结果。
#1
7
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.
Management studio在运行的查询/命令上设置无限超时。来自代码的数据库连接将具有默认超时，您可以在命令对象上更改该超时。
#2
6
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'
#3
5
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
- 不同的SET选项 - 特别是ANSI_NULLS和CONCAT_NULL_YIELDS_NULL
- locking (you might have a higher isolation level)
- 锁定（您可能具有更高的隔离级别）
- indexing needs to be rebuilt / stats updated / etc
- 索引需要重建/统计更新/等
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查询）
#4
3
Is you command timeout set? Has something in your db recently changed that is causing this proc to take longer?
你命令超时设置？你的数据库中最近有什么东西改变了导致这个proc需要更长的时间？
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
#5
2
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.
一个包含4500万条记录的表上的聚簇索引正在杀死我的SQL服务器 - 代码中的每个插入都导致了答案中描述的令人讨厌的超时。增加超时容差并不能解决我的可扩展性问题，所以我玩了索引并使主键非聚簇上的聚簇索引解锁了这种情况。
I'd appreciate comments on this to better understand how this fixed the problem.
我很感激这方面的评论，以便更好地理解这是如何解决问题的。
#6
1
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做什么，很难说这是否可能，但值得一提的是你要调查。
#7
1
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命令对象设置超时属性。
#8
0
Get the SQL profiler on it, compare results between running it in Management studio and via your app.
获取SQL分析器，比较在Management Studio中运行它和通过您的应用程序之间的结果。