I have updated the statistics on all the indexes but this query is still taking close to 40 seconds to execute. I need it to be under 20. I am not a DBA and very new at reading execution plans. I would appreciate any help I can receive that would help me improve the performance of this query.
Your query only uses about 2 seconds of CPU time. You can view that in the root node of the execution plan. It spent about 30 seconds waiting on various things.
7915 ms of that wait time is for the ASYNC_NETWORK_IO wait type. This wait type occurs when SQL Server is ready to send more data to the client but the client hasn't indicated that it's ready to receive more data yet. This can commonly be seen when viewing large result sets in the SSMS results grid. If you're seeing this same type of wait in your application then look at your code that processes the result set that's returned and check if there are unnecessary columns getting returned. Otherwise you may find it helpful to discard the result set to get a more accurate measurement of query performance. As is, you lose 8 seconds out of your 20 second goal.
You spill a small amount of data with those hash joins, yet query execution dramatically slows down as a result. I can arrange a similar result on my local machine:
The spill and join take about 0.289 seconds for me. That's about 33 times faster than what happens on your server. I understand that you may not have any control over what cloud platform that you use. However, the time sink of tuning queries by hand needs to be balanced against other solutions. The query only spends 2 seconds of CPU time. If you had better hardware then it could easily finish in under 20 seconds with no code changes required. To be completely frank with you, if someone gave me a VM with performance as poor as what you're seeing I would refuse to use it.
If you can't get better hardware then you should be able to test query performance without the spill by adding a
MIN_GRANT_PERCENT = 99 query hint. If performance significantly improves then try to improve the cardinality estimates going into the hash joins. The reason that you see the tempdb spills is that the memory grant is too small because the actual number of rows is about 20X larger than the estimated number of rows:
This complicated predicate may be contributing to the problem:
@Now BETWEEN OPL.StartDateUTC AND COALESCE(DATEADD(MILLISECOND, -3, CAST(CAST(DATEADD(DAY, 1, OPL.EndDateUTC) AS DATE) AS DATETIME)), DATEADD(MILLISECOND, -3, CAST(CAST(DATEADD(DAY, 1, @Now) AS DATE) AS DATETIME)))
One thing to try is putting the results of the join of the
Organization and the
OrganizationProductLanguage tables into a temp table. With a temp table, the query optimizer will know that you're getting 12 rows from that step instead of 1.41312. That alone may increase the estimates enough to avoid the tempdb spills.
If performance continues to be unacceptable after fixing the tempdb spills then try getting rid of some of the key lookups by including additional columns in the nonclustered indexes already in use. I expect most of the impact to come from adding the
InactivatedDateUTC columns to
[ProductAsset].[UX_ProductAsset]. Less benefit should be expected from adding
[AssetCacheData].[IX_AssetCacheData]. This may improve query execution time more than typically expected because this server applies such a large penalty to I/O.