Query makes the application respond with request timeout

Hans 2018-02-27 08:09:35

SDyckes2 2018-02-27 20:49:30
Hans,

You have provided the execution plan for the query that timed out, which provides an incomplete picture. What is the application timeout? Have you run the code to completion from SSMS? Or in Plan Explorer? By capturing the Actual Plan in PE, you will be offering a much clearer view of where in the plan the time and resources are being spent.

Based on the information provided, the Hash Match in the plan was chosen based on the number of estimated rows (424 and 666). The optimizer will request a limited amount of memory based on these estimates. Once the actual rows (919,466 and 919,465) are being processed by the Hash Match operator, a TempDB spill will occur to accommodate the much larger memory requirements, slowing down the query.

The large discrepancy between the actual and estimated rows could be due to out of date statistics, but could also be due to a Join Predicate not being optimal. I would then focus on limiting the number of records being returned by both Object9 and Object15. The sooner you can filter down the total number of records that will need to be processed, the less work that will be done by SQL and the faster the query will perform.

You may also investigate breaking the query up into smaller pieces and not perform all the joins in one query. You may be able to reduce the total number of records that need to be processed by filtering the records soooner.

Hans 2018-02-28 00:30:46
Hi, I changed the indexes so that I got a merge join instead of hash join, the query then finished in 90 seconds instead of 300. I also installed PE to get a clearer view. Thanks!
SDyckes2 2018-03-01 20:00:14
That is fantastic! You probably see a lot fewer records being processed!!