atabases are complicated because they have so many interdependent elements. The components' interdependence is most evident when trying to improve server performance. When things slow down and performance seems to flag, organizations tend to look at the server as the source of the problem and recommend hardware enhancements to improve things. The list of solutions includes multiprocessor machines, buying more RAM, adding more RAID, or adding a network segment to move data off the server.
Throwing more processors, RAM, spindles, and NICs at the server is a seductive way to address the performance problem. Hardware enhancements seem to be the most direct and timely solution, especially for production systems that can't tolerate downtime. I believe an assessment of the database's design can yield a comparable advantage. Granted the man-hours spent in design time are more costly than hardware, but if you can spare the time on design-using what's between your ears-you can often fix the problem and deliver the desired behavior.
Common Performance Problems
The most common performance problems are associated with the time it takes to accomplish insert update and query tasks. Updating and inserting probably are most irritating to users because they are more closely associated with the user's time. Querying has a lower incidence of user interaction and lower expectations than a save for insert or update. More complex queries are usually shunted off to a reporting server where they can be run with minimal interference to the day-to-day operation of the database. These three scenarios (query optimization, insert tuning, and update tuning) account for the lion's share of performance issues.
SQL Server offers two great tools to help you reach your database optimization goals, System Monitor and SQL Profiler. In this 10-Minute Solution, I present a few preparatory steps to start using Profiler for monitoring performance problems. I discuss some techniques, tools, and commands that reveal information about server behavior, while concentrating on reducing the time it takes to get results.
How do I handle performance problems with SQL Server's out-of-the-box tools?
Use SQL Profiler to help you reach your database optimization goals.