SSAS out of memory
-
Hi All
My Analysis Services in SQL2005 is acting up. It runns fine for the most part, then intermittendly grabs all available memory and then all available processor.
The hardware is dual zeon and 4 GB of ram. SSAS shares the server with SQL2005. SQL2005 and SSAS was on a virtual machine together during a testing phase of about 2 months and I did not encounter any issues then. So a SQL2000 to 2005 migration was done and then this started happening. The migration was a clean install of Windows then SQL2005 the 2005 cubes are also a redesign and not a conversion of the SQL2000 cubes.
The message in the Windows error log is: The system is out of memory.
Have anybody else encounter this?
Kindest regards
Jacques
-
I have seen this many times. Someone please correct me if I am wrong, but here is how I understand this - SQL Server will, out of the box, take up to two gigs of memory, as will SSAS. The OS needs some memory as well, so, 2 + 2 + OS could be not enough memory. We ended up splitting off the SSAS part to its own server.
-- Cory
-
I have found this occurs after processing the cubes, restarting the service periodically cures the problem. You can set SQL Server to use x amount of memory in the server options, this would prevent AS grabbing all available memory.
Phil Nicholas -
Interesting enough during the testing phase on the virtual machine that was less than half the spec of the production machine no such issues have been found. SQL grabs around 1.8 GB of ram and SSAS when running stable around 500 to 600 MB. Would SP1 mak any diffs?
-
I know it's a lot of reading but if you check out the new (AS 2005) Performance Guide it covers off the memory usage for both processing and querying and also offers hints on how to determine what to set your mem thresholds at. It also goes in to some detail on what's flushable memory and whats going to stay there until the service is recycled (e.g. metadata).
You could prob look through the TOC and go straight to the relevant section(s). Perf guide link -> here.
Cheers,
Steve.
-
I am still stunned that the virtual machine running on VMware did not have any issues running with 2GB ram operating both SQL and SSAS on Windows 2003 and that the new machine with 4GB ram is giving so much hassles.
I am reading through the doc thanks as mentioned it is a lot of reading
-
Have the data volumes changed from Test to Prod? Did you do the install and config of both systems? Do you still have the virtual machine to review it's server settings against what you've got now on the physical server?
Steve.
-
If you are running windows 2003 (32 bit only) on the new 4GB machine, have you put the /3GB switch in boot.ini? This may help as Windows doesn't take advantage of all that nice memory without the command line switch. 64 bit is much better.
-
This is a Windows 2003 server and I have not enables the /3GB switch but will do so. Both installations were default and no configs were done. This was done so that issues like this could be avoided. (Past expierience taught me a hard lesson or two when changing configs). The data volumes stayed the same. The 2000 server is also still opperational on a virtual machine now to try and assist user acceptance of 2005. AS2000 still runs just as stable as it did with some performance niggles. (Decreased the aggregations to help processing along)
-
Jacques,
Can you explain the situation again? Were SSAS 2005 and SQL 2005 installed on the one virtual server? If they ran together for 2 months *before* the migration was done, what were the instances doing? (e.g. am assuming that before the AS2K to AS2K5 conversion was done there was no activity [builds, querying etc] on the services).
Were your users querying AS2K5 on the virtual server? If yes, has the number of mix of users changed who are querying the new server?
Lastly, have you turned on the profiler for AS2K5 (and SQL if you need) to determine whether it's processing time or querying time when the memory issue is arising? It's not clear from what you've said as to whether it could be an errant user query that is causing the process to consume a lot of memory and CPU or in the cube build stage/s.
Steve.
-
Hi
Sorry for the delay in replying. But it took me 2 days to try and trap this.
A user ran a query that gathered information from the mtd and history sales partition.
The shrinkable memory then sat at 43 MB and the non shrinkable climbed up to 1.78 GB where the where the limit high is 1.67 GB and the memory low 1.57. In the profiler I can see that the query was sent and the information started reading from the history partition but completed with an error.
It seems that the Time Hierarchy is where the problem lies.
The hierarchy is Fiscal year, Season, Quarter, Month, Week then Day. Here is an example
FY - 2007
Winter - 2007
Quarter 03
February
FY - 2006
Winter - 2006
Quarter 03
February
If the users use the hierarchy and selects both February'ies without the top levels the query start jumping round and never finishes hence munching up all available resources even if you kill the connection to the server and even if the top levels in the hierarchy are also selected it does the same. But if you selected the different levels and do not use the hierarchy by selecting the fiscal year 2006 and 2007 from the "fiscal year" level and the month of Feb from the "months" level all works fine. It is as if the server does not know what to do when you select both February'ies from the hierarchy and it does not know what to do with the data or where to sum it.
I assume that the users did not use the system on the Virtual machine as they should have and that this has only now come to light. Now the challenge is how to fix the hierarchy.
-
At least you know it's a query issue
Have you made sure that your attribute relationships are all ok? The perf doc actually outlines an issue wit5h how time dims are put together (or the way we 'used' o do it) where we have previously assumed that using a key of 'February' or even '02' is ok but for uniqueness we really should use a key field like 200702 or 200602 to differentiate/uniquley ID each 'February'.
Be interested to hear what it runs out to be. Did you run a trace while the query was running? This could show you what the server is trying to do with the users query. If you get them to run the 'working' approach and then the 'not working' approach you can compare the MDX generated as well as the execution of the query.
Steve.
-
I have made some dimension modifications to try and sort it our. Still reading through the Perf doc though. I read what I needed to for the memory and now reading through the doc for the hierarchy.
The issue I found with profiler is that the server "hung" up before it could log the query. That is why it took me so long to find it and only after a user phoned me and said that things are not right we could replicate the issue by following the same steps as he did.
The server hangs up even when you are still browsing the dimensions there is no query yet. Will keep you posted on the progress (Pardon the punn)
-
Hi All
After the dimension changes that were done the above memory errors did not occur again. The server from a SSAS point of view is running smoothly at the moment.
Thanks for all the assistance guys.
Jacques
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply