March 18, 2010 at 7:05 am
We just installed a SQL Server 2005 Developer Edition x64 on Windows Server 2008 Standard x64. The server has 24GB of memory and several databases. Some of the databases are over 100GB in size. The old server that we migrated from was only 32-bit and therefore was maxing out at 2.8GB of RAM. However, on this new box, with all of this memory, it won't use much more than 85MB of memory. Is there something we're missing in the configuration? Our production box has 32GB of memory and SQL Server uses all of it. Performance is hindered since it won't use the memory it has available.
SQL Server is using SP3 and Windows has SP2 installed.
March 18, 2010 at 7:21 am
What are min and max server memory set to?
How are you monitoring how much memory SQL is using?
What's the value of Total Server Memory and Target Server Memory? (Perfmon counters)
Is there anything else memory-intensive on the server?
p.s. Developer edition? This is a development server we're talking about here?
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 18, 2010 at 7:25 am
SQL Server is using much more memory than that.
Take a look at the Total and Target Server Memory counters in Performance Monitor - you will find them under the Memory Manager for each instance installed, for example SQL Server:Memory Manager or MSSQL$InstanceName:Memory Manager.
SQL Server is allocating memory using a different mechanism.
Paul
edit: posted before I saw Gail's reply
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 7:37 am
The minimum memory is 0 and the maximum is 2147483647.
I've been monitoring the memory usage by looking at Task Manager.
I looked at the perfmon counters and they appear to be more inline with what I would expect:
Target (KB): 21,443,576
Total (KB): 9,968,152
Nothing else memory intensive. This server is entirely devoted to SQL Server.
Yes, this is a development server.
I'm assuming since the Target and Total are showing correct values, than it is using what it should be using. Why doesn't the Task Manager reflect the memory being used? It used to show accurate values in Windows Server 2003.
Even so, performance is still really slow bad. All we did was a detach of the databases on the old server, copy them over, and reattach. I would have guessed that performance would be at least as good as it was on the last server since the last server was limited in memory to 2.8 GB.
March 18, 2010 at 8:26 am
amrinea (3/18/2010)
I've been monitoring the memory usage by looking at Task Manager.
SQL Server is using a different (and better) method of allocating memory. See http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx for details.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 10:19 am
Thanks for the information. We have another x64 installation and it shows up in the Task Manager. However, it's running Windows 2003 Enterprise x64 and SQL Server 2005 Standard x64, and it appears that may be the reason why.
March 18, 2010 at 10:27 am
amrinea (3/18/2010)
Thanks for the information. We have another x64 installation and it shows up in the Task Manager. However, it's running Windows 2003 Enterprise x64 and SQL Server 2005 Standard x64, and it appears that may be the reason why.
More likely it is because the other installation has not granted the Lock Pages In Memory right to the account SQL Servers runs under, or it is not Enterprise Edition (or Standard running with that trace flag I can never remember)...or something else 😀
I think there is something in the link I posted (or a related blog entry) about how to tell if SQL Server is using Locked Pages or not. It is an entry in the error log when SQL Server starts up, something like 'using locked pages for buffer pool'. Something like that.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 18, 2010 at 10:55 am
Do you know if there are any performance benefits to having it run one way or another?
March 18, 2010 at 11:00 am
amrinea (3/18/2010)
Do you know if there are any performance benefits to having it run one way or another?
Complex question. Simplest answer is that locked pages is best, but be sure to set max server memory so that you don't starve other things of memory (like the OS!)
I would encourage you to read the other blog entries on that link from before. The CSS team explain it all very well.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply