March 4, 2011 at 12:40 am
We recently upgraded our DB server's RAM. Got 32GB now! Mostly because there wasn't another option between 16 and 32. And the owner of our company thought it would be fun. Ok, I thought it would be fun too.
Now I'm feeling a little foolish, because SQL Server is not using the extra RAM. Our main db is 90GB. It can be slow sometimes - certain queries during high volume times. I was hoping with the extra RAM it would keep more of the db in memory, and thus go faster...
Anybody have any suggestions for me? I figured this was the place to ask!
Thanks very much in advance!
Additional Info:
It's SQL Server 2008, on Windows Server 2008, all updates. I did change the "min/max server memory" values under Server Properties / Memory.
March 4, 2011 at 12:53 am
How are you checking what it is using?
How much memory do you think it's using?
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
March 4, 2011 at 7:46 pm
I was just looking at the Windows Task Manager. It's showing right now 12.35GB used, 18.5GB free "Physical Memory". So, I'm guessing it's really only using something like 9GB?
I freely admit, I'm not much of a DBA. Very good SQL developer, I can write you a query to do anything and even make it perform better. About all the rest, I know just enough to be dangerous..
Any suggestions?
More info:
I did some research about the Buffer Cache hit ratio. Pulling from sys.dm_os_performance_counters, divide the 'Buffer cache hit ratio' by the 'Buffer cache hit ratio base'. Right now it's at 100%. If I understand correctly, that means it is already pulling everything it can from RAM?
I'm not certain how long a time period that value covers though. I'll have to check it a few times during the day.
And then I'm guessing we'll pull out the extra 16GB. Call it a lesson in overkill.
March 5, 2011 at 1:32 am
Task manager lies. It's a terrible way to check SQL memory because it will give you an incorrect value (it's not great for other processes either)
Perfmon, target server memory and total server memory (sql counters)
Additional memory will only help if the poor performance is due to IO bottleneck and insufficient memory. Unless you've already tuned the hell out of the query that may not be the case.
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
March 5, 2011 at 10:37 am
Lots of factors could be in play here.
Is this 32bit or 64bit? What did you change min and max memory to? Anything else on the server?
If everything is set up OK SQL may just be using the memory it has needed (so far). Unless min memory is set the same as max memory SQL won't grab the full amount allocated unless it needs it.
any messages in the errorlog about SQL giving up memory?
---------------------------------------------------------------------
March 7, 2011 at 10:57 am
Gail, that's interesting about Task Manager. I thought RAM or I/O was the bottleneck, but perhaps not.
Target Server Memory: 27,702,232
Total Server Memory: 7,264,256
What does that mean? Googling it left me a little unclear.
George, it's 64 bit, only running SQL Server, nothing in the error log about memory. I set the min memory at 21504 MB, and the max at 29696. Are you saying I should set them both the same?
Thank you both!
March 7, 2011 at 12:56 pm
dev_maniac (3/7/2011)
George, it's 64 bit, only running SQL Server, nothing in the error log about memory. I set the min memory at 21504 MB, and the max at 29696. Are you saying I should set them both the same?
No I am definitely not saying that. Only set them the same if you want force SQL to use the max memory setting.
From those values for total and target memory I would say SQL has taken the amount of memory it actually has required so far.
Use perfmon counter Memory:available memory to confirm there is plenty of RAM still available for use on the box.
---------------------------------------------------------------------
March 9, 2011 at 4:20 am
I'd suggest setting 'lock pages in memory' against the account which is running the SQL services by using GPedit.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply