Memory Problems Windows server 2008/sql server 2008

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

LVL 14

Assisted Solution

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.

Assisted Solution

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.
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 :

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 :

LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 125 total points
ID: 33744703
Also worthwhile having a read of :

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 :


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

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now