1

I've been looking into a performance issue for the last few days and although I've improved the query runtime from 48 seconds to 11 seconds, I feel that it can be improved even further. The issue is that I'm not sure what the next steps could be in order to do that, so I thought I'd post it here!

Execution plan is here

Here is what I've been focusing on at the moment;

enter image description here

After the Clustered Index Scan and Filter, it then goes to a Hash Match (Right Anti Semi Join).

enter image description here

After that, we get the bottleneck;

enter image description here

Here is the script which is being run;

 SELECT AVG([F_TASKS].[TA_SEQ]) [TA_SEQ]
    ,COUNT([F_TASK_TIME].[TT_SEQ]) [TimeRecordsCount]
FROM [F_TASKS] WITH (NOLOCK)
LEFT OUTER JOIN [F_TASK_TIME] WITH (NOLOCK) ON [F_TASKS].[TA_SEQ] = [F_TASK_TIME].[TT_FKEY_TA_SEQ]
    AND [F_TASK_TIME].[TT_FKEY_TA_SEQ] IN (
        SELECT [F_TASKS].[TA_SEQ]
        FROM [F_TASKS] WITH (NOLOCK)
        WHERE (
                [F_TASKS].[TA_FKEY_CTR_SEQ] = 0
                OR [F_TASKS].[TA_FKEY_CTR_SEQ] IS NULL
                OR [F_TASKS].[TA_FKEY_CTR_SEQ] = ''
                OR [F_TASKS].[TA_FKEY_CTR_SEQ] IN (
                    SELECT [ContractGroupsContract].[CTR_SEQ]
                    FROM [ContractGroupsContract]
                    INNER JOIN [AccountContractGroups] ON [AccountContractGroups].[ContractGroupId] = [ContractGroupsContract].[ContractGroupId]
                    WHERE [AccountContractGroups].[AccountId] = 93
                        AND [AccountContractGroups].[Deleted] = 0
                        AND [ContractGroupsContract].[Deleted] = 0
                    )
                )
            AND (
                [F_TASKS].[TA_SEQ] > 0
                OR [F_TASKS].[TA_SEQ] IS NULL
                )
        )
WHERE (
        [F_TASKS].[TA_SEQ] IN (
            SELECT [F_TASKS].[TA_SEQ]
            FROM [F_TASKS] WITH (NOLOCK)
            WHERE (
                    [F_TASKS].[TA_SEQ] NOT IN (
                        SELECT [ProjectTaskDetails].[TaskId]
                        FROM [ProjectTaskDetails] WITH (NOLOCK)
                        INNER JOIN [Projects] WITH (NOLOCK) ON [ProjectTaskDetails].[ProjectId] = [Projects].[ProjectId]
                        WHERE (
                                (
                                    [Projects].[StateId] IN (
                                        6
                                        ,7
                                        ,8
                                        )
                                    )
                                AND (
                                    [ProjectTaskDetails].[Deleted] = 0
                                    OR [ProjectTaskDetails].[Deleted] IS NULL
                                    )
                                AND (
                                    [ProjectTaskDetails].[ProjectTaskDetailId] > 0
                                    OR [ProjectTaskDetails].[ProjectTaskDetailId] IS NULL
                                    )
                                )
                            AND (
                                [Projects].[ProjectId] = 0
                                OR [Projects].[ProjectId] IS NULL
                                OR [Projects].[ProjectId] = ''
                                OR (
                                    SELECT COUNT([ProjectContracts].[ContractId])
                                    FROM [ProjectContracts]
                                    WHERE [Projects].[ProjectId] = [ProjectContracts].[ProjectId]
                                        AND [ProjectContracts].[ContractId] NOT IN (
                                            SELECT [ContractGroupsContract].[CTR_SEQ]
                                            FROM [ContractGroupsContract]
                                            INNER JOIN [AccountContractGroups] ON [AccountContractGroups].[ContractGroupId] = [ContractGroupsContract].[ContractGroupId]
                                            WHERE [AccountContractGroups].[AccountId] = 93
                                                AND [AccountContractGroups].[Deleted] = 0
                                                AND [ContractGroupsContract].[Deleted] = 0
                                            )
                                    ) = 0
                                )
                            AND (
                                [Projects].[Deleted] = 0
                                OR [Projects].[Deleted] IS NULL
                                )
                        )
                    )
                AND (
                    [F_TASKS].[TA_FKEY_CTR_SEQ] = 0
                    OR [F_TASKS].[TA_FKEY_CTR_SEQ] IS NULL
                    OR [F_TASKS].[TA_FKEY_CTR_SEQ] = ''
                    OR [F_TASKS].[TA_FKEY_CTR_SEQ] IN (
                        SELECT [ContractGroupsContract].[CTR_SEQ]
                        FROM [ContractGroupsContract]
                        INNER JOIN [AccountContractGroups] ON [AccountContractGroups].[ContractGroupId] = [ContractGroupsContract].[ContractGroupId]
                        WHERE [AccountContractGroups].[AccountId] = 93
                            AND [AccountContractGroups].[Deleted] = 0
                            AND [ContractGroupsContract].[Deleted] = 0
                        )
                    )
                AND (
                    [F_TASKS].[TA_SEQ] > 0
                    OR [F_TASKS].[TA_SEQ] IS NULL
                    )
            )
        )
    AND (
        [F_TASKS].[TA_FKEY_CTR_SEQ] = 0
        OR [F_TASKS].[TA_FKEY_CTR_SEQ] IS NULL
        OR [F_TASKS].[TA_FKEY_CTR_SEQ] = ''
        OR [F_TASKS].[TA_FKEY_CTR_SEQ] IN (
            SELECT [ContractGroupsContract].[CTR_SEQ]
            FROM [ContractGroupsContract]
            INNER JOIN [AccountContractGroups] ON [AccountContractGroups].[ContractGroupId] = [ContractGroupsContract].[ContractGroupId]
            WHERE [AccountContractGroups].[AccountId] = 93
                AND [AccountContractGroups].[Deleted] = 0
                AND [ContractGroupsContract].[Deleted] = 0
            )
        )
    AND (
        [F_TASKS].[TA_SEQ] > 0
        OR [F_TASKS].[TA_SEQ] IS NULL
        )
GROUP BY [F_TASKS].[TA_SEQ]

I'm fairly new to execution plan analysis, so any suggestions on this issue would be very helpful.

Thanks in advance.

  • Is that run time value 169 million percent over estimate? Maybe your statistics need updated. – Jacob H Mar 4 at 17:54
  • 1
    My guess is that the query can be simplified. Have you tried rewriting the subselect in the join clauses as joins at an outer level? – Lennart Mar 4 at 20:00
  • @JacobH - This is something that I've checked and I have confirmed that the Stats are updated. Idexes are also defragged. – Tom_W Mar 5 at 8:14
  • @Lennart I've not tried it that way, but I have rewritten the OR statements to UNIONS and that improved the performance, but not enough. Plus, I would like to improve this ideally without haning that code (it's code called from an application) – Tom_W Mar 5 at 8:15
  • Surely the issue here is that SQL is estimating 2 and 1 row coming out of those two nc index seeks, and the actual numbers are 3.9 million and 9.3 million. Why does SQL Server think AccountID 93 has 2 rows when it has millions? This is your problem. It would probably help if you add the index definition to the post. – Jacob H Mar 5 at 15:06
1

Regarding the operators you called out as the bottleneck (the join from AccountContractsGroups to ContractGroupsContract):

SQL Server estimates that the seek into AccountContractGroups will be executed 3,971,931 times (once for each row coming out of the hash match join "above" it in the execution plan). This is almost exactly right, so that part is not the problem.

Further, it estimates that each seek will result in 2.34 rows being returned from the index (thus the overall estimate of 2.34 * 3,971,931 = 9,313,265). In reality, only 1 row is returned for each seek into the index.

As you mentioned, stats are very up-to-date on that index:

<StatisticsInfo Database="[HED_Live_27022020_After-Upgrade]" Schema="[dbo]" 
    Table="[AccountContractGroups]" 
    Statistics="[IDX_AccountContractGroups_ContractGroupId_AccountId_Deleted]" 
    ModificationCount="0" 
    SamplingPercent="100" 
    LastUpdate="2020-03-04T15:04:37.46" />

It doesn't look like there is a problem here related to estimates. Doing this many small seeks into an index just seems like...not a very efficient approach to the query. Especially at DOP 32.

A couple of suggestions:

Try different MAXDOP hints

Looking at the information in the execution plan, I think your server has two NUMA nodes with 16 logical processors each. I don't think running this type of query at DOP 32 is helping you here. I expect the optimizer is making some poor decisions as it scales down the cost of different operators based on the high DOP.

Also, transferring rows between all of these parallel threads becomes counterproductive after a while.

Try adding this to the end of the query and see how things change:

OPTION (MAXDOP 16)

Then try it with 8 instead of 16. See which one helps the most. These numbers are based on Microsoft's guidelines related to server-level MAXDOP.

The nice thing about this approach is:

  • it's simple (to test and implement)
  • you can implement it without changing the query text if you want (by using a plan guide)

As you mentioned not wanting to change the query text, this might be the best bang for your buck.

Break the query up

Another option is to "materialize" repetitive subqueries into a temp table, then reference that throughout the query

This subquery is referenced 4 times in the query, as part of NOT IN and IN predicates:

SELECT [ContractGroupsContract].[CTR_SEQ]
FROM [ContractGroupsContract]
INNER JOIN [AccountContractGroups] ON [AccountContractGroups].[ContractGroupId] = [ContractGroupsContract].[ContractGroupId]
WHERE [AccountContractGroups].[AccountId] = 93
    AND [AccountContractGroups].[Deleted] = 0
    AND [ContractGroupsContract].[Deleted] = 0

Put that in a temp table, and read it from there so the join doesn't have to happen over and over:

SELECT [ContractGroupsContract].[CTR_SEQ]
INTO #AccountNinetyThree
FROM [ContractGroupsContract]
INNER JOIN [AccountContractGroups] ON [AccountContractGroups].[ContractGroupId] = [ContractGroupsContract].[ContractGroupId]
WHERE [AccountContractGroups].[AccountId] = 93
    AND [AccountContractGroups].[Deleted] = 0
    AND [ContractGroupsContract].[Deleted] = 0

Then the various NOT INand IN predicates can be rewritten like this:

OR [F_TASKS].[TA_FKEY_CTR_SEQ] IN (SELECT [CTR_SEQ] FROM #AccountNinetyThree)

You may get better results by using EXISTS instead of IN, but this is probably good for a first pass.

| improve this answer | |
  • this temp table is useful but I don't think it will solve the issue. The problem is caused by this left semi join - <RelOp AvgRowSize="11" EstimateCPU="1.03767" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3971930" LogicalOp="Left Semi Join" NodeId="8" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="19.8868">. It uses nested loop and it stil will execute select from temporary table 4 million times. I support Lennart, author needs to try to rewrite the query using join instead of subquery. – Nikita Mar 5 at 19:15
  • @Nikita "It uses nested loop and it stil will execute select from temporary table 4 million times" We can only speculate on how these changes would affect the overall plan shape, it might not be a simple replacement like what you've described. I think Lennart's join suggestion as totally valid as well, I just don't have the mental model to try and rewrite the query to show that option. – Josh Darnell Mar 5 at 20:17
0

Please pay attention to the following images

enter image description here

enter image description here

enter image description here

enter image description here

One of the most important things to be careful is that you must be one of your estimated number of row with your actual number of rows Otherwise, in this case, the implementation plan will not properly select the right operators. For example, your estimate will return the value of one, while your actual statistics take a lot of the row. This can be considered because of the failure to update your statistics.

This is well mentioned in the following book : SQL Server Execution Plans

A difference this small is not worth worrying about, but a larger discrepancy can be an indication that the optimizer has used inaccurate estimations of the number of rows that will need to be processed when selecting the plan, which could result in a suboptimal plan choice. There are many possible causes of this. For example, perhaps the optimizer had to generate a plan for a query containing a Predicate on a column with missing or stale statistics, or the optimizer may have reused a plan where the data volume or distribution in a column has changed significantly since the statistics were last created or updated. Alternatively, the data distribution in a column may be very non-uniform, making accurate cardinality estimations difficult, or the query may contain logic that defeats accurate estimations. Parameter sniffing may have occurred, resulting in a plan generated for an input parameter value with an estimated row count that is atypical of the row counts for subsequent input values.

also :

Actual Number of Rows – the true number of rows returned according to runtime statistics. The availability of this value in actual plans is the biggest difference between these and cached plans (or estimated plans). Look out for big differences between this value and the estimated value.

Estimated Number of Rows – calculated based on the statistics available to the optimizer for the table or index in question. These are useful for comparing to the Actual Number of Rows.

Look out not only for "fat pipes," but also for abrupt transitions in pipe thickness as you read through the execution plan. For example, a very fat pipe at the beginning of a plan narrowing to a very thin pipe on the left-hand side of the plan suggests that filtering is happening late. Small pipes that get bigger and bigger suggest that your query is somehow multiplying data.

| improve this answer | |

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.