If the rep takes a break after pulling the record but before changing it, a
long transaction can result. Because SQL Server can reclaim transaction log
space for only finished transactions, the transaction log can't be freed until
this transaction is complete. And all data modification operations will probably
come to a grinding halt until the log can restore its empty space.
Modifying the above example to avoid the long transaction problem produces
the following pseudocode:
get the customer's name and account number
build up a query
retrieve the record
display the record
prompt the user for changes
begin transaction
update the record
check for errors
if no errors
commit transaction
else
rollback transaction
By beginning the transaction only after a user enters changes to the record,
you eliminate the potential for a long transaction. You can add some logic to
the code to ensure that another user doesn't change the record's contents
between the time you select the record and when you update it.
Tip 7: Place the Transaction Log on a Separate Device
SQL Server lets you choose
where to store the transaction log. For most read and write applications, the
transaction log is a heavily accessed disk structure because it is at the core
of SQL Server's data integrity architecture. Because I/O is often the bottleneck
on today's fast systems, spreading the I/O load among as many devices as
possible is a good idea. To accelerate I/O performance, consider placing the
transaction log on a different physical device from the database. Another reason
to put the transaction log on a different device is so that you can periodically
perform transaction log dumps without having to back up the data.
Tip 8: Use One Large Database
SQL Server developers and database administrators
frequently make the mistake of dividing information into several small
databases, rather than storing the data in one large database. Why is this a
problem? One way SQL Server speeds processing is to use its memory buffers
(i.e., cache) to reduce disk I/O. These memory buffers hold data, indexes, and
stored procedures. When a SQL Server application must access one of these
resources, the engine first looks in the appropriate memory cache area. On a
busy system, many such requests are satisfied from memory rather than from disk.
Reducing the number of times the engine must make a costly disk inquiry
accelerates access.
If you divide your data into many small databases, chances are users can't
share much of the information stored in memory, which leads to degraded
performance. For example, suppose you maintain a manufacturing system that
supports your firm's worldwide operations and resides on one large server. You
divide your data into separate databases, according to geography--for example,
WestEurope, EastEurope, WestNorthAmerica--to let users access data for only
their region. As part of this strategy, you duplicate many lookup tables in the
small databases.
The combination of separate, small databases and duplicated lookup tables
greatly reduces the potential for memory caching. Although users can be
examining identical copies of lookup tables in each database, the database
engine can't take advantage of memory caching across databases.
For example, what happens when Ted looks for part number 20993 in the
WestNorthAmerica parts table and Alex must find the same part in the EastEurope
database? Ted pulls the appropriate page into memory, yet Alex won't see that
page: Although Alex is reading a row on the same page, he retrieves it from a
different database. If one large database holds this data, Alex's process
doesn't have to issue a disk read request because the page is already in memory.
If you multiply the number of unnecessary read requests by the number of users,
you can see how performance can suffer.
Lost caching opportunities also affect stored procedures. Besides data,
users can share stored procedures (although the engine must generate query plans
if the procedure is already in use).
Although you sometimes need multiple small databases, try to use one large
database. To limit data access to appropriate users, you can choose among
several methods, such as key design and views.
Tip 9: Index Temporary Tables
Temporary tables (work tables) are transitory
storage locations that SQL Server uses to satisfy processing demands. SQL Server
and you can create these tables.
Often the engine can build an implicit temporary table. For example, SQL
Server can create temporary tables when it can't use available indexes to
process information. When SQL Server automatically creates work tables, aside
from reworking your queries or altering database index structure, you don't have
much control over these work tables. For example, you can't create indexes on
them, but SQL Server often builds indexes on the key columns of implicitly
created work tables.
You can specify the create table command to build explicit temporary tables
to hold data until the application finishes. When you explicitly create a
temporary table, you largely control how that table is indexed, if at all. If
you expect the work table to contain large amounts of data, you can create
indexes where appropriate. Doing so helps reduce the time needed to locate
information in your temporary table, which should lead to better application
response.
Tip 10: Control the Amount of Published Data
SQL Server provides powerful
replication features as part of its core capabilities. When you use these
features, following good techniques to replicate information is important. For
example, just because you can replicate a specific table doesn't mean you
should. Sometimes, administrators replicate unnecessary tables. This replication
increases the work for all machines in your environment, adds to your network's
burdens, and enlarges the amount of disk space necessary on subscription
machines.
Before you add a table to a publication, decide whether to replicate the
table. If you do, you then have to decide whether to replicate horizontally or
vertically. Either operation will reduce the amount of information SQL Server
passes among systems.
For example, to replicate an account table that has several million rows,
you must decide whether this table is a good candidate for replication: Do
multiple systems need to see the table at the same time? If the answer is yes,
you have to decide whether all subscription servers will want all the
information in the table. Chances are you can subdivide this data horizontally
(across rows) or vertically (across columns). Taking these extra analysis steps
can significantly improve performance, because you can sharply reduce the amount
of information flowing among your systems.
Future Reading
You can immediately start improving SQL Server
performance by applying any of these techniques. For in-depth information about
the tips in this article and other performance-enhancing ideas, see the
forthcoming Microsoft SQL Server: Designing and Building a High Performance
Database (Prentice Hall, Upper Saddle River, NJ, November 1996, ISBN
0-132-66222-1).