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.

To speed database creation or minimize its impact on other processes:

• Use the create database...for load option 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, Adaptive Server copies model and then initializes all of the allocation units.

  When you use for load, Adaptive Server does initialize 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-peak 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’