I have a stored procedure which returns results from an indexed view via a covering index. Usually, it runs fast (~10ms), sometimes it can run up to 8 seconds.

Here's an example random execution (note: this isn't a slow one, but the query text is the same apart from the value passed through):

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

Here's the SPROC:

ALTER PROCEDURE [dbo].[ListingSearch_ByLocationAndStatus]
    @LocationIds IdentityType READONLY,
    @StatusType TINYINT

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

(note: i added the OPTION (RECOMPILE) hint recently after some advice, but it hasn't helped.

Here's the covering index (note: the view also has a clustered index on ListingId, which is unique)

CREATE NONCLUSTERED INDEX [IX_ListingSearchView_ForAPI] ON [dbo].[ListingSearchView]
    [LocationId] ASC,
    [StatusType] ASC

I put a profiler trace on, with showplan XML statistics.

Here's a slow one (6 seconds), and the relevant plan: enter image description here

Looks exactly as i'd expect, and is the same plan when the query is fast.

Here's the zoom in on the costly part of the plan, if that helps: enter image description here

Here is the full schema of the view/backing tables, if that helps: https://pastebin.com/wh1sRcbQ


  • Indexes have been defrag'd, statistics up to date.
  • Originally query was inline against the view, but i moved to SPROC to try and help stabilize. Hasn't helped.
  • Adding WITH OPTION (RECOMPILE); hint (didn't work, so can't be parameter sniffing?)
  • Other queries in the system also sometimes run slow, and they also have no obvious issues in their plan.
  • Could be locking? Not sure how to confirm.

Any ideas on what i could try next?


  • 1
    Comments are not for extended discussion; this conversation has been moved to chat. Everyone: Please use that facility for further discussion of this question.
    – Paul White
    Sep 22, 2017 at 5:12
  • given link not working.proc query is straight forward,there is huge diff between actual and estimated number of rows which is area of concern.I think problem lies in view query.I think insufficient data.It should be close
    – KumarHarsh
    Dec 27, 2017 at 3:59
  • Have you tried running WhoIsActive (by Adam Machanic) while the query is running? whoisactive.com It includes information on waiting tasks, which should point you in the right direction.
    – MJH
    Mar 8, 2018 at 22:56
  • Have you eliminated something external to the DB causing this. Perhaps some other application causing synchronous IO to storage shared with the DB?
    – Johan
    Jun 14, 2018 at 11:55

4 Answers 4


I really don't think using the OPTION (RECOMPILE) is an effective way to eliminate the possibility of parameter sniffing.

Parameter sniffing happens when SQL is confused about a particular query and thinks its new because it sees new parameters. It's slow because it's taking extra time to generate a new execution plan.

All that option does is force SQL to produce a new plan every time which is pretty much the same thing. Instead, you might want to consider adding default parameters using this hint:

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

When choosing parameters for the default make sure to use a statistically representative set.
That will force the same plan to be used every time and eliminate the possibility of parameter sniffing. Once you do that, and determine it didn't help, then its probably safe to dismiss parameter sniffing as a possibility.


Perhaps try to force the order, so you are probably always starting with the smaller table (variable). That get's tricky with views though...

    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

or you can force a loop join if that is generally how you want to join the table variable to the view, which also will 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 

Write the name of Store procedure in the Query Editor, then Select the Store proc. name & then Select the Display Estimated Execution plan or Click (Ctrl+L). below the image of this .

Image of Display Estimated Execution plan

then Execution plans shows just next to Messages Tab in the bottom of Query Editor. then in the green color lines show the missing Index details and right click on that. Then New query open in new tab then create the INDEX. then your Query run fast.

So i used this method for Diagnose the Query who work slow. and also there are so many query or method that you can use.

Execution plan with details


If you think the problem is in blocking I will suggest you to use optimistic transaction isolation level Read Committed Snapshot (take in mind that this will put overhead on your tempDB).

REFERENCE: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

It the problem is not in the read/write blocking, you can to try add indexes on your view (the best choice of indexes depends on selectivity of your data)

CREATE NONCLUSTERED INDEX IX_ListingSearchView (LocationID, StatusType) INCLUDE (other columns...)
  • 1
    The index you suggest already exists IX_ListingSearchView_ForAPI (see the script in the question).
    – Paul White
    Sep 23, 2017 at 22:13
  • 1
    Hey, thanks for your answer. As i've said in my question though, i want to know what the problem is before i apply a fix. Otherwise i could just be overlooking the real problem. My question is about finding the problem first, then the correct solution.
    – RPM1984
    Sep 24, 2017 at 23:39
  • Can you get that slow query in your local environment? If the same query sometimes run slow and sometimes run fast it may depend on the input parameters. Can you please provide the number of logical reads and the total number of rows returned by your slow query. Sep 25, 2017 at 7:01
  • @ArtashesKhachatryan yes, it sometimes runs slow on local too. I've updated the question with an execution plan. I'm wondering if it's related to the slow queries returning more rows than the fast one, as you've said.
    – RPM1984
    Oct 3, 2017 at 1:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.