行存储索引改换成列存储索引_列存储索引增强功能

行存储索引改换成列存储索引

Data compression is required to reduce database storage size as well as improving performance for the existing data. SQL Server 2008 introduced Data compression as an enterprise version feature. Further to this, SQL Server 2016 SP1 and above supports data compression using the standard edition as well.

需要数据压缩以减小数据库存储大小以及提高现有数据的性能。 SQL Server 2008引入了数据压缩作为企业版功能。除此之外，SQL Server 2016 SP1和更高版本还支持使用标准版的数据压缩。

As per Microsoft docs, SQL Server 2017 and Azure SQL Database support row and page compression for rowstore tables and indexes, and supports columnstore and columnstore archival compression for columnstore tables and indexes.

根据Microsoft 文档，SQL Server 2017和Azure SQL数据库支持行存储表和索引的行和页面压缩，并支持列存储表和索引的列存储和列存储档案压缩。

We used to analyze the objects using the stored procedure sp_estimate_data_compression_savings. This procedure gives the estimated object size of the object after the specified compression. Until SQL Server 2017, we can estimate indexes, indexed views, heaps using this procedure.

我们曾经使用存储过程sp_estimate_data_compression_savings分析对象。此过程给出指定压缩后对象的估计对象大小。在SQL Server 2017之前，我们可以使用此过程估算索引，索引视图，堆。

In my previous article, Columnstore Index Enhancements in SQL Server 2019 – Part 1, we learned Columnstore index stats update in clone databases. SQL Server 2019 also provides enhancement to sp_estimate_data_compression_savings. In this article, we will explore the benefit out of it.

在我之前的文章SQL Server 2019中的Columnstore索引增强-第1部分中，我们了解了克隆数据库中的Columnstore索引统计信息更新。 SQL Server 2019还提供了对sp_estimate_data_compression_savings的增强。在本文中，我们将探讨从中受益。

Syntax for sp_estimate_data_compression_savings is as below:

sp_estimate_data_compression_savings的语法如下：

sp_estimate_data_compression_savings   
     [ @schema_name = ] 'schema_name'    
   , [ @object_name = ] 'object_name'   
   , [@index_id = ] index_id   
   , [@partition_number = ] partition_number   
   , [@data_compression = ] 'data_compression'   
[;]

@schema_name: Schema of the object(table,index)
@schema_name：对象的架构（表，索引）
@object_name: Name of the table or indexed views
@object_name：表或索引视图的名称
@index_id: we need to specify the ID of the index. If there is no index on the table, we can specify 0 or NULL
@index_id：我们需要指定索引的ID。如果表上没有索引，我们可以指定0或NULL
@partition_number: it is the partition number of the object. If there is no index, we need to specify NULL in this value as well
@partition_number：它是对象的分区号。如果没有索引，我们也需要在该值中指定NULL
COLUMNSTORE, or COLUMNSTORE或COLUMNSTORE_ARCHIVE in SQL Server 2019 COLUMNSTORE_ARCHIVE

When we create the columnstore index, we specify the data compression method to apply. There are two kinds of data compression applied to the columnstore index.

创建列存储索引时，我们指定要应用的数据压缩方法。列存储索引有两种数据压缩。

COLUMNSTORE: It is the default compression option is the default and specifies to compress data with the columnstore compression
COLUMNSTORE ：这是默认的压缩选项，是默认选项，它指定使用列存储压缩来压缩数据
COLUMNSTORE_ARCHIVE: we can compress data further by using this option. This is useful to compress data that is used very less frequent. This type of compression takes extra system resources in terms of CPU and Memory
COLUMNSTORE_ARCHIVE ：我们可以使用此选项进一步压缩数据。这对于压缩很少使用的数据很有用。这种类型的压缩会占用CPU和内存方面的额外系统资源

Until SQL Server 2017, sp_estimate_data_compression_savings works for row or page store data compression. This actually takes a sampling of the source object pages and creates them in the tempdb using the specified compression. Now we have