Solved

Memory Problems Windows server 2008/sql server 2008

Posted on 2010-09-22
13
817 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
Comment Utility
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 13

Expert Comment

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

Author Comment

by:SaiRam77
Comment Utility
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
 
LVL 13

Assisted Solution

by:SIM50
SIM50 earned 75 total points
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
<<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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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

10 Experts available now in Live!

Get 1:1 Help Now