1110
Part VIII: Performance Tuning and Optimization
Best Practice
Large, frequently accessed tables, with data that can logically be divided horizontally for the most
common queries, are the best candidates for partitioning. If the table doesn’t meet this criteria, don’t
partition the table.In the access of data, the greatest bottleneck is reading the data from the drive. The pri-
mary benefi t of partitioning tables is that a smaller partitioned table can have a greater
percentage of the table cached in memory.You can consider partitioning from two perspectives:■ (^) Horizontal partitioning means splitting the table by rows. For example, if you have a
large 5,000-row spreadsheet and split it so that rows 1 through 2,500 remain in the
original spreadsheet and move rows 2,501 to 5,000 to a new, additional spreadsheet,
that move would illustrate horizontal partitioning.
■ (^) Vertical partitioning splits the table by columns, segmenting some columns into a
different table. Sometimes this makes sense from a logical modeling point of view,
if the vertical partitioning segments columns that belong only to certain subtypes.
But strictly speaking, vertical partitioning is less common and not considered a
best practice.
All the partitioning methods discussed in this chapter involve horizontal partitioning.
Partitioned Views
The concept of partitioned views was introduced with SQL Server 2000. You can partition a
view locally, which means that all the underlying tables are stored on the same SQL Server
instance. You can also base a view on tables distributed across multiple servers, which is
known as a distributed partitioned view. The databases are said to be federated. You can use
linked servers to union all the data through views.With the data split into several member tables, of course, each individual table may be
directly queried. A more sophisticated and fl exible approach is to access the whole set of
data by querying a view that unites all the member tables — this type of view is called a
partitioned view.The SQL Server query processor is designed specifi cally to handle such a partitioned view. If
a query accesses the union of all the member tables, the query processor can retrieve data
only from the required member tables.A partitioned view not only handles selects, but also data can be inserted, updated, and
deleted through the partitioned view. The query processor engages only the individual
tables necessary.c49.indd 1110c49.indd 1110 7/31/2012 10:24:22 AM7/31/2012 10:24:22 AM
http://www.it-ebooks.info