SQL Server 2012 存储过程执行时间差异很大

Question

我有一个简单的存储过程，它只接受 2 个参数;第一个总是提供的，第二个总是通过观察为 null，并且只是为了不中断来自应用程序的调用。 有一个计划的 SQL 代理任务，用于存储存储过程执行统计信息，然后每周清除缓存的计划。 还有夜间计划的作业，使用默认参数使用 Ola Hallegren 的维护过程重建或重新组织索引。

最近，在

清除缓存后，存储过程执行时间有时会从正常的 2-15 毫秒跳到 120,000 毫秒。 在存储过程上调用 sp_recompile 会将执行时间恢复正常。 所有等待时间都是根据SolarWinds DPA（以前称为Ignite）的CPU/内存。

运行探查器，我可以复制 Web 应用发出的执行调用，将其粘贴到笔记本电脑上运行的 SSMS 中，并在正常 MS 范围内获取执行时间，而根据探查器，应用的持续时间要高得多。 从应用程序需要 2 分钟的呼叫将在从应用程序完成呼叫后的几秒钟内在我的笔记本电脑上<10 毫秒内完成。

我希望两个执行使用相同的缓存计划并产生相同的性能，但显然情况并非如此，而且由于我从未见过第二个参数的非空值，因此我认为这不是一个奇怪的调用，包括它可能缓存一个糟糕的计划。

下面是整个存储过程代码。 它的唯一用途是返回一个 3 行结果集，指示特定商城是否具有类型 1 到 3 的任何当前内容。

 dbo.usp_s_mall_has_rsc_content
    @mall_id        INT,
    @base_tenant_id INT = NULL
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT  1 AS showcase_item_type_id,
        IIF(EXISTS( SELECT  *
                    FROM    dbo.content_stream_owner AS cso
                            JOIN dbo.content_stream_data AS csd ON csd.content_stream_data_id = cso.content_stream_data_id
                            JOIN dbo.content_stream AS cs ON cs.content_stream_id = csd.content_stream_id
                            JOIN dbo.content_stream_logins AS csl
                                ON (csl.mall_id = cso.mall_id OR csl.base_tenant_id = cso.base_tenant_id)
                    WHERE   cso.mall_id = @mall_id
                            AND (@base_tenant_id IS NULL OR cso.base_tenant_id = @base_tenant_id)
                            AND csd.content_stream_id = 1
                            AND csd.end_dt > SYSDATETIME()
                            AND csd.hidden_flags = 0
                            AND cs.is_active = 1
                            AND csl.is_active = 1), 1, 0) AS has_content
UNION
SELECT  2 , IIF(EXISTS( SELECT  *
                    FROM    dbo.content_stream_owner AS cso
                            JOIN dbo.content_stream_data AS csd ON csd.content_stream_data_id = cso.content_stream_data_id
                            JOIN dbo.content_stream AS cs ON cs.content_stream_id = csd.content_stream_id
                            JOIN dbo.content_stream_logins AS csl
                                ON (csl.mall_id = cso.mall_id OR csl.base_tenant_id = cso.base_tenant_id)
                    WHERE   cso.mall_id = @mall_id
                            AND (@base_tenant_id IS NULL OR cso.base_tenant_id = @base_tenant_id)
                            AND csd.content_stream_id = 2
                            AND csd.end_dt > SYSDATETIME()
                            AND csd.hidden_flags = 0
                            AND cs.is_active = 1
                            AND csl.is_active = 1), 1, 0)
UNION
SELECT  3 , IIF(EXISTS( SELECT  *
                    FROM    dbo.content_stream_owner AS cso
                            JOIN dbo.content_stream_data AS csd ON csd.content_stream_data_id = cso.content_stream_data_id
                            JOIN dbo.content_stream AS cs ON cs.content_stream_id = csd.content_stream_id
                            JOIN dbo.content_stream_logins AS csl
                                ON (csl.mall_id = cso.mall_id OR csl.base_tenant_id = cso.base_tenant_id)
                    WHERE   cso.mall_id = @mall_id
                            AND (@base_tenant_id IS NULL OR cso.base_tenant_id = @base_tenant_id)
                            AND csd.content_stream_id = 3
                            AND csd.end_dt > SYSDATETIME()
                            AND csd.hidden_flags = 0
                            AND cs.is_active = 1
                            AND csl.is_active = 1), 1, 0);

Answer 1

您是否尝试过每次都重新编译存储过程？

ALTER PROCEDURE dbo.usp_s_mall_has_rsc_content
    @mall_id        INT,
    @base_tenant_id INT = NULL
WITH RECOMPILE
AS
...

我已经在我的一些存储过程中看到了这个问题。您在 IIF(...) 中嵌套了查询。 IIF只期望标量参数，因此不知何故，SQL Server认为所有涉及的表只为JOIN提供1行并使用低效的LOOP JOIN。一旦表返回几千行以参与联接，它就会爆炸。