Databases Reference
In-Depth Information
❑
Customer billing records summary query
❑
Customer billing record detail query
❑
Billing record exception insert
Depending on exactly how the system has been designed and written, these queries could result in many
different I/O patterns at the disk level, but let's take a fairly optimal scenario. In that case, the storage
level I/O for this could be something like:
❑
Customer search query
❑
Random/Sequential read
❑
Customer account details query
❑
Random/Sequential Read
❑
Customer billing records summary query
❑
Random/Sequential read
❑
Customer billing record detail query
❑
Random/Sequential Read
❑
Billing record exception insert
❑
Random Write
From this you can see that the overall use case is a mix of random and sequential read activity, as well
as a small amount of random write activity (in this case, probably just a single write activity as you are
only writing a single record). However, if you had to update an index because of that one write, there
could follow a number of random/sequential write activities as the index is updated.
Putting It All Together
How do the results from a performance test relate to the real-life performance you can expect to see from
your SQL Server application? The answer depends on the I/O characteristics of your application.
Some applications have a simple and highly predictable I/O pattern. An example of one of these might
be an OLTP system where you will expect to see a very high volume of random reads and writes. This is
easy to predict, and the relation to the performance test results is pretty clear. The user should expect to
see I/O performance very closely related to the results of random read and random write activity in the
performance tests.
On the other hand, someone with a complex DSS system where the I/O characteristics of the applica-
tion will consist of a mix of large overnight batch updates and then large query activity throughout the
day will be much more difficult, but you could start by estimating that the overnight loads will consist
of a mix of random and sequential write activity with some read activity thrown in. The challenge for
the creators of the overnight batch load is to minimize the random read activity and the random write
activity and to try to maximize the sequential write activity. In this case you can expect the system to
experience performance of maybe 50-75 percent of the sequential I/O rate from the performance test.
The daytime performance will then be a mix of random and sequential read activity. The challenge for
Search WWH ::
Custom Search