Recently a user is using a BAQ to retrieve a large dataset via OData Feed.
The user pressed refresh a few times and caused our App server to run out of memory.
This is the first time I encountered the App server out of memory, the query became suspended on the SQL server so SQL server didn’t run out of memory but the App server had the IIS process that consumed all the memory. Is there a way to prevent the App server from consuming all the memory?
There are some settings in IIS to reset appserver if the memory gets too large however you should probably stop the BAQ from doing so much data instead
What exactly is this BAQ pulling. Maybe you need CDC instead.
Centers for Disease Control …?
The BAQ is just pulling Sales data for some simple reporting in EXCEL.
Interesting, that shouldn’t be too bad, kinda odd if you run out of memory.
But then the user wanted historical data as well which caused the query to return a lot more rows.
After what happened on Monday, I have asked all users to limit their used on Odata as it bought down my server.
Toby, it’s odd that the query wouldn’t time out before consuming all your app server memory.
Do you think I missed some settings in the IIS area?
My question is, how long is it taking that query to finish?
I’m with Utah. I bet there are some improvements that can be made to make the BAQ more efficient.
Separately, sales data doesn’t change. In these cases, it might be worth looking into a system that you upload immutable data and report from there. Constantly downloading the same data over, and over again is rather inefficient.
It’s very frowned upon.
Use $top and $skip to download data in chunks
I am not suggesting you touch any IIS settings all because of a BAQ.
I’d tread carefully with that thought process.
It’s worth looking into what the demand sources on memory are if SQL Server is sharing resources with other processes. SQL won’t hesitate to play hungry hungry hippos with all the RAM if max_memory is left at default, and if that’s it then it’s time to dial back its allowed memory so the rest of the server always has enough to function.
On Monday, when the App server ran out of memory and crash.
I check on our SQL server, I had a lot of suspended query. I suspect it was the user pressing refresh multiple times and caused this to happen.
In the mean time, I have limited the amount of data being returned to 1 year. I will go and do some testing on the test server to let you know how long it takes for that particular query to finish.
@josecgomez , Didn’t you see something similar not long ago?
It is not that important in this case.
I can write a query that will finish in 3 hours and will return 1 record - it won’t result in out of memory error.
Or I can create a query that runs quickly and returns gigabytes if data. That one will result in app server crash, if total amount of memory used for its processing exceeds server’s physical memory
