Need suggestions on reducing execution time of sp

Maahi 2017-12-05 11:22:25

Hi Perf Experts,

Need some suggestions on below sp to improve performance of the sp. Currently taking 10 -15 secs to execute.Trying to see if we can reduce the overall execution time.
I have no much info about what does the stored proc does. Written by some consultant. When we run against more users, getting a performance hit. So wanted to reduce
the execution time of sp. Its completely a dynamic sp.

select @@version
–Microsoft SQL Azure (RTM) – 12.0.2000.8 Nov 17 2017 14:42:09 Copyright (C) 2017 Microsoft Corporation

Attached zip file contains all details.

  1. sp call.sql // contains the step to execute the sp
  2. sp source code.sql // source code of the sp
  3. ssms_actual_plan.sqlplan // actual execution plan generated from SQL Server Management Studio
  4. tmpA554.pesession // actual execution plan opened in plan explorer. i.e. in ssms -> right click the plan -> View with SQL Sentry Plan Explorer

Seeking for any help on speeding up the stored proc.

Observations in plan explorer

  • stored proc retuns 2 results sets
    result set1: returns 1 row
    result set2: returns 50 rows
  • no missing recommendations
  • Friom the Query Columns tab, there is skew between Actual no of rows vs Est no of rows

— from Plan Diagram tab
I see a lot of thick lines.
I see a high cost operators marked in RED
I see a DISTINCT operator .. which is forcing a SORT operation.

— From Top operations tab
I see Sort as the top
( see the estimated and Actual no of rows are way off) not sure if we update the stats with Full scan would help.

Thanks,

M

link text

Hugo Kornelis 2017-12-05 15:00:10
Hi Maahi,

First some generic remarks that are not related to your performance issue.

The code in sp_call.sql uses dynamic SQL but doesn't have to. You can simply rewrite the last statement as:

EXEC LubeAnalyst.usp_GetSamplesForInbox @LanguageId = 0,
                                        @SampleStatusId = DEFAULT,
                                        @ShowFavourites = 0,
                                        @DurationId = NULL,
                                        @KeyWord = NULL,
                                        @UserId = 70,
                                        @UserComponents = @p9,
                                        @IsAdmin = 0,
                                        @CustomerId = 2,
                                        @DepartmentId = NULL,
                                        @SiteOrVesselId = NULL,
                                        @SiteOrVessel = NULL,
                                        @FromDate = NULL,
                                        @ToDate = NULL,
                                        @Offset = 0,
                                        @PageSize = 50,
                                        @SortColumn = N'SampleCondition',
                                        @SortOrder = 0,
                                        @OverFlowPeriod = 90;

It appears that the only reason for the dynamic SQL is to allow you to specify if you want to sort ascending or descending. (The code also allows you to specify a column, but allows only a single choice: SampleCondition). Do you really need the option to order by descending instead of ascending SampleCondition? If no, I'd suggest removing the @SortColumn and @SortOrder parameters and removing the entire dynamic SQL usage. And even if you do use the option to switch sort order, you can still do this without dynamic SQL, e.g. using

ORDER BY CASE WHEN @SortOrder = 0 THEN ISNULL(samp.SampleCondition, 999) END ASC,
         CASE WHEN @SortOrder = 1 THEN ISNULL(samp.SampleCondition, 999) END DESC,
         samp.DateDrawn DESC,
         samp.SampleNumber ASC;

