It's a common mistake: a company puts a poorly performing database on larger and larger servers, throwing more and more money away while making the same mistakes, but the database never gets any better. Many performance problems stem from just a few basic issues. First, hire a real DBA. If you’re going to throw money at the problem anyway, throw it in a direction where it can do you some good. If you can’t afford a DBA, then check these few simple, but extremely effective techniques to gain tremendous performance benefits.
Separate your data and log files onto separate disks. These must be physical disks (or disk arrays), and not just logical partitions. All databases have both data and log files, and no vendor has discovered a magic formula to get around disk contention caused by putting these files on the same physical volume.
Review your indexes and queries; 90 percent of the performance issues in all databases are index/query related. Developers often write horrible queries that go completely unchecked and get pushed into production. Some of the worst performers include queries that return all columns when only a few are needed, have overly complex logic in the search criteria, or join too many tables (four is usually a sensible limit). More often than not, no thought is put into indexing in production databases. The three categories of bad indexing are: not enough indexes, too many indexes, and improper indexes.
Stay on top of your server’s memory. Memory leaks from other processes contained on your database server are also common. Monitoring your database’s target server memory counter will reveal memory being stolen from your database. In the case of a true memory leak, this activity shows up as a steady decrease in target server memory.
Create baselines and benchmarks for your database servers. You must have a solid picture of your database’s normal performance before you know that it’s not performing at peak efficiency. Then, by comparing your benchmark to your baseline, you can uncover variances in execution plan usage, stored procedure recompiles, memory, disk and CPU queuing, and space usage. After reviewing your comparative benchmark, you may need to upgrade your hardware, but never buy any hardware without solid numbers to back your decision. You will spend a lot of money and you still may not solve your problem.