Memory Problems Windows server 2008/sql server 2008

Posted on 2010-09-22
Medium Priority
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.

Question by:SaiRam77
  • 3
  • 2
  • 2
  • +4

Accepted Solution

mquiroz earned 200 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
LVL 14

Expert Comment

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

Author Comment

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.

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

LVL 14

Assisted Solution

SIM50 earned 300 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.

Assisted Solution

avirups earned 300 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.
LVL 52

Assisted Solution

by:Mark Wills
Mark Wills earned 500 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/

LVL 52

Assisted Solution

by:Mark Wills
Mark Wills earned 500 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


Author Comment

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.
LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 300 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...
LVL 52

Expert Comment

by:Mark Wills
ID: 34649236

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

Expert Comment

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

- thermoduric -
EE Community Support Moderator


Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
I’m willing to make a bet that your organization stores sensitive data in your Windows File Servers; files and folders that you really don’t want making it into the wrong hands.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
Suggested Courses

598 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