January 2, 2007 at 8:29 am
Hi there,
I have a question concerning the most efficient memory set-up for our SQL Server, as described below:
We have a Data Warehouse running SQL Server 2000 which is experiencing problems during its daily load. The SQL Server response time can be pretty unreasonable and unpredictable, and it looks like the issue is memory related.
Each morning, approx 17 Gb of data is loaded onto the Server, and approx 5 Gb of Indexes are created. The total database size is approx 90 Gb.
The Server has a total of 4Gb of memory. Adding memory is being investigated, but this may not be possible.
The Server is not dedicated to SQL Server. After the daily load, other tasks run on the Server. There is sometimes an overlap between the daily load and this processing. One of these tasks is memory intensive, taking up to 1Gb of memory to run.
SQL Server is running with Memory Management set to Dynamic, with the Min Server Memory set to 0Mb and Max Server Memory set to 2849Mb.
The box for "Reserve physical memory for SQL Server" is not checked.
Investigation has shown that the Server sets the memory at 1682Mb and leaves it set at that value. This value is held for both the "Target Server Memory" and "Total Server Memory".
Performance counters are suggesting that SQL Server needs additional memory - e.g. Database Pages > 95% of Total Pages, Cache Hit Ratio < 30, page life expectancy at about 40.
If we are not able to increase the size of the memory in the Server, then I believe that the next best thing would be to give SQL Server some more dedicated memory, although I do not want to have too much of an impact on the rest of the Server.
The initial change would be to increase the value to 2048 initially, and onwards to 2560
My questions are:
1. Would we be better off setting the Server to a Fixed memory size of 2048Mb, or leaving it set to Dynamic and setting the Min Server Memory and Max Server Memory to be the same?
I understand that by setting the Min Server Memory and Max Server Memory at the same value means that SQL Server would take the additional memory as required, up to the Max Server Memory value, but it would not release the memory afterwards.
2. As we are not using AWE memory (the Server only has 4Gb of memory), could there be any issues with increasing the memory to 2048Mb? Is memory under 4Gb treated as equal - i.e. would other tasks still be able to use the other 2Gb?
3. Would choosing to "Reserve physical memory for SQL Server" make any difference? I understand what this does, but I would expect SQL Server to use 2Gb of memory anyway.
Any answers / comments would be gratefully received.
Many thanks,
Nigel.
January 2, 2007 at 9:07 am
Nigel,
Is this SQL Server Standard or Enterprise? If it's standard, you won't get the memory higher than it is. If it's not, then you need more memory. The cache hit ratio tells you that and I'm not sure 512MB will do it. Likely you really need to add memory to this box if you want better performance. I'd probably recommend going to 6 or 8GB and give SQL 4-6 of that or more.
You can set AWE and get SQL up closer to 3GB if it's Enterprise edition, but you need to set a fixed memory size and you might starve your other process.
Bottom line, you should approach management and let them know this warehouse needs more horsepower. Maybe get a bigger box and run your other process in a VM? That way SQL can get the memory when it needs it and if not the VM can get it.
January 2, 2007 at 11:08 am
Hi Steve,
Many thanks for the response.
We are using SQL Server Enterprise. We do not currently know if we can add physical memory (it is an old box, and will be replaced shortly with a SQL 2005 implementation). I am looking to make the best of what we have at the moment.
If we take it that we are stuck with 4Gb for now, would you have a preference on setting a fixed size of 2Gb or use Dynamic with a 2Gb min size to give SQL Server more of a chance?
Cheers,
Nigel.
January 3, 2007 at 12:05 am
I wouldn't advise doing anything with memory unless you add more physical memory to the box, any changes would most likely not have a noticible effect and as Steve says could well make things worse. You might look at your processes to improve things, you say you're loading 17gb of data every day into a 90 gb database, that sort of suggest you're maybe dropping and recreating data rather than incrementing it - if so look to incremental loads.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 3, 2007 at 6:29 am
Hi Colin,
Thanks for your comments.
Yes, we are dropping and re-loading data each day, as the system that we are loading from does not have any way of identifying new or changed records. The source system is not SQL Server, so there is no simple way of putting an incremental load in place.
I was hoping to get at least some performance benefit from allocating an additional 512Mb to start off with. While it may not sound much, it represents more than 25% of additional memory allocated to SQL Server. Obviously, I am concerned about how much of an effect that this would have on the other process, which is why I was asking the Fixed / Dynamic question.
Many thanks,
Nigel.
January 3, 2007 at 3:10 pm
in real terms adding awe memory only increases the data cache which normally benefits reads the most, I'm not totally convinced it would help you adding an extra 512 mb ram. You might try enabling the /3gb switch, this gives sql server around 3gb out of 4gb to play with - I've seen problems restricting the lower memory although in other situations it's been fine - you could try this as a test but with some careful monitoring. I suspect disks are your critical area.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 21, 2007 at 4:35 pm
Hi All,
I got SQl 2000 Std and got 3 GB memory but server taking all the RAM atm SQL only taking 2GB but other applications and OS take other ram?
What do i need to do? add more RAM?
AusNetIT Solutions
Web Design | Web Hosting | SEO | IT Support
January 22, 2007 at 1:41 am
std edition is restricted to 2Gb ram.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 22, 2007 at 4:32 am
Hi,
OK, if SQL take 2GB and adding 4GB RAM totla will take the 2GB for OS and other application isn't it? ATM all 3GB is taking the Server (SQL+OS)
Can we use /3GB for SQL Std ?
AusNetIT Solutions
Web Design | Web Hosting | SEO | IT Support
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply