Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server 2008 Memory Consumption

Posted on 2011-09-19
13
Medium Priority
?
741 Views
Last Modified: 2012-05-12
I have a 64-bit Windows Server 2008 R2 Standard O/S, installed as a virtual platform.  The O/S reports that it has 12Gb of RAM installed - I don't know how much RAM the physical box has available to it.

The platform is used as a dedicated db server, running Microsoft SQL Server 2008 R2.

The db hosts 2x databases - a test db and a prod db, both for the same application.

The same application is also running on a separate db server, which is not virtualized.  That server is running 64-bit Windows Server 2003 R2 Standard editition, SP 2.  That box has 8Gb of RAM installed and is running a 64-bit version of Microsoft SQL Server 2005.  Again, its a dedicated db server and runs a PROD and TEST instance of the same db.

The problem that I have is that the 2008 platform needs to be re-started at regular intervals because it consumes all the memory - literally within a few days.  The 2005 platform hasn't been rebooted in years and consistently reports that approximately 8.5GB is being consumed.  So I don't believe its the application which is consuming the memory - indeed, no additional software has been installed on either db server.  In both cases, it is just the O/S + SQL Server.

When I re-start the MS SQL Server service on the 2008 box, the memory reduces from about 11.7Gb down to about 1.5Gb but as I say, within a few days its back up to 11.5+ GB.

When it closes in on the 12Gb limit, the box becomes unresponsive via remote desktop and starts to throw error message in SQL work sheets.

One issue which may or may not be related.  On the 2005 platform, I have a maintenance plan which does a daily full backup & backs up the transaction log every hour.  This works fine on the 2005 box but is also set up on the 2008 and has never worked.

Help...?
0
Comment
Question by:LogistixSW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 3
13 Comments
 
LVL 13

Accepted Solution

by:
dwkor earned 1000 total points
ID: 36560709
This is completely normal (that SQL Server tries to consume all memory). But the fact that box becomes unresponsive is strange. What errors do you have in the log? Do you use external stored procedures and/or open xml? Is there also the possibility that memory is overcommitted on the host?

One thing you can probably do as the first step - limit Max server memory in server/memory configuration (server properties). Try to find what setting works best giving as much memory to SQL as possible
0
 

Author Comment

by:LogistixSW
ID: 36560793
Thanks for the quick response.

All the memory settings should be  the defaults - I haven't purposefully changed them.

So on the General tab, Memory is showing as 12288(MB)

On the Memory tab, The configured values radio button is checked and:

"Use AWE to allocate memory" is not ticked.
Minimum Server Memory = 0
Maximum Server Memory = 10240
Index Creation Memory = 0
Minimum memory per query = 1024 KB


On the Processors tab (Configured values):

It has 4x processors (CPU0 through to CPU3).  
Maximum work threads: 0
Boost SQL Server priority: Not ticked


Does this help at all?

Jaime
0
 
LVL 13

Expert Comment

by:dwkor
ID: 36561210
Well, it's pretty straightforward configuration - everything should work fine. But again, there are a few factors that we don't know yet. That max server memory does not apply to a few different things that can make real memory usage higher than specified. You can try to lower it a little bit.

In any case, even if your server is under memory pressure, you should see warning messages in the log and performance degradation - system should not become unresponsive. Is there anything specific in sql server log and/or event viewer that can help to pinpoint issues? What exactly are the error messages you were talking about before.

Another big question mark is host memory configuration. You can see some "interesting" things if memory is over-committed
0
Does Powershell have you tied up in knots?

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

 

Author Comment

by:LogistixSW
ID: 36561340
OK, I've lowered the maximum memory from 10240 --> 10000

Will wait for problems to start happening (assuming that they continue) and will feed back information from logs, SQL worksheets etc in due course

Thanks
0
 
LVL 13

Expert Comment

by:dwkor
ID: 36561472
I'd start with something smaller - perhaps 8000-9000MB and rather increase it later if system works stable.
0
 
LVL 10

Expert Comment

by:Ramesh Babu Vavilla
ID: 36565196
please check the available mbytes also ,
perfmon-> add counter-> memory-> available MBytes

sql server 64bit is a bad consumer of memory, if you check it by using
dbcc memorystatus
you can see the max memory occupied is SQL Server BUFFer manager
min and max memory  u keep is only to buffer manager.
0
 

Author Comment

by:LogistixSW
ID: 36565683
See attached, this is what Perfmon reports
Capture.JPG
0
 

Author Comment

by:LogistixSW
ID: 36565690
These are the results from dbcc memorystatus
dbcc-memorystatus-20Sep2011.rpt
0
 
LVL 10

Expert Comment

by:Ramesh Babu Vavilla
ID: 36565732
Column1      Column2      Column3
VM Reserved                              12615680            
VM Committed                             10505984            
Locked Pages Allocated                   0            
SM Reserved                              0            
SM Committed                             0            MEMORYCLERK_SQLBUFFERPOOL (node 0)       KB
SinglePage Allocator                     0            
MultiPage Allocator                      400            


hi server is heathy
just open server properties-> memory-> max memory of it,
if you  use reporting services-> then leave 2 GB for reportimg
1.5 GB for Operating system
and the rest to sql server,

s
0
 
LVL 10

Assisted Solution

by:Ramesh Babu Vavilla
Ramesh Babu Vavilla earned 1000 total points
ID: 36565743
12 GB of memory is occupied by SQL Buffer Manger.

when you set the max memory option in SQLserver,
it is that you are placing the cap for SQL Server buffer manager,not the total sqlservr.exe application

you can confidently set the memory cap,it does not kills server performance
0
 

Author Comment

by:LogistixSW
ID: 36565789
OK, thanks for the vote of confidence! :-)

No, we don't use reporting services.

can you offer any help as to how the maintenance plans aren't being executed as expected (see final paragraph on the intiial post)

Thanks
0
 

Author Comment

by:LogistixSW
ID: 37037295
will log the maintenance plans question separately to close this question
0
 

Author Closing Comment

by:LogistixSW
ID: 37037305
thanks for your help guys
0

Featured Post

Does Powershell have you tied up in knots?

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

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…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

604 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