Solved

Memory Problems Windows server 2008/sql server 2008

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

 
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

Swamped with email signature updates?

Have you been given a load of changes to make to your users’ email signatures? Having to manually implement multiple signatures for every department? Let Exclaimer save you from being swamped with email signature updates!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
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…

911 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

19 Experts available now in Live!

Get 1:1 Help Now