I see parallelism in plans with very low estimated plan cost. I recommend checking the sp_configure setting "threshold for parallelism", it is probably set to 5 (the default, which was a very good value in, oh let's say 1995 or so) and you should change it to 25 or 50, then compare overall system performance against your baseline and adjust further as needed.

But you didn't ask about all that. You wanted to get some help tuning this thing. So here are, in random orders, my observations:

The execution plan shows one single query as being the most costly, at 96% of the total cost. But that is a dangerous metric, because it is based on estimated cost. If the estimates are wrong (and that is very often a root case of bad performance), then these figures are meaningless. I much rather go by the actual elapsed and actual CPU time of each step, which are normally visible in Plan Explorer – but not for the .sqlplan and .pesession files you attached (no idea why, perhaps a bug?).

I opened the .sqlplan file in SSMS and looked at the Actual Time Statistics / Actual Eapsed Time property of the operators on each plan (note that this tracks time for that operator only; you have to understand the plan as a whole to find an operator that you can be sure is active from start to finish and can therefore be used to find the elapsed time of the entire query). The query that was estimated to be the most expensive was active for 14 seconds, the rest were all less than a second. So in this case, the estimated expensive query IS indeed the one that takes all the time.

The slow query is NOT one the dynamically generated and very daunting looking query. Nor the very long and complex-looking query just before that. No, the query that causes your performance issues is in fact one of the simpler queries – this one (reformatted for better readability)

SELECT  @GreenTotalCount = SUM(CASE WHEN samp.samplecondition = 3 THEN 1 ELSE 0 END),
        @AmberTotalCount = SUM(CASE WHEN samp.samplecondition = 2 THEN 1 ELSE 0 END),
        @RedTotalCount   = SUM(CASE WHEN samp.samplecondition = 1 THEN 1 ELSE 0 END)
FROM    LubeAnalyst.sample                   AS samp
JOIN    lubeanalyst.usercomponentassociation AS comp
  ON    samp.componentid            = comp.componentid
  AND   comp.userid              = @UserId
  AND   comp.isreportassociation = 1
  AND   (   @IsAdmin             = 1
      OR    samp.componentid IN (SELECT id FROM @UserComponents));

The execution plan starts by reading the ComponentID values from UserComponentAssociation that have the matching UserID value and IsReportAssociation set to 1; this results in two rows. I assume (but cannot be 100% sure) that the values returned here are 4 and 5, the same values stored into @p9 in the call.sql file. The plan goes parallel here, with one of each values on one of each threads.

Each thread then goes into a branch where data from the Sample table is read for the ComponentID value of that thread, and compared to either @IsAdmin or the @UserComponents table. In that thread we see some very thick arrows, and warnings about a Hash Match and a Sort operator both spilling to tempdb. The reason for this spilling, which is probably a major factor for the performance issues, is that the estimated and actual row counts are different. Estimated is 232K per thread; real is 14 on one thread, and 929K on the other. Since the entire table holds just under 930K rows, that means that almost all rows in the table have the same value for ComponentID. The estimates SQL Server makes are usually based on a assumed uniform distribution and that is definitely not a correct assumption for this table.

A lot of the steps in this branch of the query appear to be superfluous. Two aggregation operators try to reduce rows by aggregating on SampleID but do not actually reduce the number of rows. The sort appears to be mostly intended to help one of those aggregates be a bit more efficient. All of these three are related to an aggregation logic that doesn't do anything at all in this specific execution. So why is this there at all?

There are two possible explanations, both of which can be easily fixed. First, it is possible that SampleID is unique in the table (the name certainly suggests this), but not declare to be unique. Can you check the table definition? If SampleID is not subject to a PRIMARY KEY or UNIQUE constraint, then please add one.

The other part of the explanation is that the table parameter is declared as a table type that does not have a PRIMARY KEY constraint. This means that SQL Server has to be defensive and create a plan that will still be correct if you have duplicate rows in @UserComponents. I recommend changing the table type to have a PRIMARY KEY constraint on its only column.

In the calling code you store just two rows in the table variable @p9. Is this procedure always called with such a low amount of values? If yes, then I recommend just using two integer parameters instead of this table-valued parameter. (And if the values are always 4 and 5, then just remove the parameters and use a hard-coded IN (4,5) in the query).

I think that this is enough for now. I don't think it will fix all the problems (for instance, the estimated row count will probably still be wrong), but it is my experience that tuning is best done one step at a time, and I have already presented several steps. Please try them out, see what happens, and post back with the updated version of the code, new .sqlplan and .pesession files, and a fresh set of question, and then perhaps we can help you further from there.

Maahi 2017-12-05 18:43:55
Hi Hugo,

Thank you for the response. One thing I tried from my end is, to do update stats with FULLSCAN and again re-ran the query, still the estimates are getting skewed. I am not sure who so?

I will give try to use MAXDOP = 1 hint at query level. Just to note, this is a SQL Azure database and not sure if we can change the server level settings.

Will try out above suggestions one by one and let you know.

Other thing is that, when I try to execute the sp, directly in PLAN Explorer, I am not able to see the separate plans just like ssms did. I can see plan for only INSERT and for the expensive query. any reason why so?

declare @p9 LubeAnalyst.udt_longlist
insert into @p9 values(4)
insert into @p9 values(5)
exec sp_executesql N'exec [LubeAnalyst].usp_GetSamplesForInbox @LanguageId, @SampleStatusId, @ShowFavourites, @DurationId, @KeyWord, @UserId, @UserComponents, @IsAdmin, @CustomerId, @DepartmentId, @SiteOrVesselId, @SiteOrVessel, @FromDate, @ToDate, @Offset, @PageSize, @SortColumn, @SortOrder, @OverFlowPeriod',N'@LanguageId int,@SampleStatusId [LubeAnalyst].[udt_integerlist] READONLY,@ShowFavourites bit,@DurationId smallint,@KeyWord nvarchar(4000),@UserId bigint,@UserComponents [LubeAnalyst].[udt_longlist] READONLY,@IsAdmin bit,@CustomerId bigint,@DepartmentId bigint,@SiteOrVesselId bigint,@SiteOrVessel bigint,@FromDate datetime,@ToDate datetime,@Offset int,@PageSize int,@SortColumn nvarchar(15),@SortOrder int,@OverFlowPeriod bigint',@LanguageId=0,@SampleStatusId=default,@ShowFavourites=0,@DurationId=NULL,@KeyWord=NULL,@UserId=70,@UserComponents=@p9,@IsAdmin=0,@CustomerId=2,@DepartmentId=NULL,@SiteOrVesselId=NULL,@SiteOrVessel=NULL,@FromDate=NULL,@ToDate=NULL,@Offset=0,@PageSize=50,@SortColumn=N'SampleCondition',@SortOrder=0,@OverFlowPeriod=90

Hugo Kornelis 2017-12-05 20:06:13
The reason the estimates are off is not bad statistics, but a bad distribution. Almost all rows in the Sample table have the same ComponentID value, and then there is a bunch of other values with just a few rows each. When the optimizer knows which value you are looking for (as in "WHERE ComponentId = 8" or "WHERE ComponentId IN (3, 18)", it can use the histogram to find the correct estimate. But your query compares against a table variable, and contents of a table variable are not sniffed. (Also, based on how the plan is built, it will actually compare to the values from UserComponentAssociation, but that has the same problem).

If the value is unknown and cannot be sniffed, SQL Server instead estimates based on number of distinct values and the assumption of a uniform distribution. My guess is that there is a total of four distinct values, so the uniformity assumption leads SQL Server to believe that there are (930K / 4 =) 265K each. That is not exactly what was in the plan but that might be related to statistics being out of date when you captured that original plan.

As to what you see in Plan Explorer, I do not know how the tool works. I know some of SentryOne's staff visit this forum, I hope one of them can comment on that.

Do let me know what the results are when you implement my initial suggestions!

Maahi 2017-12-06 19:29:48
Instead of @UserComponents TVP, used a temp table to get proper estimates. Other thing, I tried is option maxdop 1. It worked like a charm. execution time reduced to 2 secs. It was amazing. Thanks a lot for all support. Many Thanks Sir.