Data Compression in SQL Server 2008


In the Data Compression Wizard Summary, you can expand the nodes to confirm the selections done and click the Finish button to complete the Data Compression Wizard Successfully.

 

Once the Data Compression Wizard has completed successfully, the TSQL to enable the Page Level Compression will be available in the new query window and you will also see a success message as shown in the below snippet. 

 

The final step will be to run the TSQL generated by the Data Compression Wizard to enable the Page Level Compression for Production.TransactionHistory table in AdventureWorks Database.

USE [AdventureWorks]
Go
ALTER TABLE [Production].[TransactionHistory] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE)
Go

The below TSQL will help you check whether the Page Level Compression is enabled for Production.TransactionHistory Table. If the parameter value for DATA_COMPRESSION is 0 then it means that there is no compression enabled, and if the value for DATA_COMPRESSION is 1 then it means that the Row Level Compression is enabled, and if the value for DATA_COMPRESSION is 2 then it means that the Page Level Compression is enabled for the Production. TransactionHistory table.

Use AdventureWorks
Go
SELECT
 OBJECT_NAME(OBJECT_ID) AS [TABLE NAME],
 DATA_COMPRESSION AS [DATA COMPRESSION] FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) =’TransactionHistory’ AND
 DATA_COMPRESSION=’2′
Go

Conclusion
A Database Administrator needs to be very careful before enabling Data Compression for any table or index within a database. Data Compression is a CPU intensive operation and if your environment is already facing CPU crunch then this is not the best solution to look for. Before enabling Data Compression on any production table a thorough investigation is required.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |