ALTER DATABASE (Transact-SQL) - Analytics Platform System (PDW)

Applies to: Analytics Platform System (PDW)

In Analytics Platform System (PDW), ALTER DATABASE modifies the maximum database size options for replicated tables, distributed tables, and the transaction log. Use this statement to manage disk space allocations for a database as it grows or shrinks in size. This article also describes syntax related to setting database options in Analytics Platform System (PDW).

Transact-SQL syntax conventions

Syntax

-- Analytics Platform System
ALTER DATABASE database_name
    SET ( <set_database_options> | <db_encryption_option> )
[;]

<set_database_options> ::=
{
    AUTOGROW = { ON | OFF }
    | REPLICATED_SIZE = size [GB]
    | DISTRIBUTED_SIZE = size [GB]
    | LOG_SIZE = size [GB]
    | SET AUTO_CREATE_STATISTICS { ON | OFF }
    | SET AUTO_UPDATE_STATISTICS { ON | OFF }
    | SET AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<db_encryption_option> ::=
    ENCRYPTION { ON | OFF }

Arguments

database_name

The name of the database to be modified. To display a list of databases on the appliance, use sys.databases.

AUTOGROW = { ON | OFF }

Updates the AUTOGROW option. When AUTOGROW is ON, Analytics Platform System (PDW) automatically increases the allocated space for replicated tables, distributed tables, and the transaction log as necessary to accommodate growth in storage requirements. When AUTOGROW is OFF, Analytics Platform System (PDW) returns an error if replicated tables, distributed tables, or the transaction log exceeds the maximum size setting.

REPLICATED_SIZE = size [GB]

Specifies the new maximum gigabytes per Compute node for storing all of the replicated tables in the database being altered. If you're planning for appliance storage space, you need to multiply REPLICATED_SIZE by the number of Compute nodes in the appliance.

DISTRIBUTED_SIZE = size [GB]

Specifies the new maximum gigabytes per database for storing all of the distributed tables in the database being altered. The size is distributed across all of the Compute nodes in the appliance.

LOG_SIZE = size [GB]

Specifies the new maximum gigabytes per database for storing all of the transaction logs in the database being altered. The size is distributed across all of the Compute nodes in the appliance.

ENCRYPTION { ON | OFF }

Sets the database to be encrypted (ON) or not encrypted (OFF). Encryption can only be configured for Analytics Platform System (PDW) when sp_pdw_database_encryption has been set to 1. A database encryption key must be created before transparent data encryption can be configured. For more information about database encryption, see Transparent data encryption (TDE).

SET AUTO_CREATE_STATISTICS { ON | OFF }

When the automatic create statistics option, AUTO_CREATE_STATISTICS, is ON, the Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan. These single-column statistics are created on columns that don't already have a histogram in an existing statistics object.

Default is ON for new databases created after upgrading to AU7. The default is OFF for databases created prior to the upgrade.

For more information about statistics, see Statistics

SET AUTO_UPDATE_STATISTICS { ON | OFF }

When the automatic update statistics option, AUTO_UPDATE_STATISTICS, is ON, the query optimizer determines when statistics might be out-of-date and then updates them when they're used by a query. Statistics become out-of-date after operations insert, update, delete, or merge change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view.

Default is ON for new databases created after upgrading to AU7. The default is OFF for databases created prior to the upgrade.

For more information about statistics, see Statistics.

SET AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

The asynchronous statistics update option, AUTO_UPDATE_STATISTICS_ASYNC, determines whether the Query Optimizer uses synchronous or asynchronous statistics updates. The AUTO_UPDATE_STATISTICS_ASYNC option applies to statistics objects created for indexes, single columns in query predicates, and statistics created with the CREATE STATISTICS statement.

Default is ON for new databases created after upgrading to AU7. The default is OFF for databases created prior to the upgrade.

For more information about statistics, see Statistics.

Permissions

Requires the ALTER permission on the database.

Error Messages

If auto-stats is disabled and you try to alter the statistics settings, PDW outputs the error This option isn't supported in PDW. The system administrator can enable auto-stats by enabling the feature switch AutoStatsEnabled.

Remarks

The values for REPLICATED_SIZE, DISTRIBUTED_SIZE, and LOG_SIZE can be greater than, equal to, or less than the current values for the database.

Limitations

Grow and shrink operations are approximate. The resulting actual sizes can vary from the size parameters.

Analytics Platform System (PDW) doesn't perform the ALTER DATABASE statement as an atomic operation. If the statement is aborted during execution, changes that have already occurred will remain.

The statistics settings only work if the administrator has enable auto-stats. If you're an administrator, use the feature switch AutoStatsEnabled to enable or disable auto-stats.

Locking behavior

Takes a shared lock on the DATABASE object. You can't alter a database that is in use by another user for reading or writing. This includes sessions that have issued a USE statement on the database.

Performance

Shrinking a database can take a large amount of time and system resources, depending on the size of the actual data within the database, and the amount of fragmentation on disk. For example, shrinking a database could take several hours or more.

Determine Encryption Progress

Use the following query to determine progress of database transparent data encryption as a percent:

WITH
database_dek AS (
    SELECT ISNULL(db_map.database_id, dek.database_id) AS database_id,
        dek.encryption_state, dek.percent_complete,
        dek.key_algorithm, dek.key_length, dek.encryptor_thumbprint,
        type
    FROM sys.dm_pdw_nodes_database_encryption_keys AS dek
    INNER JOIN sys.pdw_nodes_pdw_physical_databases AS node_db_map
        ON dek.database_id = node_db_map.database_id
        AND dek.pdw_node_id = node_db_map.pdw_node_id
    LEFT JOIN sys.pdw_database_mappings AS db_map
        ON node_db_map .physical_name = db_map.physical_name
    INNER JOIN sys.dm_pdw_nodes nodes
        ON nodes.pdw_node_id = dek.pdw_node_id
    WHERE dek.encryptor_thumbprint <> 0x
),
dek_percent_complete AS (
    SELECT database_dek.database_id, AVG(database_dek.percent_complete) AS percent_complete
    FROM database_dek
    WHERE type = 'COMPUTE'
    GROUP BY database_dek.database_id
)
SELECT DB_NAME( database_dek.database_id ) AS name,
    database_dek.database_id,
    ISNULL(
       (SELECT TOP 1 dek_encryption_state.encryption_state
        FROM database_dek AS dek_encryption_state
        WHERE dek_encryption_state.database_id = database_dek.database_id
        ORDER BY (CASE encryption_state
            WHEN 3 THEN -1
            ELSE encryption_state
            END) DESC), 0)
        AS encryption_state,
dek_percent_complete.percent_complete,
database_dek.key_algorithm, database_dek.key_length, database_dek.encryptor_thumbprint
FROM database_dek
INNER JOIN dek_percent_complete
    ON dek_percent_complete.database_id = database_dek.database_id
WHERE type = 'CONTROL';

For a comprehensive example demonstrating all the steps in implementing TDE, see Transparent data encryption (TDE).

Examples: Analytics Platform System (PDW)

A. Alter the AUTOGROW setting

Set AUTOGROW to ON for database CustomerSales.

ALTER DATABASE CustomerSales
    SET ( AUTOGROW = ON );

B. Alter the maximum storage for replicated tables

The following example sets the replicated table storage limit to 1 GB for the database CustomerSales. This is the storage limit per Compute node.

ALTER DATABASE CustomerSales
    SET ( REPLICATED_SIZE = 1 GB );

C. Alter the maximum storage for distributed tables

The following example sets the distributed table storage limit to 1,000 GB (1 terabyte) for the database CustomerSales. This is the combined storage limit across the appliance for all of the Compute nodes, not the storage limit per Compute node.

ALTER DATABASE CustomerSales
    SET ( DISTRIBUTED_SIZE = 1000 GB );

D. Alter the maximum storage for the transaction log

The following example updates the database CustomerSales to have a maximum SQL Server transaction log size of 10 GB for the appliance.

ALTER DATABASE CustomerSales
    SET ( LOG_SIZE = 10 GB );

E. Check for current statistics values

The following query returns the current statistics values for all databases. The value 1 means the feature is on, and a 0 means the feature is off.

SELECT [name],
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases;

F. Enable auto-create and auto-update stats for a database

Use the following statement to enable create and update statistics automatically and asynchronously for database, CustomerSales. This creates and updates single-column statistics as necessary to create high-quality query plans.

ALTER DATABASE CustomerSales
    SET AUTO_CREATE_STATISTICS ON;
ALTER DATABASE CustomerSales
    SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE
    SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Related content

  • Last updated on