August 3, 2014 at 8:54 pm
I am running an MDX query from SSMS "Server1" retrieving data from an Analysis Services cube located on a different server "Server2". and get a "System.Out of memory" exception. Using Reporting Services (from Workstation) to report the same data from the same Analysis Services there is no exception.
I would like to know if the memory shortage is on the SSMS server (Server1) or on the Analysis Services Server (Server2) and the possible resolution. If the solution is to increase virtual memory on one server or another I can address this but the only things on the web I can find do not address the server to reference.
Thank you in advance.
August 3, 2014 at 9:11 pm
Is the query returning a crazy huge hunk of data?
Can you try running SSMS on a workstation instead of the server?
Most likely it's a client side error with SSMS, not really a "SQL Server" out of memory error.
The Redneck DBA
August 4, 2014 at 10:35 am
Jason,
Thanks for your response. I am a newbie using MDX for retrieving data and I hope to answer your questions:
The amount of data retrieved is approx. 40,000 rows and retrieves up to 190,000 fine using SSRS only.
I actually "think" I am running from the workstation vs. the server (I connect to the server using the workstation SSMS application and then connect to the Analysis Services server). The workstation currenty has 20 GB of virtual storage available.
I hope this addresses your questions and I really appreciate any help/advice you may have.
August 4, 2014 at 7:40 pm
Howard C. BAchtel-438731 (8/4/2014)
Jason,Thanks for your response. I am a newbie using MDX for retrieving data and I hope to answer your questions:
The amount of data retrieved is approx. 40,000 rows and retrieves up to 190,000 fine using SSRS only.
I actually "think" I am running from the workstation vs. the server (I connect to the server using the workstation SSMS application and then connect to the Analysis Services server). The workstation currenty has 20 GB of virtual storage available.
I hope this addresses your questions and I really appreciate any help/advice you may have.
Even if you have 20GB of RAM on your workstation, I believe SSMS is still a 32-bit application, so it won't be able to use that much. Whenever I've seen that error, I was pulling back tons more data than I really needed from OLTP databases. I assume the same situation probably applies OLAP/MDX queries. Can you maybe pull it back in smaller chunks if you really need all of that data? Or maybe return fewer fields? Or if you're really needing the data, maybe make an SSRS report and export it to whatever format works for you? (Excel, PDF, etc.)?
The Redneck DBA
August 5, 2014 at 6:15 am
Thanks for the suggestions. I have reduced the size about as much as I can to get any form of meaningful result (e.g. 1 of 23 sites and 1 of 6 measures). I can create and run the extract in SSRS but do not have a good handle on being able to run and export automatically using SSIS and execute on schedule using SSMS agent.
Would you have any suggestions on where I can find guidance on that approach?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply