July 5, 2008 at 8:43 am
We just had a company upgrade or SQL 2000 server to a new higher powered box and SQL 2005. The old machine had 4 gigs of RAM and consistantly had 2 gigs free during day to day operations. The new machine has 24 gigs of RAM and barely has 500 megs free during day to day use. Is this normal? Is there a document available on this site dealing with the tuning of SQl 05'?
I did a reboot on the machine and the perf log showed 23.5 gigs available, then I ran a backup and the available memory dropped to 250 megs immediately and stayed there even while the system idled for the next 12 hours.
Can anyone shed some light on whats going on here? Thanks
July 5, 2008 at 9:28 am
SQL Server 2005 memory usage is documented and, unless set lower, will use all except 256Mb of available physical memory.
See SQL Server BOL on how to configure 'max server memory (MB)' and 'min server memory (MB)'
SQL = Scarcely Qualifies as a Language
July 5, 2008 at 10:30 am
To add to what Carl has given you, I would recommend setting the max memory for no more than 20GB (leave 4GB for the OS) and set the min memory to no more than 18GB.
If you are running anything else on this system (e.g. SSIS packages, other applications, report server, etc...), then you need to drop those values down even more to allow the other applications access to the memory they need.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 5, 2008 at 11:21 am
I looked through the management studio but i couldnt find where to set the option. Where do I look for it?
July 5, 2008 at 11:38 am
In SSMS, connect to the server using Object Explorer - right-click the server name and select properties. Select Memory and you can change it there.
From a query, you can do the following:
sp_configure 'show advanced options';
go
Reconfigure With override;
go
Execute sp_configure 'max server memory (MB)', 20480; -- max memory = 20GB
Execute sp_configure 'min server memory (MB)', 18432; -- min memory = 18GB
Either way, this setting is dynamic and does not require the server to be restarted.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 6, 2008 at 8:10 am
Works awesome. Thanks so much guys!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply