sp_estimate_data_compression_savings (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instâ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 row, page, columnstore ou columnstore. Se a tabela, o índice ou a partição já estiver compactado, você poderá usar este procedimento para estimar o tamanho da tabela, do índice ou da partição se ele for recompactado ou armazenado sem compactação.

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 de SQL do Azure.

A partir do SQL Server 2022 (16.x), você pode compactar dados XML fora da linha em colunas usando o tipo de dados xml, reduzindo os requisitos de armazenamento e memória. Para obter mais informações, consulte, CREATE TABLE e CREATE INDEX. sp_estimate_data_compression_savings suporta 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.

Para estimar o tamanho do objeto se ele fosse usar 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 um índice equivalentes criados no tempdb. A tabela ou o índice criado em tempdb é 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 = ] N'schema_name'
    , [ @object_name = ] N'object_name'
    , [ @index_id = ] index_id
    , [ @partition_number = ] partition_number
    , [ @data_compression = ] N'data_compression'
    [ , [ @xml_compression = ] xml_compression ]
[ ; ]

Argumentos

[ @schema_name = ] N'schema_name'

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

[ @object_name = ] N'object_name'

O nome da tabela ou exibição indexada em que o índice está. @object_name é sysname, sem padrão.

@index_id [ = ] index_id

A ID do índice. @index_id é int e pode ser um dos seguintes valores:

  • o número de identificação de um índice
  • NULL
  • 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 da partição de um índice ou heap
  • NULL
  • 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 para todas as partições do objeto proprietário, especifique NULL.

@data_compression [ = ] N'data_compression'

Especifica o tipo de compactação a ser avaliada. @data_compression é nvarchar(60) e pode ser um dos seguintes valores:

  • NONE
  • ROW
  • PAGE
  • COLUMNSTORE
  • COLUMNSTORE_ARCHIVE

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

@xml_compression [ = ] xml_compression

Aplica-se 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 para compactação XML deve ser calculada. @xml_compression é bit e pode ser um dos seguintes valores:

  • NULL (padrão)
  • 0
  • 1

@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. Esse tamanho 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 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 com 8.000 bytes de comprimento e reduzir seu tamanho em 40%, ainda poderá ajustar apenas uma linha em uma página de dados. Não há economia.

Se os resultados da execução sp_estimate_data_compression_savings em uma tabela ou índice descompactado 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 é maior 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 compartilhado intencional (IS) é adquirido na tabela durante essa operação. Se um bloqueio IS não puder ser obtido, o procedimento será bloqueado. A tabela é verificada no nível de isolamento padrão de leitura confirmada.

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, usando o fator de preenchimento existente para índices no objeto de origem.

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

Permissões

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

Limitações

No SQL Server 2017 (14.x) e versões anteriores, esse procedimento não se aplicava a índices columnstore e, portanto, não aceitava os parâmetros COLUMNSTORE de compactação de dados e COLUMNSTORE_ARCHIVE. No SQL Server 2019 (15.x) e versões posteriores, e no Banco de Dados SQL do Azure e na Instância Gerenciada de 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 TempDB com Otimização de 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 e COLUMNSTORE_ARCHIVE quando os COLUMNSTORE metadados TempDB com otimização de memória estão habilitados.

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 de SQL do Azure, sp_estimate_compression_savings dá suporte à estimativa da compactação de arquivo 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 COLUMNSTORE opções e COLUMNSTORE_ARCHIVE desse procedimento, o tipo do objeto de origem fornecido para o 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 parâmetro @data_compression é definido como ou COLUMNSTORE 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 de chave e todas as 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 rowstore (índice clusterizado, índice não clusterizado ou heap), se houver colunas no objeto de origem que tenham um tipo de dados que não tem suporte em um índice columnstore, sp_estimate_compression_savings falhará com um erro.

Da mesma forma, quando o parâmetro @data_compression é definido como NONE, ROWou PAGE ou e o objeto de origem é um índice columnstore, a 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 de chave e a coluna de partição da tabela, se houver, como uma coluna incluída)

Observação

Ao estimar a compactação rowstore (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 rowstore (não clusterizado).

Exemplos

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

R. Estimar a economia com a compactação ROW

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

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

B. Estime a economia com compactação PAGE e XML

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores

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

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

Conteúdo relacionado