July 15, 2012 at 4:00 pm
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 🙂
July 15, 2012 at 11:23 pm
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:
July 16, 2012 at 12:01 pm
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
July 18, 2012 at 3:58 am
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.
July 18, 2012 at 4:32 am
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.
July 18, 2012 at 6:15 am
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
July 18, 2012 at 11:51 am
@ 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 🙁
July 18, 2012 at 12:34 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2012 at 12:36 pm
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.
July 18, 2012 at 3:40 pm
@ 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
July 18, 2012 at 3:52 pm
In short, max server memory's not configured properly. If that's left at default, SQL thinks it can use up to 2000 TB of memory. Probably OK if you have more than 2000 TB of memory on your server, but I'm guessing you probably don't.
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
July 19, 2012 at 10:42 am
Ahh ok
So I have set it to use max memory of 7GB
Lets see what happens
August 2, 2012 at 5:11 pm
Sorry for late reply but limiting the RAM in SQL server management Studio has fixed the issue
🙂
Thank you so much guys
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply