Introduction

If you have progressed through the use of SQL Server and are looking toward optimization, you have seen a vast array of capabilities, applications, objects, and numerous interactions. All this functionality, of course, comes with a cost. A database server can and usually does service the largest load of all computerized processes in any corporation.

It would certainly be easy to resolve any problem that exists in a computer environment if the solution were only to provide more hardware. Many techniques are available to minimize the response time for each query and to maximize the throughput of the entire database server by reducing network traffic, disk I/O, and/or CPU time. Many factors affect performance, including the logical and physical structure of the data, as well as tradeoffs between conflicting uses of the database, such as online transaction processing and decision support.

Of course, maximizing the hardware is always important. With the price of RAM so low, you can bulk up your servers and desktops at a very reasonable cost (considering what I paid to maximize RAM in all my machines two years ago). Hard disk space and processor power is also relatively inexpensive in contrast to a few years ago. So the first consideration on obtaining the most from your machines is to spend the money where it will provide the best and fastest results. Put as much RAM in the machine as it can hold and look at the hard drive, processing ability, and network infrastructure for optimum storage, speed, and throughput.

On the database system and operating system software side of things, performance issues should be considered throughout the development cycle, not just at the end when the system is implemented. Ongoing adjustments are always a part of any complete solution, but you should still always design with performance issues in mind. Many significant performance issues are resolved through careful design from the outset. To most effectively optimize performance, you must identify the areas that utilize the most resources and then concentrate your efforts on those areas.

This chapter appears at the end of the book, but that is more because of the way Microsoft put together the exam study guide than anything else. Don't let the fact that this is the last chapter reduce its significance. The materials covered will not only be a major focus on the exam, but also on the job.