Creating or altering a database

Creating or altering a database is I/O-intensive; consequently, other I/O-intensive operations may suffer. When you create a database, Adaptive Server copies the model database to the new database and then initializes all the allocation pages and clears database pages.

The following procedures can speed database creation or minimize its impact on other processes:

Use the for load option to create database if you are restoring a database, that is, if you are getting ready to issue a load database command.

When you create a database without for load, it copies model and then initializes all of the allocation units.

When you use for load, it postpones zeroing the allocation units until the load is complete. Then it initializes only the untouched allocation units. If you are loading a very large database dump, this can save a lot of time.
Create databases during off-hours if possible.

create database and alter database perform concurrent parallel I/O when clearing database pages. The number of devices is limited by the number of large i/o buffers configuration parameter. The default value for this parameter is 6, allowing parallel I/O on 6 devices at once.

A single create database and alter database command can use up to 32 of these buffers at once. These buffers are also used by load database, disk mirroring, and some dbcc commands.

Using the default value of 6, if you specify more than 6 devices, the first 6 writes are immediately started. As the I/O to each device completes, the 16K buffers are used for remaining devices listed in the command. The following example names 10 separate devices:

create database hugedb
      on dev1 = 100,
      dev2 = 100,
      dev3 = 100,
      dev4 = 100,
      dev5 = 100,
      dev6 = 100,
      dev7 = 100,
      dev8 = 100
log on logdev1 = 100,
      logdev2 = 100

During operations that use these buffers, a message is sent to the log when the number of buffers is exceeded. This information for the create database command above shows that create database started clearing devices on the first 6 disks, using all of the large I/O buffers, and then waited for them to complete before clearing the pages on other devices:

CREATE DATABASE: allocating 51200 pages on disk ’dev1’
CREATE DATABASE: allocating 51200 pages on disk ’dev2’
CREATE DATABASE: allocating 51200 pages on disk ’dev3’
CREATE DATABASE: allocating 51200 pages on disk ’dev4’
CREATE DATABASE: allocating 51200 pages on disk ’dev5’
CREATE DATABASE: allocating 51200 pages on disk ’dev6’
01:00000:00013:1999/07/26 15:36:17.54 server  No disk i/o buffers are available for this operation. The total number of buffers is controlled by the configuration parameter ’number of large i/o buffers’.
CREATE DATABASE: allocating 51200 pages on disk ’dev7’
CREATE DATABASE: allocating 51200 pages on disk ’dev8’
CREATE DATABASE: allocating 51200  pages on disk ’logdev1’
CREATE DATABASE: allocating 51200  pages on disk ’logdev2’

When create database copies model, it uses 2K I/O.

In Adaptive Server version 12.5.03 and above, the size of the large I/O buffers used by create database, alter database, load database, and dbcc checkalloc is now one allocation (256 pp), not one extent (8 pp). The server thus requires more memory allocation for large buffers. For example, a disk buffer that required memory for 8 pages in earlier versions now requires memory for 256 pages.

See the System Administration Guide.