sp_estimate_data_compression_savings (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Retorna o tamanho atual do objeto solicitado e faz a estimativa do tamanho do objeto para o estado de compactação solicitado. A compactação pode ser avaliada para tabelas inteiras ou partes de tabelas. Isso inclui heaps, índices clusterizados, índices não clusterizados, índices columnstore, exibições indexadas e partições de tabela e índice. Os objetos podem ser compactados usando compactação de arquivo compactado de linha, página, columnstore ou columnstore. Se a tabela, índice ou partição já estiver compactada, você poderá usar este procedimento para estimar o tamanho da tabela, índice ou partição se ela for recompactada ou armazenada sem compactação.

A partir do SQL Server 2022 (16.x), você pode compactar dados XML fora da linha em colunas usando o tipo de dados, reduzindo os xml requisitos de armazenamento e memória. Para obter mais informações, veja CREATE TABLE (Transact-SQL) e CREATE INDEX (Transact-SQL). sp_estimate_data_compression_savings oferece suporte a estimativas de compactação XML.

Observação

Compactação e sp_estimate_data_compression_savings não estão disponíveis em todas as edições do SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, confira Edições e recursos com suporte no SQL Server 2022.

O sys.sp_estimate_data_compression_savings procedimento armazenado do sistema está disponível no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure.

Para estimar o tamanho do objeto se ele usasse a configuração de compactação solicitada, esse procedimento armazenado faz uma amostra do objeto de origem e carrega esses dados em uma tabela e índice equivalentes criados no tempdb. A tabela ou índice criado em tempdb é então compactado para a configuração solicitada e a economia de compactação estimada é calculada.

Para alterar o estado de compactação de uma tabela, índice ou partição, use as instruções ALTER TABLE ou ALTER INDEX . Para obter informações gerais sobre compactação, consulte Compactação de dados.

Observação

Se os dados existentes forem fragmentados, é possível reduzir seu tamanho sem usar compactação recriando o índice. Para índices, o fator de preenchimento será aplicado durante a recriação de um índice. Isso pode aumentar o tamanho do índice.

Convenções de sintaxe de Transact-SQL

Sintaxe

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'
   , [ @xml_compression = ] xml_compression
[ ; ]

Argumentos

@schema_name [ = ] 'schema_name'

O nome do esquema de banco de dados que contém a tabela ou o modo de exibição indexado. schema_name é sysname. Se schema_name for NULL, o esquema padrão do usuário atual será usado.

@object_name [ = ] 'object_name'

O nome da tabela ou do modo de exibição indexado em que o índice está. object_name é sysname.

@index_id [ = ] index_id

A ID do índice. index_id é int e pode ser um dos seguintes valores: o número de ID de um índice, NULL ou 0 se object_id for um heap. Para retornar informações de todos os índices de uma tabela base ou exibição, especifique NULL. Se você especificar NULL, também deverá especificar NULL para partition_number.

@partition_number [ = ] partition_number

O número da partição no objeto. partition_number é int e pode ser um dos seguintes valores: o número de partição de um índice ou heap, NULL ou 1 para um índice ou heap não particionado.

Para especificar a partição, você também pode especificar a função $PARTITION . Para retornar informações de todas as partições do objeto proprietário, especifique NULL.

@data_compression [ = ] 'data_compression'

O tipo de compressão a ser avaliado. data_compression pode ser um dos seguintes valores: NONE, ROW, PAGE, COLUMNSTORE ou COLUMNSTORE_ARCHIVE.

Para o SQL Server 2022 (16.x) e posterior, NULL também é um valor possível. data_compression não pode ser NULL se xml_compression for NULL.

@xml_compression [ = ] xml_compression

Aplicável a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

Especifica se a economia deve ser calculada para compactação XML. xml_compression é bit e pode ser NULL, 0 ou 1. O padrão é NULL.

xml_compression não pode ser NULL se data_compression for NULL.

Valores do código de retorno

0 (sucesso) ou 1 (falha).

Conjunto de resultados

O conjunto de resultados a seguir é retornado para fornecer o tamanho atual e estimado da tabela, índice ou partição.

Nome da coluna Tipo de dados Descrição
object_name sysname Nome da tabela ou exibição indexada.
schema_name sysname Esquema da tabela ou exibição indexada.
index_id int ID de um índice.

0 = Heap

1 = Índice clusterizado

> 1 = Índice não clusterizado
partition_number int Número da partição. Retorna 1 para uma tabela ou índice não particionado.
size_with_current_compression_setting (KB) bigint Tamanho da tabela, índice ou partição solicitada como existe atualmente.
size_with_requested_compression_setting (KB) bigint Tamanho estimado da tabela, índice ou partição que usa a configuração de compactação solicitada; e, se aplicável, o fator de preenchimento existente, e supondo que não haja fragmentação.
sample_size_with_current_compression_setting (KB) bigint Tamanho do exemplo com a definição de compactação atual. Isso inclui qualquer fragmentação.
sample_size_with_requested_compression_setting (KB) bigint Tamanho do exemplo criado usando a configuração da compactação solicitada e, se aplicável, o fator de preenchimento existente e nenhuma fragmentação.

Comentários

Use sp_estimate_data_compression_savings para estimar a economia que pode ocorrer quando você habilita uma tabela ou partição para compactação de linha, página, columnstore, columnstore ou compactação XML. Por exemplo, se o tamanho médio da linha puder ser reduzido em 40%, você poderá reduzir o tamanho do objeto em 40%. Um aumento de espaço poderá não ser obtido porque isso depende do fator de preenchimento e do tamanho da linha. Por exemplo, se você tiver uma linha de 8.000 bytes e reduzir seu tamanho em 40%, ainda poderá ajustar apenas uma linha em uma página de dados. Não há poupanças.

Se os resultados da execução sp_estimate_data_compression_savings em uma tabela ou índice não compactado indicarem que o tamanho aumentará, isso significa que muitas linhas usam quase toda a precisão dos tipos de dados, e a adição da pequena sobrecarga necessária para o formato compactado é mais do que a economia da compactação. Nesse caso raro, não habilite a compactação.

Se uma tabela já estiver habilitada para compactação, você poderá usar sp_estimate_data_compression_savings para estimar o tamanho médio da linha se a tabela estiver descompactada.

Um bloqueio de intenção compartilhada (IS) é adquirido na tabela durante essa operação. Se um bloqueio IS não puder ser obtido, o procedimento será bloqueado. A tabela é verificada sob o nível de isolamento confirmado de leitura padrão.

Se a configuração de compactação solicitada for igual à configuração de compactação atual, o procedimento armazenado retornará o tamanho estimado sem fragmentação de dados e usando o fator de preenchimento existente para índices no objeto de origem.

Se o ID do índice ou da partição não existir, nenhum resultado será retornado.

Permissões

Requer SELECT permissão na tabela e no banco de dados que contém a tabela VIEW DATABASE STATE e VIEW DEFINITION no tempdb.

Limitações

Antes do SQL Server 2019 (15.x), esse procedimento não se aplicava a índices columnstore e, portanto, não aceitava os parâmetros de compactação de dados COLUMNSTORE e COLUMNSTORE_ARCHIVE. A partir do SQL Server 2019 (15.x) e no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure, os índices columnstore podem ser usados como um objeto de origem para estimativa e como um tipo de compactação solicitado.

Quando os metadados do TempDB otimizados para memória estão habilitados, não há suporte para a criação de índices columnstore em tabelas temporárias. Devido a essa limitação, sp_estimate_data_compression_savings não há suporte com os parâmetros de compactação de dados COLUMNSTORE e COLUMNSTORE_ARCHIVE quando os metadados do TempDB otimizados para memória estão habilitados.

O SQL Server 2022 (16.x) Release Candidate (RC) 0 não estima economias para índices XML.

Considerações sobre índices columnstore

A partir do SQL Server 2019 (15.x) e no Banco de Dados SQL do Azure e na Instância Gerenciada SQL do Azure, sp_estimate_compression_savings dá suporte à estimativa da compactação de arquivo morto columnstore e columnstore. Ao contrário da compactação de página e linha, a aplicação da compactação columnstore a um objeto requer a criação de um novo índice columnstore. Por esse motivo, ao usar as opções COLUMNSTORE e COLUMNSTORE_ARCHIVE deste procedimento, o tipo do objeto de origem fornecido ao procedimento determina o tipo de índice columnstore usado para a estimativa de tamanho compactado. A tabela a seguir ilustra os objetos de referência usados para estimar a economia de compactação para cada tipo de objeto de origem quando o @data_compression parâmetro é definido como COLUMNSTORE ou COLUMNSTORE_ARCHIVE.

Objeto de origem Objeto de referência
Heap Índice columnstore clusterizado
Índice clusterizado Índice columnstore clusterizado
Índice não clusterizado Índice columnstore não clusterizado (incluindo as colunas chave e quaisquer colunas incluídas do índice não clusterizado fornecido e a coluna de partição da tabela, se houver)
índice columnstore não clusterizado Índice columnstore não clusterizado (incluindo as mesmas colunas que o índice columnstore não clusterizado fornecido)
Índice columnstore clusterizado Índice columnstore clusterizado

Observação

Ao estimar a compactação columnstore de um objeto de origem de armazenamento de linhas (índice clusterizado, índice não clusterizado ou heap), se houver colunas no objeto de origem que tenham um tipo de dados que não tenha suporte em um índice columnstore, sp_estimate_compression_savings falhará com um erro.

Da mesma forma, quando o parâmetro é definido como NONE, ou PAGE e o @data_compression objeto de origem é um índice columnstore, ROWa tabela a seguir descreve os objetos de referência usados.

Objeto de origem Objeto de referência
Índice columnstore clusterizado Heap
índice columnstore não clusterizado Índice não clusterizado (incluindo as colunas contidas no índice columnstore não clusterizado como colunas chave e a coluna de partição da tabela, se houver, como uma coluna incluída)

Observação

Ao estimar a compactação do armazenamento de linhas (NONE, ROW ou PAGE) de um objeto de origem columnstore, certifique-se de que o índice de origem não contenha mais de 32 colunas de chave, pois esse é o limite com suporte em um índice de armazenamento de linhas (não clusterizado).

Exemplos

R. Estimativa de economia com compactação ROW

O exemplo a seguir estima o Production.WorkOrderRouting tamanho da tabela se ela for compactada usando ROW compactação.

USE AdventureWorks2022;
GO
EXEC sys.sp_estimate_data_compression_savings
     'Production', 'WorkOrderRouting', NULL, NULL, 'ROW';
GO

B. Estimativa de economia com compactação PAGE e XML

Aplica-se a: SQL Server 2022 (16.x)

O exemplo a seguir estima o Production.ProductModel tamanho da tabela se ela for compactada usando PAGE compactação e o valor xml_compression estiver habilitado.

USE AdventureWorks2022;
GO
EXEC sys.sp_estimate_data_compression_savings
     'Production', 'ProductModel', NULL, NULL, 'PAGE', 1;
GO

Conteúdo relacionado