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

UserSegmentation
Are users segmented by customer, division, or company? If your database has to support different
segments of users that play all the user roles, then you'll want to look for opportunities of partitioning
tables by a segmented identifier. An identifier can optimally be a foreign key to another table or can
simply be a code value. Adding the identifier into all the transactional tables allows for scalability in the
future of using logical or physical partitioning strategies.
UserLocation
Where are the users located? Do you have requirements to deal with data delivery through low-speed
means like modems or cell phone networks? These requirements force you to be interested in where
those Transactional Role users are located regardless of whether they are real humans or services. The
performance requirements are different when you need to cope with network latencies or just simple
application latency from users walking away from machines in the middle of transactions. You'll need
this information to formulate a strategy of maintaining transactional integrity across this latency and to
develop a locking strategy.
What Is theNature of theUser-RoleAccessRequirements?
Within each user role, you need to dig into what the users are actually going to be doing. Entering in
the results of a phone call has a different data throughput then handling a programmable logic control
streaming in data through a service. Find out the percentage each user role performs for insert, update,
read, and delete operations. You are looking for use-case based activities such as frequent update and
select activities occurring in a single table with high transaction volume. These operations can cause
contention for a common resource.
WhatIstheUserResponseTimeRequirement?
Consideration of the user response requirements is important. Understanding user-role access require-
ments leads into determining whether user response time cannot be compromised or whether a high
level of performance is even required. In the case of capturing PLC (programmable logic control) output,
an architectural decision to add a messaging protocol like MSMQ (Microsoft Messaging Queue) to pro-
vide the failsafe capabilities can release the real-time performance requirements from the database. The
queue messages can then be processed in an offline manner providing the same functionality without the
failure points. Other activities like user-based data entry may not need high performance. Does the row
entered need to be immediately available for another process?
Design efforts should focus on determining whether the main activities of a user role are read only or
transactional. Performance tuning is about tradeoffs. Optimizing tables for reporting operations renders
insert and update operations suboptimal. If most of the activities performed by a role are read only, then
you can develop strategies to replicate and distribute data to other servers to reduce the workload on a
core transactional server.
WhatIstheUptimeRequirement?
In today's world, uptime is becoming a 24-hour-a-day, 7-days-a-week requirement. Intra-Corporate,
single-time zone systems may sometimes be more forgiving. Knowing your downtime windows provides
the opportunity to increase performance by performing statistics updates, stored procedure recompila-
tion, index rebuilding, and other maintenance activities. You can also choose to run the batch-based use
cases during this time.