May 24, 2010 at 10:02 am
I am pretty green when it comes to SQL Server, and I have done my best to research what the potential problem is, any help would be much appreciated.
We are running MS Sql Server 2008 Standard, the computer has 8 GB of RAM. Basically our setup is that we have an application that is continuously pushing data up to the Server (it is a monitoring system) and then there is another application that views the data. Over time (about 2-3 weeks) all queries across the board begin taking longer and longer. The fix so far has been to stop the service and restart it and then everything is running at top speed again. SQL server is using an extraordinarily large amount of memory and to my untrained eye it seems that this may be the culprit although, from what I have read that is normal behavior. In any event I am wondering is there some cash I need to clean, what could be causing this.
Thanks in advance to all.
May 24, 2010 at 11:06 am
If you are running 64-bit SQL, you must set the max memory. SQL is a memory hog and it will happily use all available memory and ask for more.
What's the output of the following?
SELECT @@version
What are the values for min and max memory settings? (server properties -> memory)
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
May 24, 2010 at 12:37 pm
Thank you for your response:
SELECT @@version
Yields:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)
Minimum server memory is 0 MB
Maximum server memory is 2147483647 MB which would translate too 2 097 152 gigabytes
do you believe I should set this to a number that is smaller than the amount of memory on my server
May 24, 2010 at 1:02 pm
Yes. I would suggest that you set max memory no higher than 6GB, that's if the server is dedicated to SQL (nothing else on it). If there are other apps/services then you need to set the max memory lower to allow the other apps space.
Leave the min at 0. No good reason to set it if the server is dedicated to SQL.
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
May 24, 2010 at 1:53 pm
I will do that Thank you. I appreciate your thoughts on this matter. Obviously since the issue takes weeks to show up this will be a slow process of changing something and then waiting to see what happens.
May 24, 2010 at 2:28 pm
You may also want to consider adding some database optimisation jobs. Consider rebuilding indexes over the weekend.
Leo
There are 10 types of people in the world.
Those who understand binary and and those that don't
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply