Site Loader
Rock Street, San Francisco

Compression reduces storage requirements. Using row and page compression reduces the storage for tables benefitting the reduction of IO demand or increase density of data in cache. Compression depends on how IO and processor bound a system. If extreme IO bound, maximum page compression is best. If extreme processor bound, no compression will be best. In data warehousing environments or tables without continuous updates, row and page compression are effective to reduce IO demands with negligible processor cost. Running a test to of row and page compression will provide any additional cost of insert and update and determining if the compression is even beneficial” (Lees, 2017).Data compression is implemented differently on different types of table. Rowstore tables and indexes use data compression to help reduce the size of database. Saving space helps to improve performance of I/O intensive workloads. Why? Data is stored in fewer pages and queries need read fewer pages from disk as a result. Extra CPU resources are required on the database server to compress and decompress data while data is exchanged with the application.Row and page compression can be configured on the following database objects:Entire table stored as a heap.Entire table stored as a clustered index.Entire nonclustered index.Entire indexed view.Separate partitioned tables and indexes.”Columnstore tables and indexes always use columnstore compression not user configurable by archival compression. It reduces data size to afford extra time and CPU resources to store and retrieve data. Columnstore archival compression can be configured on the following database objects:Entire columnstore table or clustered columnstore index.Entire nonclustered columnstore index.Separate partitioned columnstore tables and indexes” (Microsoft, 2017).Here are some examples of table, row, page, even backup compressions from Microsoft, 2017:Table (Enables row compression on all partitions in the specific table)USE AdventureWorks2012;GOEXEC sp_estimate_data_compression_savings ‘Production’, ‘TransactionHistory’, NULL, NULL, ‘ROW’ ; ALTER TABLE Production.TransactionHistory REBUILD PARTITION = ALLWITH (DATA_COMPRESSION = ROW); GO Index (index ID 2 is rebuilt and compressionspecified)USE AdventureWorks2012;GOSELECT name, index_idFROM sys.indexesWHERE OBJECT_NAME (object_id) = N’TransactionHistory’; EXEC sp_estimate_data_compression_savings    @schema_name = ‘Production’,    @object_name = ‘TransactionHistory’,    @index_id = 2,    @partition_number = NULL,    @data_compression = ‘PAGE’; ALTER INDEX IX_TransactionHistory_ProductID ON Production.TransactionHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);GO Here are some examples of table, row, page, even backup compressions from Borland, 2012:Row (no data compression)USE AdventureWorks2012; GO  CREATE TABLE TestRowCompression ( ItemID INT ,   ItemName CHAR(50) ,   DateAdded DATETIME ,   UnitPrice MONEY ,   ItemLength DECIMAL ,   ItemWidth DECIMAL  ) WITH ( DATA_COMPRESSION = NONE); Row (compressed)ALTER TABLE dbo.bigTransactionHistory REBUILD WITH (DATA_COMPRESSION = ROW);sp_spaceused ‘dbo.bigTransactionHistory’; Page (using sp_spacedused)ALTER TABLE dbo.TestPageCompression REBUILD WITH (DATA_COMPRESSION = PAGE);sp_spaceused ‘dbo.TestPageCompression’; Determine level of compression availableEXEC sp_estimate_data_compression_savings ‘dbo’, ‘TestRowCompression’, NULL, NULL,’ROW’;Manage future data growthALTER TABLE dbo.TestRowCompression REBUILD WITH (DATA_COMPRESSION = ROW); 

Post Author: admin


I'm Eunice!

Would you like to get a custom essay? How about receiving a customized one?

Check it out