Since most, if not all, applications
need to store, retrieve, and manipulate data, it follows that a major factor in
the design of an application infrastructure is the way in which its data is
managed. Microsoft® SQL Server™ 2000 provides secure, scalable data-storage
services for relational data, and includes extensive management capabilities and
support for high availability. However, while SQL Server is designed to be
as “self-optimizing” as possible, there are some considerations that need to be
made in any application to meet the specific security, performance, scalability,
and availability requirements of the Microsoft Internet Data Center solution.
This chapter is designed to identify those considerations and discuss strategies
for designing a SQL Server–based solution for the Internet Data Center architecture.
This chapter is designed to be read
by anyone involved in planning or implementing a secure, high-performance,
SQL Server–based solution. Specifically, database
professionals in the following roles will find it useful:
·
Database administrator
·
Database application developer
The strategies discussed in this
chapter are application-agnostic. They provide suggestions for areas of
investigation in which you need to optimize the performance and scalability of
an application, and should be considered general good practice. Since each
particular application differs in varying degrees, a specific set of
performance-optimization configurations cannot be given. Instead, you should
apply the guidance given here to your own particular circumstances and
requirements.
Best practices for securing SQL
Server can be found in the Chapter 5 Security Design and therefore will not be
discussed further in this chapter.
To design a secure,
high-performance, and highly available data farm, you need staff fulfilling the
following roles:
·
Database system engineer (database administrator
and database developer)
·
Shared disk/network storage administrator
·
Network administrator
·
Application architect
To build the data farm, you need the
following:
·
Database System
Engineers to build the database servers, database objects and database
code.
·
Application Architect
to design the distributed application.
·
Developers to write code for Data Dependent Routing and
data access.
·
Hardware Technician to assemble hardware.
The following hardware guidance
applies to high-volume data farms.
·
A minimum of 8 CPUs per server is recommended.
·
Storage Area Networks (SANs), although not
required, are strongly recommended between the servers.
Scalability of the Internet Data Center architecture requires the
following:
·
Microsoft Windows® 2000 Advanced Server or
Windows 2000 Datacenter Server (preferred).
·
SQL Server 2000 Enterprise Edition.
The server and components must be
certified for use with the Windows 2000 operating system. After the
manufacturer certifies each server, the server is added to the Windows 2000
Hardware Compatibility List (HCL). You can find the HCL at:
http://www.microsoft.com/hcl/
There are four main factors that
affect the performance of SQL Server 2000:
·
Configuration of SQL Server 2000
·
Physical data model
·
Logical data model
·
SQL statements
In this section, we will discuss
each of these factors, and identify best practices that can help optimize the
performance of your SQL Server application.
One of the most obvious ways to
improve the performance of a SQL Server application is to optimize the
configuration of the server itself. In many ways, SQL Server 2000 can be
described as ”self tuning,” in
that it dynamically adjusts its configuration settings based on activity and
resource utilization. However, there are a number of installation- and
configuration-related decisions that can greatly enhance your application’s
performance.
Production SQL Server 2000
databases generally should not be installed on internal disks. By installing
each database on its own external disk sub-systems, data read/write operation
performance can be enhanced. As more disks are installed, the risk of a failure
increases. Thus, in order to avoid data loss due to hardware failure, you should
run all environments on RAID (redundant array of inexpensive disks) systems.
Doing so provides a level of fault-tolerance and reduces the risk of data loss
due to hardware failure.
The most common RAID level in
database environments is a combination of RAID level 0 and 1. This combination
is also known as RAID 10 or RAID 0/1.
For performance reasons, the
following SQL Server files should be split into different storage sets:
·
Transaction log files
·
Tempdb files
·
Data files
·
Index files
It is strongly recommended that the
transaction log files and tempdb files be put on RAID 0/1 but not on RAID 5,
because of the performance penalty caused by high write I/O rates. Similarly,
the data files and index files should also be stored on RAID 0/1 storage sets,
but in the case of an application that is not write-intensive can be placed on
RAID 5.
To calculate the number of database
files and storage sets, you have to take the application’s storage and access
characteristics into consideration.
Detailed information on RAID levels
and SQL Server can be found in the MSDN article Microsoft
SQL Server 7.0 Performance Tuning Guide by Henry Lau, found at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/msdn_sql7perftune.asp
By default, SQL Server 2000
allocates as much physical memory as is available on the server, while leaving
the operating system sufficient resources to prevent excessive paging. Besides
the executable code, the allocated memory is used for the SQL Server memory
pool. The memory pool consists of the following areas:
·
System-level data structures
·
Log-cache
·
Procedure-cache
·
Connection context
·
Buffer cache (also known as the data cache)
The default memory configuration
automatically determines the size needed for each of these areas.
SQL Server is configured to automatically and dynamically allocate and
de-allocate memory for each of these areas as needed for their optimal
performance.
To configure the allocated memory
manually, you can use the following parameters:
|
Parameter |
Function |
|
max server memory (mb) |
Maximum memory allocated by
SQL |
|
min server memory (mb)
|
Minimum memory allocated by
SQL |
|
min memory per query (KB) |
Minimum amount of memory
allocated by each query |
|
index create memory (KB) |
Amount of memory used by sort
operations during index creation |
Table 1. Memory allocation parameters
It is generally not recommended that
you change the default memory options. However, if there are any other
applications running on the same machine (for example, BizTalk Server), you can
prevent SQL Server from allocating too much memory by setting the max server memory parameter. Conversely, you can
prevent other applications from grasping (all) memory needed by SQL (for
example, the data cache) by setting the min server
memory parameter to a value other than 0.
For each query, SQL Server 2000
allocates the appropriate amount of memory. Specifically, queries with intensive
hashing or sorting operations are memory-intensive. It is not recommended that
you perform hash or sort operations on disk.
In order to detect sorting
operations performed on disk during a query, run the query exclusively on your
machine and look for I/O activities on the disk or RAID array where you placed
the tempdb file(s). To look for these activities, use the Windows 2000
Performance Monitor, which uses a predefined counter for I/O activities. If your
query results in sort operations
being performed on disk, it is possible to allocate more memory using the
min memory per query parameter. To calculate the
figure for the min memory per query parameter, you
can take the following steps:
1. Note the machine’s total memory.
2. Subtract memory for Microsoft Windows 2000
and other applications running on this machine.
3. Subtract memory that you want to assign to the
memory pool.
4. Divide the rest by the number of queries you
expect to run simultaneously.
Note: In general, it is not
recommended that you change this option, because SQL Server 2000 allocates
the memory per query dynamically, taking all the other components of the
SQL Server memory into consideration.
Like all the other parameters, index create memory is self-configured. If you expect
to have difficulties with a create-index operation because of necessary sorting,
increase this value.
To set the parameters mentioned
above, you have to enable show advanced options by using the sp_configure stored
procedure.
sp_configure "show advanced options", 1
go
Then set the desired parameter
values. For the new values to take effect, you must run RECONFIGURE.
You can now set the configuration
options using the sp_configure-system stored procedure as shown here:
sp_configure "min server memory", 32
go
More information on memory
configuration can be found in the following resources:
·
"Configuration Option Specifications" in
SQL Server Books Online
·
Microsoft SQL Server 2000 Performance Tuning
Technical Reference
You should strive to reduce I/O
activity whenever possible. SQL Server 2000 produces I/O activities in the
following three situations:
·
Writes to the transaction logs due to logging.
·
Reads from or writes to data or index files that
stem from requests that cannot be fulfilled by the data cache.
·
Miscellaneous disk activity due to
SQL Server system behavior, such as sorting data on the disk or
periodically performing checkpoints.
The following two parameters can
influence I/O performance:
|
Parameter |
Function |
|
recovery interval (min) |
Minimum number of minutes that
SQL Server needs to recover databases. |
|
max async io |
Maximum outstanding I/O requests
per file. |
Table 2. Parameters affecting SQL Server I/O
performance
With the recovery interval parameter, you can influence the
checkpoint interval. After SQL Server 2000 is installed, the recovery interval parameter is set at approximately one
minute. In other words, SQL Server calculates the number of checkpoints so
that the recovery time remains at approximately one minute by default.
Consequently, if you would like to reduce I/O activities that are a result of
cache flushing, you have to increase the recovery
interval parameter. Increased recovery time is a drawback of this method.
In addition to increasing the
recovery interval, you can improve I/O performance by taking advantage of the
asynchronous I/O requests that are sent from SQL Server to
Windows 2000 and the disk controller. By default, SQL Server sends a
maximum of 32 outstanding I/O requests to a file, which is acceptable for
non-sophisticated disk controllers. In order to reduce disk-head movements,
intelligent controllers can handle more than 32 requests per file. However, with
increasing max async io, there is an upper limit after which
performance declines.
Generally, it is not recommended
that you change the recovery interval parameter
because that increases the overall recovery time. However, if system monitoring
reveals a lot of I/O activities that result from check pointing (for example, in
an application with a lot of small transactions), increasing the recovery interval parameter can have some positive
impacts on performance.
You can configure the parameters
discussed above using the sp_configure-system stored procedure, as shown in
these examples:
To manipulate asynchronous I/O:
sp_configure "max async IO",
<value>
Go
Reconfigure
Go
To manipulate checkpoint
frequency:
sp_configure "recovery interval",
<value>
Go
Reconfigure
go
More information on managing I/O
activity can be found in the following resources:
·
"Recovery Performance" in SQL Server Books
Online
·
Deploying Microsoft SQL Server 7.0: "Chapter 5,
Hardware Selections and Configurations"
A system with more than one CPU, in
which all processors have access to one large memory area (RAM), is called a
symmetric multiprocessor machine (SMP). SMP is the current technology that
scales out best. With an SMP system, SQL Server 2000 uses multiple threads
to divide tasks between different CPUs. For instance, the parallel query option
allows SQL Server 2000 to use multiple threads to split certain queries in
order to scan tables. The threads are processed in parallel by dedicating each
thread to a different CPU. Well-tuned database instances should generally have a
cache-hit ratio of 98% or higher. If this figure is not being achieved, the
bottleneck may be due to insufficient CPU power. Additionally, the clock speed
of a single CPU, or a number of CPUs, can have a major impact on the overall
performance.
The following configuration
parameters can be used to control SMP behavior.
|
Parameter |
Function |
|
affinity mask |
Association between a processor
and a thread |
|
max worker threads |
Number of worker threads
available to SQL Server |
|
max degree of parallelism |
Number of processors to use in
parallel plan execution |
|
cost of threshold for
parallelism |
The threshold to create and
execute parallel plans |
Table 3. SMP configuration parameters
With the affinity mask parameter, it is possible to exclude
certain processors from being used by SQL Server 2000 threads.
Consequently, one additional effect is to reduce or even eliminate hopping of
threads to different processors and thereby increase the effect of the processor
cache. The default value of 0 allows SQL Server 2000 to use all available
CPUs. If, for example, another application runs on the same machine, it is
possible to dedicate SQL Server 2000 threads to certain processors.
The default value for the max worker threads parameter is 255, which is
acceptable for most applications. SQL Server 2000 uses thread pooling if
there are more than 255 concurrent connections. This means that the next
available worker thread can handle the request without increasing the max worker threads parameter.
The max
degree of parallelism parameter determines the number of threads assigned to
a single query. It is often called the parallel degree for a query. The max degree of parallelism parameter takes the number of
available CPUs into consideration, and is therefore restricted by the use of the
affinity mask parameter.
Using parallel query,
SQL Server 2000 has to consider the cost of the splitting of this query and
the reunion of the intermediate results. SQL Server 2000 compares the
estimated costs for a non-parallel execution of a query with the value specified
in the cost of threshold for parallelism parameter.
If the estimated cost is higher, SQL Server 2000 parallelizes the query on
SMP systems. In other words, the cost of threshold for
parallelism parameter prevents SQL Server 2000 from executing simple
queries in parallel where no significant performance advantage is gained.
It is not recommended that you
change any of the parameters mentioned above, except for the affinity mask parameter. This parameter should be
changed only if another application or significant workload is running on the
same machine. Also, in such a situation, you have to monitor CPU utilization by
different processes in order to dedicate SQL Server 2000 threads to
less-used or even idle CPUs. To monitor the CPU utilization, use the
Windows 2000 Performance Monitor. Select the % User Time
counter from the processor object and select all the processors that are
present.
To set any of the parameters
mentioned above, use the stored procedure sp_configure to set Show Advanced Options
to 1.
The affinity mask, max degree of
parallelism, and cost threshold for parallelism parameters can also be
set using Enterprise Manager.
More information on query processing
in an SMP server can be found in the following resources:
·
MS SQL Server 7.0 Query Processor by Goetz Graefe,
Jim Ewel, and Cesar Galindo-Legaria
·
Parallel query: "Max Degree of Parallelism
Option" and "Cost Threshold for Parallelism Option" in SQL Server Books
Online
·
Assigning threads to processors: "Affinity Mask
Option" in SQL Server Books Online
·
Number of worker threads: "Max Worker Threads
Option" in SQL Server Books Online
Another factor that can affect
performance is the physical data model of the database. The choices you make
when designing the physical storage for your database objects can have a
significant impact on database performance.
The physical data model includes all
design aspects of a database model that can be modified without changing any
components of the application. For example, there is no need to modify the SQL
statement after creating a new index on a certain column.
SQL Server 2000 uses file
groups to distribute table or index data across disks. Database objects, such as
tables or indexes, belong to one file group. A file group has at least one
physical file. When a file group with more than one file is created,
SQL Server 2000 uses a proportional fill strategy, allocating space in each
file, in proportion to the file size. For example, if file1 has 600MB
free space and file2 has 100MB free space, SQL Server 2000
allocates six extents of pages in file1 and one extent of pages in file2.
The data model of an application is
generally stored in a single database. As a rule, data is placed using files and
file groups that belong to the database. Separated databases, or even separated
instances, are normally used only for a single application in the following
situations:
·
Tables have totally different access patterns.
For example, some tables belong to the OLTP part of the application and others
are for reporting issues.
·
Scalability is enhanced by load-balancing
transactions among different server instances. This solution can be used when
data placement using files and file groups is not sufficient. Often this
approach uses distributed partitioned views to provide a consistent view of the
data across multiple servers.
·
There is a need for a high-availability solution
using SQL Server 2000 Cluster solution.
When using files and file groups to
distribute data within a single database, you need to consider the following
factors:
·
Number of file groups to be created.
·
Number of data files to be created.
·
Number of data files per file group.
·
Which datafile should be placed on which storage
set (or disk)?
·
Which database object (such as table or index)
should be placed on which file group?
The optimal configuration depends on
the access characteristics of the application. To analyze the access patterns,
isolate use cases that represent typical or critical user behaviors. With this
information, you should be able to decide how many file groups and data and
index files need to be created. Then determine which database objects (for
example, tables, indexes, procedures and functions) have to be placed on which
file group.
In order to exploit any
SQL Server 2000 parallel options, such as parallel table scans, parallel
index scans, or parallel JOIN operations, it is strongly recommended that you
create additional files and file groups. SQL Server 2000 uses drive letters
to select I/O requests that can be run in parallel. By default, the
SQL Server 2000 application databases have only one file group, named the
primary file group. This is where the database system tables are stored. Each
file group has at least one file. Each additional data or index file should be
dedicated to a different drive (which is a different drive letter) or different
RAID arrays. Consequently, a parallel query request is supported optimally by
the storage hardware, if each request is fulfilled by different disks or
stripes.
The guidelines for performing this
task are as follows:
·
Use file groups to place objects on specific
physical disks.
·
Create one or more secondary file groups for
additional files and make this new file group the default. Use the primary file
group to contain only system tables.
·
As a rule, you should not have more than one data
file per disk.
·
Use file groups to spread your data across as
many disks as possible.
·
If the application has a frequently accessed hot
spot, consider placing it on a disk of its own.
·
Spread data that will be used together in joins
to different files.
·
Separate sequential scans from those that are
accessed directly (using an index).
·
Separate non-clustered indexes from table
data.
·
If you use horizontal partitioning, place your
partitions on different storage sets or disks.
·
Consider using a different database when database
tables have totally different access patterns (read intensive vs. write
intensive).
To add a file group and an
additional file to an existing application database, use the ALTER DATABASE
command.
create a second file group for the Northwind
database
alter database Northwind add file group secondary
add one data file to the new file group
alter database Northwind add file
( name = 'logical_filename',
filename = 'c:\Program Files\Microsoft SQL Server\MSSQL\
DATA\physical_filename.NDF',
SIZE= required_size
)
to file group secondary
Then place new database objects on
the new file group by specifying the file group's name:
create table table_name(…) on secondary
The same task can be accomplished by
using the SQL Server 2000 Enterprise Manager. To add file groups and files,
use the SQL Server Enterprise Manager, under database properties.
More information on using files and
file groups can be found in the following resources:
·
Deploying Microsoft SQL Server 7.0, Notes
from the field: page 212ff.
·
"Using Files and File Groups" in SQL Server
Books Online
·
"Querying Distributed Partitioned Views" by Kalen
Delaney and Itzik Ben-Gan. SQL Server Magazine (http://www.sqlmag.com/,
September 2000.
There are two methods by which
SQL Server 2000 accesses data—either by scanning the entire table
sequentially or by using indexes to locate the pages that contain the required
data directly. In the context of performance, it is helpful to define an index
as a secondary, redundant data structure containing the indexed columns ordered
in a B-tree structure. Using the sequential scan method, all rows of a table are
read in the sequence of their physical storage. Alternatively, it is possible to
use an index to access only those rows that satisfy the selection criteria.
SQL Server 2000 uses two different index strategies:
·
Non-clustered index
·
Clustered index
The main difference between these
two strategies is that a clustered index sorts table data and stores it in the
order of the clustered index. Clustered indexes enhance performance by storing
table data together with index leaf-level data. This means that whenever
SQL Server 2000 reads the entry of a clustered-index-leaf node, it
simultaneously reads the table’s data row. There can be only one clustered index
per table at a time and the clustered index cannot be separated into different
file groups from the table data.
A non-clustered index, on the other
hand, stores only a unique identifier (rowid) on the leaf level. Consequently,
with non-clustered indexes, additional I/O is necessary to retrieve table data.
The drawback of a clustered index is
apparent when a clustered index and a non-clustered index exist on the same
table. The non-clustered index does not point directly to the table row, but it
uses the clustered-index values as pointers to table rows. Consequently, any
read operation going through a non-clustered index has to first descend the
B-tree of the non-clustered index. After finding the value on the leaf level, it
has to descend the B-tree of the clustered index. In addition, an UPDATE of
clustered-indexed columns causes row movements because the updated rows have to
be placed according to the sort order of the clustered index.
Plan indexes carefully, trying for a
minimal set of indexes that are useful for the specific read/write operations in
your application. If there are too many indexes, the optimizer spends too much
time creating and evaluating potential execution plans. In addition, too many
indexes decrease write performance. You should build non-clustered indexes on
columns for queries that are restricted in the WHERE predicate and have a high
selectivity.
Clustered indexes accelerate
operations in the following situations:
·
Queries that need to have sorted results or
sorted intermediate results, for example, JOIN operations.
·
Queries that fetch one or more steady intervals.
These queries usually contain the BETWEEN predicate in the WHERE clause. The
table rows sorted by clustered indexes are placed according to the sort order of
the clustered index. Consequently, queries SELECT ranges of values with minimal
I/O reads.
Clustered indexes slow down
operations on any other indexes. You should be careful while using clustered and
non-clustered indexes together on one table:
·
If a non-clustered index is highly accessed and
traversed, the clustered index will also be traversed each time in order to
retrieve the actual row.
·
Creating a clustered index on larger columns or
indexes with more than one column causes the B-tree index structure to become
deeper. For faster data-access times, you should try to keep the clustered index
as slim as possible.
·
Using clustered indexes on columns that are
frequently updated can cause extensive page overflowing and re-indexing.
Consider using non-clustered indexes
first wherever indexes are needed. Examine the columns in the WHERE predicate
and the SELECT list. A covering index operation is possible if the index
includes all columns of the WHERE predicate and the columns of the SELECT list.
The precondition here is that the value of the left-most index column must be
given in the WHERE predicate when composite indexes are used; otherwise,
SQL Server 2000 will not take this index into consideration.
Use the query analyzer to monitor
whether a certain query uses the indexes you defined. Copy the query you want to
evaluate to the query-entry window and press the shortcut ”Display estimated execution plan” or press
CTRL-L. The window that appears will display the graphical query execution plan.
The query is not actually run, but estimates of the resource requirements are
shown.
For example, suppose an application
stores customer’s orders in a master-detail relationship between the customer
and the Orders tables. The following example SQL statement selects the orders of
the customer with the customer ID 2020:
select ct.cust_name, od.ord_item_id,
od.ord_it_name
from customer ct, orders od
where ct.cust_id = 2020 and
ct.cust_id = od.cust_id
You can use the Query Window in
Query Analyzer, as shown in Figure 1, to trace whether indexes are used
appropriately:
`
Figure 1. Query execution plan without indexes
In Figure 1, the graphical output
shows two table scans followed by a nested-loop JOIN operation. To improve the
performance of this query, you could create two indexes that support the
restriction in the WHERE predicate, as shown in the following code example:
create unique clustered index idx_cust_pk on
customer(cust_id)
create unique clustered index idx_ord_pk on
orders(cust_id, ord_item_id)
To confirm that the new indexes are
used in the query, you can once again use Query Analyzer, as shown in Figure
2.
Figure 2. Query execution plan with indexes
Both indexes are now used to support
the query.
More information on indexes and
query-execution plans can be found in the following resources:
·
Deploying Microsoft SQL Server 7.0, "Planning
Indexes," Page 234
·
"Graphically Displaying the Execution Plan Using
SQL Query Window" in SQL Server Books Online
Data is stored in two different
database objects: tables and indexes. SQL Server 2000 tables store data in
sequential non-ordered or ordered memory structures. The sequential non-ordered
memory structure is called a heap table and the
ordered memory structure is called a clustered
table. The main differences between the two memory structures are that the
rows of heap tables are stored in random sequence and the pages are only linked
sequentially, whereas the rows of clustered tables are sorted according to the
column(s) of the clustered index. Consequently, the placement of a new row is
calculated during the INSERT using the index value, and the row is stored in the
appropriate page. By default, each data and leaf-level index page will be filled
to 100%. If the table and its indexes grow in the future, you risk
page-splitting and rebalancing the B-tree. The same is true if the table is
updated by rows that are longer than the original values. Page-splitting can
have negative influence on I/O performance. To prevent page splitting, use the
following parameters:
|
Parameter |
Function |
|
Fillfactor
|
Empty space to leave on each
page |
|
Pad_index |
Specifies the space to leave open
on each page (node) in the intermediate levels of the
index |
Table 4. Parameters to prevent page splitting
The fillfactor parameter affects table data pages and index
leaf level pages whereas the pad_index parameter
affects the non-leaf level (nodes) of a B-tree index. You must always use the fillfactor parameter with the pad_index parameter because the pad_index parameter uses the fillfactor value.
To optimize I/O performance, you
must achieve a balance between keeping the overall size of the database down by
minimizing free space on pages and avoiding page-splitting by applying a
fillfactor.
It is not recommended that you
change the default value in read-only applications such as data warehouse
applications. Data warehouse applications are incrementally loaded, which means
that primary key values are inserted in increasing order, avoiding any
page-splitting issues.
It is recommended that you reduce fillfactor and pad_index to
a value of less than 100% when an INSERT in ordered memory structures is
expected, such as an index, a clustered table, or an UPDATE with rows that may
be longer than the original rows.
The fillfactor option is used in the
CREATE TABLE or CREATE INDEX statement, and the pad_index option is used in the
CREATE INDEX statement.
Tables are created with the
following T-SQL statement:
CREATE TABLE(….)
[WITH FILLFACTOR = value]
[ON file_group]
Nonclustered indexes are created with the
following T-SQL statement:
CREATE [UNIQUE] INDEX index_name
ON table_name (column_name,..)
[PAD_INDEX]
[WITH FILLFACTOR = value]
[ON file_group]
Clustered indexes are created with
the following T-SQL statement:
CREATE [UNIQUE] CLUSTERED INDEX
index_name
ON table_name (column_name,..)
[PAD_INDEX]
[WITH FILLFACTOR = value]
[ON file_group]
Be aware of the fact that each
PRIMARY KEY constraint creates a unique clustered index with default values.
Other values than the default, for example, the fillfactor parameter, have to be specified when
creating the PRIMARY KEY constraint.
To monitor page-splitting, use the
Windows 2000 Performance Monitor and choose the Page Splitting counter from
the Access Method object.
Use the following statement in the
query window to display table and index fragmentation in detail. The key
indicator is scan density. A value below 100 indicates some degree of
fragmentation.
DBCC SHOWCONTIG (table_name)
[WITH ALL_INDEXES]
Fragmentation on the leaf level can
be solved online by using the DBCC INDEXDEFRAG statement. The drawback is that
this type of reorganization is not as effective as the following off-line
methods.
In order to solve any page
splitting, rebuild the indexes using the DROP_EXISTING clause:
CREATE [UNIQUE] [CLUSTERED] INDEX
index_name
ON table_name (column_name,..)
[DROP_EXISTING]
[PAD_INDEX]
[WITH FILLFACTOR = value]
[ON file_group]
For example, suppose an application
collects orders from customers. The orders are inserted into the following
table.
CREATE TABLE orders (
customer_id int not null,
item_id int not
null,
order_date datetime not null,
amount int,
….)
Now, suppose that an analysis of
queries run against this table shows that a non-unique clustered index should be
defined on the columns (customer_id, item_id) to improve read performance. In
this scenario, there is a potential danger of page splitting because the
customer_id and the item_id do not grow sequentially with each new INSERT.
Consequently, SQL Server 2000 has to INSERT the new rows according to the
defined sort order in the clustered index. To prevent page splitting, you can
decide to use a value of less than 100 for the fillfactor and pad_index
parameters to create the clustered index and the table. The primary presumption
for the fillfactor parameter is the percentage of
active customers and items in this application that place a certain number of
orders within a certain time interval. The lower this percentage is, the lower
the fillfactor parameter.
The following CREATE INDEX statement
could be used to create an index with a fillfactor of 75%:
CREATE
CLUSTERED INDEX idx_Orders
ON orders
(customer_id, item_id)
DROP_EXISTING
PAD_INDEX
WITH FILLFACTOR
= 75
Note: If you monitor page splitting
on table pages, you have to export the table data, drop the table, create a new
table, and import the table data again. After table data is imported, create the
indexes on the new table.
More information on managing indexes
and page splitting can be found in the following resources:
·
"Heap Tables" and "Clustered Tables" in
SQL Server Books Online
·
"CREATE INDEX and Create table" in
SQL Server Books Online
·
"DBCC SHOWCONTIG" in SQL Server Books
Online
·
Deploying Microsoft SQL Server 7.0, "Planning
Indexes," Page 234ff.
The logical model of the database
determines how you structure your data logically, using tables and
relationships. This design can also have a great impact on the performance of
your application.
Partitioning is a way to split up
table or index data. Data rows are placed in different physical tables and
indexes. Data in a database can be partitioned in one of two ways—vertically or
horizontally. In this section we’ll examine both of these approaches, using the
following sample customer-order data:
|
ord_id |
ord_item_id |
ord_it_name |
amount |
unit_price |
cust_id |
cust_name |
cust_first |
country |
|
1 |
5567 |
Cement |
10.9 |
$120.0 |
A1230 |
Miller |
Justus |
Germany |
|
2 |
9876 |
Concrete |
4.5 |
$60.50 |
B2345 |
Schulz |
Bob |
USA |
|
3 |
3654 |
Blocks |
7 |
$12.60 |
C5679 |
Vogt |
Martin |
USA |
|
4 |
1211 |
Pipes |
3 |
$10.0 |
A1230 |
Miller |
Justus |
Germany |
|
5 |
5567 |
Cement |
12.5 |
$60.50 |
C5679 |
Vogt |
Martin |
USA |
|
6 |
4655 |
Slump |
9 |
$5.55 |
A1230 |
Miller |
Justus |
Germany |
|
7 |
1211 |
Pipes |
133 |
$10.0 |
A1230 |
Miller |
Justus |
Germany |
Table 5. Sample customer-order data
Vertical
Partitioning
The most common kind of partitioning
in a database design is vertical partitioning, usually a result of normalizing
the database. Normalization is the process of separating semantically different
objects. This results in a more compact storage of data, because repetition
groups or NULL values are eliminated. By normalizing a database it is possible
to reduce I/O load on full-table scans and increase database-cache hit ratios.
The only drawback to normalization is that additional joins have to be
performed, which results in greater CPU utilization (and possibly more disk
I/O), if columns of both partitions are required. In such a situation, you have
to monitor elapsed time of those SQL statements that retrieve instances of
tables taking part in the join, to come to a conclusion on which data model is
faster.
You can normalize the customer-order
data shown above by splitting the data into two tables—one for orders
and one for customers. In order to join the two tables, you have to add the
foreign-key column cust_id to the
orders table.
Orders
|
ord_id |
ord_item_id |
ord_it_name |
amount |
unit_price |
cust_id |
|
1 |
5567 |
Cement |
10.9 |
$120.0 |
A1230 |
|
2 |
9876 |
Concrete |
4.5 |
$60.50 |
B2345 |
|
3 |
3654 |
Blocks |
7 |
$12.60 |
C5679 |
|
4 |
1211 |
Pipes |
3 |
$10.0 |
A1230 |
|
5 |
5567 |
Cement |
12.5 |
$60.50 |
C5679 |
|
6 |
4655 |
Aggregate |
9 |
$5.55 |
A1230 |
|
7 |
1211 |
Pipes |
133 |
$10.0 |
A1230 |
Customers
|
cust_id |
cust_name |
cust_first |
country |
|
A1230 |
Miller |
Justus |
Germany |
|
B2345 |
Schulz |
Bob |
USA |
|
C5679 |
Vogt |
Martin |
USA |
Tables 6 and 7. Customer-order data split into two
tables for normalization
Vertical partitioning affects the
design of the application because it increases the number of logical tables. To
a certain extent, changes to the logical model can be abstracted from client
applications using Views. However, any data modifications performed using the
view may require additional code (such as an INSTEAD-OF trigger) to affect
multiple underlying tables.
Normalization has several
performance advantages. In a fully normalized database, data exists only once.
Therefore, the amount of data in database tables is smaller and I/O operations
that include the data of a single table are generally faster. Another benefit of
eliminating data redundancy in the database is that all modification operations
(INSERT, UPDATE, DELETE) are run
in only one place. For instance, if you store a customer name in a table
containing orders, and that customer has placed more than one
order, every UPDATE operation on the customer-name column results in several
operations instead of the one that would be used in a fully normalized database
schema.
On the other hand, normalization has
some performance drawbacks, of which joining tables has the greatest impact. For
this reason, a fully normalized database schema is often not able to provide
optimal performance, especially for queries joining several tables or queries
calculating large amounts of data. In these cases, you may choose to
denormalize the
database to enhance performance.
The process of denormalization can include the following
techniques:
·
Duplicated columns
·
Calculated values
Duplicated
Columns
Joining tables can have a negative
impact on system performance because the JOIN operation is one of the most
time-consuming database operations of all. When a bottleneck is caused by a JOIN
operation, you should consider eliminating foreign keys by duplicating columns
in the referenced tables. For example, the following T-SQL shows a typical
master-detail relationship of customers and orders:
create table customer(
cust_id
int
NOT NULL,
cust_name varchar(20),
cust_first varchar(20),
country
varchar(20))
create table orders(
ord_id int NOT
NULL,
ord_item_id int,
ord_it_name varchar(20),
amount int NOT NULL,
unit_price
money,
cust_id
int)
The following constraints are
applied:
alter table customer add primary key (cust_id)
alter table orders add primary key (ord_id)
alter table orders add foreign key (cust_id)
references customer(cust_id)
The following query retrieves the
total amount of valued orders per customer and item:
select ct.cust_name, ct.cust_first, ct.country,
od.ord_it_name, sum(od.amount * od.unit_price)
from customer ct, orders od
where ct.cust_id = od.cust_id
group by ct.cust_name, ct.cust_first, ct.country,
od.ord_it_name
This results in a JOIN operation.
Duplicating the columns from the
SELECT list and the primary key of the customer table in the orders table helps
avoid the JOIN operation.
create table orders_customers(
ord_id int NOT
NULL,
ord_item_id int,
ord_it_name varchar(20),
amount int NOT NULL,
unit_price
money,
cust_id
int,
cust_id
int
NOT NULL,
cust_name varchar(20),
cust_first varchar(20),
country
varchar(20));
The equivalent query for the new
table is now as follows:
select oc.cust_name,
oc.cust_first,oc.country,oc.ord_it_name, sum(oc.amount * oc.unit_price)
from orders_customers oc
group by oc.cust_name, oc.cust_first, oc.country,
oc.ord_it_name
The drawback of denormalization is
that each time an order is generated and inserted, you also have to insert the corresponding
customer data. In addition, when customer data has to be updated due to a change
in the customer name, all corresponding customer rows have to be updated;
otherwise, the database is inconsistent. Finally, a customer that has been
deleted must be deleted in the order table, or the corresponding entry has to be
set to NULL.
Calculated
Values
A fully normalized database schema
does not store any calculated data, such as sums or averages. Instead these
aggregates are calculated at run time using all detail data. In general, queries
benefit from keeping calculated values in tables instead of generating them at
run time as shown in the example above. The drawback is that you must implement
additional functionality to control data redundancy.
For example, if you want to fetch
the information for customers that have placed orders for more than $10,000
each, you would extend the query in the following way:
select ct.cust_id, od.ord_it_name, sum(od.sale)
from customer ct, orders od
where ct.cust_id = od.cust_id and(od. amount *
od.unit_price) > 10.000
group by ct.cust_id, od.ord_it_name;
In this case, SQL Server 2000
is not able to take advantage of any index that supports the new restriction
because the restriction is calculated as an expression (function) of two
columns. In order to support this type of restriction, add to the original
orders table a computed column that can be named sale. A sale is computed as
unit_price * amount.
Alter table orders add sale AS amount *
unit_price
While this defines the calculation
as a part of the table, the expression is still resolved at runtime, as shown in
the query-execution plan in Figure 3.
Figure 3. Calculated column resolved at runtime
The compute scalar node shows that
the sale column is not stored permanently in the database; rather, it is
calculated at runtime. Computed columns can be used in SELECT lists, WHERE
clauses and ORDER BY clauses.
To improve SELECT performance
further, create an index on the computed column. This (function-based) index
stores the results of the function (sale = amount * unit_price) in the database.
Consequently, the query engine can take this access path into consideration. A
precondition for indexing computed columns is that the column has to be
deterministic (for further details, see SQL Server Books Online:
Deterministic and Nondeterministic Functions). The statement to create the
required index is shown below:
create index idx_sale on orders(sale)
The execution plan in Figure 4 shows
how the index is used when retrieving the sales data.
Figure 4. Indexed calculated column
This query plan has the same number
of nodes as the previous plan. Nevertheless, it increases response time because
the query engine is able to take advantage of the restriction given in the WHERE
predicate that specifies sales
greater than $10,000. SQL Server does not have to scan through all the rows
of the order table and eliminate all those that do not meet the restriction, as
in the previous execution plan, but it fetches them directly by using the new
index idx_sale.
Generally, denormalizing the
database schema is not recommended because there are some negative effects:
·
Denormalization significantly modifies the design
of your database.
·
You need to implement a strategy to avoid update
anomalies.
Applying denormalization to your
database schema is recommended only in the case that performance cannot be
achieved by applying the recommendations given in the previous chapters.
Good candidates for duplicating are
usually all nonvolatile columns, that is, columns whose values are rarely
modified. If you have to duplicate volatile columns, it is strongly recommended
that you create a corresponding trigger to ensure data integrity.
Calculated values are not stored in
the database, but are computed each time you SELECT them. You do not have to
UPDATE them when an INSERT or UPDATE is made to the underlying columns.
The denormalization process should
be done step by step, if at all, and must be carefully planned and executed.
Search for parts of the database that can be denormalized to achieve better
performance only if the expected performance goals are not met.
More information on normalization
and denormalization can be found in the following resources:
·
Conceptual Database Design—An Entity-Relationship
Approach by Carlo Batini, Stefano Ceri, and Shamkant Navathe
·
Database Modeling and Design by Toby J. Teorey
·
A Practical Guide to Logical Data Modeling by George
Tillmann
·
"Creating Indexes on Computed Columns " in
SQL Server Books Online
·
"Deterministic and Nondeterministic Functions "
in SQL Server Books Online
Horizontal
Partitioning
Horizontal partitioning is a way to
split large numbers of table rows into multiple tables called partitions. The
tables all have equal structures; that is, the same column names and data types.
Horizontal partitioning involves partitioning criteria (for example, the time
column) that dedicate table rows to the different partitions. Each partition
contains a certain range of values. As an example, each month of a year is split
to its own partition. There are two main performance-related reasons for
horizontally partitioning a table:
·
To distribute the data among different physical
file groups in a single database, thereby minimizing resource contention.
·
To distribute the data across multiple servers,
thereby scaling out the database to enhance concurrency.
·
To horizontally partition our
customer order data, we could split the orders_customers table into four
partitions using the ord_id column
as our partitioning criteria.
Partition
Orders_customers_part1
|
ord_id |
ord_item_id |
ord_it_name |
amount |
Unit_price |
cust_id |
cust_name |
cust_first |
country |
|
1 |
5567 |
Cement |
10.9 |
$120.0 |
A1230 |
Miller |
Justus |
Germany |
|
2 |
9876 |
Concrete |
4.5 |
$60.50 |
B2345 |
Schulz |
Bob |
USA |
Partition
Orders_customers_part2
|
ord_id |
ord_item_id |
ord_it_name |
amount |
Unit_price |
cust_id |
cust_name |
cust_first |
country |
|
3 |
3654 |
Blocks |
7 |
$12.60 |
C5679 |
Vogt |
Martin |
USA |
|
4 |
1211 |
Pipes |
3 |
$10.0 |
A1230 |
Miller |
Justus |
Germany |
Partition
Orders_customers_part3
|
ord_id |
ord_item_id |
ord_it_name |
amount |
Unit_price |
cust_id |
cust_name |
cust_first |
country |
|
5 |
5567 |
Cement |
12.5 |
$60.50 |
C5679 |
Vogt |
Martin |
USA |
|
6 |
4655 |
Slump |
9 |
$5.55 |
A1230 |
Miller |
Justus |
Germany |
Partition
Orders_customers_part4
|
ord_id |
ord_item_id |
ord_it_name |
amount |
Unit_price |
cust_id |
cust_name |
cust_first |
country |
|
7 |
1211 |
Pipes |
133 |
$10.0 |
A1230 |
Miller |
Justus |
Germany |
Tables 8, 9, 10 and 11. Horizontal
partitioning of sample customer-order data
In order to enforce the partitioning
criteria, a CHECK constraint is used. For example, the CREATE TABLE statements
for the tables shown above would be as follows:
CREATE TABLE
Partition_Orders_customers_Part1
(ord_id integer primary key
CONSTRAINT ckPartition1 CHECK(order_id > 1 AND ord_id < 3),
ord_item_id integer,
ord_it_name varchar(20),
amount double,
Unit_price money,
Cust_id char(5),
Cust_name varchar(20),
Cust_first varchar(20),
Country varchar(20))
CREATE TABLE
Partition_Orders_customers_Part2
(ord_id integer primary key
CONSTRAINT ckPartition2 CHECK(order_id > 2 AND ord_id < 5),
ord_item_id integer,
ord_it_name varchar(20),
amount double,
Unit_price money,
Cust_id char(5),
Cust_name varchar(20),
Cust_first varchar(20),
Country varchar(20))
CREATE TABLE
Partition_Orders_customers_Part3
(ord_id integer primary key
CONSTRAINT ckPartition3 CHECK(order_id > 4 AND ord_id < 7),
ord_item_id integer,
ord_it_name varchar(20),
amount double,
Unit_price money,
Cust_id char(5),
Cust_name varchar(20),
Cust_first varchar(20),
Country varchar(20))
CREATE TABLE
Partition_Orders_customers_Part4
(ord_id integer primary key
CONSTRAINT ckPartition4 CHECK(order_id > 6),
ord_item_id integer,
ord_it_name varchar(20),
amount double,
Unit_price money,
Cust_id char(5),
Cust_name varchar(20),
Cust_first varchar(20),
Country varchar(20))
To retrieve data from the
partitioned tables, use a view that combines all tables with the UNION operator.
SQL Server 2000 supports partitioned views, keeping the application design
transparent, which means that it does not affect the syntax of the SQL
statements. With regards to performance, whenever you have to change the
partitioning criteria (for example, when you apply partitioning using a
different column or add a new partition to an existing set of partitions due to
data growth) the syntax of the SQL statement still remains the same. The DBA has
only to modify the partitioned view.
There are two types of partitioned
views:
·
Local Partitioned
Views: all partitions reside on one SQL Server 2000 instance.
·
Distributed Partitioned
Views: partitions are spread across different SQL Server 2000 instances
(known as a server federation). In other words, each member table (one
partition) of the partitioned view can be stored on a separate member
server.
To create a local partitioned
view:
1. Generate as many partitioned tables as necessary using
the CREATE TABLE statement.
2. Apply a CHECK constraint on the column that contains the
criteria for partitioning.
3. Create a view using the UNION ALL operator over all
partitions, as shown below.
CREATE VIEW customer_orders
AS
SELECT * FROM
Partition_orders_customer_part1
UNION ALL
SELECT * FROM
Partition_orders_customer_part2
UNION ALL
SELECT * FROM
Partition_orders_customer_part3
UNION ALL
SELECT * FROM
Partition_orders_customer_part4
Note: Designing a federated server
solution using distributed partitioned views is discussed in the next section of
this chapter "Scaling Out with Federated SQL Servers."
It is advisable to use horizontal
partitioning in the following situations:
·
Where a request cannot be supported by an index.
For example, when the requested ranges are simply too large, scanning one or a
few partitions is much less I/O intensive than scanning the whole table. This is
true if requests can be fulfilled by only one or a few partitions. The reason
for this is that SQL Server 2000’s query engine only takes those partitions
that fulfill requests into account (provided that the query contains the column
of the partitioning CHECK constraint in the WHERE predicate).
·
To fulfill parallel SQL Server requests., SQL Server 2000 parallel
query execution, such as parallel query scans, can be supported by hardware.
Partitions can be placed to different disks or even different SQL Server
2000 instances.
·
Write-intensive operations on indexed tables.
·
Direct-access patterns or SQL queries that use
indexes. These can benefit from B-trees with fewer levels. Indexed partitions
compared to the whole indexed table produce shallower B-trees. Smaller B-trees
are re-balanced much faster if page splitting occurs due to write operations on
the index and a smaller B-tree is traversed in a smaller amount of time by
SELECT queries.
In addition, well-designed
partitions help DBAs to maintain their databases. On a partition level, DBAs can
perform all of the operations they might normally perform on a table level. For
example, partitions created by a time criteria support exporting or importing
historical data. Space for new data can be allocated by adding a new partition
and old data can be removed by dropping an existing partition.
Horizontal partitioning is
recommended only as long as the query engine restricts the access to those
partitions that are known to contain the relevant data. If you use local or
distributed partitioned views in more complex queries, such as joins with the
other tables, the query engine might use a different plan containing all
partitions. To avoid this, you must carefully design your logical database
schema so that related data is horizontally partitioned in such a way that
cross-partition joins are minimized.
For more information on horizontal
partitioning, see the following resources:
·
"Using Portioned Views" in SQL Server Books
Online
·
"Creating a Partitioned View" in SQL Server
Books Online
·
"Designing Partitions" in SQL Server Books
Online
The indexed view feature of
SQL Server 2000 allows you to store the data set specified in the SELECT
statement of the view that is permanently in the database. This is also known as
“materializing the view.” This redundant storage of the view’s contents has the
advantage of accelerating certain queries, at the cost of additional disk usage.
SQL statements that reference the view or the base table(s) can retrieve the
data from the pre-aggregated data set made up of data that is stored as an
indexed view. In other words, the query engine takes the indexed view into
consideration even if the query addresses the underlying tables only. Inserts
and updates to the base table(s) are automatically applied on the storage set of
the indexed view.
An indexed view is created from a
regular view by applying a unique clustered index to the view. The specified
data set is then stored as a cluster or ordered-memory structure. After creating
the clustered index, any other non-clustered index can be applied on the storage
set.
It is recommended that you use
indexed views for the following situations because of the substantially better
response times that result.
·
For read-intensive applications. Indexed views
are less recommended in write-intensive environments because inserts or updates
of the underlying tables mean that SQL Server 2000 has to maintain
additional updates for the indexed views.
·
In a situation where SELECT statements retrieve a
result set that is an aggregate, including summation or other calculations from
one or more different tables. In this case, a pre-aggregate result set stored as
an indexed view speeds up read performance tremendously.
To create an indexed view, do the
following:
1.
Create a view with the SCHEMABINDING option. This
option does not allow changes to the schema of any of the underlying objects
participating in the materialized view. You cannot create an index on any view
without using this option.
2.
Create a unique clustered index on the view. The
view is materialized at this point.
3.
Create any other non-clustered indexes on the
view
More information on indexed view can
be found in the following resources:
·
"SQL Views" in SQL Server Books Online
·
"Creating an Indexed View" in SQL Server Books
Online
·
"Using Indexes on Views” in SQL Server Books
Online
·
"Resolving Indexes on Views" in SQL Server Books
Online
·
"Designing an Indexed View" in SQL Server Books
Online
·
"Creating Indexes on Computed Columns" in SQL
Server Books Online
·
"Introducing Indexed Views," by Kalen Delaney;
SQL Server Magazine, May 2000.
This
section contains guidelines on how to code T-SQL statements for optimal
performance.
Record-oriented database access
means that an application sends requests for each row to the database server. In
the worst case, a request is equivalent to one SQL statement that retrieves the
requested row. The database is used as a ‘"dummy" access operator, which
supplies rows for the application. The logic for row interpretation is
implemented in the client layer.
Set-oriented database access means
that an application’s request returns a certain quantity of rows from the
database at one time. With this access method, you involve the logic of the
database in the processing of records. In the most extreme situations, the total
request is performed by the database, with no involvement of other tiers.
The advantage of a set-oriented
approach over a record-oriented approach is that communication between the
client and the database is reduced. Records do not have to be transferred to the
client, but can be processed on the server. In addition, you take advantage of
all the features of a database server that can improve performance, such as the
caching mechanism or indexing strategies. A drawback of using set-oriented
queries is that scalability can be reduced because the database is a shared
resource and is important to keep the user-specific processing to a minimum.
It is recommended that you code
T-SQL statements to be as set-oriented as possible. In other words, you must try
and replace IF……ELSE flow control statements with the SQL Server 2000 CASE
function whenever possible. The whole operation is then processed in the context
of the server side and the performance gain is tremendous because the
intelligence of the database server is utilized.
For example, customers may be
classified in an application according to their revenue. The customer’s actual
revenues may be stored in the following revenue table:
Create table revenue(
Cust_id
int
NOT NULL PRIMARY KEY,
Tot_sale
money,
Cust_type
varchar(2))
Customer’s orders are placed in the
following order table:
create table orders(
ord_id int NOT NULL
PRIMARY KEY,
ord_item_id int,
ord_it_name varchar(20),
amount int NOT NULL,
unit_price
money,
sale AS (amount * unit_price),
cust_id
int)
The revenue of each customer is
identified and classified according to the methodology of the ABC analysis. "A"
customers have revenue of more than $10,000; "B" customers have a range between
$1,000 and $10,000; and "C" customers have revenue of less than $1,000. INSERT
this information in the revenue table.
For the first solution, presume that
you have 100 customers, the cust_id increments from 1 to 100. In such a case,
the following code using a single SELECT statement will work:
CREATE PROCEDURE classify_cust
AS
DECLARE @h_ci int, @h_tot_sale money,
@h_cust_type varchar(2)
SET @h_ci =0
while @h_ci <= 100
SET @h_ci = @h_ci + 1
BEGIN
Select sum(sale) AS sales INTO h_tot_sale
From orders
Where cust_id = @h_ci
IF @h_tot_sale <= 1000
SET @h_cust_type = 'C'
IF @h_tot_sale > 1000 and @h_tot_sale <
10000
SET @h_cust_type = 'B'
ELSE
SET @h_cust_type = 'A'
insert into revenue(cust_id, tot_sale, cust_type)
values (@h_ci, @h_tot_sale, @h_cust_type)
END
Within this procedure, each record
of the table orders is processed individually. Use the singleton SELECT
statement SELECT … INTO … This SELECT statement is processed for each record.
The database is utilized as a dummy access operator, which supplies rows for the
application. The total intelligence for row interpretation lies with the host
(client) context.
A better way would be to use a
cursor and fetch the data from the cursor. The advantage to the previous
solution is that the SELECT statement is processed only once. The result set is
held in the cursor. Each row is then fetched from the cursor for further
processing in the context of the host variables.
CREATE PROCEDURE classify_cust
AS
DECLARE @h_ci int, @h_tot_sale money,
@h_cust_type varchar(2)
DECLARE cust_sale CURSOR FOR
Select cust_id, sum(sale) AS sales
From orders
group by cust_id
OPEN cust_sale
FETCH NEXT FROM cust_sale INTO @h_ci,
@h_tot_sale
WHILE @@FETCH_STATUS = 0
BEGIN
IF @h_tot_sale <= 1000
SET @h_cust_type = 'C'
IF @h_tot_sale > 1000 and @h_tot_sale <
10000
SET @h_cust_type = 'B'
ELSE
SET @h_cust_type = 'A'
insert into revenue(cust_id, tot_sale, cust_type)
values (@h_ci, @h_tot_sale, @h_cust_type)
FETCH NEXT FROM cust_sale INTO @h_ci,
@h_tot_sale
END
CLOSE cust_sale
DEALLOCATE cust_sale
In this solution, continue to
process each single record by fetching it to the host (client) variables (h_ci,
h_tot_sale, h_cust_type) in order to classify the customers using the IF … ELSE
construct followed by an INSERT statement in the revenue table.
SQL Server 2000 can perform this
iteration completely without processing each single record to the host side.
Therefore, use the set operator CASE, which can be used within the SELECT list
of a T-SQL statement.
insert into revenue(cust_id, tot_sale, cust_type)
Select cust_id, sum(sale) AS tot_sale, cust_type = CASE
WHEN sum(sale) <= 1000 THEN
'C'
WHEN sum(sale) > 1000
and sum(sale) < 10000 THEN 'B'
WHEN sum(sale) >= 10000
THEN 'A'
END
From orders
group by cust_id
The whole procedure is now written
in one T-SQL statement. The total processing is being performed completely
within the database server. Thus, the number of communications between the
client and the database server is reduced to a minimum. The server takes over
the quantity-oriented processing, contrary to row-type processing demonstrated
in the first and second examples.
The sample database shows these
response times for the different access patterns:
|
Elapsed time |
CPU in ms |
I/Os |
|
Solution 1 |
14200 |
4317 |
53103 |
|
Solution 2 |
12916 |
4126 |
57602 |
|
Solution 3 |
1170 |
100 |
4314 |
Table 12. Response times for different T-SQL
implementations to return identical data
More information on Transact-SQL can
be found in the following resources:
·
"CASE" in SQL Server Books Online
·
The Guru’s Guide to Transact-SQL by Ken Henderson
SQL is a non-orthogonal language;
that is, it is possible to use different SQL statements to get an equivalent
result set. Generally, each variation can have different performance drawbacks
as a result of the way the query processor parses the statements (for example,
amount of data to be retrieved, number of rows in the tables, physical data
model). It is not possible to give a general recommendation on which formulation
is preferred, but you should be aware of the fact that there are different ways
to retrieve equivalent result sets.
This section introduces three
solutions to retrieve the same set of data. This example extends the example
from the previous section, "Record-Oriented Versus Set-Oriented SQL." In this
scenario, you want to see orders that do not have an entry in the revenue
table.
Customer’s actual revenue is stored
in the following table:
Create table revenue(
Cust_id
int
NOT NULL PRIMARY KEY,
Tot_sale
money,
Cust_type
varchar(2))
Customer’s orders are placed in the
following order table:
create table orders(
ord_id int NOT NULL
PRIMARY KEY,
ord_item_id int,
ord_it_name varchar(20),
amount int NOT NULL,
unit_price
money,
sale AS (amount * unit_price),
cust_id
int)
There are three possible solutions
for retrieving set differences that meet the user’s request. They are as
follows:
·
Non-correlated subquery
·
Correlated subquery
·
Outer join
Non-correlated Subquery
This subquery is non-correlated
because no point of reference exists between the inner and outer SELECT
statement:
select od.ord_id, od.ord_it_name, od.amount
from orders od
where cust_id NOT IN (select cust_id from
revenue)
Therefore, the inner SELECT
statement is always initially evaluated and then its result is transmitted to
the outer SELECT statement, as shown in Figure 5.
Figure 5. Query execution plan for a non-correlated
subquery
To get the difference between the
orders table and the revenue table, the query engine spills the data of the
cust_id column of the revenue table in a temporary table. This data is then
scanned for each entry in the primary key of the orders table due to the nested
loop join that performs the (outer) join.
Correlated
Subquery
This subquery contains an iterative
reference or per-data row with the outer SELECT statement:
select od.ord_id, od.ord_it_name, od.amount
from orders od
where NOT EXISTS (select rv.cust_id from orders od,
revenue rv where od.cust_id = rv.cust_id)
Therefore, correlated subqueries
must be supplied and processed with the column value per row of the outer SELECT
statement, as shown in Figure 6.
Figure 6. Query execution plan for a correlated
subquery
In this case, the query engine first
(inner) joins the revenue table and the orders table of the subquery. The
resulting cust_id data is spilled to the temporary table. Then, the nested-loop
join performs the necessary outer JOIN operation to determine the set
difference.
Outer
Join
Similarly, you can use a SELECT
query to retrieve the same result set using a JOIN condition.
SELECT od.ord_id, od.ord_it_name,
od.amount
FROM orders od LEFT OUTER JOIN revenue rv ON od.cust_id =
rv.cust_id
WHERE rv.cust_id is NULL
This is shown in Figure 7:
Figure 7. Query execution plan for an outer join
This execution plan is similar to
that of the noncorrelated subquery except for the filter node. The filter node
is necessary to determine which rows are specified in the WHERE predicate.
Using an Index to
Avoid a Temporary Table
You have seen three syntactically
different SQL statements that are semantically equal—they all retrieve the same
row set. Each of them: the noncorrelated subquery, the correlated subquery, and
the outer join solution, shows different execution plans. Which one is faster
depends on the specific situation (actual data volume, current load, etc.).
All three execution plans have one
thing in common: The query engine has to spill data rows to a temporary table
(stored in the tempdb database and existing only for the lifetime
of the query). Depending on the data volume, this could result in a long elapsed
time for the query. To avoid this, we show an indexing strategy that results in an execution plan
without a temporary table:
1. Drop the primary key on the
orders table.
2. Create a new primary key with the
following option:
alter table orders add CONSTRAINT pk_nc primary key
NONCLUSTERED (ord_id);
3. Sort the table data according to
the foreign key:
create clustered index idx_orders on
orders(cust_id)
Figure 8 illustrates this execution
plan:
Figure 8. Query execution plan with clustered
index
The new execution plan does not use
a temporary table. The nested-loop JOIN algorithm is replaced by a merge JOIN
because the query engine then takes into account the fact that the data of both
tables is now stored in the sort order of the join criteria. SQL Server
2000 has only to retrieve the rows sequentially and merge them in order to
perform the outer JOIN operation.
As you have seen in this example,
there are usually many ways to write SQL statements. It is recommended that you
evaluate each SQL statement and then implement the most appropriate one for your
performance needs. In this case, the temporary table can result in a performance
bottleneck. The only possible way to avoid it is to create a clustered index on
the key used in the join.
More information on using joins and
subqueries can be found in the following resources:
·
Hash Joins and Hash Teams in Microsoft SQL Server
by Graefe, Bunker, and Cooper
·
Understanding the New SQL: A Complete Guide by Simon
Melton
·
"Creating Outer Joins" in SQL Server Books
Online
This section helps you meet
performance goals in a situation where sorted result sets are required. Ordered
rows are processed strictly by using the ORDER BY clause. Using DISTINCT or
UNION operators requires sorting in order to remove duplicate values.
When a sort order is not specified,
rows are generally selected according to their physical placement on the disk
and the query engine's chosen access strategy.
Both JOIN operations and sorting
data can be very time consuming. Therefore, one of your tuning goals should be
to avoid sorting within the access plan. Rewriting queries that require sorting
is not recommended, but it is advisable to find an appropriate storage structure
for your queries that require sorting, so that sorting becomes unnecessary.
An example of this is if you want to
analyze a customer’s orders, sorted according to their amount. By default, the
rows are selected in ascending order.
select ord_id, cust_id, ord_it_name, amount
from orders
where cust_id = ‘value’
order by amount
Index with
Sort Column
Customer orders are placed in the
following order table:
create table orders(
ord_id int NOT
NULL,
ord_item_id int,
ord_it_name varchar(20),
amount int NOT NULL,
unit_price
money,
sale AS (amount * unit_price),
cust_id
int)
alter table orders add primary key NONCLUSTERED
(ord_id)
create index idx_ord1 on orders(cust_id)
SQL Server 2000 uses the
execution plan shown in Figure 9 to determine the result set:
Figure 9. Execution plan to
sort data
The access plan shows three typical
nodes: an index scan to determine the value specified in the WHERE predicate, a
bookmark lookup to retrieve the data specified in the SELECT list, and a sort
node to sort the results according to the ORDER BY clause.
The optimizer decides how sorting
can be best accomplished, depending on the available physical access options and
other criteria. Now you can attempt to investigate the effects of using a sorted
physical access. The tuning strategy presented here is meant to reduce the use
of resources for processing a SQL statement. The execution plan demonstrates
three steps that need to be followed to process the SQL statement. Performance
improvements can now be realized through the reduction of the three steps.
Create index idx_ord2 on orders (cust_id,
amount)
In this case, the given value of
cust_id (cust_id = ‘value’) is sorted by the column amount. With this
information, the optimizer should be motivated to establish the hit rate with
the help of the index, as shown in Figure 10.
Figure 10. Execution plan without sorting
As is obvious from this execution
plan, the optimizer establishes the hit rate without additional sorting. This
means that the optimizer recognizes the physical order in index idx_ord2 without
any further manual intervention. The following two nodes that remain in the
execution plan:
·
The non-clustered index idx_ord2 determined the
hit rate, satisfying the query condition in the WHERE predicate.
·
The remaining columns in the SELECT list are
retrieved by accessing the base table, which is indicated by the bookmark
lookup.
Index-Only
Access
Another potential tuning
optimization can be implemented for the query shown in the previous example. The
expression list contains only the columns ord_id, cust_id, ord_it_name, and amount. These columns are redundantly stored in the
following index:
Create index idx_ord3 on orders(cust_id, amount, ord_id, ord_it_name);
Figure 11. Execution plan with covering index
Notice that, in this execution plan,
the result set is now completely retrieved from the index idx_ord3. Sorting and
accessing the base table is eliminated. This is known as a covering-index
operation or an index-only access.
Clustered
Index Access
An alternative to the covering-index
operation is a clustered table that covers the data rows according to the
required sort order.
Create unique clustered index idx_ord4 on
orders(cust_id, amount,
ord_id);
It is not necessary to involve all
columns in the new index because SQL Server 2000 retrieves the whole row on
the leaf level of the clustered-index tree, as shown in Figure 12.
Figure 12. Execution plan with clustered index
In this execution plan, the query is
again satisfied only by accessing the clustered index and not by sorting the
rows returned from the actual data pages. You have obtained a "single row"
execution plan similar to that obtained through the covering-index operation.
But this concept goes further and has two advantages:
·
The storage structure selected here enables
simultaneous reading and selecting of the index idx_ord4 entries and the
corresponding rows of the base table orders.
·
There is no need to store all columns in the
expression list redundantly in an index.
The first step in finding
performance bottlenecks is to identify the important functions or statements
that are responsible for the highest load. Define the characteristic values
within a specified time window regarding the operation frequency, priority,
requested response time, and load distribution. You can only achieve the
performance goal of the application if the design of these functions is optimal.
Collect the use cases for
performance measurements. The following questions should help, in addition to
any questions that relate to other factors that are important in certain
situations:
·
What are the most frequent tasks (calls from end
users, interface calls, system calls, etc.)?
·
What are the most critical functions regarding
elapsed time (for example, selecting data from different tables that fill one
end-user dialog)?
·
What are the most important functions (main task
of your application)?
·
What functions consume the most resources
(functions that are very complex regarding the implemented algorithm)?
Collect each function or statement
in a script so that you can run them against your database. For example, the
function can be one or a group of SQL statements, T-SQL procedures, or a part of
your code that sends T-SQL statements to the database and retrieves data. To
test and measure your tuning interventions, you should run the collected scripts
multiple times to ensure performance during different stages of your development
process.
You have now been introduced to many
different aspects of SQL Server 2000 that can be optimized, such as
configuration, physical and logical data models, and SQL statements. This
section gives an overview of how to measure performance using the tools that
come with SQL Server 2000.
The overall performance of a system
can be generally defined by the elapsed time of a certain use case or a test
case. The elapsed time is the amount of time it takes to complete the whole use
case, which is also the time the user must wait to finish the use case. For
performance issues, elapsed time of a test case is defined as follows:
Elapsed time = CPU time + I/Os
measured
While this is definitely not a
correct mathematical calculation of the total amount of time, it helps you
determine whether the selected test case is CPU or I/O bound and provides
guidelines for further tuning interventions.
To monitor and trace the
SQL Server 2000 application, use the following three tools:
·
SQL Performance Monitor
·
SQL Profiler
·
SQL Query Analyzer
Use the SQL Performance Monitor to
measure CPU activities, I/O activities, and memory usage.
To measure CPU utilization, run your
test case to make sure that work is being done. To monitor CPU utilization, use
this predefined counter:
·
The % user-time counter of the Processor
object
You should select all processors by specifying the number
that appear in the window. The
counter gives the processor utilization per second.
It is important to note that if the
processor utilization stays continuously at 80% or above, you have a CPU
bottleneck. You should then start to analyze your test case in detail. Some
possible reasons for the bottleneck are as follows:
·
Number and type of SQL statements
·
Number and type of JOIN operations
·
Number of sort operations
Use the SQL Profiler and/or SQL
Query Analyzer to get a more detailed analysis of the identified query.
The following predefined counter is
useful for monitoring disk activities:
·
The disk transfers per second counter of the
Physical disk object.
The following are possible causes of
an I/O bottleneck resulting from large disk activities:
·
Physical data model design, for example, number
of data and index files
·
RAID configuration, for example, number of
storage sets
·
Logical data model, for example, partitioning
·
SQL statements, for example, transaction
design
The data cache is the most important
aspect of SQL Server 2000 .
The following predefined counter is reserved to measure the percentage of data
pages that are found in the data cache rather than read from the disk:
·
The Buffer Cache Hit Ratio counter of the Buffer
Manager object.
It is recommended that you have a
cache-hit ratio of more than 95%. If the monitored value is significantly lower,
do the following:
·
Check the configuration of the max-server-memory
parameter in SQL Server 2000
·
Add more memory
Use the SQL Profiler to find
critical SQL statements and measure the effects of tuning interventions. With
the SQL Profiler, you can clearly monitor the time a single SQL statement takes
to run. Focus your attention on the duration, reads, writes, and CPU columns to
find the critical SQL statements.
Use the SQL Query Analyzer to see
the query execution plan and observe the interventions. For specific examples,
please refer to “Set Differences” and “Sorting Data.”
SQL Server 2000 delivers
impressive performance when the application is optimized and the hardware is
scaled up to the appropriate level. But in situations where a fully optimized
database and application support a database load beyond the reasonable
expectations of a single server, scaling out may help. Scaling out provides
scalability for a system where throughput is a higher requirement than providing
highest speed for each individual transaction. By scaling out using
SQL Server 2000, you can support the tremendous processing loads and growth
requirements of the largest Web or enterprise systems.
When you want to get more out of
your application, you will undoubtedly consider a way to make it support a
larger workload and perform faster. However, it is important to appreciate that
scalability is only one of the factors that make up the overall performance of
an application. You will need to prioritize your requirements and balance the
others in a way that works for the system requirements and for the staff who
support the system. When discussing a database's performance, it is common for
database professionals to measure success in terms of throughput, response time,
and availability.
Throughput is a measurement of how
many concurrent transactions the database can successfully handle. This is
commonly measured in transactions per second, but can be more accurately
measured by tracking \SQLServer:SQL Statistics\Batch Requests/sec in relation to
\Processor(_Total)\% Processor
Time. These two counters should rise and fall in parallel. Actual response time
can be measured on the server as the time elapsed per query .
Perceived response time involves all
parts of the system and can be measured as the interval of time between a user
issuing a request and receiving the results. In general, most high-scalability
approaches aim to maximize throughput, but you should always consider the
effects of any design on response time. To be considered scalable, a database
application must support the required throughput for the anticipated number of
concurrent users while providing acceptable response times for all user
requests.
Before
making the decision to scale out, you should consider your application design
carefully. Scaling out comes at the cost of adding complexity to the design and
management of the system. Scaling out should be a final step in increasing the
scalability of an already highly scalable and high-performing database. In most
cases, you should scale up, rather than out, unless you are dealing with a
distributed system.
The following list provides some
initial design considerations, which can also be used to determine how much your
system might benefit from scaling out, whether it is a current system, a new
one, or one being migrated from another database platform.
·
Are you trying to support a high volume of
concurrent transactions? The decision to scale out is most often driven by the
need to support a database load that exceeds the capacity of a large
multiprocessor server. The transaction volume isn’t the real problem; rather it
is the overall load on the Database Management System (DBMS).
Concurrency
issues are frequently based on application-design problems, but can also be
based on disk I/O limitations. This problem can be addressed by adding more
physical disks, adding more or better array-controller channels, using file
groups on multiple large sets of disks, or significantly increasing the amount
of memory to reduce physical I/O with a larger cache. Because applying standard
solutions can increase concurrency, this issue alone does not make your system a
candidate for a federated scenario.
The key factor
is the processing-capacity requirements. The primary reason for scaling out is
that your requirement exceeds the processor capacity of the largest SMP
(symmetric multiprocessing) hardware available.
·
Is transaction throughput more important than the
speed of each individual query? This question becomes important if your
application frequently requires data from a partition on a remote server. How
often do you need to collect data from multiple servers in order to create a
resultset? The fewer calls that must be made to remote servers to accumulate all
the data to satisfy the request, the better performance will be.
The more
frequently you collect remote data for the total resultset, the greater the
performance issue will become. However, this is less of a concern in cases where
a query is used only occasionally or the query can be directed to the server
containing the majority of the data related to that result set. For example, if
a set of information is frequently queried, you should organize the data and
queries so that the queries run on the same node as the data, and so that as
much related information as possible is available locally on that node.
·
Can you store related partitioned data together
on one server in the federation (this process is known as collocating the data), and then direct the
incoming query to the server that has that data? A key consideration for scaling
out is considering how the data will be organized, and how data access will
occur, along slices of the database.
One
performance consideration is the locality of the data, which is the measure of
how much of a query can be
completed with local data rather than with remote data. If you have a high
degree of data locality, then you will get better performance.
·
How much data needs to move between servers? You
can get this number by multiplying bytes per row by number of rows. If you
frequently transfer a large amount of data, approaching thousands of kilobytes
per rowset, this performance consideration should influence your design. In this
case, look for a more optimal way of partitioning your data, or a better way to
handle data access.
As with
everything else in the database, the right approach to partitioning data depends
on usage. For queries that are used frequently, examine the data involved and
estimate how often these are executed compared to how much data is requested. If
you cannot collocate related data,
calculate the amount of data that you expect will be requested from remote
servers. If the rowsets involved are large (approaching thousands of kilobytes)
and the frequency of the requests is high, consider organizing the data
differently. If the rowsets are large, but the query will be run only
occasionally, this might also provide acceptable performance for the system as a
whole.
·
What is the size of the data involved? It is easy
to assume that you should simply partition your largest tables. Companies
considering this scenario may often have large tables, for example, over a
billion narrow rows or over 20 million wide rows (over 5 KB). However, if you
are using partitioning as part of a scale-out solution, you should partition
based on data-access patterns rather than just choosing the largest tables (even
if they are in the terabyte range). Some partitionable tables are apt to grow
large, but size alone should not be the deciding factor.
Data size is also a factor in the
amount of data exchanged between servers. While large volumes could become an
issue in extreme cases, the linked server connection can easily support
exchanges of hundreds or thousands of rows per transaction, depending on the
byte size of the rows in question. The exchange of large volumes of data becomes
a problem only if the sheer volume of remote traffic degrades response time to
an unacceptable level.
Server federations are groups of
computers running SQL Server on which the data for a particular application
is distributed. In many ways, a server federation is similar to a clustered Web
farm in that it includes multiple servers that share the processing load between
them. However, there is one fundamental difference between a SQL Server
federation and a load-balanced cluster. In a load-balanced cluster, the servers
are exact replicas of one another, allowing all user requests to be serviced by
any member of the cluster. In a SQL Server federation, each server contains
a subset of the data and requests for a specific piece of data can be satisfied
only by the server on which that data is located.
Note: Because the word “cluster” is
frequently used to refer to a group of servers, be careful to distinguish
between a “federation” and a failover cluster. Although the two configurations
can be used together, they are entirely different technologies.
Designing a scale-out solution of
any type requires analysis and careful planning. There are several techniques
that can be applied here:
·
Data-dependent routing
·
Distributed partitioned views
·
User-defined partitioning methods (such as a
hash)
·
Replication
·
Message Queuing (also known as MSMQ)
·
A combination of these
Although these techniques can be
combined in many different ways, depending on your requirements, the classic
federated-server scenario combines the use of distributed partitioned views with
data-dependent routing, usually with some form of replication. This is not the
required design, however, and you may decide instead to simply use one of these
strategies, depending on your needs.
The first step in designing a
federation is to determine how to partition the data. To work properly, the
partition must be created on a key value that can be used exclusively to direct
queries to the correct data partition. For example, in an e-commerce solution,
you may want to partition customer-profile data (such as names, addresses,
e-mail addresses, telephone numbers, and so on) across multiple servers, as
shown in Figure 13.
Figure 13. A SQL Server federation
In this example, SQLServer1 contains
all the customer profile records for customers 1000 to 1999, SQLServer2 contains
customers 2000 to 2999, and SQLServer3 contains customers 3000 to 3999. You can
use any key to partition the data, but you should try to use a key that will
result in a fairly even distribution by usage across the servers. It is vital to understand
that the data must be distributed by usage, not by number of rows. If your
application design does not allow you to distribute data easily by usage, then
you might consider partitioning via hash, a technique which allows a more random
distribution. Hash partitions are covered later in this document.
Of course, some applications may
need to retrieve data across two or more partitions. To enable this kind of
access, an abstraction of the underlying tables must be created.
One way to abstract the partitions
from the client application is to create a distributed partitioned view (DPV) on
each server in the federation. This view unites the distributed data by
combining data from both the local server and the remote servers to produce a
single consolidated view of all the data in the partitioned table. When the view
on each server in the federation is duplicated, all of the data in the
partitioned table is made available regardless of which server the client
application connects to. Figure 14 shows a distributed partitioned view.
Figure 14. A distributed partitioned view
In this example, a distributed
partitioned view named pv_CustomerProfiles is defined on all of the servers in
the federation. This view can be used to retrieve data from any of the
underlying tables that provide a single consolidated view of the customer data.
SQL Server 2000 provides support for updateable distributed partitioned
views, allowing data in multiple servers to be modified through a view, if the
design requirements are met (for specific details, see SQL Server 2000
Books Online). Additionally, the SQL Server 2000 query engine can
intelligently evaluate queries on distributed partitioned views to avoid
unnecessary remote server queries.
However, the main purpose of the
distributed partitioned view is to create a layer of abstraction that makes the
federation simpler to understand conceptually. While it works efficiently, it
has one shortcoming: if one server in the federation becomes unavailable, the
view will return an error message after either the query plan is recompiled (for
example, if you query the view infrequently, so that it is not stored in the
cache) or the data in the unavailable partition is queried.
Because of this shortcoming, you
might decide to reserve DPVs for situations that require administrative
simplicity, and largely ignore them in your application design. Instead, you
might decide to employ data dependent routing or user-defined partitioning
(described later in this document) whenever possible.
Data partitions (also called
partition members) are like any ordinary table except that they represent a
subsection of a larger data set, and they are always indexed on and queried by
the partitioning key. If the partition member table participates in a DPV, then
the key must meet the special requirements for partitioning and is governed by a
check constraint. See SQL Server Books Online for more information.
Clearly you must be able to manage
the system you design. Managing federated servers is already complex; you do not
want to add to this by causing unnecessary difficulty for administration. The
cost of administrative complexity can be measured in the cost of support, not
only in the additional man-hours required, but in the skill level required for
the job. Additionally, system downtime results when the complexity of an issue
exceeds the skill or time available for support. Your group should be able to
estimate the cost of downtime per unit of time, in terms of potential revenue
lost, work-time lost, customers lost, and other less tangible costs, such as
damage to the professional reputation of the company if the application is
customer-facing. Therefore, before undertaking federated design, you must be
willing to commit to a good design, a sound test plan, and a high level of
support.
Keeping these issues in mind,
carefully plan out how you will distribute and re-distribute data across
partitions. You may choose to partition more than one table to co-locate data
that will be joined frequently. If you are not using transaction-log marks for
your backups, and transactionally consistent data is a requirement, you might
also consider co-locating data that must be transactionally consistent. This
way, each partitioned section, is a complete unit, whether it is a database or
simply a file group (such as you might have if you located more than one
partition on the same server).
In some methods of partitioning, you
might have more than one partition on a single server, perhaps even in the same
database. In this case, it is conceptually simpler to place the partitions on
separate file groups. Note that this is not essential. Even if you restore a
file group, the entire database will remain transactionally consistent. So at
first it might seem that simplicity of concept is the only advantage to be
gained by separating partitions that exist in the same database by file groups.
However, a more important advantage
gained by the use of file groups is speed of restore time. If you need to
restore only one section of your database, rather than the entire database, your
downtime will be considerably shorter. Because the database will need to recover
consistency between the restored file group and the rest of the database, the
recovery process should be even quicker if all related data is located on the
file group. Also, separating it this way requires far less analysis if you later
decide to move the data, because it is already a cohesive unit. Finally, each
file group can be monitored separately for I/O by using fn_virtualfilestats.
You should give careful
consideration to which tables in your database application will benefit from
being partitioned, and which are the most suitable keys to partition them on. In
general, you should partition the tables that are most frequently accessed (not
necessarily the tables containing the most data). When choosing a partitioning
key, you should bear in mind that partitioning on a numerical key yields better
performance than using a character-based key. If no suitable key can be found,
you must either create a new partitioning-key column or use a hashing algorithm
to partition the data based on one of the existing fields.
A point that should be clarified at
the design stage is that you are partitioning the data so that each server does
a fair share of the work. To accomplish this, you cannot simply partition to
distribute the data by value. You must distribute it by usage. That means that
you should expect that your partitions will not be even; and in some cases there
will be a vast difference in number of rows per partition.
For contrast, let’s look at an
example that would not work. Let’s say you partitioned the Orders table by
the OrderID (an identity column), so that the first partition contained orders 1
to 999, the second contained orders 1000 to 1999, and the third contained orders
2000 to 2999. Although this is an “even distribution”, this would result in two
immediate problems. First, because OrderIDs are added sequentially, your third
partition would be the only one that would grow, and you would soon need to
repartition the data to make the range of key values in each partition larger.
Secondly, the orders that are most recent would be queried more heavily than the
others, which would result in most of your activity going to the third
partition.
Therefore, you must examine how the
data is queried and understand how the system will grow before you design the
partitions. This may result in your data being unevenly distributed by number of
rows, because the amount of data on each partition is only relevant in relation
to how often it is accessed.
The hardware configuration for the
servers in your federation does not need to be physically identical. The
hardware should be equal in processor and memory because you are (or should be)
using the partitions equally, but if you find that one partition has a
significantly larger amount of data, you could compensate by adding more disks
to that server instead of creating another partition on a new server. If the
usage of one partition is higher, so that its data is more frequently accessed
and you are facing bottlenecks on throughput, you should either redistribute the
data so that the servers are equally used or add another server to the
federation. Your design must be flexible enough to accommodate a change in the
data’s location.
Many database operations involve
accessing related data using joins, key constraints, or triggers, and the
benefits of distributed partitioning may be lost if these require remote server
calls. Therefore, once you have identified data that is frequently accessed, you
should also decide what related data could be most strategically co-located on
the same server. There are a number of approaches to handling related data when
partitioning across multiple servers:
Collocation. The
standard method is to partition the foreign-key tables along with the original
partitioned table. Of course this approach works only if the foreign-key tables
can be partitioned successfully on the same basis as the original table. For
example, suppose our e-commerce site allowed customers to add review comments
for each product offered on the site. The comments could be stored in a table
called ProductReviews and related to the customer profile partition member
tables on the CustomerID field. Since each review relates to only a single
customer, the ProductReviews table can be successfully partitioned so that the
review for an individual product resides on the same server as the record for
the customer who created it.
De-normalization. Some
related data will not be so easy to partition alongside the original partitioned
table. For example, our e-commerce database may include a table named
DeliveryMethods, listing the delivery methods (such as standard, express, or
overnight) that customers can choose. Customers may indicate a preference for a
particular default delivery method in their profile by including a
PreferredDeliveryMethod field in the CustomerProfiles table as a foreign key to
the DeliveryMethods table.
Since it is likely that multiple
customers will select the same preferred delivery method, it may be prudent to
de-normalize the database by duplicating some of the supplier data as additional
columns in the CustomerProfiles tables. This would allow commonly accessed
DeliveryMethod data (such as the name and cost of the delivery method) to be
retrieved along with each customer, eliminating the need to join to the
DeliveryMethods table.
It is also be necessary to consider
how frequently data is added to the DeliveryMethods table, and how frequently
the duplicated columns of the DeliveryMethods table are updated. If your design
for maintaining data integrity for the duplicated data involves updating all
related data inside one transaction, the table is locked until this is
completed. There are ways to get around this programmatically, but it is
important to be careful of what trade-offs you make in trying to increase
scalability. While you may expect slightly lower response time per transaction
in exchange for the ability to support nearly unlimited numbers of transactions,
you do not want to drastically reduce response time through a faulty design.
Replication.
De-normalization can alleviate remote server calls; however, it can make the
preservation of data integrity more complicated. One approach to alleviating
this problem is to use SQL Server replication to distribute the same data
over the servers in the federation. For example, to prevent the integrity issues
associated with de-normalizing the delivery-methods data, you could instead
replicate the DeliveryMethods table to all three servers, allowing a complete
list of all delivery methods to be retrieved from any server in the federation.
Data integrity is maintained because any updates to the delivery-methods
information are replicated.
However, using replication will
force you to sacrifice some CPU and memory resources. In a very active system,
the price for this may be higher than is ideal. If this is the case for your
design, you should test a scenario where you partition the partitionable tables
across the federation, but store all of the remaining tables that would
otherwise need to be replicated together on a single server, as shown in Figure
15.
Figure 15. Storing common data on a single
server
This design allows the partitioned
data to be retrieved through the distributed partitioned view, but provides a
single common location for all other data. You should note that this location
could be one of the members of the federation or an additional, separate
server.
Replication Alternative.
Replication takes a toll on the server's processor, memory, and disk
utilization. If you have planned your server to accommodate this additional
load, and your DBA is experienced in maintaining replication, then you should
not be concerned. There is an alternative, however. You could use INSTEAD-OF
triggers to update all replicated data. For example, if you have a lookup table
that must exist on every server in the federation, but you don’t update it on a
continual basis, then the trigger would be a simpler, more efficient solution.
The INSTEAD-OF trigger, created on every copy of the lookup table, would perform
the data change on every copy of the table (including the copy that has the
trigger on it).
It’s useful to understand the role
of each part of the federated configuration individually, so you can better
choose how to implement them as a group.
Data dependent routing (DDR) refers
to the technique of directing requests to the appropriate partition through the
use of code and either a lookup table or a hash key.
Distributed partitioned views (DPV)
create a simple way of accessing partitions by using a view, rather than by
referencing each table individually.
A hash partition is more similar to
DDR, in the sense that you will be directed to the server containing the data.
Network bandwidth and cross-server
queries will drive your decision. This is easier to explain by analogy. Imagine
that the connection between the servers is a sidewalk. If you are using a
high-speed network between servers, imagine a wide sidewalk; if you have a
standard network connection, imagine a narrower sidewalk. Each person represents
100 K of data. So, queries with smaller results (or fewer people) will be easy
to accommodate until the volume of queries grows too large. If you are issuing
requests that involve larger result sets, this could be represented as a large
group of people walking together on the sidewalk. It is easy to see that the
speed with which a person arrives at his destination depends on how many people
are using the sidewalk at the same time. This is the sole reason that a
high-speed network is required between federated servers.
If you expect to regularly have
queries that require data from more than one server, then it is simpler to use a
combination of DDR or hash partitioning and DPV. It is also possible to control
the results from different servers programmatically, but this can quickly become
very complex, depending on what type of queries are issued. Note that there is
no apparent performance loss incurred when you query multiple partitions located
on the same server.
To create a federated solution, you
must perform some basic configuration on each member server. The basic steps are
discussed below:
There are a number of tasks that
must be performed to set up a server federation. These tasks are described
here.
Each federated server must have a
linked-server definition for each of the other servers. You can add a
linked-server definition using the sp_addlinkedserver system stored procedure,
or by using SQL Server Enterprise Manager.
Note: At this point, you should
consider what type of security you will need between servers. Also consider how
new users will be added to the system, and any other security policies that may
be in place. It is not a good idea to simply map all users to the SA account of
the remote server, for obvious reasons.
For example, to add SQLServer2 and
SQLServer3 as linked servers on SQLServer1, the following Transact-SQL could be
run:
EXEC sp_addlinkedserver @server =
'SQLServer2',
@provider = 'SQLOLEDB'
@datasrc = 'SQLServer2'
EXEC sp_addlinkedserver @server =
'SQLServer3',
@provider = 'SQLOLEDB'
@datasrc = 'SQLServer3'
The @server parameter is used to
specify an internal name for the remote server. This can be different from the
actual NetBIOS name of the computer, but when building a server federation it is
recommended that you use the actual computer name. This way, table references in
the partitioned view are consistent across all member servers (since the local
table can be referenced using the local server’s NetBIOS name). The @provider
parameter specifies the data-access provider used to connect to the remote
server, and the @datasrc parameter specifies the NetBIOS name of the remote
server. The sp_addlinkedserver stored procedure supports additional parameters
to allow non-SQL Server databases to be linked. These are not required when
linking SQL Server data sources.
Similar statements would need to be
run on SQLServer2 (to link SQLServer1 and SQLServer3), and on SQLServer3 (to
link SQLServer1 and SQLServer2).
This server option, configured via
sp_serveroption, ensures that the query processor does not request meta data for any of the linked tables
until data is actually needed from the remote partition member table. This must
be set on each of the federated servers.
Note: You should not set this option
if your partitions are all in the same database on the local server. This
situation would arise if you are designing partitions for anticipated future
scalability, but you are not yet implementing a federation.
To set the lazy schema validation
option, run the following statement on each federated server:
EXEC sp_serveroption @@servername, ‘Lazy
Schema Validation’, true
Once the servers have been set up,
you must create the necessary database objects for a partitioned database.
The member tables that will contain
the partitioned data must be created on the servers in the federation. The table
schemas used to create these tables must be identical (with the exception of the
constraint used to partition the data) across each of the federated servers. A
constraint must be defined on the partitioning-key column to ensure that each
table can contain only the appropriate data for the server on which it will
reside. This constraint is used by the query engine when devising the execution
plan for a query on a distributed partitioned view.
For example, the following CREATE
TABLE statement could be run on SQLServer1 to create a table for customers 1000
to 1999:
CREATE TABLE CustomerProfiles
(
CustomerID INTEGER
CONSTRAINT ck_Profiles
CHECK(CustomerID BETWEEN 1000 AND 1999),
FirstName Varchar(20),
EMail Varchar(70)
CONSTRAINT PK_CProfiles PRIMARY KEY(CustomerID)
)
On SQLServer2, the CREATE TABLE
statement would be similar, as shown here:
CREATE TABLE CustomerProfiles
(
CustomerID INTEGER
CONSTRAINT ck_Profiles
CHECK(CustomerID BETWEEN 2000 AND 2999),
FirstName Varchar(20),
EMail Varchar(70)
CONSTRAINT PK_CProfiles PRIMARY KEY(CustomerID)
)
And to complete the set, the
statement on SQLServer3 would look like this:
CREATE TABLE CustomerProfiles
(
CustomerID INTEGER
CONSTRAINT ck_Profiles
CHECK(CustomerID BETWEEN 3000 AND 3999),
FirstName Varchar(20),
EMail Varchar(70)
CONSTRAINT PK_CProfiles PRIMARY KEY(CustomerID)
)
Important: All SET options should be
identical on each server when the table is created. Otherwise, the DPV will not
be updatable.
Note that the name of each table is
the same in the example. If you name each member table with the same name, the
statement used to access the member table will be consistent across all member
servers.
However, if you are currently
designing a partitionable application in a single database with the intention of
one day separating the partitions into separate servers, then you must name the
partition member tables with unique names. In this case, choose names that have
nothing to do with the key range. For example, if you named your third table
CustomerProfiles_3000_3999, when you later repartition the table to distribute
usage evenly between servers, the name would no longer be appropriate.
Finally, the partitioned view must
be created on each member server. It is worth noting that this step can be
performed only after the member tables and the linked-server definitions have
been created on all of the member servers, which
makes scripting the creation of server federation more complex than it at first
appears. To do this, you must run a script to create the member tables, define
linked servers, set the lazy schema validation option on each server, and then
run a separate script to create the partitioned view on each server.
The partitioned view consists of a
select statement for each of the member tables consolidated using the UNION
operator (specifying the ALL option prevents SQL Server from removing
duplicate rows in the result set). For example, the CustomerProfiles view would
be defined on SQLServer1 using the following statement:
CREATE VIEW pv_Customers
AS
SELECT CustomerID, FirstName, EMail
FROM
SQLServer1.FederatedRetail.dbo.CustomerProfiles
UNION ALL
SELECT CustomerID, FirstName, EMail
FROM
SQLServer2.FederatedRetail.dbo.CustomerProfiles
UNION ALL
SELECT CustomerID, FirstName, EMail
FROM
SQLServer3.FederatedRetail.dbo.CustomerProfiles
Similar statements would be run on
SQLServer2 and SQLServer3 so that each server in the federation contains a view
named pv_CustomerProfiles including all customer profile records.
Because the view exists on each
server, it can be queried as if it were a table on any server. So, if you were
using stored procedures for access, you could create the following stored
procedure to retrieve a customer profile record by CustomerID.
CREATE PROC getCustomerProfile (@CustomerID
Int)
AS
SELECT CustomerID, FirstName, EMail
FROM pv_Customers
WHERE CustomerID = @CustomerID
It is important to note that,
because the data is partitioned by the CustomerID, you must include that column
whenever you search the table in order to avoid searching every partition. For
example, if you created the following procedure, it would be less efficient
because it would search every partition.
CREATE PROC getCustomerByEMail (@Email
Varchar (70) )
AS
SELECT CustomerID, FirstName, EMail
FROM pv_Customers
WHERE EMail = @EMail
By including the CustomerID, you can
make the query much more efficient.
Based on the query and the
constraints, redundant partitions are discarded at compile time, if possible, or
at run time with a mechanism called startup filters or dynamic filters. A
startup filter is a condition on the parameters in the query that indicates if
the partition needs to be accessed. The cost of evaluating a startup filter
locally is dramatically lower than going to a remote server, executing a query
and then discovering that no rows qualify.
To see your partitioned query
running, examine a showplan output showing startup filter plans. You will see
that all the partitions are acknowledged in the plan, but the startup filter
registers the range of values that are available per partition. You can use the
"set statistics i/o on" and the
"set statistics showplan on" options for more complete information.
When you query the table, you get
the showplan output below. When the query runs, the startup expressions prune
redundant partitions dynamically. When the key values are not known at the time
the plan is built, SQL Server builds an execution plan with conditional
logic to control which member tables are accessed. This execution plan has
startup filters—conditional logic that controls which member table is accessed
based on the input parameter value. You will see in the graphical Query Analyzer
view that all of the remote partitions are acknowledged in the display. The
unnecessary partitions will be pruned from the execution plan at run time, based
on the parameter supplied, and in the statistics profile you will see that only
one partition is actually queried.
SET STATISTICS IO ON
SET STATISTICS PROFILE ON
EXEC getCustomerProfile 1020
Statistics I/O shows that the CustomerProfiles table was
scanned.
|
Statistics I/O Returns |
|
Table 'CustomerProfiles'. Scan
count 1, logical reads 2, physical reads 2, read-ahead reads
0 |
Statistics profile also shows that the CustomerProfiles
table on the first partition had a run performed against it and that the
clustered index on the primary key (named PK_CProfiles in this example) was
used.
|
Statistics Profile
Returns |
|
Executes |
StmtText |
|
1 |
SELECT * FROM pv_Customers
WHERE CustomerID =
@CustomerID |
|
1 |
Concatenation |
|
1 |
Filter(WHERE:(STARTUP
EXPR([@CustomerID]<=1999 AND [@CustomerID]>=1000))) |
|
1 |
Clustered Index
Seek(OBJECT:([FederatedRetail].[dbo].[CustomerProfiles].[PK__CProfiles),
SEEK:([CustomerProfiles].[CustomerID]=[@CustomerID]) ORDERED
FORWARD) |
|
1 |
Filter(WHERE:(STARTUP
EXPR([@CustomerID]<=2999 AND [@CustomerID]>=2000))) |
|
0 |
Remote Query(SOURCE:(Server2), QUERY:(SELECT
Col1015,Col1014,Col1013 FROM (SELECT Tbl1003."CustomerID" Col1013,Tbl1003."FirstName" Col1014,Tbl1003."EMail" Col1015 FROM
"FederatedRetail"."dbo"."CustomerProfiles" Tbl1003) Qry1016 WHERE
Col1013 |
|
1 |
Filter(WHERE:(STARTUP
EXPR([@CustomerID]<=3999 AND [@CustomerID]>=3000))) |
|
0 |
Remote Query(SOURCE:(Server3), QUERY:(SELECT
Col1020,Col1019,Col1018 FROM (SELECT Tbl1008."CustomerID" Col1018,Tbl1008."FirstName" Col1019,Tbl1008."EMail" Col1020 FROM
"FederatedRetail"."dbo"."CustomerProfiles" Tbl1008) Qry1021 WHERE
Col1018 |
Analyzing the queries and execution
plans is an important part of preparing to partition data. When partitioning an
existing database, it is a good practice to add some audit code to your queries
or run SQL Profiler to establish baseline information on the specific queries
used, the data they access, and how frequently it is done. This information will
provide valuable insights on the best possibilities for distributing your
data.
To be updateable, a partitioned view
must meet the requirements for updateable partitioned views as described in
SQL Server 2000 Books Online. When you want to update a partition through a
DPV, you must use set XACT_ABORT on, as in the following example:
CREATE PROC ChangeEmail (@CustomerID Int,
@NewEMail varchar(50))
AS
SET XACT_ABORT ON
BEGIN TRAN
UPDATE pv_Customers
SET Email = @NewEMail
WHERE CustomerID = @CustomerID
IF (@@error <> 0)
GOTO ErHand
COMMIT TRAN
RETURN 0
ErHand:
If @@trancount > 0
BEGIN
ROLLBACK TRAN
RETURN -1
END
If you are updating the partition
without using a distributed partitioned view, you do not need the XACT_ABORT
setting, because you can interact directly with the partition member table.
If your tables do not meet the
requirements for an updatable partitioned view, you can also consider updating
them by using an INSTEAD-OF trigger. The triggers would be invoked instead of
any INSERT, UPDATE, or DELETE statement issued against the view, and would
contain code that examines the data, determines the correct partition, and
performs the required action on that partition member table. However, keep in
mind that the query optimizer would not usually build execution plans for a view
utilizing an INSTEAD-OF trigger that are as efficient as the plans for a true
partitioned view.
As with any other type of system,
application design has a great influence on performance. By using data-dependent
routing, and thus using routing information to go directly to the server in
question, you can reduce traffic between servers running SQL Server. This
method uses code to determine where the target data is located and then routes
connections to the appropriate server. The data is laid out no differently than
it would be for a distributed partitioned view. The difference with
data-dependent routing is that the information on how to go after the data is
made available to the application. Data-dependent routing does not use the
linked-server connections; instead, it sends the data requests directly to the
appropriate server. It is best if the databases containing the partitioned table
have identical names and structures. Differences can be accounted for in the
routing table, but this adds some complexity to the design.
Figure 16 shows a federated server
solution in which data-dependent routing information is used in the middle
tier.
Figure 16. Data-dependent routing logic
The standard federated-server design
uses both distributed partitioned views and data-dependent routing to take full
advantage of all possible performance opportunities. A simple way to do this is
to create a routing table that contains information regarding which server has
the data that the application is requesting. Deciding where to put the routing
table is important. For ease of administration, you can put the table in
SQL Server, where it can be updated easily and as often as necessary. You
should, however, design your application in such a way that the routing table
does not need to be retrieved from SQL Server each time you want to
retrieve data from the partitioned table. On the first request from each
application server, the routing information can be retrieved from the
SQL Server database and cached (for example as an application-level
recordset in IIS or as an array in the COM+ Shared Property Manager). All future
requests then use the cached data to determine the most appropriate server to
connect to in order to retrieve specific records.
As an alternative to using a routing
table, you could choose instead to send the data to the correct partition or
server using a custom hashing algorithm that is based on the partitioning
column. This facilitates repartitioning, because you can simply alter the
algorithm to change the number of partitions.
A key consideration here is in
determining which method will most easily allow you to manage the data
partitions by moving the data around to balance usage. Failing to do this could
be disastrous.
Assuming a routing table is used,
the table should contain at least enough information to determine the most
appropriate server on which to run a query for specific data. You may wish to
include additional columns that allow different routing rules to apply at
different times (for example, to accommodate a maintenance schedule), or more
complex routing logic. For example, the logic necessary to route requests
appropriately in our example e-commerce solution could look something like
this:
|
Lower Range |
Upper Range |
Column Name |
Table Name |
Server
Instance |
Begin Date |
End Date |
Active Flag |
|
0 |
10 |
Category |
Products_1 |
SQLServer1 |
01 jan 2001 |
|
Y |
|
11 |
20 |
Category |
Products_2 |
SQLServer2 |
01 jan 2001 |
|
Y |
|
21 |
30 |
Category |
Products_3 |
SQLServer3 |
01 jan 2001 |
|
Y |
Table 13. Sample routing table for the example
e-commerce solution
This table is just a sample, and you
can design it with whatever information you need and store it in any format
appropriate to your application. The Begin Date and End Date columns provide
some flexibility in regards to scheduling outages and failovers, in the event
that you need to transfer control to an alternate server, or if you are
repartitioning without a maintenance window. A good practice is to add
error-handling code that will detect and report any server outage. Also, add
audit columns to (at least) all the tables that relate to the partitioned data.
Although your routing logic can be
as complex as you need it to be, you should endeavor to keep it relatively
simple to avoid causing a negative impact on performance. It may seem obvious,
but you should carefully performance-test your routing logic to ensure that the
overhead it introduces is less than the overhead caused by the remote queries it
prevents!
Note: Although we are dealing
exclusively with Federated Servers here, you can also use DDR in any application
as a way of programmatically connecting to an alternate data source, in the
event that the primary one becomes unavailable.
To cache the routing information in
an ASP application, an application-level variable can be initialized in the
global.asa script along with the connection string to the server that contains
the routing table, as shown here:
<SCRIPT LANGUAGE=VBScript
RUNAT=Server>
Sub Application_OnStart
Set Application("rsRouting") = Nothing
Application("CONN_STRING") = _
"PROVIDER = SQLOLEDB;DATA SOURCE=(local);” & _
“INITIAL
CATALOG=FederatedRetail;” & _
“USER ID=retailsite;PASSWORD=password;"
End Sub
</SCRIPT>
The application-level variable is
checked on the ASP script that needs to request a specific user’s profile data.
If it does not contain routing information, a recordset containing the routing
table is retrieved and disconnected. This recordset can then be used by any
future data requests. The code in the ASP that was used to retrieve product data
could look similar to this sample, which assumes the CustomerID of an
authenticated user is passed to the server as a cookie:
<%
Dim Lgn
Dim rs
Dim strConn
Dim strInstance
Lgn = Request.Cookies("LoginID")
'Add routing information to cache if it is not already
there
If Application("rsRouting") Is Nothing Then
Set rs = server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3
rs.Open "SELECT * FROM routing",
Application("CONN_STRING"), 3, 4
Set rs.ActiveConnection = Nothing
Set Application("rsRouting") = rs
End If
‘Construct correct connection string from cached routing
table
Dim conFound
Application("rsRouting").MoveFirst
conFound=False
Do Until Application("rsRouting").EOF OR ConFound
If Application("rsRouting").fields("LowerRange").Value
<= Cint(Lgn) And Application("rsRouting").fields("UpperRange").Value >=
Cint(Lgn) Then
conFound = True
strInstance =
Application("rsRouting").fields("ServerInstance").value
strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=" &
strInstance & _
";INITIAL CATALOG=FederatedRetail;” & _
“USER ID=retailsite;PASSWORD=password;"
'Note that standard security is
used because the IUsr_X
'user is a local account (i.e. not a domain
member)
End If
Application("rsRouting").MoveNext
Loop
'Get Profile Data
Dim rsCustProfile
Dim cmd
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
.ActiveConnection = strConn
.CommandText =
"GetCustomerProfile"
.CommandType = 4
.Parameters.Append(cmd.CreateParameter("LoginID", 3,
1, 4, Lgn))
End With
Set rsCustProfile = cmd.Execute
Response.Write "Hello " &
rsCustProfile.fields("FirstName").value Response.Write “<BR>Your email
address is " &
_
rsCustProfile.Fields("EMail").Value
%>
In many e-commerce applications, the
actual data access is performed by code in a component, rather than directly on
the ASP pages. In this scenario, you must cache the routing information so that
it can be re-used by the components in the application. The COM+ Shared Property
Manager (SPM) can be used for this purpose. The SPM is an object used in COM+
applications to store variant values. Since the SPM cannot be used to store object variables
(such as recordsets), you may need to convert the routing table to an
array. This can be done using the GetRows method of a recordset object as
shown in the following code sample:
Public Function getCustomer(ByVal intLoginID As Integer)
As ADODB.Recordset
Dim SPM As COMSVCSLib.SharedPropertyGroupManager
Dim SPG As COMSVCSLib.SharedPropertyGroup
Dim SP As COMSVCSLib.SharedProperty
Dim bExists As Boolean
Dim strConn
Dim strInstance
Set SPM = New
COMSVCSLib.SharedPropertyGroupManager
Set SPG = SPM.CreatePropertyGroup("RoutingProps", LockSetGet,
Process, bExists)
Set SP = SPG.CreateProperty("routingtable", bExists)
'Add Routing table to cache if it is not already there
If Not bExists Then
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM routing", CONN_STRING,
adOpenStatic, adLockBatchOptimistic
Set rs.ActiveConnection = Nothing
SP.Value = rs.GetRows
End If
'Get Correct connection string cached routing table
Dim aRouting
aRouting = SP.Value
Dim i As Integer
For i = 0 To UBound(aRouting)
If aRouting(0, i) <= intLoginID And aRouting(1, i)
>= intLoginID Then
strInstance = aRouting(4, i)
strConn = "PROVIDER = SQLOLEDB;DATA SOURCE=" & _
strInstance & _
";INITIAL CATALOG=FederatedRetail;” &
_
“INTEGRATED SECURITY=sspi;"
'Note that integrated
security can be used because
'the COM+ application can be assigned a domain
account
Exit For
End If
Next i
'Get Profile Data
Dim cmd As ADODB.Command
Dim rsCustomer As ADODB.Recordset
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = strConn
.CommandText =
"GetCustomerProfile"
.CommandType =
adCmdStoredProc
.Parameters.Append cmd.CreateParameter("LoginID",
adInteger,
adParamInput, 4,
intLoginID)
End With
Set rsCustomer = cmd.Execute
Set getCustomer = rsCustomer
End Function
Partitioning on a hash is similar to
data-dependent routing. The difference is that the key used to select the data
is based on a hashing algorithm in which a calculation on the key yields a
result that determines the server on which the record is stored. For the
following example, customers must be able to log in to the Web site to activate
their user profile. The E-mail field is the best choice for a partitioning key;
however, it has a character field of varying lengths, and in most cases it is
preferable to use a numeric value for partitioning. You should also design the
partitions to be based on a dynamic value, for ease of maintenance.
You could easily combine a hash with
a routing table, perhaps to compensate for the lack of an integer key or the use
of multiple column keys, but the following example was chosen for this chapter
because it shows a way to use a hash without a routing table.
There are several ways to use a hash
for partitioning—this is just one method. Here, a modulus with a value equaling
the number of servers in the federation is used to identify and map to the
servers, eliminating the need for a routing table for the federation (although
you might still want to include a routing table for manageability). To display
how using a modulus of three works for three data locations, run the following
code snippet and observe that it creates 100 rows in the table variable, and the
hash assigns a number between 1 and 3 to each row. In this example, there are
three servers, so the modulus is set to 3. (Note that in this example, one is
added to the hash result just to make the result more intuitive: numbering the
servers and partitions 1 through 3, rather than 0 through 2. ).
set nocount on
declare @Locations smallint,
@counter
tinyint,
@current
tinyint
select @current = 1, @counter = 100, @Locations = 3
declare @tab table (modulos int)
while @counter >= @current
begin
insert @tab SELECT (@current %
@Locations) + 1
select @current = @current + 1
end
select [Server assigned followed by total locations]
=
modulos from @tab group by modulos
compute count (modulos)
This premise is applied in the
following function, which assigns a hash value based on the ASCII values of the
first and last letters of the e-mail alias (the part before the @ sign). Here is
an example:
create function dbo.udf_hashmail2
(@EMail varchar(100), @GroupServerCount int)
returns table
as
return (select
[Server or Group ID] =
(ascii(left(@EMail, 1))
% @GroupServerCount) + 1 ,
[Partition ID] =
(ascii( substring( @EMail,
charindex('@', @EMail) - 1,
1))
% @GroupServerCount) + 1
)
GO
-- This statement shows how this function
could be executed:
declare @email varchar(100),
@gl_servercount smallint
select @gl_servercount = 3,
@email = 'plato@msn.com'
select [Server or Group ID],
[Partition ID]
from dbo.udf_hashmail2 (@email,
@gl_servercount)
So, when a new customer profile is
created, the hash keys determine where the data will be inserted. And when
customers log in to the system, they provide their e-mail address, and the hash
key is determined in order to locate their record.
The final part to making this work
is the server names and partition names. You could combine this method with a
lookup table, such as in DDR. Or, you could name the servers with the same name
plus the identifying number. Likewise, each partition would have the same name
with different numbers in the suffix (for example, MyServer001, MyServer002, and so on).
A similar naming strategy could be used for the partitions within the server.
When the application loads, it would generate the partition locations (servers
and tables, in this example), and cache this information. The following code
sample shows how this name could be generated in T-SQL:
create function dbo.udf_get_locname
(@hashkey smallint,
@locname varchar(30) )
returns table
as
return (select [Location of Data] =
@locname+
substring( '00'+
convert(varchar(3), @hashkey),
len(@hashkey),
3
)
)
GO
-- This statement shows how this function
could be executed:
declare @hashkey smallint,
@locname varchar(30)
select @hashkey = 3
,@locname = 'MyServer'
select [Location of Data] from dbo.udf_get_locname
(@hashkey, @locname)
Typically, you wouldn’t want to have
to connect to the server to get this information; however, the T-SQL version of
it will be useful any time you need to get this information from the server when
you are already there. Specifically, to allow the DBA to extract data for a
reporting system, or to locate
records.
The following code shows how a
middle-tier component to determine the correct server and partition associated
with a particular e-mail address could be written in Microsoft Visual
Basic®:
Public Function HashPartition(ByVal strEMail As String) As
String
intNumServers = 3
strBaseServerName = "Server00"
strBaseTableName = "Table00"
'First, assign a hash values using the EMail
address
Dim intServerID As Integer
Dim intPartitionID As Integer
intServerID = (Asc(strEMail) Mod intNumServers) +
1
intPartitionID = (Asc(Mid$(strEMail, InStr(strEMail,
"@") - 1)) _ Mod intNumServers) + 1
'Now assign the server and table names
Dim strServerName As String
Dim strTableName As String
strServerName = strBaseServerName &
CStr(intServerID)
strTableName = strBaseTableName &
CStr(intPartitionID)
'Return the hashed values as XML
Dim xmlHashData As String
xmlHashData = "<hashdata>"
xmlHashData = xmlHashData & "<servername>"
& strServerName & _ "</servername>"
xmlHashData = xmlHashData & "<tablename>"
& strTableName & _ "</tablename>"
xmlHashData = xmlHashData &
"</hashdata>"
HashPartition = xmlHashData
End Function
In this case, the servers and
partitions are named using a predictable pattern, and the hash data is returned
as an XML string in the following format:
<hashdata>
<servername>Server002</servername>
<tablename>Table001</tablename>
</hashdata>
While the logic shown here could be
effectively handled in either the
middle tier or the data tier, your choice of where to store the logic should be
based on manageability and flexibility needs, and on what your staff can
definitely support while upholding your operations standards. Usually processing
logic is kept in both places—the modulus, server, and partition names are stored
in the database, but cached in the application layer.
Hash partitioning is most effective
when it is a very simple matter to adjust the modulus when you add more servers
or partitions. You could add another variable to the code shown here so that
there could be a different number of servers than partitions. However, if the
number of partitions per server varies, then you would need a table similar to
the DDR routing table to determine the location of the data.
You should store the information on
modulus, server, and partition names in global variables; avoid hard-coding any
names which might later need to be changed. If you were using DDR with hash
partitioning, you could then store the server and partition names and the
current and new modulus in the routing table. This would be a requirement only
if the server and partition names did not follow a predictable pattern.
By using either hash partitions or
DPV, an application can be kept abstracted from the federation. However, there
is a great advantage to designing the system as partitioned throughout the
entire application—functionality can be put either in the middle tier or in the
data tier to react to changes in data distribution and adjust its own processes.
For example, you should consider
implementing procedures and code that would allow data to shift between
partitions without any disruption of service. One way to do this is to add a few
columns to the partition member tables: status (with values of current,
relocate, delete), NewGroupServerID and NewPartitionID. Each time a profile is
retrieved, the application checks the status of the record to see if it has been
flagged to be moved to another partition. If it has, the application should
contain logic which will allow it to recheck that record’s location if it is
retrieved again during the same session.
Additionally, the DBA should
consider adding monitoring functionality to the database server in order to
detect higher levels of activity on the partitions. However, you should create
an alert that the DBA can use to inform the administrator that higher usage has
been detected on one of the partitions, rather than automating a process which
might initiate repartitioning. This allows the DBA to make an informed decision
about how to repartition the data and to initiate this process intentionally at
a planned time.
Your federation may have tables that
must be duplicated on each server in the federation. If you are not using
replication to do this, you will need to update changes to these tables on each
server. A simple way to do this is to use INSTEAD-OF triggers. Here is an
example of how to do this (note that for the sake of clarity, error handling has
been omitted in this example):
-- table that must be copied to each server
CREATE TABLE ProfileOptions
( [ProfileOptionID] int
not null,
[SettingGroupID] int not null,
[Description]
char(50) not null,
[ActiveFlag]
char(1) not null default('Y')
)
GO
CREATE TRIGGER IO_Trig_I_ProfileOptionRepl on
ProfileOptions
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
IF (NOT EXISTS
(SELECT p.[ProfileOptionID]
FROM
[Server001].[DB1].[dbo].[ProfileOptions] p
JOIN [inserted]
i
ON
p.[SettingGroupID] =
i.[SettingGroupID]
AND p.[Description] = i.[Description])
)
INSERT
INTO
[Server001].[DB1].[dbo].[ProfileOptions]
SELECT [ProfileOptionID],
[SettingGroupID],
[Description],
case when [ActiveFlag]
not in ('Y', 'N')
then 'Y' else [ActiveFlag]
end
FROM [inserted]
IF (NOT EXISTS
(SELECT p.[ProfileOptionID]
FROM
[Server002].[DB2].[dbo].[ProfileOptions] p
JOIN [inserted]
i
ON
p.[SettingGroupID] =
i.[SettingGroupID]
AND p.[Description] = i.[Description])
)
INSERT
INTO
[Server002].[DB2].[dbo].[ProfileOptions]
SELECT [ProfileOptionID],
[SettingGroupID],
[Description],
case when [ActiveFlag]
not in ('Y', 'N')
then 'Y' else [ActiveFlag]
end
FROM [inserted]
IF (NOT EXISTS
(SELECT p.[ProfileOptionID]
FROM
[Server003].[DB3].[dbo].[ProfileOptions] p
JOIN [inserted]
i
ON
p.[SettingGroupID] =
i.[SettingGroupID]
AND p.[Description] = i.[Description])
)
INSERT
INTO
[Server003].[DB3].[dbo].[ProfileOptions]
SELECT [ProfileOptionID],
[SettingGroupID],
[Description],
case when [ActiveFlag]
not in ('Y', 'N')
then 'Y' else [ActiveFlag]
end
FROM [inserted]
END
GO
CREATE TRIGGER IO_Trig_U_ProfileOptionRepl on
ProfileOptions
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE p
SET p.[Description] = isnull( i.[Description],
p.[Description]) ,
p.[ActiveFlag] =
case when i.[ActiveFlag] not in ('Y','N')
then 'Y' else i.[ActiveFlag]
end
FROM [Server001].[DB1].[dbo].[ProfileOptions] p
INNER JOIN [inserted] i
ON
p.[ProfileOptionID] =
i.[ProfileOptionID]
AND p.[SettingGroupID] = i.[SettingGroupID]
UPDATE p
SET p.[Description] = isnull( i.[Description],
p.[Description]) ,
p.[ActiveFlag] =
case when i.[ActiveFlag] not in ('Y','N')
then 'Y' else i.[ActiveFlag]
end
FROM [Server002].[DB2].[dbo].[ProfileOptions] p
INNER JOIN [inserted] i
ON
p.[ProfileOptionID] =
i.[ProfileOptionID]
AND p.[SettingGroupID] = i.[SettingGroupID]
UPDATE p
SET p.[Description] = isnull( i.[Description],
p.[Description]) ,
p.[ActiveFlag] =
case when i.[ActiveFlag] not in ('Y','N')
then 'Y' else i.[ActiveFlag]
end
FROM [Server003].[DB3].[dbo].[ProfileOptions] p
INNER JOIN [inserted] i
ON
p.[ProfileOptionID] =
i.[ProfileOptionID]
AND p.[SettingGroupID] = i.[SettingGroupID]
END
GO
CREATE TRIGGER IO_Trig_D_ProfileOptionRepl on
ProfileOptions
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
IF EXISTS (select [ProfileOptionID]
from [deleted])
UPDATE p
SET [ActiveFlag] =
'N'
FROM [Server001].[DB1].[dbo].[ProfileOptions] p
JOIN [deleted] d
ON
p.ProfileOptionID = d.ProfileOptionID
AND
p.SettingGroupID = d.SettingGroupID
IF EXISTS (select [ProfileOptionID]
from [deleted])
UPDATE p
SET [ActiveFlag] =
'N'
FROM [Server002].[DB2].[dbo].[ProfileOptions] p
JOIN [deleted] d
ON
p.ProfileOptionID = d.ProfileOptionID
AND
p.SettingGroupID = d.SettingGroupID
IF EXISTS (select [ProfileOptionID]
from [deleted])
UPDATE p
SET [ActiveFlag] =
'N'
FROM [Server003].[DB3].[dbo].[ProfileOptions] p
JOIN [deleted] d
ON
p.ProfileOptionID = d.ProfileOptionID
AND
p.SettingGroupID = d.SettingGroupID
END
GO
To set up a development environment,
you have two options.
·
You can combine all partitions into one database,
provided the names of the partition member tables are unique. Or, you can put
them into separate databases on the same instance. This is the simplest method,
and it can work quite well if you are designing a partitionable system because
you want it to be able to scale beyond ordinary design in order to prepare for a
dramatic growth in usage.
·
Alternatively, you can create an instance of
SQL Server on a single computer for each partition. This will be a
near-exact simulation of the production environment, with the only difference
being the linked servers. Because there is a small difference in the way
authentication behaves when it does not have to travel beyond the physical
server, you might find that you have security issues with the links when you put
the partitioned databases on separate servers.
Note: It is possible to run a
production system that consists of partitions on multiple instances running on
the same physical server, but it is not recommended, because there is no real
advantage to it. Letting SQL Server balance the load across resources is a
better way to isolate resources between the servers, because these partitions
are all part of the same system. However, if you must partition within one
server, do so on one instance, and divide your partitions on different file
groups, as discussed earlier in this chapter in "Federation Design
Considerations."
If you are designing a partitionable
system, regardless of whether you are immediately going to implement it as a
federation, you should test it thoroughly with a realistic test script on
separate servers in a federation (rather than separate instances on the same
server). This will allow you to find any remaining issues and eliminate them
before the system is in production.
A vital part of the testing process
is learning how to maintain the system. If it is a new system, you should create
sufficient data so that you can do some long-running tests, simulate different
types of failures and work on recovering from them. This will help you create
some practical documentation to guide you in the production environment.
You should perform the following
tasks while your test scripts are running, and document your findings (including
the amount of time each action takes):
·
Collect performance counters and profiler traces
to observe a baseline (and keep the data to compare performance after making
changes).
·
Monitor activity per partition.
·
Backup and restore a single partition and
multiple partitions.
·
Restore data that has become out of synch between
servers (if this possibility applies to your design).
·
Verify how your standard routine maintenance
affects the server during testing.
·
Implement object changes to partitioned objects
without causing major outages.
·
Repartition the data with minimal (if any)
downtime.
·
Update the routing table to make a change (that
is, change or add a partition, remove a partition, redirect one to a new
server).
It’s a good idea to test disaster-recovery plans as well,
so that you do not have to test your plan in production if a genuine problem
occurs. The following tests are good preparation:
·
Remove a partition while the application is
running, then restore it, and record how the application reacted.
·
Invalidate the routing table with erroneous data
(if it's a separate object) and see what happens.
·
If you are using clusters, fail them over and see
what happens. Try one at first, but then fail all of them over to test the
extremes. Once you have them working, fail back to your original servers. Take
careful notes on the process and what you learn.
·
If you have a remote server site, you should
stage failing over to the remote site.
If you complete these types of tests
and document the lessons you learn well, you will be very well prepared to
support the system in production.
Successfully partitioning a large
existing system usually requires a series of improvements. The tables chosen for
partitioning in each step are usually those that will give the highest
performance gain at that time. Data usage shifts over time, so in order to
remain effective, partitions will occasionally need to be adjusted.
Balancing the load among the tables,
or repartitioning, is a challenging endeavor. Currently, there is no automated
method to do this. If you have made a miscalculation in planning your
partitions, you may decide to repartition an entire table. For example, if you
partition your order table by a sequential order number, you will soon find that
the last partition in the group not only grows largest, but also is queried most
frequently. If instead you partition by geographical region, you still may find
that one area grows faster or is queried much more frequently. In either case,
you will need to rebalance the load by moving some of this data around. This
will be less disruptive if you do it more frequently, moving smaller amounts of
data.
The most straightforward way to do
this is to disable the relevant constraint temporarily while you shift the data
between partitions, and then re-create the constraint when you are finished. If
your application is able to use data-dependent routing exclusively, at least for
the duration of the partition adjustment, you do not have to worry about the
downtime. You should shift the data around first, update your routing table,
update any data-dependent-routing-related or COM objects that contain
partition-specific information, refresh any caching of the data-dependent
routing information, and finally delete the rows that should no longer be in a
particular partition because they have been moved to a better location.
If you add a new partition, you will
have much the same type of work to perform, in addition to altering any
distributed partitioned view or other SQL procedures, functions, or tasks
(especially backup tasks) that need to be aware of the new partition.
It is good to keep in mind that
partitioned tables are just like any other table, aside from the necessary
partition maintenance. Each server and database in a federation must be
separately maintained. They still need index maintenance, and they still need to
be backed up. Occasionally, you may want to run DBCC checks in order to be as
thorough with maintenance as possible. One advantage to maintenance within a
federation is that you can run maintenance on all partitions at the same time,
because each partition is an independent database and maintenance is run
separately on each. Each partition would also benefit from parallel processing
during DBCC and index builds, a
feature of Enterprise Edition.
The following sections provide tips
on creating an optimal backup-and-restore strategy for partitioned
databases.
SQL Server 2000 does not
require you to coordinate backups across member servers. If you do not require
transactional consistency between member servers, backups can be made of each
database independently, without regard for the state of the other member
databases. This method has minimal synchronization overhead so that it has the
lowest possible effect on transaction processing. Another option is to attempt
to back up all databases simultaneously. However, this option does not scale. It
is extremely difficult to manage, and it does not allow sharing of backup
devices.
If transactional consistency—making
sure all partitions can be backed up to the same point—is a requirement, you can
achieve this in SQL Server 2000 by using named marks in the transaction
log. This special mark allows a database to be restored to a named point with
partitioned views. Name marks enable all databases with member tables to be
synchronized to the same point. This requires the recovery mode to be set to
full for each database that is part of the partition.
During backup-and-restore
operations, distributed partitioned views and data-dependent-routing partitioned
databases must remain synchronized. Because data-dependent routing is
essentially code-controlled partitioning, you can employ methods such as marked
backups. To stay transactionally current and avoid transaction loss, you can use
coding or middle-tier technology.
Data-dependent routing can also take
advantage of log marks. Unlike partitioned views, however, data can get out of
synch because each server is completely independent from the others, from a
partitioned standpoint. This means that each server is theoretically only linked
in the application code, even if the servers are linked to put the mark in the
transaction log.
To restore a partitioned database
that uses data-dependent routing, roll each database back to the damaged
server’s last known good mark to ensure that all servers are synchronized.
Rolling back can result in lost transactions. This may be handled with another
technology besides SQL Server, such as Message Queuing or COM+, or with
code that uses XML to store the transaction state. If you choose one of these
technologies, test carefully before relying on it to recover lost data.
For complete details on marking
transactions, see “Backup and Recovery of Related Databases” and “Recovering to
a Named Transaction” in SQL Server Books Online.
To enhance SQL Server
availability, you can use Windows Clustering and SQL Server failover
clustering. SQL Server 2000 makes SQL Server clustering easier to
install, configure, and maintain than in earlier versions. It also provides
support for four-node environments and better support of multiple-instance
failover environments.
The following flowchart illustrates
the design process involved in deciding on an effective clustered
SQL Server implementation.
Figure 17. Clustered SQL Server implementation
design process
Windows 2000 and
SQL Server 2000 support the shared-nothing cluster model, which means that
each node of the cluster is an independent computer with its own resources and
operating system. Each node manages its own resources and provides non-sharing
data services. In case of a node failure, the disks and services running on that
node may fail over to (or restart on) a surviving node, but only one node is
managing one particular set of disks and services at any given time.
Figure 18. Cluster network connection
You can configure failover
clustering of SQL Server 2000 in one of two ways—a single-instance failover
(active/passive) configuration, or a multiple-instance failover (active/active)
configuration.
In the single-instance failover
configuration, the cluster runs a single instance of SQL Server. If the
main server fails, the other server in the cluster can run the same instance. In
this configuration, the two servers share a master database and the set of user
databases.
In the multiple-instance failover
configuration, which is not currently used in the Internet Data Center design, each of the two active
nodes has its own instance of SQL Server. Each instance of SQL Server
is a different installation of the full service one and can be managed,
upgraded, and stopped independently.
To implement a multiple-instance
failover configuration, you need to do the following:
·
Have at least two instances of SQL Server
installed on the cluster.
·
Configure each instance to run on a certain node
as its primary server.
Figure 19. A multiple-instance failover
configuration
Databases that refer to each other
often should be placed on the same SQL Server instance. The following are
examples of databases that are good candidates for placement in a separate
instance:
·
Microsoft Commerce Server 2000 for catalog
databases and application databases
·
Microsoft BizTalk™ Server for XLANG and scheduler
databases
Before implementing a
multiple-instance failover configuration, you must assess the expected load on
each of the database applications and determine whether or not a single node
will be able to handle the combined load in the event of a failover. If not,
then you should consider using two single-instance-failover-mode clusters
instead.
Federated servers do not provide any
failover capability. Each federated server is still an independent
SQL Server, and must be treated as one for the purpose of any available
technology that is implemented along with the federation, such as failover
clustering or log shipping.
You could, for example, create each partition on a failover
cluster as a separate virtual server on a dedicated SQL Server failover
cluster. That way, if you have three database partitions, you would need six
servers to make three independent failover clusters, thus gaining the failover
ability in your federation. This requires some additional work in the
application, because now in addition to handling DDR, you must also include code
that is cluster-aware. This approach is shown in Figure 20.
Figure 20. Clustering each partition
If your database is partitioned
across three virtual servers, the best solution would be to use a minimum of
four servers to create the N+1 scenario using Windows 2000 Datacenter, as
shown in Figure 21.
Figure 21. A four-node cluster
In conjunction with or in place of
clustering, you can use log shipping to create a warm standby server. This
server can be one massive server to host all the partitioned databases (unless
your databases do not have unique names), or a 1:1 ratio of partitioned servers
to log-shipped servers.
In this chapter, you have seen how
SQL Server can be configured to provide a secure, high-performance,
scalable, and highly available data store.
When designing a database solution,
you should take into account the specific needs of your application. This will
affect the security settings and server configuration options you choose. In a
production environment, your SQL Server should generally use Windows
authentication, with prudent network, file-system, registry, and
physical-security measures in place.
Your application design should
incorporate suitable indexes, and the logical and physical design should ensure
the best performance for your specific data-access requirements.
Before scaling out a SQL Server
solution, you should consider the application design and manageability
implications of a distributed database. This includes identifying the most
appropriate data-routing solution, such as data-dependent routing tables or
hashing.
Windows clustering is the preferred
approach to providing high availability. You should carefully assess the number
of databases you need to support and the expected load on each, and then choose
between a single-instance failover and a multiple-instance failover
configuration as appropriate.
