Solved

Memory Problems Windows server 2008/sql server 2008

Posted on 2010-09-22
13
823 Views
Last Modified: 2016-02-15
Dear All.

We have the ssas cube server was setup; the server has been very sluggish.  Under the Task Manager for the server the physical memory has been at about 99% for most of the time.  On a few occasions it got so slow that we resorted to restarting the machine.  This seemed to get things faster but when I opened up the task manager again, I noticed that the RAM taken up started at about 4GBs and steadily climbed back up to the 32GBs that are in the machine.  When I looked at the processes it seems that sqlservr.exe is taking up about 22GBs, and when I matched up the PID with the instance I noticed it was the process that was servicing the localserver instance of SQL server.

I restarted the sqlservr.exe process and the physical memory went from 99% to about 36%, it stayed there for some time but eventually started climbing up again, and currently it is at 88%.  

The above problem I am not able to detect and any reccomendations highly appreciated.

Thanks.
0
Comment
Question by:SaiRam77
  • 3
  • 2
  • 2
  • +4
13 Comments
 
LVL 7

Accepted Solution

by:
mquiroz earned 50 total points
ID: 33736493
for mssql to take a lot of memory is not always a bad thing, the performance issue has more to do with index usage and a proper structure of the database
0
 
LVL 14

Expert Comment

by:SIM50
ID: 33738883
In SQL server options, you can specify how much memory to use.
0
 

Author Comment

by:SaiRam77
ID: 33739233
Dear SIM50,


Can you elaborate on this statement"In SQL server options, you can specify how much memory to use."
and let me know how to do this.

Thanks
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 14

Assisted Solution

by:SIM50
SIM50 earned 75 total points
ID: 33739464
In SQL server management studio or enterprise manager (depends on your version of SQL server), right click on the SQL server and go to properties. There is a tab called memory. You can specify minimum and maximum memory allowed to use by SQL server.
I would also run the tool called SQL Server Profiler to see what is causing this. This tool is a part of SQL server.
0
 
LVL 8

Assisted Solution

by:avirups
avirups earned 75 total points
ID: 33740992
Setting the Max Memory is a good idea but it will not solve your problems here, because if the sql server needs so much memory to run its operations and it does not get it, it will eventually slow down your processes.

I agree with the first post partially that you should be looking to tune your SQL Server for better performance so that the memory utilization is optimal and then the SQL Server if it does not require so much memory will not acquire it.
Following are a list of probable areas which may cause SQL Server to use up more memory than normal:
1. BAD indexing;
2. BAD queries;
3. I/O bottle neck in the server;
4. etc.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 125 total points
ID: 33743899
Yep, would set max memory - have a look in books on line on how to do that. If you have 32gb total then do not allocate any more than 28GB (and probably look at 26 to start with).

Also, there was a recent discussion with MVP's : http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9c484963-bdff-42ce-9bc9-2f1e32de0c7f

There is apparently a problem where a hotfix is available. Might be related to yours, though, yours seems to be simply consuming all the memory, and it does need some memory management.

Have a look at the KB article : http://support.microsoft.com/kb/974609/


0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 125 total points
ID: 33744703
Also worthwhile having a read of : http://sqlcat.com/technicalnotes/archive/2010/02/08/microsoft-sql-server-2008-analysis-services-consolidation-best-practices.aspx

Especially given the cube comments. How much work does this server do ? Is it both the transaction server and the Analysis Services server ?

There is a link to a word document about half way down dealing specifically with SSAS performance : Analysis Services 2008 Performance Guide

Also read the BOL for checking memory, you do need to gather / check some of the memory counters : http://msdn.microsoft.com/en-us/library/ms176018.aspx

0
 

Author Comment

by:SaiRam77
ID: 33746048
I did some research and i got the results from a query which Calculates average stalls per read, per write, and per total input/output for each database file.

Please see the attached file and recommend me appropriately.
Copy-of-VBS-SQLServerStalls.xlsx
Copy-of-VBS-SQLServerWaits.xlsx
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 75 total points
ID: 33754061
<<The above problem I am not able to detect and any reccomendations highly appreciated>>
Based on the information provided, IO seems more of an issue that inner memory pressure.  Please confirm that information by looking at IO contention (Performance Monitor --> Physical Disk --> Avg sec per Write/Read) and design a file storage strategy to support your application needs .

Hope this helps...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 34649236
Hmmm...

in SQL 2008 there are some memory management issues which do need to be managed.

there are other factors such as IO than can appear to be a memory (or CPU) problem which does require further analysis.

SQL server efficiency is based on a number of things such as indexing.

I would recommend a split :
http:#33736493  50 points
http:#33739464  75 points
http:#33740992  75 points
http:#33743899  75 points    as accepted
http:#33744703  50 points
http:#33754061  75 points
0
 

Expert Comment

by:thermoduric
ID: 34709720
Starting the automated closure procedure to implement the recommendations from the participating Expert(s).

- thermoduric -
EE Community Support Moderator
http://www.experts-exchange.com/Q_26756950.html

0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question