There is a lot more performance tuning to do but it was amazing the difference this made. I was curious why SQL Server wasn't taking advantage of the extra memory on the system. All of this is explained in the SQL Server 2005 online help.
Craig Carter -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Opela, Gary L Contr OC-ALC/ITMA Sent: Wednesday, January 16, 2008 6:20 AM To: [email protected] Subject: Re: Resolved: Server Configuration Recommendations Bravo Craig. Thanks for posting the solution to the 'net, as many people are running SQL 2k5 with Windows Server 2k3. I hope this will help someone else out as well. Thanks, Gary Opela, Jr Sr. Remedy Developer Leader Communications, Inc. 405 736 3211 -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Phil Murnane Sent: Tuesday, January 15, 2008 5:00 PM To: [email protected] Subject: Re: Resolved: Server Configuration Recommendations Thanks, Craig! ----- Original Message ---- From: Craig Carter <[EMAIL PROTECTED]> To: [email protected] Sent: Tuesday, January 15, 2008 3:26:19 PM Subject: Resolved: Server Configuration Recommendations I wanted to follow-up and provide some changes I made that had a HUGE impact on performance and resolved the majority of our problem. Although we had 16GB on the system, the majority of it was not being used and performance monitor showed everything was waiting for disk. Average disk queue length was long and the bar never dropped off 100. The SQL Server service had allocated memory/virtual memory up to 1.7 GB but was holding steady at that amount. We're running Windows 2003 Enterprise (32-bit) and the documentation stated SQL Server 2005 will not exceed the virtual memory setting on Windows Server 2003 (32-bit) unless you enable AWE memory allocation. There was also an "enable lock pages in memory option" that I enabled for the account SQL Server was running under--but it appears this may not be needed unless you are running under Windows 2000 or Windows XP. The setting by default is off in SQL Server 2005. I also updated the Virtual memory settings on the server to "system managed" which increased it by 800%. After restarting SQL Server, the SQL Server service memory usage dropped from 1.7GB to 130M and the paging file jumped from about 2.6 GB to almost 7 GB. The processors are two to three times busier now but still only averaging about 30%. The physical memory in use jumped by 4 GB and you can tell SQL Server is now going well beyond the virtual server limits and 4GB limits imposed by the operating system. In summary, the database was not being allowed to exceed the virtual memory limits and 4GB operating system limit shared with everything else. Now that is has plenty of breathing room, it's flying right along. There is still a lot of tweaking to do but enabling AWE allocation made a huge difference. Thanks for all your suggestions. If you are running SQL Server 2005 on a Windows Server 32-bit version with more than 4GB, check this option out. Craig Carter ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" ________________________________________________________________________ ____________ Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
