SQL Memory configuration ! Have I got it Wrong ??? Pls Help
-
Hi All
I would appreciate any help from you all with regards to this:
I have a SQL 2005 standard installation with windows server 2003 enterprise Operating system
The server has 7GB of memory available to it, but as I know SQL 2003 standard edition would make use of the total server memory available to it.
Here are the changes that I have made. (I have converted it all to GB for easy reading)
I added the 3GB switch to the boot.ini file
I enabled AWE
I set mini server memory to 5GB
I set max server memory to 6GB
I turned on Performance monitor and the Target server memory counter = 6GB, the total server memory is also set to 6GB.
Now after all the changes above, the server seems to run slower and looking at task manager, the PF usage is around 6GB.
Can anyone please confirm that the changes I made were indeed correct and possibly educate me on the configuration and how to make the best use of server memory etc.
I look forward to hearing from you all.
Thanks
-
No need to cross-post... at least one answer elsewhere.
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
John,
Have you added \PAE switch in boot.ini? If it is not there, please add that switch and then reboot the server once.
Regards
Utsab Chattopadhyay
-
assuming your server is a dedicated sql server then I'd advise making the min and max memory settings the same.
You say it runs slower - with regard to what? The changes you have made do different things, the 3Gb switch expands lower memory use which might cause problems if you run a lot of external processes, such as SSIS, xml stuff etc. etc. AWE increases the memory for data caching. You might want to remove the 3gb switch , it can sometimes cause problems.
I assume we are talking 32bit here ?
I'm not quite sure what you expect to see - at startup sql server will take 5gb of ram, you say your server has 7gb of ram - that's an odd figure - is that the total physical memory installed on the box?
The o/s and other processes will take memory and there should be an amount of free memory, excluding the page file. as sql server works it will grab another gb of memory, but only as the data cache fills.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hi
the machine is 32bit
The box is a dedicated sql box, what's running slower at the moment seems to be queries, compared to when AWE wasnt enabled.
What i am concerned off here is that SQL might be doing a log of paging, which I think was caused by the enabling of the AWE feature.
Please advice
-
should be easy to see what's happening, run up a perfmon to check. There should be no, well almost, paging at all on a sql server.
There should be some free memory as shown in task manager.
Just wondering did you set the memory correctly? - get your maths correct?
Use master
go
exec dbo.sp_configure 'min server memory',6144
exec dbo.sp_configure 'max server memory',6144
exec dbo.sp_configure 'awe enabled',1
reconfigure with override
go
will set to 6GB.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
ps .. restart sql service.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hi
I think I have managed to figure out what the problem is, I reduced the max server memory to 3GB and the time the query runs has reduces significantly, i.e from 45mins to less than 20mins.
Although AWE is enabled and there is 8GB physical memory, if i set max server memory to 7GB, the Page file usage goes up to 7GB and everything runs really slow.
I dont quite understand the link, but on my C:, it has around 4GB free and that is the only drive where the page file is, I am wondering, is there any link between Max server memory, Page file on drives and also performance.
Also, the C: is the only drive with virtual memory specified.
Cheers
-
very strange - you could use perfmon/task manager to figure out what is using ther page file.
I usually try to place the page file on a fast drive . I also set it to a fixed size.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hi
Thanks for your reply Colin, I didnt quite understand the link as well, what I just noticed that setting the max server memory to 7Gb seemed to kill the system (I suppose windows wasnt too happy having 1GB and SQL having 7GB, if i switch off the service, the page file usage falls to around 300mb, whilst if the service is running it reaches the max server memory setting in GB), I am running a test by incrementing the max server memory bit by bit and checking the performance of 1 specific query, in order to arrive at a setting that would make use of the highest amount of memory and not negatively impact performance.
At the moment, I have reached 6GB max server memory settting and the run time seemed to have halved the time it normally takes if it was on 7GB max memory.
Also, with the page file on C:, can this be moved elsewhere ?
Lastly, with regards to virtual memory set on disks, can you do this on SAN based disks as well or just the local disks ?
Thanks in advance
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply