Our warehouse management package has a bottleneck (one of many) with a stored procedure and the major slowdown is due to the query that produces this execution plan.
https://www.brentozar.com/pastetheplan/?id=HkNg65elP
The stored proc can take anywhere from 3 to 10 seconds to run which is quite slow in the context of the business process it runs in.
Some additional info: Yes, there is one table where a full table scan is being done, but this table is narrow, and only has 76 rows. The query does some left joins and some sorting which is needed to produce the correct top result. Overall, it is a bit of a "Rube Goldberg" type query and could probably be simplified, but my goal is to see if it is possible to help with some indexing (which I've done and it's helped a little) or even some small tweaks to the query if needed.
In the end, I need to know where to focus next based on the plan.
Here is the query:
SELECT TOP 1 loc.location_id, loc.wh_id
FROM t_item_uom itu WITH (NOLOCK)
INNER JOIN t_class_loca clc WITH (NOLOCK)
ON itu.wh_id = clc.wh_id
AND ISNULL(dbo.usf_get_item_class_dia_ovrd ('13098271', '895', itu.uom, NULL), itu.class_id) = clc.class_id
INNER JOIN t_location loc WITH (NOLOCK)
ON clc.wh_id = loc.wh_id
AND clc.location_id = loc.location_id
INNER JOIN t_pick_area pka WITH (NOLOCK)
ON pka.pick_area = loc.pick_area
AND pka.wh_id = loc.wh_id
AND (pka.pick_area <> N'LABEL' OR (pka.pick_area = N'LABEL' AND 0 IS NULL AND 0 IS NULL) )
AND (pka.pick_area_type = 'R' OR (pka.pick_area_type = 'V' and 0 IS NULL) )
INNER JOIN t_zone_loca zlc WITH (NOLOCK)
ON loc.wh_id = zlc.wh_id
AND loc.location_id = zlc.location_id
INNER JOIN (
SELECT loc.wh_id, loc.pnd_location_id --, loc.location_id
FROM t_location loc with (nolock)
inner join t_class_loca clc WITH (NOLOCK)
on clc.location_id = loc.location_id
and clc.wh_id = loc.wh_id
and clc.class_id = 'APPAREL'
LEFT JOIN t_stored_item sto with (nolock)
ON sto.put_away_location = loc.location_id
AND sto.wh_id = loc.wh_id --BTH 20160907 missing wh_id
AND sto.put_away_location IS NOT NULL
AND sto.type = 0
WHERE loc.type in ('I','M')
AND loc.pnd_location_id IS NOT NULL --BTH 20160907 - remove from having clause, add here
GROUP BY loc.wh_id, loc.pnd_location_id, loc.c3
HAVING ((COUNT(sto.hu_id) < 100
and loc.pnd_location_id IS NOT NULL --BTH 201600907
and c3 is null)
OR (COUNT(sto.hu_id) < 500 --and loc.pnd_location_id IS NOT NULL --BTH 201600907
and c3 = 'BULK'))
) as pnd
ON pnd.wh_id = loc.wh_id
AND pnd.pnd_location_id = loc.pnd_location_id
LEFT OUTER JOIN t_put_rules_empty_and_unalloc_locs_by_pnd tpr WITH (NOLOCK)
ON tpr.pnd_location_id = loc.pnd_location_id
AND tpr.class_id = itu.class_id
AND tpr.wh_id = loc.wh_id
LEFT OUTER JOIN t_work_q q WITH(NOLOCK)
ON q.location_id = loc.location_id
AND q.wh_id = loc.wh_id
AND q.work_type = '08'
AND q.work_status = 'U'
WHERE loc.status = 'E'
AND ISNULL(q.work_q_id, 0) = 0
AND ( loc.c3 is null or loc.c3 not in ('R','H','S'))
AND (
(
loc.type = 'M'
AND ( (SELECT TOP 1 max_sku_count
FROM t_zone zone2 (NOLOCK)
WHERE zone2.wh_id = loc.wh_id
AND loc.zone = zone2.zone) >
(SELECT COUNT(sto2.item_number)
FROM t_stored_item sto2 (NOLOCK)
WHERE loc.wh_id = sto2.wh_id
AND loc.location_id = sto2.location_id
)
OR '13098271' IN (SELECT sto2.item_number
FROM t_stored_item sto2 (NOLOCK)
WHERE loc.wh_id = sto2.wh_id
AND loc.location_id = sto2.location_id
)
)
)
OR (loc.type = 'I' AND itu.unit_volume = 0 AND itu.nested_volume = 0)
OR (loc.type = 'I' AND loc.capacity_volume = 0)
OR (loc.type = 'I' AND loc.capacity_volume >= 0 +
(CASE
WHEN 0 = 0 THEN 0
ELSE 0
END * (1 - 1)
)
)
)
--Ensure that only one item is designated to the location
AND (loc.type = 'M'
OR (loc.type = 'I' AND NOT EXISTS (SELECT 1
FROM t_stored_item sto2 WITH (NOLOCK) -- Sum the item in the location to determine volume
WHERE sto2.wh_id = loc.wh_id
AND sto2.put_away_location = loc.location_id)))
AND itu.wh_id = '895'
AND itu.item_number = '13098271'
AND clc.class_id = 'APPAREL'
AND zlc.zone = 'ALL'
ORDER BY
tpr.percent_empty_and_unalloc DESC,
loc.type,
loc.user_count,
loc.picking_flow,
loc.location_id
Based on some quick peeking at your execution plan. Based on the costs, issue 1 & 3 will give you the best ROI.
First issue: Key Lookup
On your index IDX_wh_id_status_pnd_location_id you are missing column zone in the INCLUDE part of your index.
Second issue: Implicit Conversions
In short: you are comparing columns from different types. Make sure you compare columns of the same type. If these are Foreign key columns the type in both tables should be exactly the same. If they are parameters , change the type or cast/covert them.
Third issue: Aggregation
You have an aggregation (Max, Count, Avg, ...) on [t_stored_item].[sto].put_away_location with a row estimation of 129108.
Try creating an indexed view for this part of the query with the aggregation in the indexed view. Use the indexed view instead. More info
The estimation is also far from the actual, you could try rebuilding your statistics, but it might not help. Why? Read this
Forth issue: User Definied function
You have a INNER JOIN with usf_get_item_class_dia_ovrd is it possible to write the logic of the user defined function inline? The code is often optmized when we do it inline, now the scalar function is executing row-by-row instead of set based.
Fifth issue: constant scans - actual number of rows 0
This might not be a big issue, but often this happens when expressions cancel each other out. Dummy example : 1 = 0 will always evaluate to 0 rows, so SQL server replaces it with an empty constant scan.
In complex queries this is something you might not find immediately. This doesn't have a high impact on performance but you might get better execution plans when you remove these from the queries.
If interested watch this video to get a better understanding of the query optimizer. (Kind of old, but still relevant)
Bonus: parameter sniffing
You mentioned it’s a stored procedure. Often stored procedures have issues with parameters sniffing.