SSRS调用存储过程超时排查：SSMS执行正常但SSRS超时

我有一个存储过程sp_SalesSnapshot，在SSMS中执行仅需11秒即可生成19行数据集，但添加至SSRS后，即便将超时时间设置为10分钟仍会触发超时。我制作过诸多复杂报表，这是首次遇到此类问题。该存储过程使用3个临时表，最终仅输出一个数据集，代码如下：

USE [ReportServer]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_SalesSnapshot]
    @year AS int,
    @period AS varchar(2)
AS
BEGIN
    DECLARE @date AS DATE 

    SET @date = CONVERT(DATE, CAST(@Year - 2 AS VARCHAR(4)) + '-01' + '-01') --concat('01-01-', @year - 1)

    SELECT DISTINCT 
        JOBNO, MATLIST 
    INTO
        #jqh 
    FROM
        FDCLIENT.dbo.JOBQUOTEHEADER (NOLOCK) 

    SELECT 
        inhouse.dbo.removeNumbers(slc.region) AS region,
        SUM(opsd.val) val,
        sli.slyear,
        sli.period,
        CASE
            WHEN opsd.warehouse IN ('12', '13') 
                THEN 'Panel'
            WHEN (UPPER(#jqh.MATLIST) LIKE '%FIRE%')
                 OR (UPPER(#jqh.matlist) LIKE '%FD%')
                 OR (opsd.warehouse = '21' AND stk.alpha LIKE 'CF%') 
                THEN 'Fire'
            WHEN opsd.warehouse = '21' 
                THEN 'Composite'
            ELSE 'Other'
        END AS panelcompfire
    INTO 
        #temp
    FROM 
        live.scheme.opsadetm (nolock) opsd
    INNER JOIN
        live.scheme.slcustm slc (nolock) ON opsd.customer = slc.customer
    INNER JOIN 
        live.scheme.slitemm (nolock) sli ON opsd.dated >= @date
                                         AND opsd.invoice = sli.item_no
    INNER JOIN
        live.scheme.stockm stk (nolock) ON stk.warehouse = opsd.warehouse
                                        AND stk.product = opsd.product
    LEFT OUTER JOIN 
        #jqh (nolock) ON sli.refernce = #jqh.JOBNO COLLATE database_default
    WHERE 
        (sli.slyear = @year)
        AND opsd.val <> 0
    GROUP BY
        inhouse.dbo.removeNumbers(slc.region),
        sli.slyear, sli.period,
        CASE
            WHEN opsd.warehouse IN ('12', '13') 
                THEN 'Panel'
            WHEN (UPPER(#jqh.MATLIST) LIKE '%FIRE%')
                  OR (UPPER(#jqh.matlist) LIKE '%FD%')
                  OR (opsd.warehouse = '21' AND stk.alpha LIKE 'CF%') 
                THEN 'Fire'
            WHEN opsd.warehouse = '21' 
                THEN 'Composite'
            ELSE 'Other'
        END 

SELECT 
    region,
    panelcompfire AS product,
    SUM(CASE WHEN period = '01' THEN val ELSE 0 END) AS [01],
    SUM(CASE WHEN period = '02' THEN val ELSE 0 END) AS [02],
    SUM(CASE WHEN period = '03' THEN val ELSE 0 END) AS [03],
    SUM(CASE WHEN period = '04' THEN val ELSE 0 END) AS [04],
    SUM(CASE WHEN period = '05' THEN val ELSE 0 END) AS [05],
    SUM(CASE WHEN period = '06' THEN val ELSE 0 END) AS [06],
    SUM(CASE WHEN period = '07' THEN val ELSE 0 END) AS [07],
    SUM(CASE WHEN period = '08' THEN val ELSE 0 END) AS [08],
    SUM(CASE WHEN period = '09' THEN val ELSE 0 END) AS [09],
    SUM(CASE WHEN period = '10' THEN val ELSE 0 END) AS [10],
    SUM(CASE WHEN period = '11' THEN val ELSE 0 END) AS [11],
    SUM(CASE WHEN period = '12' THEN val ELSE 0 END) AS [12]
into #pivot
FROM #Temp (nolock)
GROUP BY 
        region, panelcompfire
ORDER BY 
            region, panelcompfire;


select 
    #pivot.product,
    #pivot.region,
    ltrim(rtrim(a.Representative)) as Representative,
    case @period
        when '01' then [01]
        when '02' then [02]
        when '03' then [03]
        when '04' then [04]
        when '05' then [05]
        when '06' then [06]
        when '07' then [07]
        when '08' then [08]
        when '09' then [09]
        when '10' then [10]
        when '11' then [11]
        when '12' then [12]
    end as monthSales,
    case @period
        when '01' then bud.APRIL
        when '02' then bud.MAY
        when '03' then bud.JUNE
        when '04' then bud.JULY
        when '05' then bud.AUGUST
        when '06' then bud.SEPTEMBER
        when '07' then bud.OCTOBER
        when '08' then bud.NOVEMBER
        when '09' then bud.DECEMBER
        when '10' then bud.JANUARY
        when '11' then bud.FEBRUARY
        when '12' then bud.MARCH
    end as monthbudget,
    case 
        when @period in ('01','02','03') then [01]  + [02]  + [03]
        when @period in ('04','05','06') then [04]  + [05]  + [06]
        when @period in ('07','08','09') then [07]  + [08]  + [09]
        when @period in ('10','11','12') then [10]  + [11]  + [12]
    end as qtrSales,
    case 
        when @period in ('01','02','03') then bud.APRIL     + bud.MAY       + bud.JUNE
        when @period in ('04','05','06') then bud.JULY      + bud.AUGUST    + bud.SEPTEMBER
        when @period in ('07','08','09') then bud.OCTOBER   + bud.NOVEMBER  + bud.DECEMBER
        when @period in ('10','11','12') then bud.JANUARY   + bud.FEBRUARY  + bud.MARCH
    end as qtrbudget,
    [01]    + [02]  + [03]  +
    [04]    + [05]  + [06]  +
    [07]    + [08]  + [09]  +
    [10]    + [11]  + [12]  
    as YTDSales,
    bud.APRIL       + bud.MAY       + bud.JUNE      +
    bud.JULY        + bud.AUGUST    + bud.SEPTEMBER +
    bud.OCTOBER     + bud.NOVEMBER  + bud.DECEMBER  +
    bud.JANUARY     + bud.FEBRUARY  + bud.MARCH     as YTDBudget 
from #pivot (nolock)
    inner join [ReportServer].[dbo].[budget_fiscal] bud (nolock)
        on  #pivot.region = bud.REGION collate database_default
           and #pivot.product = bud.PRODUCT collate database_default
           and bud.[year] = @year
    inner join [ReportServer].[dbo].[cpio_area_fy24] a (nolock)
        on #pivot.region = a.Area collate database_default
order by #pivot.region, #pivot.product


-- Drop the temporary table
drop table #jqh
DROP TABLE #Temp;
DROP TABLE #pivot;

END

排查与解决方案 #

1. 统一执行上下文设置 #

SSRS与SSMS的默认ANSI设置可能存在差异，直接影响执行计划生成。在存储过程开头添加以下语句：

SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;

同时在SSMS中模拟SSRS的调用方式执行存储过程，确认参数类型与传递方式完全一致。

2. 解决参数嗅探问题 #

存储过程可能因参数嗅探生成低效执行计划，可通过以下方式强制重新生成计划：

• 在存储过程的最终查询末尾添加OPTION (RECOMPILE)
• 或使用ALTER PROCEDURE [dbo].[sp_SalesSnapshot] WITH RECOMPILE重建存储过程
• 清理缓存执行计划：DBCC FREEPROCCACHE (OBJECT_ID('sp_SalesSnapshot'));

3. 优化临时表索引 #

临时表缺少索引会导致大表关联时性能骤降，在临时表创建后添加针对性索引：

-- #jqh创建后添加索引
CREATE NONCLUSTERED INDEX IX_jqh_JOBNO ON #jqh(JOBNO);

-- #temp创建后添加索引
CREATE NONCLUSTERED INDEX IX_temp_region_period ON #temp(region, period, panelcompfire);

4. 优化函数调用与分组逻辑 #

避免在GROUP BY中重复执行inhouse.dbo.removeNumbers函数，可提前计算结果存入临时表：

-- 先将region处理结果存入临时表
SELECT inhouse.dbo.removeNumbers(slc.region) AS region, slc.customer
INTO #region_map
FROM live.scheme.slcustm slc (nolock);

-- 后续关联时直接使用#region_map的region字段

5. 检查SSRS数据源配置 #

• 确认SSRS数据源的连接字符串、用户权限与SSMS一致
• 同时设置数据源超时与报表超时，避免单方面设置遗漏

内容的提问来源于stack exchange，提问作者Sam Towle