Database Reference
In-Depth Information
Where to Focus Efforts
When you tune a particular system, pay special attention to the data access layer (the database queries and stored
procedures executed by your code or through your object relational mapping engine or otherwise that are used to
access the database). You will usually find that you can positively affect performance in the data access layer far
more than if you spend an equal amount of time figuring out how to tune the hardware, operating system, or SQL
Server configuration. Although a proper configuration of the hardware, operating system, and SQL Server instance is
essential for the best performance of a database application, these fields have standardized so much that you usually
need to spend only a limited amount of time configuring them properly for performance. Application design issues
such as query design and indexing strategies, on the other hand, are unique to your code and data set. Consequently,
there is usually more to optimize in the data access layer than in the hardware, operating system, or SQL Server
configuration. Figure
1-3
shows the results of a survey of 346 data professionals (with permission from Paul Randal:
Figure 1-3.
Root causes of performance problems
As you can see, the first two issues are T-SQL code and poor indexing. Four of the top six issues are all directly
related to the T-SQL, indexes, code, and data structure. My experience matches that of the other respondents. You
can obtain the greatest improvement in database application performance by looking first at the area of data access,
including logical/physical database design, query design, and index design.
Sure, if you concentrate on hardware configuration and upgrades, you may obtain a satisfactory performance
gain. However, a bad SQL query sent by the application can consume all the hardware resources available, no matter
how much you have. Therefore, a poor application design can make hardware upgrade requirements very high, even
beyond your cost limits. In the presence of a heavy SQL workload, concentrating on hardware configurations and
upgrades usually produces a poor return on investment.
