December 13, 2007 at 7:30 am
We've got a production server running Server 2003 64 bit with SQL Enterprise 64 bit. It has 48 cpus and 48 Gig ram. It's using a SAM that dwarfs the 14 Gig database we have. CPU usage flucuates around 20%.
My issue is that sql is taking most of the 48 Gigs of ram but Process Explorer shows it is only using 720Megs of what its taken. :crazy: I'm also starting to get a lot of timeouts.
What can I look for? Can I get you any more info?
Thanks!
December 13, 2007 at 7:57 am
How is it configured? I assume you have PAE set up and SQL set for a specific amount of memory?
Which counters are you checking to get your numbers.
December 13, 2007 at 9:02 am
Since it's 64 bit we don't have PAE enables.
The upper memory limit is 2000000000 MB set by my previous boss. We haven't changed it since.
I using Process Explorer to get these process memory numbers since Task Manager wont give the actual.
I'm also running a job to clear some cache periodically. Not a great idea but we're trying some things. This job is detailed here.
And I was just told that they changed the parrallelism from 8 to 4 last month and they just changed it back to 8 trying to fix a locking problem we have right now. Grrrr.
December 13, 2007 at 9:07 am
I read somewhere that you have to be careful of the standard windows tools for monitoring memory usage as they don't give an accurate picture of higher memory usage.
December 13, 2007 at 9:19 am
My new boss showed me Process Explorer. I'm still exploring it but it seems pretty good so far.
December 13, 2007 at 12:41 pm
Fantastic. Thanks for the link. The old problems I described were with the old windows task manager application which simply wasn't geared up to cope with hyperthreaded CPUs and AWE memory.
December 13, 2007 at 2:01 pm
^ Yeah, it's a nice utility.
Another piece of the puzzle I just found is that the amount in ram is about the same size as all the databases on the server. There is one database that typically was only 2 gig meant for logging. I wasn't informed that the coders changed this to log more information and it was now 28 Gigs in size.
So that could mean all the databases are in memory with only 800meg allowed for our main database to work?
I'm over my head when it comes to memory and caching on 2005. I could use some help or some great links for reading. Thanks!
December 13, 2007 at 5:26 pm
This is great:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
There's also lots of good info here:
http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx
and
December 15, 2007 at 5:28 am
use sys.dm_os_performance_counters to check your memory usage. make sure max memory is set to maybe 3gb under the maximum memory. you can leave min memory at 0, max memory setting is critical.
I'd suggest you disable HT it will probably give you serious problems otherwise.
you normally fix the size of the page file in 64bit to something sensible - probably 8gb and then monitor and adjust. It is possible to run 64bit without a page file.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 17, 2007 at 2:52 pm
Thanks for the resources everyone. I've been going through them over the past couple of days and I'm not sure what I'm even looking for now. :hehe:
We do have a swap file but I don't think it's being used much as it's only 2 Gigs and not changing in size. I remember that they kept it small when configuring so that it would use all the memory first. They toyed with the idea of just trashing the swap file as well.
Some of those resources lead me to believe that the procedure cache is taking up most of the memory. At present we mainly use ad hoc queries. We'll be changing that with the next generation but for now this is what I have.
I'm also using Idera's SQL diagnostic manager that we purchased last week. So I'm trying to learn what all the indicators are telling me as well.
December 17, 2007 at 4:25 pm
ah - what patch levels are you running on o/s and sql server? I had problems with proc cache memory and a problem with ad-hoc queries, you need at least sp2 3186 for sql server, you should also get sp2 for the o/s.
( there's series of posts and links to other blogs on this - I worked with ms support on this issue ) sadly I resorted to clearing the proc cache every day - not the best idea but ti did help
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 18, 2007 at 6:46 am
Do you have SP2 installed for SQL server 2005? There was a change in the calculations used for the size of the procedure cache.
http://sqlug.be/blogs/wesleyb/archive/2007/01/24/486.aspx
SQL Server 2005 RTM & SP1: 75% of server memory from 0-8GB + 50% of server memory from 8Gb-64GB + 25% of server memory > 64GB
SQL Server 2005 SP2: 75% of server memory from 0-4GB + 10% of server memory from 4Gb-64GB + 5% of server memory > 64GB
This would be a significant difference with the 48 GB you have.
Idera is pretty good at helping you isolate the memory usage. We have been using it for 10 months now, information from it pushed us to SP2 earlier than we had planned due to this memory issue. I can help more with this if you wish.
December 18, 2007 at 8:24 am
phred: >>I've been going through them over the past couple of days and I'm not sure what I'm even looking for now.
Never ceases to amaze me that companies will buy such big, expensive hardware and yet not have an expert DBA on hand to ensure things are set up and maintained correctly and also to tune things when performance isn't up to snuff.
Have your company hire a professional to give you a performance review and also to mentor you so you can be better at your task of investigating issues and keeping things running smoothly in the future. You will both greatly benefit from the experience!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 19, 2007 at 6:19 am
I was able to get IT to install Server 2003 SP2 this morning and after the reboot memory usage is HALF. CPU usage is down to 10% at the moment.
Thank you so much for your help in getting this solved.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply