Query makes the application respond with request timeout

Any help with this will be very appreciated, thank you!

avatar image By Hans 6 asked Feb 27 at 08:09 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first


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.

avatar image By SDyckes2 96 answered Feb 27 at 08:49 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

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!

avatar image By Hans 6 answered Feb 28 at 12:30 AM
more ▼
(comments are locked)
avatar image SDyckes2 Mar 01 at 08:00 PM

That is fantastic! You probably see a lot fewer records being processed!!

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Feb 27 at 08:09 AM

Seen: 54 times

Last Updated: Mar 01 at 08:00 PM