To most effectively optimize Microsoft SQL Server performance, you must identify the areas that will yield the largest performance increases over the widest variety of situations and analyze these areas. Otherwise, you may expend significant time and effort on areas that may not yield sizable improvements.
This section focuses primarily on areas that years of experience by the Microsoft SQL Server Support team have shown to be of practical value in real-world situations.
Experience shows that the greatest benefit in SQL Server performance can be gained from the general areas of logical database design, index design, query design, and application design. Conversely, the biggest performance problems are often caused by deficiencies in these same areas. If you are concerned with performance, you should concentrate on these areas first because very large performance improvements can often be achieved with a relatively small time investment.
While other system-level performance issues, such as memory, cache buffers, hardware, and so forth, are certainly candidates for study, experience shows that the performance gain from these areas is often minimal. SQL Server manages available hardware resources automatically (for the most part), reducing the need, and therefore the benefit, of extensive, system-level hand tuning.
Microsoft SQL Server opens up opportunities for platform-layer performance improvements with large amounts of memory, symmetrical multiprocessing, parallel data scan, optimizer enhancements, and disk striping. However, as large as these improvements are, they are finite in scope. The fastest machine can be bogged down with inefficient queries or a poorly designed application. Therefore, even with the additional performance features of SQL Server, it is of paramount importance to optimize the database, index, query, and application design.
Most performance problems cannot be successfully resolved solely with a server-side focus. The server is essentially a "puppet" of the client, which controls what queries are sent and thereby what resources are obtained and released. Although some tuning is possible on the server side, successful resolution of performance problems usually depends on acknowledging the dominant role the client plays in the problem and analyzing client application behavior.
The following are some suggestions that, based on experience, have yielded significant performance gains.