January 28, 2010 at 10:23 pm
I need help from all the people familiar with inner workings of SQL 2005 memory managemnt. Educate me!
i have a few systems that exhibit similar strange memory usage patterns. Memory allocation by SQL server slowly grows till it allocates all of the memory allowed and stays on this level for 1-2 hours. Then memory usage suddenly dropps down to very low level (under 512 MB) and starts to slowly grow again until the whole thing repeats, about every 24 hours, 12 hours for some other servers.
it does not seem to cause any problems, although systems could be faster.
All servers are runing SQL 2005 Standard x64 SP3 on Win2003. Hardware is Dual Quad Core AMD processors, 16GB RAM, Hitachi SAN, hosted on Polyserve Matrix.
I have attached an image of the graph to give you an idea, not sure it will show correctly.
thanks in advance for the insight.
January 29, 2010 at 10:43 am
Bump, anyone? Please....
February 3, 2010 at 7:30 pm
Since its happening at 00 and 12 hours, my best guess is there could be a backup job (Ideally that should be an external utility like litespeed) running that time which purge all sql memory....
If this doesn't help check what are the jobs running at 00 and 12 hours...
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
February 3, 2010 at 11:29 pm
well, that's just the thing. there is nothing running at these times. This server is hosting 3 SQL instances that have backup jobs at different times, around 9 PM. And why would a backup job free up the memory anyway?
The times are not very consistent, it's not always at 00 and 12.
February 4, 2010 at 6:12 am
sam rebus (1/28/2010)
I need help from all the people familiar with inner workings of SQL 2005 memory managemnt. Educate me!i have a few systems that exhibit similar strange memory usage patterns. Memory allocation by SQL server slowly grows till it allocates all of the memory allowed and stays on this level for 1-2 hours. Then memory usage suddenly dropps down to very low level (under 512 MB) and starts to slowly grow again until the whole thing repeats, about every 24 hours, 12 hours for some other servers.
it does not seem to cause any problems, although systems could be faster.
All servers are runing SQL 2005 Standard x64 SP3 on Win2003. Hardware is Dual Quad Core AMD processors, 16GB RAM, Hitachi SAN, hosted on Polyserve Matrix.
I have attached an image of the graph to give you an idea, not sure it will show correctly.
thanks in advance for the insight.
1) If you have an HP system, their iLO stuff had a nasty bug that caused memory trimming.
2) Win2003 SP1 or 2 has a nasty bug that will do the same thing: large file copy
3) check your error log for messages like "memory has been paged out"
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 4, 2010 at 1:15 pm
sam rebus (2/3/2010)
well, that's just the thing. there is nothing running at these times. This server is hosting 3 SQL instances that have backup jobs at different times, around 9 PM. And why would a backup job free up the memory anyway?The times are not very consistent, it's not always at 00 and 12.
Now more information provided...like there are 3 instances, and its happening at multiple hours
External backup tools purge the memory when they took backup cause its run by OS not sql server.
Can you put through the analysis you already have done cause that will avoid asking such questions again and again in 10 threads...
Like when was the first time you notices it, was there any change happened after which this behaviour got noticed? paste the system info or attach that as text file so that most of the information needed is provided.
what else...try this run perfmon for user and previledge processes on this box to later validate when this happens what are the processes were running...that should give you some clue...
Hope this helps:)
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply