存储过程在SQL Server 2008中挂起 [英] Stored Procedure is hang in SQL Server 2008

问题描述

存储过程在过去两年中在SQL Server 2008上运行，没有任何问题。突然，它上周挂了3次。我们检查没有阻塞SQL，连接是活动的。低CPU和低服务器的内存使用情况。
服务器每个月重启一次。

The stored procedure is running on SQL server 2008 in the past 2 years without any issues. Suddenly, it hang 3 times last week. We checked no blocking SQL, the connection is active. Low CPU & memory usages of the server. The server is reboot every month.

在这个存储过程中，它执行一些其他存储过程，创建一些本地temp表（例如#tblStock）和表变量（例如@tblStock）和 进行一些计算。

In this stored procedure, it executes some other stored procedures, creates some local temp tables (e.g #tblStock) and table variables (e.g @tblStock) and do some calculations.

一旦存储过程挂起并且我们终止了这个工作。然后重新运行就可以了。

Once the stored procedure hang and we kill the job. Then rerun is fine.

推荐答案

请查看SPROC当前处理的数据大小，正如你所说，它是在2年前开发的，并不确定它当时和现在处理的数据。请查看SPROC成本，优化它并重新索引记录。这些将有助于您开始使用
。  

Please review the Size of the data the SPROC is currently processing, as you are saying it was developed 2 years back and not sure about the data it process then and now. Please review the SPROC cost, optimize it and re-index the records. These will help you to get started with.  

这篇关于存储过程在SQL Server 2008中挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！