我有一个存储过程,它通过覆盖索引从索引视图返回结果.通常,它运行速度很快(~10ms),有时可以运行长达8秒.
这是一个示例随机执行(注意：这不是一个慢的,但查询文本除了传递的值之外是相同的)：
declare @p2 dbo.IdentityType insert into @p2 values(5710955) insert into @p2 values(5710896) insert into @p2 values(5710678) insert into @p2 values(5710871) insert into @p2 values(5711103) insert into @p2 values(6215197) insert into @p2 values(5710780) exec ListingSearch_ByLocationAndStatus @statusType=1,@locationIds=@p2
这是SPROC：
ALTER PROCEDURE [dbo].[ListingSearch_ByLocationAndStatus]
@LocationIds IdentityType READONLY,@StatusType TINYINT
AS
BEGIN
SET NOCOUNT ON;
SELECT -- lots of fields
FROM [dbo].[ListingSearchView][a] WITH (NOEXPAND)
INNER JOIN @LocationIds [b] ON [a].[LocationId] = [b].[Id]
WHERE [a].[StatusType] = @statusType
OPTION (RECOMPILE);
(注意：我在一些建议后最近添加了OPTION(RECOMPILE)提示,但它没有帮助.
这是覆盖索引(注意：该视图在ListingId上也有一个聚簇索引,这是唯一的)
CREATE NONCLUSTERED INDEX [IX_ListingSearchView_ForAPI] ON [dbo].[ListingSearchView]
(
[LocationId] ASC,[StatusType] ASC
)
INCLUDE ( -- all the fields in the query) WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,SORT_IN_TEMPDB = OFF,DROP_EXISTING = OFF,ONLINE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)
GO
我使用showplan XML统计信息打开了一个探查器跟踪.
看起来和我期望的完全一样,并且在查询速度很快时是相同的计划.
以下是视图/支持表的完整模式,如果有帮助：https://pastebin.com/wh1sRcbQ
笔记：
>索引已经过碎片整理,统计数据是最新的.
>最初查询是针对视图的内联,但我转移到SPROC尝试并帮助稳定.没有帮助.
>添加WITH OPTION(RECOMPILE);提示(没有工作,所以不能参数嗅探？)
>系统中的其他查询有时运行缓慢,并且他们的计划中也没有明显的问题.
>可以锁定吗？不知道如何确认.
关于我接下来可以尝试的任何想法？
谢谢
解决方法
我真的不认为使用OPTION(RECOMPILE)是消除参数嗅探可能性的有效方法.
当sql对特定查询感到困惑并认为它是新的时,会发生参数嗅探,因为它会看到新的参数.它很慢,因为它需要额外的时间来生成新的执行计划.
所有这些选项都是强制sql每次生成一个新计划,这几乎是一回事.相反,您可能需要考虑使用此提示添加默认参数：
OPTION(OPTIMIZE FOR(@LocationIds='xx',@StatusType='xx'))
选择默认参数时,请确保使用具有统计代表性的集.这将迫使每次使用相同的计划并消除参数嗅探的可能性.一旦你这样做,并确定它没有帮助,那么可能安全地将参数嗅探视为可能.
