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.
ALTER TABLE [Production].[TransactionHistory] REBUILD PARTITION = ALL
(DATA_COMPRESSION = PAGE)
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.
OBJECT_NAME(OBJECT_ID) AS [TABLE NAME],
DATA_COMPRESSION AS [DATA COMPRESSION] FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID) =’TransactionHistory’ AND
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.