Compression consumes resources and takes up space

   Compression is the process of reducing the data storage space of a file or in this case database without decaying the integrity or performance of the underlying performance. Many compression algorithms and programs exist and their use is common place (i.e. like when you zip a folder prior to sending it to a colleague you are compressing the directory of files to make it smaller for transmission across the network). Normally SQL Server administrators utilize compression in order to improve query performance and to reduce I/O read/writes. It is useful to understand that compression doesn’t always improve performance for every database or every query and while the reasons for this are varied it can sometimes be because the compression work itself consumes resources and takes up space in the form of metadata.                SQL Server has since the 2008 version included the Data Compression Wizard in order to make this process easy for users with a relatively simple to use GUI. All you need to do is right click a table, select Storage, and then Manage Compression. This will launch the Compression Wizard and provide the user with a couple options in terms of how to execute the compression on the data.                The user must decide whether to utilize row compression or page compression, which is offered as a drop down decision under compression type. Row compression will change the data format in which values are stored within the database. These changes include switched fixed width elements to variable width and eliminating blank spaces. Page compression performs the same data format changes as row compression while also performing prefix compression and dictionary compression.                Once the user has selected the compression type they will utilize the wizard shows them both the current size of the database as well as what the new storage size will be so that they can see the space savings that will be achieved with the compression. You can choose to either execute the compression immediately from the wizard, to schedule the compression for a particular time, or to create a T-SQL script to be executed in the future.


I'm James!

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

Check it out