Best Practices for Designing for Performance from the Start - Professional SQL Server 2005 Performance Tuning - page 504

in the database. You'll use this metadata later in the chapter to examine data flow growth, and having
it provides the answers to most of the common questions that you'll get later on from a planning and
performance tuning perspective. These data are stored in the Benchmark_UseCase_Object_Assn table in
the benchmark data model. To provide use case information at the persona level, take the time to map
the personas to each use case as shown in Table 14-4.

Table 14-4: Mapping Personas to Use Cases

Persona Use Case

Sue Order Taker Entering a sales order

B2B Order Taker Entering a sales order

Matt Supervisor Approving a sales order

John Report Running a sales report

What Is theSoftwareApplication'sCoreArchitecture?
It's not enough to have all your ducks in a row from the database perspective. Many highly tuned
databases have been brought to their knees over a poorly designed application or an overly permis-
sive WHERE predicate in a report. If you are not involved with the application design, have a discussion
with the architect of the software to get this type of understanding before you start making performance
tuning plans. Here are some of the big performance killers that you would be interested in:

Large result sets: Many apps take the stance of delivering all the rows to the client and allow
the client to perform paging and sorting. Although there are use cases where this is legitimate,
evaluate whether the users are just paging two or three pages into the results and changing the
search criteria. Allowing the client to manage this activity in this case (like most cases) is wasteful.

❑

❑ Disconnected composite activities: Apps that have composite-type operations should send

those operations to the server in a composite action. Activities that perform multiple network
round trips to validate and then look up the next action in a process stack should move that deci-
sion step into a business tier or into a parent stored procedure.

❑ Long-running transactions: Apps that open transactions over long periods create blocking and

resource contention issues. There are applications that calculate invoice detail while holding
transactions open for hours. Applications of that type could be improved by using a state type
on the header table that is updated upon completion of building the detail and only hold trans-
actions at the detail level.

Incorrect cursor usage: There are two types of design issues to monitor. The first refers to apps
that open keyset cursors on database tables and then update through the recordset. If this is
occurring at any great frequency and your app is not designed for a single-user, then have this
reworked to send update and insert transactions through a stored procedure. This could also
refer to iterative solutions performed in TSQL instead of using set-based approaches. To uncover

❑