特定连接池上的存储过程超时 [英] Stored procedure timing out on particular connection pool

问题描述

我有一个存储过程，它在从我们的网站(通过网站连接池)调用时偶尔会超时.一旦超时，它就会一直锁定到超时状态，直到使用 drop/create 或 sp_recompile 从 Management Studio 会话重新编译该过程.

I have a stored procedure which occasionally times out when called from our website (through the website connection pool). Once it has timed out, it has always been locked into the time-out, until the procedure is recompiled using drop/create or sp_recompile from a Management Studio session.

当它超时时，使用 Management Studio 对同一过程使用相同的参数没有超时.

While it is timing out, there is no time-out using the same parameters for the same procedure using Management Studio.

通过 Management Studio 执行ALTER PROCEDURE"并(相当彻底地)更改过程的内部执行并没有清除超时 - 在运行完整的 sp_recompile 之前它不会清除.

Doing an "ALTER PROCEDURE" through Management Studio and (fairly drastically) changing the internal execution of the procedure did NOT clear the time out - it wouldn't clear until a full sp_recompile was run.

存储过程以OPTION (RECOMPILE)

该过程调用了两个函数，它们在产品的其余部分中无处不在.使用这些函数的其他程序(以类似方式)都可以正常工作，即使在相关程序超时期间也是如此.

The procedure calls two functions, which are used ubiquitously throughout the rest of the product. The other procedures which use these functions (in similar ways) all work, even during a period where the procedure in question is timing out.

如果有人可以就可能导致此超时的原因提供任何进一步的建议，我们将不胜感激.

If anyone can offer any further advice as to what could be causing this time out it would be greatly appreciated.

存储过程如下:

ALTER PROCEDURE [dbo].[sp_g_VentureDealsCountSizeByYear] (
  @DateFrom         AS DATETIME = NULL
  ,@DateTo          AS DATETIME = NULL
  ,@ProductRegion   AS INT = NULL
  ,@PortFirmID      AS INT = NULL
  ,@InvFirmID       AS INT = NULL
  ,@SpecFndID       AS INT = NULL
) AS BEGIN
-- Returns the stats used for Market Overview
DECLARE @IDs    AS IDLIST
INSERT INTO @IDs
    SELECT IDs
    FROM dbo.fn_VentureDealIDs(@DateFrom,@DateTo,@ProductRegion,@PortFirmID,@InvFirmID,@SpecFndID)

CREATE TABLE #DealSizes (VentureID INT, DealYear INT, DealQuarter INT, DealSize_USD DECIMAL(18,2))
INSERT INTO #DealSizes
    SELECT vDSQ.VentureID, vDSQ.DealYear, vDSQ.DealQuarter, vDSQ.DealSize_USD
    FROM dbo.fn_VentureDealsSizeAndQuarter(@IDs) vDSQ

SELECT 
    yrs.Years Heading
    ,COUNT(vDSQ.VentureID)          AS Num_Deals
    ,SUM(vDSQ.DealSize_USD) AS DealSize_USD  
FROM tblYears yrs
    LEFT OUTER JOIN #DealSizes vDSQ ON vDSQ.DealYear = yrs.Years
WHERE (
        ((@DateFrom IS NULL) AND (yrs.Years >= (SELECT MIN(DealYear) FROM #DealSizes))) -- If no minimum year has been passed through, take all years from the first year found to the present.
            OR
        ((@DateFrom IS NOT NULL) AND (yrs.Years >= DATEPART(YEAR,@DateFrom)))   -- If a minimum year has been passed through, take all years from that specified to the present.
    ) AND (
        ((@DateTo IS NULL) AND (yrs.Years <= (SELECT MAX(DealYear) FROM #DealSizes))) -- If no maximum year has been passed through, take all years up to the last year found.
            OR 
        ((@DateTo IS NOT NULL) AND (yrs.Years <= DATEPART(YEAR,@DateTo)))   -- If a maximum year has been passed through, take all years up to that year.
    )
GROUP BY yrs.Years
ORDER BY Heading DESC
OPTION (RECOMPILE)
END

推荐答案

如果你想在每次执行 SP 时重新编译它，你应该声明它重新编译；您的语法仅重新编译最后一次选择:

If you wanted to recompile SP each time it is executed, you should have declared it with recompile; your syntax recompiles last select only:

ALTER PROCEDURE [dbo].[sp_g_VentureDealsCountSizeByYear] (
  @DateFrom         AS DATETIME = NULL
  ,@DateTo          AS DATETIME = NULL
  ,@ProductRegion   AS INT = NULL
  ,@PortFirmID      AS INT = NULL
  ,@InvFirmID       AS INT = NULL
  ,@SpecFndID       AS INT = NULL
) WITH RECOMPILE

我不知道您程序的哪一部分导致了问题.您可以尝试注释掉选择部分以查看从表函数创建临时表是否会产生性能问题；如果没有，那么查询本身就是一个问题.您可以按如下方式重写过滤器:

I could not tell which part of your procedure causes problems. You might try commenting out select part to see if creating temp tables from table functions produces performance issue; if it does not, then the query itself is a problem. You might rewrite filter as following:

WHERE (@DateFrom IS NULL OR yrs.Years >= DATEPART(YEAR,@DateFrom))
  AND (@DateTo   IS NULL OR yrs.Years <= DATEPART(YEAR,@DateTo))

或者，也许更好，声明 startYear 和 endYear 变量，相应地设置它们并像这样更改位置:

Or, perhaps better, declare startYear and endYear variables, set them accordingly and change where like this:

declare @startYear int
set @startYear = isnull (year(@DateFrom), (SELECT MIN(DealYear) FROM #DealSizes))
declare @endYear int
set @endYear = isnull (year(@DateTo), (SELECT MAX(DealYear) FROM #DealSizes))
...
where yrs.Year between @startYear and @endYear

如果 WITH RECOMPILE 没有解决问题，并且删除最后一个查询也没有帮助，那么您需要检查用于收集数据的表函数.

If WITH RECOMPILE does not solve the problem, and removing last query does not help either, then you need to check table functions you use to gather data.

这篇关于特定连接池上的存储过程超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！