SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2005 High Memory Usage gradually over time


SQL Server 2005 High Memory Usage gradually over time

Author
Message
loda117
loda117
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 13
Hi guys,
I have a sql server 2005 running on a 2003 R2 64bit server
Lately I have noticed that the "sqlservr.exe will start to consume more and more memory over time. I restarted the server and it went down to normal little over 1.5GB
So i started to monitor it and over 5 days time, it went all the way up to 7.92GB. total physical RAM in the server is 8GB .
I have never worked with sql before and have been reading a lot about them but need some pointers to where to start with this issue.

Thank you Smile
Saleem K
Saleem K
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 13
This behavior is normal and is an intended behavior of the SQL Server buffer pool. By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on the physical memory load reported by the operating system. As long as enough memory is available to prevent paging (between 4 - 10 MB), the SQL Server buffer pool will continue to grow. As other processes on the same computer as SQL Server allocate memory, the SQL Server buffer manager will release memory as needed. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.

You can establish upper and lower limits to the amount of memory (buffer pool) used by the SQL Server database engine with the min server memory and max server memory configuration options. Before you set the min server memory and max server memory options, review the references in the section titled "Memory" in the following Microsoft Knowledge Base article: 319942 HOW TO: Determine Proper SQL Server Configuration Settings

See also:
http://support.microsoft.com/kb/321363
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/7f2825b4-99e1-4974-a287-52675f360ca1
loda117
loda117
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 13
Thank you for the response and links
I will take a look at them here soon.
I forgot to mention that this server acts as a sql mirror which is pretty much copying the DBs from the first SQL
No users connect to this server or run querys to this server
Issue I have with it right now that over time memory usage will go up to 7.92GB or higher and I get tons of high memory usage alerts when i remote in the OS is very sluggish
I have no choice but to reboot the server and same process repeats over 5 days of time
Arjen Krap
Arjen Krap
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 215
I wouldn't worry much about his behavior. As it has been said this is normal behavior. Over time SQL Server will allocate otherwise unused (wasted) memory. It will give it back when other processes request for more memory. You should always expect this behavior unless your database is much smaller than available memory. (I have seen this a few years ago with SQL Server 2000 on a machine with 4 GB memory and a single 200 MB database.)

As this server is used only for mirroring, how does this memory usage compare to the memory usage on the principle server?

SQL Server will populate the buffer cache on the mirror, so it won't need to load those pages from disk when you fail over. This is one of the reasons why mirroring fails over more quickly than a traditional SQL Server cluster setup.

You should configure the memory settings on the mirror server the same way as on the principle server. In fact the mirror server should be identical in hardware and software configuration. See http://support.microsoft.com/kb/2001270 for a few more things to consider when setting up database mirroring in SQL Server.

Also if you use an SAN the database files on the principle server should be on different spindles than the mirror server. This is very important for both performance and availability.

As long this server is only used only for mirroring you shouldn't worry about the memory usage. However the sluggish response time of the mirror server is alarming. Is this same behavior also noticeable on the principle server? If not, something might be wrong at the mirror server. It could be either underpowered or faulty hardware, incorrect settings, or incorrect firmware and/or software versions.
anthony.green
anthony.green
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23910 Visits: 6519
Have you set a max memory limit on the server or is it left at the default 2TB?

If default change it to around 2GB less than total server RAM.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220149 Visits: 46279
Chapter 4: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


loda117
loda117
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 13
@ Arjen
Principle server sits at around 7GB and goes up and down but never to extreme as the SQL Mirror
Only things I have changed recently was the one of the raid 5 drives which failed and I had to rebuild the raid with new drive
Right after that this started to happen ... that said I did get the same drive model which are already in use

I am not sure if I decrease the max memory that might mess something up.. I am new to SQL server environment

AS for the hardware issues or under powered.. I have ran tests on all Hardware using DELL Open manage and everything in there is reporting fine except for the Raid controller Battery which I am going to order right now and will replace it once it gets back but I don't really think the battery has anything to do with this issue

Basically I know i am asking for too much here but I sorta need a step by step guidense on what to use to see where this RAM is getting used and why isn't SQL letting go of some of the memory once it allocates it Sad
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220149 Visits: 46279
loda117 (7/18/2012)
why isn't SQL letting go of some of the memory once it allocates it Sad


Because it's designed not to. What you're seeing there is documented, by design behaviour.

See the book chapter I linked to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Arjen Krap
Arjen Krap
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 215
You have might other processes running on the principle server which are not running the mirror server. This way there is more memory for SQL Server on the mirror to take and none to give back to. Doesn't seem to be problem to me.

Could be a coincidence that you noticed this behavior started when the disk was replaced. Are you sure this was not happening before? Do you have an automatic memory usage monitor which keeps a history?

As long you don't have problems I wouldn't spend a lot of time in this.
loda117
loda117
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 13
@ Gila
I am reading through that chapter right now

@Arjen
I guess I might be over thinking about it too much
I don't know how much memory it was using before replacing of the drive
Only thing is that i got alerts the day after i repaced the drive that memory was low on the mirror and when i logged in through console the OS was very sluggish and memory was up to 7.96GB in use most of it by SQLSERVR.exe
So i panicked and restarted the server
Everything went back to normal
5 days later same alert logged into the server and same behavior
so thats when i started to research it and find out what it could be causing it
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search