msmdsrv.exe taking substantial memory

Wondering if you have this problem or not, it seems SQL 2005 AS msmdsrv.exe taking lots of memory, I looked at 64 bit server, and 32 bit server, compare with other SQL server services, msmdsrv.exe runs about 500,000 - 600,000 K on 64 bit server, vs. 87,000 k for sqlserver.exe, and 67,000 k for reportserver.exe, and connect to AS through Management Studio also takes long time. I have default setting for all services.

I have one cube database which is about 240 MB in size and do full process, we have all SQL 2005 services in one 64 bit box with 8 way processor and at least 4 gz memory.

I am getting a little frustrated by this, any pointer?

Thanks

Not to become an emotional help, just to clarify the situation

Analysis Server is completely different from SQL server. It is not relational but what is called OLAP and DM server. It works and implements completely different set of data structures. It is queried using different query language- MDX.
One example: Nature of relational databases is such that because of the frequent updates you often you cant caches of your data in memory. For Analysis Server the situation is completely different. The more things it can put in memory, the better- the faster it can answer user queries.

In many enterprise applicaiton it is recommended you place you Analysis Server on different machine from your relation database.

I understand that you have a small cube, but having all the servers running on your machine, fighting for resources can be cause you see for some of the slowdowns.

HTH

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Thank you so much for your comments. For right now, we have to put everything in one server becuase of licenses issue. So if I put all sql server services in one server, do you recommend set up memory limitation on Analysis Service can use instead of default setting?

I remember default setting is 75 or 80% of all avaliable memory, should I change that to lower percentage,so it will not affect other running servies?

Thanks

|||See whether this KBA http://support.microsoft.com/kb/922852 & http://support.microsoft.com/kb/900390 are any help here.|||

If SSAS is only using 500-600 Mb on a 4Gb box, it does not sound like there is too much of an issue. I would be more worried about why the sqlserver is so low.

There are a couple of perfmon counters that you can check under SQL Server: Memory Manager object called "Target Server Memory" and "Total Server Memory". The Target amount is what the server "wants" and the Total is what it is currently using. Ideally these should be the same value, but if your system is under memory pressure your Total will be lower than your target.

|||

Thanks all for your very helpfull advices. This is the test I did againist our test server, which of course not much usage. But I am a little concern about fighting resources among Database engine, RS, AS and SSIS. Have you all setup memory limit for your AS other than default setting?

Thanks