PAE, awe and 3GB
-
I know I have asked this a zillion times but here I go again. My server has 6GB RAM. I added /PAE and /3GB to the boot.ini, enabled awe, sized memory in SQL to use a fixed 5GB (it was using 2GB prior to all this), started the service with a login that has the ability to lock pages in memory and increased the page file to 8GB (from 4GB). I have not seen an improvement in performance. Is there more I should be doing (or less)???
Terry -
Presumably you are running SQL2LK EE and Win2k Adv?
Previously were you seeing memory as being an issue? If SQL had plenty of spare memory previously and was not having to move data in and out of the cache (you were seeing little disk queuing) you wouldn't really see much in the way of a performance improvement.
-
We are running SQL EE and advanced server. I've never seen an issue with memory in dev, where I playing around with this. I do see it as an issue on our production server, which we want to beef up the RAM on. Are there any additional settings that need to happen? I've searched many sites and it appears that I have all my bases covered but I want to be certain before I do this to our production server. Can PAE become a bottleneck?
Terry -
It looks as though you have everything configured correctly. Just bear in mind that unless SQL is using a lot of memory and having to pull a lot of data in and out of the cache you are not going to notice a performance increase. In the majority of cases you won't see an improvement in a dev server, however considering the load on a prod server is a lot higher you should see improvements.
You are putting the hard cap on SQL so you should not run into PAE/AWE issues.
-
Thnaks, I appreciate the responses. I'm always a little leery about applying something to production that I haven't quite proven as being an improvement.
Terry -
Is there any way that you could put a load on the dev server equivalent of that on production just as a proof of concept?
-
my 2ct
I suppose you've already checked :
- http://qa.sqlservercentral.com/columnists/jsack/aweadventures.asp
- http://www.sql-server-performance.com/awe_memory.asp
- http://www.devarticles.com/c/a/SQL-Server/How-to-Perform-a-SQL-Server-Performance-Audit/4/
- use perfmon to check on your I/O activity
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
-
Few things you should look into
1. Your cache hit ratio
2. Index fragmentation (when is the last time you re-index the DB ?)
3. statistics (when is the last time you run??)
More memory is good but won't solve problem if the above not been done right.
-
I reindex the database nightly and update statistics nightly as well. I do see buffer cache taking hits periodically throughout the day and figured the additional memory would help. Your thoughts on this?
Terry -
What's the top 10 slowest SQL statement, did you run a execute plan to see how this SQL really work. Are all the columns on the where clause have proper indexes.
I did not see you answer the cache hit ratio, this is important to identify possible cause. You may need run dbcc perfmon to check the numbers.
-
John,
On the server I'm getting a cache hit ratio of 99.3%. What else within DBCC PERFMON should I watch?
Terry -
In general terms adding memory increases the size of data cache so should decrease the level of physical i/o on the disk subsystem.
I assume your databases actually are greater than the cache size, I have heard of running a 1 gb database with 4 gb of ram < grin >
When I've increased memory I've usually seen a decrease in physical i/o and an increase in cpu activity. As to what goes faster, well I assume you had an issue in the first place? Cache hit ratio should improve.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply