Database Reference
In-Depth Information
7.
Create a clustered index as follows:
CREATE CLUSTERED INDEX IX_PARTN_ADWCompany ON Sales.ADWCompany
(ParentCompanyID, CreateDate) ON ADW_IDX
Now let us plan for the vertical data partitioning:
1.
Divide the tables based on the number of rows on a page, which depends on the
width of the table.
2.
Vertical splitting is a method of reducing the width of the table by splitting the column
of the table into multiple tables.
3.
It is ideal to keep frequently used columns in one table and other columns in
other tables.
4.
In order to help the performance, it is ideal to involve equi-join between two or more
partitioned tables by ensuring the partitioning column is the same on which the
tables are joined.
5.
The tables and indexes should be collocated, which means they should use the same
named partition function and mapping should be based on the Partition Scheme for
an aligned index.
Now, let us plan for the hardware configuration and filegroup placement:
1. To achieve performance and better I/O operations for data, always place each
partition filegroup on a different physical disk drive.
2. Hardware-based solutions can be implemented by using RAID (Redundant Array of
Independent Disks) levels such as 0, 1, 5, and 1+0.
3. The RAID levels are as follows:
RAID 0
is data striping with no redundancy or fault tolerance
RAID 1
is mirroring, where every disk is managed as a copy
RAID 5
is stripping with parity, disk contents can be recreated from the
parity stored on the other disks in the array
RAID 10
, or
1+0
, is a combination of
RAID
1
and
RAID
0
. This is the best
configuration for write performance as each disk has a mirrored duplicate,
offering the fault tolerance of
RAID
1
with the performance advantages of
RAID
0
4. The hardware-based RAID uses intelligent drive controller and a redundant array of
disk drives to help protect against data loss in case of media failure and performance
improvement on read and write operations.
5. Using RAID level 1 through 5 will automate redundancy and fault-tolerance at the
hardware level with no overhead on the system processor.
6. Place the filegroups on the single RAID 5 array, this is not optimal for
TEMPDB
database or transaction logs.
Search WWH ::
Custom Search