Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Server 2005 High Memory Usage gradually over time Expand / Collapse
Author
Message
Posted Sunday, July 15, 2012 4:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 23, 2012 3:31 PM
Points: 6, 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 :)
Post #1329903
Posted Sunday, July 15, 2012 11:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 23, 2014 6:40 AM
Points: 51, 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
Post #1329938
Posted Monday, July 16, 2012 12:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 23, 2012 3:31 PM
Points: 6, 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
Post #1330310
Posted Wednesday, July 18, 2012 3:58 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 1, 2014 2:36 AM
Points: 51, Visits: 199
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.
Post #1331334
Posted Wednesday, July 18, 2012 4:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 8, 2014 1:45 AM
Points: 5,221, Visits: 5,120
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
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

Post #1331350
Posted Wednesday, July 18, 2012 6:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #1331407
Posted Wednesday, July 18, 2012 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 23, 2012 3:31 PM
Points: 6, 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


Post #1331688
Posted Wednesday, July 18, 2012 12:34 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
loda117 (7/18/2012)
why isn't SQL letting go of some of the memory once it allocates it


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 2008, MVP
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

Post #1331743
Posted Wednesday, July 18, 2012 12:36 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 1, 2014 2:36 AM
Points: 51, Visits: 199
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.
Post #1331745
Posted Wednesday, July 18, 2012 3:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 23, 2012 3:31 PM
Points: 6, 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


Post #1331857
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse