關於診斷“有時”慢查詢的建議

我有一個儲存過程，它通過覆蓋索引從索引視圖返回結果。通常，它執行速度很快（約 10 毫秒），有時可以執行長達 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 統計資訊進行了探查器跟踪。

這是一個緩慢的（6秒），以及相關的計劃：

看起來完全符合我的預期，並且在查詢快速時是相同的計劃。

如果有幫助，這裡是計劃中昂貴部分的放大圖：

這是視圖/支持表的完整架構，如果有幫助的話：https ://pastebin.com/wh1sRcbQ

筆記：

• 索引已經過碎片整理，統計數據是最新的。
• 最初查詢是針對視圖內聯的，但我轉移到 SPROC 以嘗試幫助穩定。沒有幫助。
• 添加WITH OPTION (RECOMPILE);提示（不起作用，所以不能是參數嗅探？）
• 系統中的其他查詢有時也會執行緩慢，而且他們的計劃中也沒有明顯的問題。
• 能上鎖嗎？不確定如何確認。

關於下一步我可以嘗試什麼的任何想法？

謝謝

我真的不認為使用OPTION (RECOMPILE)是消除參數嗅探可能性的有效方法。

當 SQL 對特定查詢感到困惑並認為它是新的，因為它看到了新參數時，就會發生參數嗅探。它很慢，因為它需要額外的時間來生成新的執行計劃。

該選項所做的只是強制 SQL 每次都生成一個新計劃，這幾乎是一回事。相反，您可能需要考慮使用以下提示添加預設參數：

OPTION(OPTIMIZE FOR(@LocationIds='xx',@StatusType='xx'))

在選擇預設參數時，請確保使用具有統計代表性的集合。

這將迫使每次都使用相同的計劃，並消除參數嗅探的可能性。一旦你這樣做了，並確定它沒有幫助，那麼將參數嗅探視為一種可能性可能是安全的。

也許嘗試強制執行順序，因此您可能總是從較小的表（變數）開始。不過，這對視圖來說很棘手……

   SELECT  -- lots of fields
   FROM    @LocationIds [b] WITH (NOEXPAND)
           INNER JOIN  [dbo].[ListingSearchView][a] WITH (NOEXPAND) 
               ON [a].[LocationId] = [b].[Id]
   WHERE   [a].[StatusType] = @statusType
   OPTION (FORCE ORDER);

或者您可以強制循環連接，如果這通常是您希望將表變數連接到視圖的方式，這也將強制執行順序……

   SELECT  -- lots of fields
   FROM    @LocationIds [b] WITH (NOEXPAND)
           INNER LOOP JOIN  [dbo].[ListingSearchView][a] WITH (NOEXPAND) 
               ON [a].[LocationId] = [b].[Id]
   WHERE   [a].[StatusType] = @statusType
   --leaving this here so you don't get an annoying warning 
   OPTION (FORCE ORDER);

引用自：https://dba.stackexchange.com/questions/185054