sp_estimate_data_compression_savings (Transact-SQL)
Aplica-se a: 
SQL ServerBanco 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
NULL0se 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
NULL1para 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:
NONEROWPAGECOLUMNSTORECOLUMNSTORE_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)01
@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 = Heap1 = Í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
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de