Memory
-
Hello,
I've created the alert for Memory-Pages/sec Alert Over 2 in Performance Monitor, I'm getting net send message as Pages\sec is being over 2. I'm confused and what should I do to overcome it. My production server has 523 MB Ram and I've specified SQL Server to use memory dynamically setting max as 440 MB. Can you please guide me.should I make Maximum MB in SQL Server less.
Thanks
-
523 aint a lot for a production box. Buy more, its cheap! I dont have my tuning book here, but I think 2 is a bit on the low side for that counter. Are you having a performance issue, or just concerned with this counter?
Andy
-
Looking at the SQL 2K Resource Kit:
"If Memory: Page Reads/sec > 5, this is bad for performance."
Pages/sec > 0 does show it's using the paging file, but there's always going to be some paging activity. You can help matters by:
a) increasing the amount of memory SQL Server is allowed to use
b) getting more RAM and doing (a)
The reason I mention (a) is because you've 83MB of RAM between the max and what SQL Server is allowed to use. I don't what else you're running on the box, but you can probably squeeze a little more towards SQL Server's way. However, that gain is going to be minimal, so (b) really comes to the forefront, as Andy has already mentioned.
A good question that needs to be asked is how often is the Pages/sec alert kicking off? Intermittent spikes are generally not a cause for concern.
K. Brian Kelley
K. Brian Kelley
@kbriankelley -
Can you guys guide me How much more memory should I get? I'm running only SQL Server on the box.
Thanks
-
quote:
Can you guys guide me How much more memory should I get? I'm running only SQL Server on the box.How much money do you have?
I'd say you should at least throw in another 512MB, but it of course depends on how heavily used your server is, and in which way it's used.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Answer: (hate to say it) It depends. I have a SQL Server with 512 that hums. Depends on the load. If you are getting lots of paging and the site is slow, then go for another 512MB. If you can afford it, go for more. You can never had too much.
Steve Jones
-
Please keep in mind that you have to look at everything before you decide - make sure you have found the cause and not the symptom! That said, I'd say another 512, though you want to look at how your server supports it...# of slots, max capacity, etc.
On the two servers I manage, one has 4g and the other 8g - runs very smoothly, lots of headroom.
Andy
-
Like everyone else, I'll have to say it depends. Factors like the size of your database, the average number of concurrent users, the peak number of users, etc. all need to be considered.
Inside SQL Server 2000 says to go conservatively and increase based on empirical evidence, but where I am employed, we tend to take the opposite approach. We look at the numbers and then make a choice that should generally be considered overkill a couple of times over. Never know what management will read in the latest of issue of CIO magazine or the like that will have to be implemented IMMEDIATELY.
K. Brian Kelley
K. Brian Kelley
@kbriankelley -
Hi all
Memory is a tricky one at times. You can take the approach where you buy as much as you can (because it is cheap!) vs how much you will be allocating to sql*server. Now I dont have any sort of evidence to prove this, but I believe performance can be impacted from allocating too much ram...this is applicable to two areas...
a) Not controlling sql*server memory usage growth and thus impacting other applications, incoming user sort areas, and impacting OS performance.
b) Letting sql*server grab too much ram for a somewhat small/mif-sized OLTP based application. I believe this may result in (over time) internal fragmentation of its memory area, slower performance (buffer cache) etc...
Now b) is what I am really trying to highlight here. Classic examples are in Oracle where people allocate massive SGA's and adversely impacts performance.
Ill try and come up with a range of tests and see if this is applicable for sql*server.
Comments? 🙂
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Chris,
I havent done any real testing, but I doubt your theory. We recently bumped our 8 way box from 4g to 8g, which changed from giving SQL about 2.7g to about 7.5g of RAM (using AWE and /PAE /3GB). So far I have no evidence of any type of degradation.
Andy
-
Andy
Thanks for the comments Andy. Fair call, I need to take a closer look at the internals when I get a chance. Ive basically found similar results myself from 512Mb to 1gig sql*server memory alloc but nothing too formal.
Btw, thats a big server you have. Perhaps not the place for it but what was your reasoning to go for a large SMP box over, say, 4 2way clustered boxes? not saying its a bad thing but interested either way 🙂
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Chris,
We're using a very expensive 3rd party app that makes extensive use of server side cursors. When we had it running on a 4 way 550 with 1g of RAM it was maxed out. Moving to 2g helped a lot, we were short on memory. Thats with about 200 concurrent users. Not much I can do to fix the app, so only choice was to buy more power. 8 way box plus an external fibre channel array (9 drives plus a hot spare) now runs at avg of 10% utilization. We needed room to grow. More importantly, if you're running near max the box becomes much more sensitive to load - which would kill response times.
I'd like to cluster at some point, but I think if/when we do it will be with another 8 way box. I think box plus drives, OS, cost us around 80k. Money well spent.
Andy
-
Andy
Fair call.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Hi Andy,
What is the name of your tuning book?
It may be usefull to have one at home.
Best regards,
Sica
-
I use the MS SQL 7 Performane Tuning...something like that. Try mspress.microsoft.com. There is a new one out for SQL2K that I haven't picked up yet. Good book, sometimes more detail than you care about!
Andy
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply