Managing Data Compression in SQL Server 2008

Data Compression in SQL Server 2008:

The size of the databases are increased drastically due to

- Movement of data from flat files into the databases due to east of management, accessibility, analysis and reporting.

- Significant amount of data stored in databases is born digitally.

Hence the hardware needed to maintain the databases size is increases leading to more manageability cost. A need arise from the customer to reduce the size of the database. Also another thing to be noted is that lot of data is loaded into memory than would have been possible.

SQL Server compresses the data in two ways:

i) Row Compression:

There is a chance that common prefix to be available across the columns and rows. Storing the common prefixes only once and then referencing it from other columns. It uses the same analogy between char and varchar for all fixed data types.

ii) Page Compression:

In this type of compression, if we have same values across the rows in different pages then the value is stored only once and others have references to it.

Managing Compression using SQL Server Management Studio:

Data Compression can be configured across partition level for tables and indexes separately by launching the Data Compression Wizard.

It can be launched from the context menu of the specified table/index.

Steps to configure the data compression :

1. Open the SQL Server Management Studio 2008 (Start ->Programs->Microsoft SQL Server 2008->SQL Server Management Studio).

2. Connect to the SQL Server and expand databases to navigate to the required database.

3. Select the table/index where the data compression needs to be enabled. Right click the table/index and select Storage->Manage Compression as shown below:

4. A wizard of data compression wizard opens with the welcome page as follows. There is a check box for the user to configure whether the welcome page needs to be shown next time the wizard is launched.

5. Click next to launch the Select Compression Page. This page is used to enable/disable compression on a partition basis. This page also provides the information of data available (number of rows) on the partition basis. Also it provides the information about estimated size for the requested compression.

Different ways of configuring the compression:

Per partition configuration:

a. Click the drop down and select the type of the compression for each partition.

b. Select the particular partition and right click and select the type of partition needed.

In case if all partitions needs a same compression type. Click the check box and select the type of compression which needs to be enabled across all the partitions from the drop down beside it as shown below

In order to know the estimated compressed size, click the calculate button which displays the requested compressed space.

6.

Click next to launch the output options page

Three output options are available:

Action items:

a) Create script : Generates the T-SQL script (without execution).

b) Run Immediately : Execute the configured compression.

c) Schedule : Schedules the job at the specified date and time.

Script Options:

i) Script to file : Generates the script in a new file .

ii) Script to clipboard : Generates the script and copies to clip board.

iii) Script to new query window: Generates the script in the new query window (default option)

7. Click Next to show summary of the table, database and server where the compression needs to be managed.

8. Click finish button to exit the wizard.

At any point of time,

Help Button : This provides the help information about the particular page.

Prev : Navigates to the previous page.

Next : Navigates to the next page.

Cancel : Cancels the page.

The generated T-SQL is as follows:

Sample T-SQL :

ALTER TABLE <tableName> REBUILD PARTITION = <partitionNumber> WITH (DATA_COMPRESSION = <compressiontype>)

<compresstype> = {ROW | PAGE|NONE}

ALTER TABLE [HumanResources].[Department] REBUILD PARTITION = 1 WITH(DATA_COMPRESSION = ROW )

ALTER

TABLE [HumanResources].[Department] REBUILD PARTITION = 2 WITH(DATA_COMPRESSION = ROW )

ALTER

TABLE [HumanResources].[Department] REBUILD PARTITION = 3 WITH(DATA_COMPRESSION = PAGE )