SSAS page file usage
-
Hello all. I've worked with SQL for quite a while, but new to SSAS. Hopefully someone can help me figure this out.
When one of my users runs a fairly large query on a cube, it appears to me that SSAS grabs huge amounts of the page file, which eventually results in locking up the server and me having to kill the SSAS service. There is still a large amount of physical memory available on the server during these times, which doesn't surprise me since I set the Memory\TotalMemoryLimit to 15% (of ~100GB). But how can I stop SSAS from grabbing large amounts of page file? The screenshot below was taken during one of the times when SSAS was consuming the page file. Note the SSAS process at the top of the list in ProcessExplorer (using 57GB private bytes and 28GB working set). Also note that task manager shows ~13GB available physical memory. What is going on here?
-
What version of the OS and SSAS are you running?
Steve.
-
Also, have you run the profiler to look at what is happening when your user/s are running this particular query? Did you build this cube, and if so, do you have aggregations and attribute relationships set up correctly?
Steve.
-
Steve - thanks for the input. I'm running SQL 2008 R2 on Windows Server 2003 SP2. I did build the cube, and yes, it is entirely possible that something is wrong with it. But I have done some basic queries against the cube and what I have seen looks right.
Our users aren't writing raw queries against the cube, they are using a 3rd party tool called Tableau. I have found how to reproduce the condition in Tableau, so I could trace to see what the query is. My MDX knowledge is very limited though.
-
THe trace is more to see how the resultset is being generated (ie reading from aggregations, aggregating base data on the fly etc). Hence, if you know you have aggregations set up, depending on the query, you should expect to see the result being obtained from the aggs (again, this is query dependent).
You prob want to look at the SSAS Performance Guide (google that literal title) - there's a few mentions of the page file and paging, one in particular relates to a setting for SSAS, when running on 2003, that could impact how and when it pages (in particular, forcing it not to). This doesn't relate to win2k8 though and the details fo why are in there.
You also prob need to identify what you're trying to fix. To me, the paging would be a symptom of a different problem (cube performance) but if you are happy with your cube design and setup, then the paging becomes the issue that you want to fix.
Steve.
-
Are you using Windows 64xbit, if yes you may need to look at the LOCK PAGES in Memory option which will prevent it from using the page file.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483
-
Sorry forgot to mention Enterprise edition?
-
Yes, I'm on x64 and SQL Enterprise. The SQL service user account is already granted the lock pages in memory right.
I've done a little more testing and it looks like when this particular query kicks off, the physical memory usage shoots up until it reaches the limit I imposed (TotalMemoryLimit) then continues to grab memory from the page file until the server becomes non-responsive. Even trying to cycle the SSAS service is non-responsive.
I tried setting the "PreAllocate" setting to 15 (from the Analysis Services Performance Guide). All that did (not surprisingly) is preallocate the specified amount of memory, but once the working set arrived at the preallocated amount, it just kept climbing above it. Steve - I assume this is what you were referring to - it did mention that PreAllocate is not effective on WS2008, but more effective on WS2003. I don't see how PreAllocate will cap the usage though.
There's probably something wrong in my cube, but I find it hard to believe that there is no cap to put on memory/page file usage.
My cube does have the potential for large queries. The data source is an inventory snapshot, consisting of 1 snapshot per day for about 500,000 items, over about 1 year. I am aggregating all measures as sums and avgs by day, month, quarter, and year.
-
Also, from the trace, this appears to be the query that causes all the problems:
SELECT{[Measures].[YTD Qty Itm Sold]} DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS,
NON EMPTY CROSSJOIN(
[Dim Branch].[Br Nm].[Br Nm].AllMembers,
CROSSJOIN(
[Dim Date].[Date].[Date].AllMembers,
[Dim Inventory].[Itm Num].[Itm Num].AllMembers)) DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON ROWS
FROM [Inventory]
WHERE
StripCalculatedMembers({[Dim Date].[YQMD].[Calendar Year].&[2010]:[Dim Date].[YQMD].[Calendar Year].&[2011]})
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply