Having problems understanding what what can be improved

Alex shaw 2017-11-30 19:57:56

I have spent a few days looking at how to improve some queries, using sentryone, so far the only easy to understand part has been the index analysis, where it suggests changes to indexes.

I am not really undersdtanding from looking at he query plan what can be is wrong and what can improved, and how. For example there are many parts in the attached query which have red notation, does this mean there is an issue?

When I ran the same plan as an estimated plan, the figure where significantly different, on different nodes,
index spool(eager spool) was at 46% and red, I tried to reseach if this was an issue, nearly always people discussing it on the net say it depends on the plan. Which means to understand if it is an issue i haev to get someone else to look at it.

I was hoping that sentryone would be easier to read and understand, ie what is an issue, how to check it, and fix it etc.
I have looked for a walk-through, tutorials of the product but couldnt find anything, is there a video/s i can check.

Also some explanation of what is and isnt wrong with the above query would help a lot thanks.

Hugo Kornelis 2017-11-30 20:43:34
The problem with computerized tools is that they sound so true. Even if they are not.

In some of the plans I see a big red "300%", which sounds scary. Hovering my mouse I read that this is because the actual rowcount exceeded the estimation. Yes, 1 estaimted; 3 actual, a 300% error. But in a different section of the plan we are processing 6.6 million rows. Guess how interesting that 2-row difference in the leftmost part of the plan is? Exactly: not at all!!

Plan Explorer also highlights several percentages in red. These percentages are based on cost estimates. SQL Server estimates the cost of each operator. If all operators in a plan combined have a cost of 12, and one operator has a cost of 1.2, then that would be 10% of the total. Plan Explorer marks the higher percentages yellow, orange, or red, so you can find the "expensive" parts of a plan faster.
(A very nice feature of Plan Explorer is that, in an actual plan, it does not look at the costs that SQL Server provides, which are still only the original estimates, but adjusts those costs based on the actual number of rows – though the plans you attach give me reason to doubt if their algorithms are really correct).

Unfortunately, there are in my opinion no shortcuts where it comes to understanding execution plans. Sure, you can memorize common patterns and their standard fixes, but that can be dangerous. I believe that if you want to get into query tuning, you need to learn to listen to what execution plans tell you – by learning how to read them.

Let's zoom in on what appears to be the expensive part of your plan (where the fat lines are – line width represent number of rows). The action starts with a Nested Loops operator, doing a Left Anti Semi Join operation. Nested Loops means that it requests a row from its top input, then opens the bottom input and keeps requesting rows until done; after that it moves to the second row from the top input, resets the bottom input, and repeats. This is efficient when the top input has just a few rows, and the bottom input is cheap. With 6.6 million rows in the top input, the first criterium is not met; I must say that I am a bit surprised the optimzer didn't opt for a different join type here. But with the task you have set out for the optimizer, none of the supported join operators would have been ideal so I guess this plan was the lesser of all evils

The logical join type, left anti semi join, means that rows from the top input are passed if no matching row is found in the bottom input, or eliminated from the data if there was a match found. (This also means that the operator is optimized to stop processing the bottom input when a match was found). In other words, this type of join directly implements the NOT EXISTS you have in your query.

The top input itself is not very interesting. When called, the COmpute Scalar will call the Index Scan, which reads a row from the table; Compute Scalar then computes the two LEFT expressions in your select list and the result is returned to the Nested Loops operator.

The bottom input is far more interesting. The Top operator is not, though. It is a leftover from a phase in the optimization. Its role is to ensure that after the first row is read no more rows need to be produced. However, the Nested Loops operator has that same logic built in when using one of the "Semi" join types so this operator is useless; however it costs so little that there is no need to remove it.

What IS interesting is the Index Spool. When first called (for the first row from the top input), it will not return a row as fast as possible – no, it will continue calling the Index Scan operator and storing all results in a temporary table. Which is then indexed. Only after all rows have been processed will it return the requested row (defined by its Seek Predicates – a row with matching Marchant ID and SKU Number). Or return nothing if no such row is found.

The Nested Loops operator receives this result and then decides whether or not to pass the data it read from the top input. After that, it will be called again, and that means it requests the next row from the top input, resets the bottom input, and requests a row from there. This time the Index Spool will work differently. It has already processed all data it can read and stored it in a temporary table, with an index; so now it merely uses the index to find a matching row, (You can see this if you look, in the properties, at the actual number of executions – millions for the Top and the Index Spool, but just one for the Index Scan).

This plan shows that the optimizer found no good index, and decided that creating a (temporary) index on the spot would be cheaper then executing without any index; that's why you get an Index Spool. It is often a trigger to wonder whether, perhaps, you should create an index. That index is permanent and therefor saves the time of building it every time the quuery runs (but at the cost of more work when data in the table changes).

For this specific query, I expect that adding a nonclustered index on (merchant_id, sku_number) on the rw_ls_import_temp table will do wonders for execution time and resource usage.