September 12, 2008 at 9:08 am
Due to what's probably a poorly written application, the Page File increases throughout the day and affects CPU performance especially during peak transaction processing. We seem to have enough RAM now on the server (it averages but there's a direct relationship between CPU performance and the Page File.
The only way I can reduce it is to restart SQL Server which purges it. This reduces CPU utilization but it's a little "risky" to have to always restart SQL Server for this reason.
We're running SQL Server 2005 SP2 Version 9.00.3050, 64-bit Standard, have 18gb RAM available to SQL, and 4-dual core AMD64 processors.
Has anyone experienced this and how did you take care of the problem? Thanks!
September 12, 2008 at 9:16 am
Can you explain how Page File increases can be affecting your CPU performance? And how you figured this out?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 12, 2008 at 11:00 am
I restarted SQL Server in the middle of our peak transaction period (I reasoned I had nothing to lose because the system grinds to a halt anyway). I watched Windows Task Manager/Performance and after restarting SQL the Page File went to 5gb from a peak of 20 and cpu went from a 97-100% threshold to high 80's.
This also caused the # of "Non-Anonymous" users count from our web servers to drop by 20%. We have found that there is a correlation between these numbers and poor performance on our SQL Server box.
Thanks for any suggestions you can give!
September 12, 2008 at 11:14 am
I keep seeing posts like this. There is an MS article about this here - http://support.microsoft.com/kb/918483
Unfortunately the only workaround that is given is for Enterprise version of SS2005. I would be curious to know if this fits your situation as well. Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 12, 2008 at 11:14 am
Not sure that's a correlation. If the page file is growing, I'd make it larger to start with. Restarting SQL Server can clear things out and it changes access patterns as some queries might not get resubmitted, users expect it to be slow on restart (and it is as plans are built), etc.
I'd clean up the pagefile and set it to the proper size first and then look for other issues.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 12, 2008 at 11:16 am
Thanks for your suggestions!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply