如何使用SQL Server数据压缩来节省空间

In the previous article, on tracking database file storage usage How to track SQL Server database space usage with built-in functions and DMVs, I introduced some ideas and calculations for figuring out when your database is going to run out of headroom on the file system – hopefully in time to get an order in the works to increase that space!

在上一篇文章中，有关跟踪数据库文件存储使用情况如何使用内置函数和DMV跟踪SQL Server数据库空间使用情况 ，我介绍了一些思路和计算方法，用于弄清数据库何时将耗尽文件系统上的余量。 –希望能及时得到订单，以扩大该空间！

In the meantime, though, you know it will be smart to do all you can to minimize the storage footprint of your database. SQL Server data compression, available in Enterprise versions, is one such approach. The idea is simple: Find repetitive sections of data in a table and store unique information just per row or page or column store index. Modern compression algorithms can reduce the on-disk footprint by 40-60% or even more, depending on the type of data.

但是，与此同时，您知道尽一切努力将数据库的存储空间降至最低是明智的。 企业版中提供SQL Server数据压缩就是这样一种方法。 这个想法很简单：在表中查找数据的重复部分，并仅按行，页面或列的存储索引存储唯一信息。 根据数据类型的不同，现代压缩算法可以减少40-60％甚至更多的磁盘占用空间。

Compressing your SQL Server data is a simple way to cram more into your limited disk space. In this article, I’ll start off by showing you how you can find out what tables and indexes are not compressed. This will give you vital insight into what to prioritize when you get to work on those space hogs!

压缩SQL Server数据是一种将更多数据填充到有限磁盘空间中的简单方法。 在本文中，我将向您展示如何找出未压缩的表和索引。 这将为您提供重要的见解，使您可以了解在处理这些太空猪时应优先考虑的事项！

Before we get started, let me answer a question you may be asking about performance. Compressed tables use extra CPU cycles to compress/decompress their contents. However, you will need less I/O to read and write those tables, since they now occupy (much) less space on disk. Unless you are severely CPU-bound – in which case you have bigger problems – compression may well enhance your system throughput by reducing I/O.

在开始之前，让我回答一个您可能会问到的性能问题。 压缩表使用额外的CPU周期来压缩/解压缩其内容。 但是，您将需要更少的I / O来读取和写入这些表，因为它们现在在磁盘上占用的空间要少得多。 除非您的CPU受到严重限制（在这种情况下您会遇到更大的问题），否则压缩可以通过减少I / O来很好地提高系统吞吐量。

哪些表和索引未压缩？ (What tables and indexes are not compressed?)

Two handy DMVs will give us this information. Here is a query that will extract a neat overview of compression opportunities:

两个方便的DMV将为我们提供此信息。 这是一个查询，它将提取压缩机会的完整概述：

SELECT DISTINCT
    s.name,
    t.name,
    i.name,
    i.type,
    i.index_id,
    p.partition_number,
    p.rows
FROM sys.tables t
LEFT JOIN sys.indexes i
ON t.object_id = i.object_id
JOIN sys.schemas s
ON t.schema_id = s.schema_id
LEFT JOIN sys.partitions p
ON i.index_id = p.index_id
    AND t.object_id = p.object_id
WHERE t.type = 'U' 
  AND p.data_compression_desc = 'NONE'
ORDER BY p.rows desc
 

Running this against the AdventureWorks database, you might get a result set like this:

在AdventureWorks数据库上运行此命令，可能会得到如下结果集：