20

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
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);

(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
)
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

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

Notes:

  • 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?

Thanks

  • 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 '17 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 '17 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 '18 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 '18 at 11:55
2

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.

1

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
    OPTION (FORCE ORDER);

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 
    OPTION (FORCE ORDER);
0

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

-1

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://docs.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 '17 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 '17 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. – Artashes Khachatryan Sep 25 '17 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 '17 at 1:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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