使用SSMS管理数据压缩

SQL Server 2008的一个伟大的新特性是数据压缩，它实际上减少了存储并加快了访问速度，这是一个令人惊讶的组合。通常，压缩策略意味着较差的性能权衡，但在这里不是这样。SSMS 2008还有一个有用的向导，可以帮助我们决定可以选择的压缩级别。SQL Server 2008支持两级数据压缩——行级和页级。还有备份压缩，它是完全独立的，但也非常有用。行级压缩查看一行中的每个列，并尝试基于没有填充为列提供的空间的数据进行压缩。页级压缩包括行级压缩的特性，但也会查看数据页上的多个行，以获得可由单个代码替换的重复，从而节省空间。这两种方法都由SQL Server透明地处理，因此不需要更改现有代码。每种方法都节省了空间，还减少了I/O和处理时间，从而提高了性能。这是一种评估是否使用数据压缩以及在SSMS 2008中隐藏的数据压缩级别的好方法。 Just right-click on your table and choose Storage then “Manage Compression”. This launches the Data Compression Wizard. For instance, if you do this for the FactInternetSales table in the AdventureWorksDW database you will be able to calculate the space savings for both row and page level compression. By default, the 60,000 rows take up 17MB. Row level compression brings it down to 12.3MB. Page level compression brings it down to 8.6MB which is around 50% in storage savings. Not bad. You can then have the wizard generate a script to enable the compression or have it compress the table immediately. I ran a simple query to get all the rows in the table both before and after the compression and the execution plan showed a subtree cost of .91 milliseconds before compression and .36 after. That’s quite impressive. Normally we have to make a choice between performance and storage but not in this case. The only bad news here is that compression is an Enterprise Edition feature so you have to pay for it. Oh well, you can’t win them all. Cheers Brian