SQL Memory ! What is the best setting ?
-
Hi All,
I currently have quite a few SQL boxes, all have around 3GB of memory on it, they are often very slow with queries etc.
After looking at the memory setting, i found out that SQL was assigned dynamic memory with a max value of 2.5GB.
Can anyone advice me on the most suitable memory strategy ?
-
I'd say that would be good.
How mutch is SQLserver actualy using (task manager) ?
If sqlserver has not enough memory, it has to swap and _that_ may be what's strangling you.
In that case you may want to invest in actual RAM.
How big are your db-files ?
how big are your tables ?
...
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Seems to me that your server is on the
smalltiny side
Adding /3Gb to your startup parameters of Windows may have your sqlserver using up to then meanth 2,5Gb.
By default it will not take more than 1,7Gb.
Which version of sqlserver are you running (and sp) on which OS ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
By default windows only allows an application to use up to 1,7Gb ram.
Adding the /3Gb windows startup parameter, for systems up to 4Gb ram, windows will allow an application to use up to 3 Gb.
SQL2000 now has sp4. Can you test if SP4 has enhancements for your system ?
Maybe this link helps explaining it : http://www.sql-server-performance.com/awe_memory.asp
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
My server hasnt got 4GB RAM on it, so I can I make SQL server use more than 1.7GB.
Your solution is under the assumption that my box has 4GB of memory, it only has 3, and I want SQL server to use at least 2.5GB.
How can i also change the windows start up parameters.
-
In the servers Boot.ini file add :
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINNT="whatever windowssystem " /fastdetect /3GB
You will have to reboot the server to activate it
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
John,
I'm not sure that you're actually going to be able to achieve what you want with only 3Gb available.
The /3Gb parameter needs to be placed in the Windows boot.ini file, and then reboot the Windows machine.
On a 4Gb machine, this tells Windows to allow a user process (or processes) to access 3Gb of virtual address space, instead of the normal 2Gb. It should then expect to have 1Gb remaining for kernel processes.
Clearly, if user processes were able to address up to 3Gb of memory when there is only 3Gb available in total, then things wouldn't work well. Exactly how Windows would reserve sufficient memory for the OS in these circumstances isn't clear, but it would certainly reserve a substantial amount for itself, and this could leave you short of your 2.5 Gb target.
-
every litle bit of ram helps in this case !
Keep in mind windows itself needs some memory, I guess the 500mb you're leaving will do if it's a dedicated SQLServer server.
(only sqlserver running on it)
once again
Maybe this link helps explaining memory configs for sqlserver : http://www.sql-server-performance.com/awe_memory.asp
Don't get blinded by "AWE" in the title !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
i'd suggest the 3GB switch with max server memory set to 2.5. Keep an eye on your free memory and non-paged pool.
it sounds as though your SQL server may be undersized. What do you see for buffer/cache hits ratio.
-
What is the value of the Page Life Expectancy counter in the BufferManager object? If this stays above 300, it usually means that you have enough memory, no matter how small your databases are.
jg
-
"Buffer Cache Hit ratio is around 65% on average."
65% is much too small of a Buffer Cache Hit ratio. You want this number to be consistantly above 90% and idealy between 98% - 100%. Your server is undersized. Do as Alzdba suggests and look into using the /3GB switch in your boot.ini file and set your . After rebooting, use Windows Performance Monitor to look at SQLServer:MemoryManager>>Total Server Memory, and SQLServer:MemoryManager>>Target Server Memory. This will tell you if SQL Server is actually making use of that extra memory above 2GB. I believe that someone has already stated that the /3GB switch is meant for systems with 4GB of memory so it is not certain that your 3GB system can even take advantage of this.
In my opinion, even with the extra memory above 2GB, your system will still be short on memory. I would consider adding memory and taking advantage of AWE.
-
"Buffer Cache Hit ratio is around 65% on average."
That's still surprising on a 1.8 Terrabytes system with only 1,7Gb ram for sqlserver.
Maybe you can optimize by investigation partitioned tables or verticaly split some of your objects. Investigate which data is mostly queried and conclude a "split"-action.
Anyway if you can add more RAM !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply