msmdsrv.exe is taking 99% cpu

I've tried rebooting the server, restarting services, etc, but that only seems to help for about 5-10mins.

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed. Is there a way to narrow down what (and why) might be causing that process to hog so much CPU time and making the whole server slow?

I have seen this problem before; and I remember the solution was to apply the latest service pack for Analysis services. SP4.

Which service pack are you running?|||

So sorry I didn't include that info.

SSAS 2005 , SP1
1.8Ghz, 1.5gb ram with around 5 users connecting.
The memory usage wasn't at all bad when this happened. The msmdsrv.exe process took about 35mb of RAM, and there was 800-950mb free, but the cpu was at its peek.

|||

This happens during quering of the cube and not the processing?

I suggest that you download the Performance Guide which is pointed to at the top post in this message group.

Are you using any real time update of the cube or is it a full MOLAP in all dimensions and in the measure groups?

Regards

Thomas Ivarsson

|||

tee_user5 wrote:

When the cpu usage was that high, I profiled the cube for a 3 minute period and no queries were being performed.

The whole server is slow when this happens, not just AS.

Right now the server is operating much better and msmdsrv is using 300mb. I'm not sure why it wasn't using more ram yesterday.

Note: sqlserver is also on this server and it's not an option to move it at this time. It is now using 600mb of ram when the server is stable.

Update:
I just saw the msmdsrv process take 900mb+, leaving the available amount of memory less than 1mb. Then what happened is that msmdsrv released the memory and then cranked on the cpu. It's now at 99%.

If I look at the event logs, there is an out of memory error logged from today (it's in there for yesterday as well)
--

Event Type: Information
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 2
Date: 2/16/2007
Time: 10:12:31 AM
User: N/A
Computer: name
Description:
The system is out of memory.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

--
I understand that it would be advisable to add more memory - but I'm not sure how quick that can be done. Also, why did the process basically give up? It had 900mb of memory, but then it dropped it all and worked down the cpu and continues to crank on it at 99%

|||

More info:

When the server acted up, it was always from the same user. I looked at their setup, and their permissions are done through windows domain groups and the 2 groups in which the user is placed put them in 2 different roles - which each have access to 1 section of a customer region. I took the person out of one of the roles and tried access again - there was no slowness on the server. Any clue as to why something like this would happen with 2 roles hooked up?

|||

I can recreate the problem by putting a test user in 2 different Cube roles - this doesn't even involve windows groups nor any measures!

Each role is for 1 region - east and central.

When I plop on the region attribute for a display, the server starts to take a dive.
Note: only the region attribute was used. I would expect 2 different regions to show. Here is the code that the cube browser creates:

CREATE SESSION
SET [sales].[{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0] AS
'
{
{ [Customer].[Region].[All] },
AddCalculatedMembers([Customer].[Region].[Region].MEMBERS)
}
'

SELECT
NON EMPTY [{AF0CD6C2-0256-4225-AE4C-CF8C3091654D}Pivot2Axis1Set0]
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS
FROM [sales]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR

I'm going to see if SP2 helps, but please throw out any ideas. This problem is detrimental to the server.

|||

It is very hard to tell anything about your problem without knowing more about the limit of RAM on your server and how complex the cube is, regarding number of dimensions and each fact table size(number of records) in the cube.

Regards

Thomas Ivarsson

|||

That info was in my 3rd post.
There is little complexity because we have only 3 regions and I could create the problem just by looking at that attribute.

SP2 seems to have fixed the issue, but I'm needing to do more involved testing to make sure it's no longer an issue.

|||Hi

I'm having a similar problem. I have a machinne with 2 fisical processors and two logical, 4GB of memory RAM and 800 GB of free disc space. I'm describing Power Eddge Dell Server. The problem apears when a user perform a query to the cube, no matter the client tool used to make the query the server uses all it's memory and processor capacity an then pased a few minutes It aborts the operation and disconect any user. At this moment they are using Officce Web Component but I'm using the SQL's browser and I have the same problem.

I've made some test using two diferent machins. One of them with SQL Server 2005 SP1 y the other one with SQL Server 2005 SP2 and the results are 100% similar.

I really apreciate if anyone can help me. Thank you very much.|||

does it differ any if the simpliest query is executed?

select from [cube name]

|||

It does not differ depending the complexity of the query. I get the problem, sometimes, only changing then members of a dimention, already included in the query. At this moment the case is on Microsoft Support Engeneers hands. If I receive a useful response from them I will let you know about it.

Thank you very much for your help.

|||

Hi,

I'm afraid I'm facing the same issue.

We are running a 64bit W2003 Server Enterprise with SQL2005 Enterprise Edition (64bit, SP2) on it. The System has 4 CPUs and 8GB of RAM. On the system we are running a Datawarehouse and a SSAS DB.

Now for the error: When I query an OLAP Cube (no mather if with Excel, OWC or the internal Cube Browser in VS) the client application randomly stops responding and you can see that one of the 4 CPUs is peaked on 100% and the memory consumed by SSAS is constantly increasing.

The memory consumption goes up to about 7,5 GB RAM, then the memory is paged (I can see an entry in the event log) and the memory is increasing again, now starting vom 100K Memory. The whole system also is non-respondive in these cases, you can hardly work on the system. The only sol?ution i found so far is restarting the SSAS Service.

The occurace of this error is not bound to a special query or complexity ... after about 5 minutes of "normal" analysis (drilldown, filter, ...) the problem occurs.

Is there an updated information about this issue available?

Greetings Montechristo

|||

We also have this problem.

Using a Intel Xeon QuadCore 3.4 GHz CPU with 3.5 GB ram and W2003Server, SQL2005 SP2.
SSAS and SSRS on the same server.

After a while (at least once a day, with 30 something users average a day) the msmdsrv.exe climbes to ~33% usage on each core.
It also eats up almost all available memory.

Only a restart on SSAS releases it.

During the "hangup" a "SQL server profiler"-session shows no activity.

Any idas?

|||

Since there seem to be a lot of implementations that suffer from this issue, is there any official comment/workaround/solution to this problem available?

Greetings