Home

	Expand Search

Performance and Tuning Guide Volumes 1 - 3 (Online Only)

Chapter 5 Controlling Physical Data Placement

Chapter 5

Controlling Physical Data Placement

This describes how controlling the location of tables and indexes can improve performance.

Object placement can improve performance

Symptoms of poor object placement

Underlying problems

Using sp_sysmon while changing data placement

Terminology and concepts

Guidelines for improving I/O performance

Spreading data across disks to avoid I/O contention

Isolating server-wide I/O from database I/O

Keeping transaction logs on a separate disk

Mirroring a device on a separate disk

Creating objects on segments

Using segments

Separating tables and indexes

Splitting large tables across devices

Moving text storage to a separate device

Partitioning tables for performance

User transparency

Partitioned tables and parallel query processing

Improving insert performance with partitions

Restrictions on partitioned tables

Partition-related configuration parameters

How Adaptive Server distributes partitions on devices

Space planning for partitioned tables

Read-only tables

Read-mostly tables

Tables with random data modification

Commands for partitioning tables

alter table...partition syntax

alter table...unpartition Syntax

Changing the number of partitions

Distributing data evenly across partitions

Using parallel bcp to copy data into partitions

Getting information about partitions

Using bcp to correct partition balance

Checking data distribution on devices with sp_helpsegment

Updating partition statistics

Steps for partitioning tables

Backing up the database after partitioning tables

Table does not exist

Table exists elsewhere in the database

Table exists on the segment

Special procedures for difficult situations

Clustered indexes on large tables

Alternative for clustered indexes

Problems when devices for partitioned tables are full

Adding disks when devices are full

Adding disks when devices are nearly full

Maintenance issues and partitioned tables

Regular maintenance checks for partitioned tables