SQL 如何修复 System.Data.SqlClient.SqlException：超时已过期

How to fix System.Data.SqlClient.SqlException: Timeout expired

提问by CRM Guy

Hi When I am trying to access my application by URL I am getting the error screen containing the below error. Previously I get it fixed by using dbcc commands in backend..bt now this is not working..

嗨，当我尝试通过 URL 访问我的应用程序时，我收到包含以下错误的错误屏幕。以前我通过在后端使用 dbcc 命令来修复它..bt 现在这不起作用..

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

超时已过。操作完成前超时时间已过或服务器未响应。说明：在执行当前 Web 请求期间发生未处理的异常。请查看堆栈跟踪以获取有关错误及其在代码中的来源的更多信息。

Exception Details: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

异常详细信息：System.Data.SqlClient.SqlException：超时已过期。操作完成前超时时间已过或服务器未响应。

Source Error:

源错误：

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

执行当前 Web 请求期间生成了未处理的异常。可以使用下面的异常堆栈跟踪来识别有关异常来源和位置的信息。

here is my source code for the stored proc I am calling from my .NET application.

这是我从 .NET 应用程序调用的存储过程的源代码。

      USE [TCO]
GO
      ALTER PROC [dbo].[SP_TCOV3] (@year INT) AS
DECLARE @rpt_year INT;
IF @year > 2011 
BEGIN
SET @rpt_year = 2011;
END
ELSE
BEGIN   
SET @rpt_year = @year;
END
DECLARE @From_Date DATETIME='01-01-2012'  -- mm/dd/yyyy
DECLARE @End_Date DATETIME=(SELECT TOP 1 DATEADD(dd, -DAY(DATEADD(m,1,dbo.ManpowerCost.payment_date)), DATEADD(m,1,dbo.ManpowerCost.payment_date)) as value
FROM dbo.ManpowerCost order by payment_date desc)  

(
SELECT
        allApps.[Application Name],          
        '$'+(CAST((CAST(allApps.[BAM Staff Support] AS DECIMAL(10,2))) as varchar(50)))AS [BAM Staff Support],
        '$'+(CAST((CAST(allApps.[BAM Non-Shell Support] AS DECIMAL(10,2))) as varchar(50)))AS [BAM Non-Shell Support] ,
        '$'+(CAST((CAST(allApps.[BSM DBA Support] AS DECIMAL(10,2))) as varchar(50)))AS [BSM DBA Support] ,
        '$'+(CAST((CAST(allApps.[Middleware Support] AS DECIMAL(10,2))) as varchar(50)))  AS [BSM Middleware Support],
        '$'+(CAST((CAST(allApps.[IRM Logical Access] AS DECIMAL(10,2))) as varchar(50)))AS [IRM Logical Access] ,
        '$'+(CAST((CAST(allApps.[Application Licensing and Maintenance] AS DECIMAL(10,2))) as varchar(50))) AS [Application License and Maintenance],
        '$'+(CAST((CAST(allApps.[Middleware Licensing and Maintenance] AS DECIMAL(10,2))) as varchar(50)))as [Middleware License and Maintenance],  
        '$'+(CAST((CAST(allApps.[Database Licensing and T-System DBA Maintenance] AS DECIMAL(10,2))) as varchar(50))) AS [TS DBA Maintenance],
        '$'+(CAST((CAST(allApps.[Hosting and Storage] AS DECIMAL(10,2))) as varchar(50)))AS [Hosting and Storage]  ,
        '$'+(CAST((CAST(allApps.[Telecom Connection Charge] AS DECIMAL(10,2))) as varchar(50))) AS [Telecom Connection Charges],
        '$'+(CAST((CAST(allApps.[Total Application TCO] AS DECIMAL(10,2))) as varchar(50)))AS [Total Application TCO] 
FROM
        --dbo.FN_TCOV3(@rpt_year)AS allApps 
        dbo.FN_TCOV3_Report(@From_Date,@End_Date)AS allApps 

)ORDER BY allApps.[Application Name]

The same proc is working if I do not use start date and end date parameter that I have used above and call dbo.FN_TCOV3(@rpt_year)AS allAppsinstead of dbo.FN_TCOV3_Report(@From_Date,@End_Date)AS allApps, the only diff. b/w these two function is one takes only a year as parameter and the other takes 2 parameter namely start and end date.

如果我不使用上面使用的开始日期和结束日期参数并调用dbo.FN_TCOV3(@rpt_year)AS allApps而不是 dbo.FN_TCOV3_Report(@From_Date,@End_Date)AS allApps，则相同的过程正在运行，这是唯一的差异. b/w 这两个函数一个只需要一年作为参数，另一个需要两个参数，即开始和结束日期。

I need to use both the parameters , Pls assist.

我需要使用这两个参数，请帮忙。

回答by jbl

If your query reallyneeds to run for a long time, you can extend the TimeOut period by setting the CommandTimeoutproperty of your SqlCommandobject ( myCom.CommandTimeout = 300;for a 5 minutes timeout ; also set the corresponding page Server TimeOut accordingly)

如果你的查询确实需要长时间运行，你可以通过设置CommandTimeout你的SqlCommand对象的属性来延长myCom.CommandTimeout = 300;超时时间（5分钟超时；也相应地设置相应的页面Server TimeOut）

That said, if you don't know why your query is running that long, you have toinvestigate and fix it.

也就是说，如果您不知道为什么您的查询运行那么长时间，您必须调查并修复它。

Search for :

搜索 ：

• missing indexes
• locks
• inefficient algorithms

• 缺少索引
• 锁
• 低效算法

回答by Howard Shlom

In Sql Server Management Studio, highlight the query and click Display Estimated Execution Plan. Index Seek is good. If there is an index scan, create an index on the column(s) on your table. This fixed the problem for me.

在 Sql Server Management Studio 中，突出显示查询并单击显示估计的执行计划。索引搜索很好。如果有索引扫描，请在表的列上创建索引。这为我解决了问题。