Question I am running SQL Server on a Windows 2000 server box which has a single CPU with more than one-half GB RAM. I have a problem with SQL taking the lion’s share of RAM and making other CPU and memory intensive applications starve.
My main application database is 1.5 GB and is growing at the rate of 5 – 10 MB per day. Both INSERTS and queries are taking longer and longer as I have yet to add any indexes to the tables in the database, or to optimize my queries.
Before moving onto creating indexes and optimizing queries, I need to make SQL Server work well with the other applications running on the same server. Will placing the MDF files and the LDF files on two different physical hard drives help? How do I make SQL Server run under lesser priority, or make it use only a restricted amount of RAM/CPU?
Answer Let me start by answering your specific questions, and then I will offer some general advice on how to optimize your SQL Server.
Placing the MDF and LDF files on separate arrays helps I/O performance, although you won’t see a huge performance increase. If you decide to do this, I would recommend you put your MDF files in a RAID 5 or RAID 10 array, and your LDF files on a RAID 1 or RAID 10 array, for optimum performance. In addition, you don’t want any other disk activity on the array with the LDF files.
While it is technically possible to assign SQL Server less priority on your server’s single CPU, I don’t recommend it, and won’t even discuss how to make this “hack” work. It is not a feature of SQL Server. If your server had more than one CPU, you can very easily tell SQL Server to limit how many of the total number of available CPUs to use. But in your case, where you only have one CPU, this option is not available.
If you want to limit the amount or RAM SQL Server can access, you can do this fairly easy. To do this, right-click on the server name in Enterprise Manager, and then choose “Properties”. From the “Properties” sheet, select the “Memory” tab. Here, under Dynamically configure SQL Server memory,”, move the slider under “Maximum (MB)” to an amount less than the maximum amount of RAM in your server. The smaller you make this number, the less RAM SQL Server will get, leaving the rest to the other application(s). Of course, when you so this, SQL Server’s performance most likely will suffer. You will want to use the Performance Monitor tool to track the SQL Server Buffer Cache Hit Ratio to see what happens to it after reducing the amount of RAM available to SQL Server. This ratio should ideally be above 99%, but never below 90% for best performance.
Now that I have answered your specific questions, here is some more advice on how to optimize the performance of SQL Server.
First, ideally, SQL Server should run on a dedicated SQL Server, not sharing applications as you describe you do now. Of course, you may not have a choice, but you can guarantee that this will hurt the performance of SQL Server, and the other applications.
Second, I would immediately begin to add appropriate indexes to your database. You can use the SQL Server Profiler and Index Wizard to give you a good start.
Third, you need to use Profiler to identify slow queries and begin to tune them.
After doing all of this, you can set back for a while, and observe the performance of your SQL Server, monitoring it with Performance Monitor. You may find that you are happy with the current performance, or you may find that performance is still suffering. If performance is still a problem, there are hundreds of other performance-related tips on this website to help you along.
]]>