October 16, 2007 at 4:56 am
I am running sql2005 64bit on a 2 node cluster dedicated as a SQL server, and i am getting users saying it is running slow
the machine has 16gb Ram
the sp_configure settings are
max server memory (MB)16214748364721474836472147483647
is this correct as in Task manager it is showing sqlserv.exe is using 13,341,424K, i do not have the AWE switch as i read it is not needed for 64bit.
What are the best settings for this machine and should the mem usage be that high in task manager
Regards
October 16, 2007 at 6:22 am
there are just so many posts about memory on 64bit - why oh why didn't microsoft make this aspect of setup clear?
I only use enterpise x64 so if it's std I may be wrong on advice.
you must set max memory from within sql server , awe or not, and you must enable lock pages in memory for the server service otherwise what seems to happen is that sql server sees the page file as part of physical memory.
make sure w2k3 is optimised for programs not system cache. fix your page file to a set size.
use sysperfinfo or sys.dm_os_performance_counters to check your actual memory usage.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 16, 2007 at 6:47 am
Thank you for your reply, it seems non of those setting have been set on the server! do you know what the best max memory setting should be with 16gb Ram.
the paging file is set to 3209MB custom on drive C, with the recommended 24573MB, should this be increased to recommended( although there isnt enough space on C for max only 20000MB) or does it need to be set to system managed. (data is stored on SAN)
Thanks again
Andy
October 16, 2007 at 6:53 am
Hi,
You would want to leave at least 1GB for operating system, but 2GB is better to reduce OS paging. So, you would set SQL Server to 14GB and retain 2GB for operating system, assuming you have only one instance installed.
Hope this helps,
Phillip Cox
October 17, 2007 at 3:22 am
The paging file can be split over multiple drives. At my old place, all drives were on the SAN except for 1 local drive. The local drive was used to hold just the system page file (fixed size) and the system \TEMP folder. On C: (in the SAN) the page file was only 200 MB - you need a certain minimum size on C: for snap dumps and you get a message if your value is too small.
However, if restart time in a failure is a critical factor then you need to split the page file over multiple drives. If you need the server to be back up (say) 5 minutes after a Windows crash, then you need to look at how long it would take Windows to dump all its memory to a single page file. Divide this time by whatever factor is needed to meet your restart SLA, and you have the number of disks that need to hold page file data. Then define page files on all these disks, all usaing equal and fixed size files. Dump procesing will now not be the bottleneck in meeting your restart SLA
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 17, 2007 at 3:33 am
have you considered it may not be a RAM issue. how are your disks configured ?
MVDBA
October 17, 2007 at 5:30 am
The databases and logs are stored on a SAN, seperate drives for each, these are controlled by our Unix team so do not know anymore about them.
October 17, 2007 at 5:43 am
download yourself a copy of sql diagnostic manager from idera (idera.com at a guess)
you get a 15 server 30 day evaluation - should be able to identify what your bottlnecks are.
MVDBA
October 17, 2007 at 2:30 pm
you should set a minimal paging file, the old 1.5 times memory is outdated now on large memory boxes, can you imagine applying this to a server with 128Gb ram? It is suggested on large memory servers you may want to do away with the page file altogether.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 18, 2007 at 1:02 am
We have a 16Gb ram x64 server with a 24Gb pagefile. And we will change that to 4Gb pagefile asap because of this.
During a systems test, the sqlserver instance grabbed 15Gb memory. When we started anothre sqlserver instance, it needed to pageout the 15Gb to free some ram for the new instance.
During this free-action, the instance hung for more than 60seconds !
If the page file were e.g. 4Gb, then the page operation would stop after 4Gb and everything would be available soner.
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
October 19, 2007 at 4:53 am
ALZDBA (10/18/2007)
We have a 16Gb ram x64 server with a 24Gb pagefile. And we will change that to 4Gb pagefile asap because of this.During a systems test, the sqlserver instance grabbed 15Gb memory. When we started anothre sqlserver instance, it needed to pageout the 15Gb to free some ram for the new instance.
During this free-action, the instance hung for more than 60seconds !
If the page file were e.g. 4Gb, then the page operation would stop after 4Gb and everything would be available soner.
Make sure that you put a hard cap on each instance that you are running to prevent this kind of thing from happening.
October 19, 2007 at 4:59 am
Indeed, that's the first thing we did, specify a max server memory for each instance.
That, along with our implementation of Windows System Resource Manager, makes it all more manageble.
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
October 20, 2007 at 8:58 pm
In 64 bit hardware & software for SQL Server 2005 you do not need to setup AWE. MIN-MAX memory for SQL Server is used for Buffer pool so carefull measure should be taken. I will not touch or configure unless i have couple of instance running on the server or it is configured for Multinode cluster(ACTIVE\ACTIVE).
If you have Single instance cluster(Active\Passive) then don't configure MIN-MAX & no need of AWE in 64bit. lock page memory is required though.
October 22, 2007 at 7:58 am
We encountered a similar situation. We have two node SQL clusters with Dell 1955 blades (16 Gb) and an EMC SAN. The first "fix" was getting a driver patch from Dell. This helped, but did not solve the problem. After a lot of research, trial and error, we discovered that the way our programs used the databases caused SQL to use a large number of Multi Page Allocations. MPA's use memory outside of the SQL buffer pool. Since we configured the Max memory to be 14Gb, there was only 2Gb to handle the OS and other memory (where the MPA's were located). By changing the Max memory for SQL to 12Gb, there was enough memory available and it now runs very fast.
October 22, 2007 at 10:05 pm
I came across this article which gives more information about "Multi Page Allocator"
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply