The buffer cache hit ratio compares the number of logical reads to the number of
physical reads; if 10 percent of the logical reads require physical reads, the buffer cache hit ratio
is 90 percent. Low hit ratios identify databases that perform a high number of physical reads;
extremely high hit ratios such as found in this example may identify databases that perform
an excessive number of logical reads. You must look beyond the buffer cache hit ratio to the
commands that are generating the logical reads and the physical reads.
In Your Application Design, Strive to Avoid Trips to the Database
Remember that you are tuning an application, not a query. When tuning database operations, you
may need to combine multiple queries into a single procedure so that the database can be visited
once rather than multiple times for each screen. This bundled-query approach is particularly
relevant for ???thin-client??? applications that rely on multiple application tiers. Look for queries that
are interrelated based on the values they return, and see if there are opportunities to transform them
into single blocks of code. The goal is not to make a monolithic query that will never complete;
the goal is to avoid doing work that does not need to be done. In this case, the constant backand-
forth communication between the database server, the application server, and the end user??™s
computer is targeted for tuning.
This problem is commonly seen on complex data-entry forms in which each field displayed
on the screen is populated via a separate query.
Pages:
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230