March 12, 2007 at 6:52 am
I know how to check how much memory my sql server is using, I wanted to know how you were doing it.
I would definitely go to the latest version of Sql Server and double check if your version of SQL Server can use the extra 2gb you are buying.
March 12, 2007 at 7:02 am
I sometimes think we go around and around chasing our tails on some of these posts - yes sql standard can only use 2gb of ram ( 32bit sql 2000 ) BUT - so many programs run out of process so putting standard on 3gb or better still 4gb allows plenty of resource to avoid starving sql server. dts, sqlmaint, many xp_ , some ( or maybe all ) oledb, maybe open rowset, large query plans take memory outside the sql server allocation , most servers these days have a battery of diagnostics running on them ( sometimes of dubious value ) and how many dba's go around stopping unnecessary services? All these eat into memory and it's very easy to degrade performance. Although I don't like seeing lots of unused ( free ) memory, I personally think having 4gb ram on sql standard editions makes sense.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 12, 2007 at 9:02 am
I am not saying 4 GB with SQL Server Standard Edition it is a bad idea, but I just wanted to make sure he knew that it was memory that will not get used for SQL Server data buffers. Even though the documentation is fairly clear on this, there are a lot of people that are not aware of it.
If the amount of memory that SQL Server is able to use increases from 1.7 GB to 2 GB, it may not seem that that good an investment.
March 12, 2007 at 9:25 am
The reason we need the extra two gigs is because this server also has our ERP system runnning on it. I have not been able to sell the notion that we need an app server.
I've been trying for almost two years now to sell this internally and hope it happens before end of May. The odds are looking better.
Todd
March 25, 2007 at 7:35 pm
I would like to go somewhat further on this "memory leak" question.
We have a dedicated SQL 2000 Std SP3a with /3GB switcg. The server has 4 GB, which is by std edition limited to 2 GB for SQL, and it too grabs all possible memory after some basic (although heavy) report actions on our Axapta ERP application. No problem there.
However, once it reaches the max 1.7 GB we run more (other) SQL inquiries and you would think that SQL would use the 1.7 GB memory (swapping it with the current relevant data) but it doesn't. The raid controller memory will be working like crazy (and it is only MB's in size) and the drives go in overtime.
So do we have a leak that SQL is not releasing the 1.7 GB to itself?
On another note, we also see that the page life expectancy goes from 2000 - 3000 to only 100 - 200 and performance is down the tube.
We tried this situation on different similar server and even with less memory (to get faster to the proble). We actually got down to 5 MB free memory, per Taks Manager.
Any advice on this issue?
Patrick
March 26, 2007 at 4:42 am
the drop in page life expectancy shows thta the data cache is insufficient and you're swopping lots of data, or flushing the data cache almost continuously. That you're seeing physical disk io just confirms this. Sadly your only option is to move up to enterpeise sql or switch to 64bit.
Short term you could look to tune queries that produce excessive io, technically a query which does 100k io could flush nearly 800Mb of data. ( each io is a page read == 8k )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply