I have taken over development of an Access 2003 Project against SQL Server 2005 that is having serious performance issues. Upon investigation I discovered the SQL Server is configured to run Exchange Server, File & Print Services and five instances of SQL Server on a HP server with two disks and 2 GB of RAM.
I think the SQL Server instances are causing the problems; each instance takes about 1.7 GB of virtual memory. The instances being run are the following:
- Access / Office Application
- Backup Exec
- Windows Update Service
- SMS montitoring
I noticed when the Access Application is running slowly, the free CPU usage is around 90%. I think the SQL Server instances are I/O bound so that the context switches are causing the performance problems.
I plan to increase RAM and the number of disks. But I'm also interested in reducing the number of SQL Server 2005 instances by turning off several services and hopefully sharing SQL Server instances for applications like Backup Windows Update and SMS Services.
Reducing the number of SQL Server instances will definitely help performance. There isn't really any reason that all these applications couldn't use a single database instance. The upside to this will be that they can all share a single pool of RAM. The downside is you can't specify how much RAM each one uses.
If I was the administrator on this box, I'd pull all the SQL Server databases into a single instance of SQL Server, and if possible move that instance to another server. With everything that this server is doing it's very possible that the two disk drives are killing the system. Without having performance monitor data handy, I can't dig into the system specifically, but those would be two excellent places to start.
Because Exchange Server is on there, you'll want to limit your SQL Server databases to about 768 MB of RAM total. This way Exchange Server can have some RAM, as well as the operating system.
Dig deeper on Microsoft SQL Server 2005
Related Q&A from Denny Cherry, Contributor
Troubleshoot long running stored procedures with SQL Server Profiler by adding events to find the command with high run time.continue reading
Planning an upgrade to SQL Server 2008? Our expert weighs in on advantages and hardware requirements you should consider before upgrading to SQL ...continue reading
Learn how to troubleshoot SQL Server 2005 temporary table performance problems when a stored procedure execution time is slower than usual.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.