Implementation Plan Download link: https://www.dropbox.com/s/3spvo46541bf6p1/Execution%20plan.xml?dl=0
I am using SQL Server 2008 R2
I have a rather complicated stored procedure that requests too much memory when it is executed. Here is a screenshot of the execution plan:
http://s15.postimg.org/58ycuhyob/image.png
The basis of the request probably requires a lot of customization, as evidenced by the massive number of evaluation lines, but this is beyond the point. Regardless of the complexity of the request, it should not request 3 gigabytes of memory after execution.
How to prevent this behavior? I tried the following:
- DBCC FREEPROCCACHE to clear the plan cache. It brought nothing.
- Setting the RECOMPILE parameter at the SP and SQL level. Again, this does nothing.
- Debugging with the MAXDOP parameter, 0 to 8. The same problem.
The query returns approximately ~ 1k rows on average, and it scans a table with more than 3 million rows, with about 4 tables joining together. Executing a query returns a result in less than 3 seconds in most cases.
Edit:
Another thing that uses prompts for queries is not viable for this case, since the parameters for our case are very different.
Edit2:
Downloaded execution plan on request
Edit3:
I tried to restore / reorganize fragmented indexes. Apparently, they were few, but nothing serious. In any case, this did not reduce the amount of memory provided and did not reduce the number of evaluation lines (if this is somehow connected).