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"
