sp_estimate_data_compression_savings (Transact-SQL)

  • Articolo

Si applica a:SQL Server database SQL di Azure Istanza gestita di SQL di Azure

Restituisce le dimensioni correnti degli oggetti richiesti e stima le dimensioni dell'oggetto per lo stato di compressione richiesto. La compressione può essere valutata per intere tabelle o parti di esse, Sono inclusi heap, indici cluster, indici non cluster, indici columnstore, viste indicizzate e partizioni di tabella e indice. Gli oggetti possono essere compressi usando la compressione di archiviazione row, page, columnstore o columnstore. Se la tabella, l'indice o la partizione è già compressa, è possibile utilizzare questa procedura per stimare le dimensioni della tabella, dell'indice o della partizione se viene ricompressa o archiviata senza compressione.

A partire da SQL Server 2022 (16.x), è possibile comprimere i dati XML fuori riga nelle colonne usando il xml tipo di dati, riducendo i requisiti di archiviazione e memoria. Per altre informazioni, vedere CREATE TABLE (Transact-SQL) e CREATE INDEX (Transact-SQL). sp_estimate_data_compression_savings supporta le stime della compressione XML.

Nota

Compressione e sp_estimate_data_compression_savings non sono disponibili in ogni edizione di SQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL Server, vedere Edizioni e funzionalità supportate di SQL Server 2022.

La sys.sp_estimate_data_compression_savings stored procedure di sistema è disponibile in database SQL di Azure e Istanza gestita di SQL di Azure.

Per stimare le dimensioni dell'oggetto se fosse necessario utilizzare l'impostazione di compressione richiesta, questa stored procedure esegue l'esempio dell'oggetto di origine e carica questi dati in una tabella e un indice equivalenti creati in tempdb. La tabella o l'indice creato in tempdb viene quindi compresso nell'impostazione richiesta e viene calcolato il risparmio di compressione stimato.

Per modificare lo stato di compressione di una tabella, di un indice o di una partizione, utilizzare le istruzioni ALTER TABLE o ALTER INDEX . Per informazioni generali sulla compressione, vedere Compressione dei dati.

Nota

Se i dati esistenti sono frammentati, potrebbe essere possibile ridurne le dimensioni senza utilizzare la compressione ricompilando l'indice. Per gli indici, il fattore di riempimento viene applicato durante la ricompilazione. Questo potrebbe comportare un aumento delle dimensioni dell'indice.

Convenzioni di sintassi Transact-SQL

Sintassi

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
[ ; ]

Argomenti

[ @schema_name = ] 'schema_name'

Nome dello schema del database che contiene la tabella o la vista indicizzata. schema_name è sysname. Se schema_name è NULL, viene usato lo schema predefinito dell'utente corrente.

[ @object_name = ] 'object_name'

Nome della tabella o della vista indicizzata su cui si trova l'indice. object_name è sysname.

[ @index_id = ] index_id

ID dell'indice. index_id è int e può essere uno dei valori seguenti: il numero ID di un indice, NULL o 0 se object_id è un heap. Per restituire informazioni per tutti gli indici per una tabella di base o una vista, specificare NULL. Se si specifica NULL, è necessario specificare anche NULL per partition_number.

[ @partition_number = ] partition_number

Numero di partizione nell'oggetto . partition_number è int e può essere uno dei valori seguenti: il numero di partizione di un indice o di un heap, NULL o 1 per un indice o un heap non partizionato.

Per specificare la partizione, è anche possibile specificare la funzione $PARTITION . Per restituire le informazioni per tutte le partizioni dell'oggetto, specificare NULL.

[ @data_compression = ] 'data_compression'

Tipo di compressione da valutare. data_compression può essere uno dei valori seguenti: NONE, ROW, PAGE, COLUMNSTORE o COLUMNSTORE_ARCHIVE.

Per SQL Server 2022 (16.x) e versioni successive, null è anche un valore possibile. data_compression non può essere NULL se xml_compression è NULL.

[ @xml_compression = ] xml_compression

Si applica a: SQL Server 2022 (16.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure.

Specifica se calcolare i risparmi per la compressione XML. xml_compression è bit e può essere NULL, 0 o 1. Il valore predefinito è NULL.

xml_compression non può essere NULL se data_compression è NULL.

Valori del codice restituito

0 (esito positivo) o 1 (errore).

Set di risultati

Per offrire informazioni sulle dimensioni correnti e stimate della tabella, dell'indice o della partizione, viene restituito il set di risultati seguente.

Nome colonna Tipo di dati Descrizione
object_name sysname Nome della tabella o della vista indicizzata.
schema_name sysname Schema della tabella o della vista indicizzata.
index_id int ID di un indice:

0 = heap

1 = indice cluster

> 1 = Indice non cluster
partition_number int Numero di partizioni. Per una tabella o un indice non partizionato viene restituito 1.
size_with_current_compression_setting (KB) bigint Dimensioni attuali della tabella, della partizione o dell'indice richiesto.
size_with_requested_compression_setting (KB) bigint Dimensioni stimate della tabella, dell'indice o della partizione che utilizza l'impostazione di compressione richiesta; e, se applicabile, il fattore di riempimento esistente e presupponendo che non vi sia alcuna frammentazione.
sample_size_with_current_compression_setting (KB) bigint Dimensioni del campione con l'impostazione di compressione corrente. È inclusa qualsiasi frammentazione.
sample_size_with_requested_compression_setting (KB) bigint Dimensioni del campione creato utilizzando l'impostazione di compressione richiesta e, se applicabile, il fattore di riempimento esistente e senza frammentazione.

Osservazioni:

Usare sp_estimate_data_compression_savings per stimare i risparmi che possono verificarsi quando si abilita una tabella o una partizione per riga, pagina, columnstore, archivio columnstore o compressione XML. Ad esempio, se la dimensione media della riga può essere ridotta del 40%, è possibile ridurre potenzialmente le dimensioni dell'oggetto del 40%. Si potrebbe non ottenere un risparmio in termini di spazio a seconda del fattore di riempimento e delle dimensioni della riga. Ad esempio, se si dispone di una riga lunga 8.000 byte e si riducono le dimensioni del 40%, è comunque possibile inserire una sola riga in una pagina di dati. Non ci sono risparmi.

Se i risultati dell'esecuzione sp_estimate_data_compression_savings in una tabella o in un indice non compresso indicano che le dimensioni aumentano, ciò significa che molte righe utilizzano quasi tutta la precisione dei tipi di dati e l'aggiunta del piccolo sovraccarico necessario per il formato compresso è maggiore del risparmio derivante dalla compressione. In questo caso raro, non abilitare la compressione.

Se una tabella è già abilitata per la compressione, è possibile usare sp_estimate_data_compression_savings per stimare le dimensioni medie della riga se la tabella non è compressa.

Durante questa operazione viene acquisito un blocco condiviso con finalità (IS) nella tabella. Se non è possibile ottenere un blocco IS, la procedura verrà bloccata. La tabella viene analizzata con il livello di isolamento read committed predefinito.

Se l'impostazione di compressione richiesta è uguale all'impostazione di compressione corrente, la stored procedure restituirà le dimensioni stimate senza frammentazione dei dati e utilizzando il fattore di riempimento esistente per gli indici nell'oggetto di origine.

Se l'indice o l'ID di partizione non esiste, non vengono restituiti risultati.

Autorizzazioni

È richiesta SELECT l'autorizzazione per la tabella VIEW DATABASE STATE e VIEW DEFINITION sul database contenente la tabella e su tempdb.

Limiti

Prima di SQL Server 2019 (15.x), questa procedura non si applicava agli indici columnstore e pertanto non accettava i parametri di compressione dei dati COLUMNSTORE e COLUMNSTORE_ARCHIVE. A partire da SQL Server 2019 (15.x) e in database SQL di Azure e Istanza gestita di SQL di Azure, gli indici columnstore possono essere usati sia come oggetto di origine per la stima che come tipo di compressione richiesto.

Quando i metadati tempDB ottimizzati per la memoria sono abilitati, la creazione di indici columnstore nelle tabelle temporanee non è supportata. A causa di questa limitazione, sp_estimate_data_compression_savings non è supportato con columnstore e COLUMNSTORE_ARCHIVE parametri di compressione dei dati quando i metadati tempDB ottimizzati per la memoria sono abilitati.

SQL Server 2022 (16.x) Release Candidate (RC) 0 non stima i risparmi per gli indici XML.

Considerazioni sugli indici columnstore

A partire da SQL Server 2019 (15.x) e in database SQL di Azure e Istanza gestita di SQL di Azure, sp_estimate_compression_savings supporta la stima della compressione dell'archivio columnstore e columnstore. A differenza della compressione di pagine e righe, l'applicazione della compressione columnstore a un oggetto richiede la creazione di un nuovo indice columnstore. Per questo motivo, quando si utilizzano le opzioni COLUMNSTORE e COLUMNSTORE_ARCHIVE di questa procedura, il tipo dell'oggetto di origine fornito alla routine determina il tipo di indice columnstore utilizzato per la stima delle dimensioni compresse. Nella tabella seguente vengono illustrati gli oggetti di riferimento utilizzati per stimare il risparmio di compressione per ogni tipo di oggetto di origine quando il @data_compression parametro è impostato su COLUMNSTORE o COLUMNSTORE_ARCHIVE.

Oggetto Source Oggetto di riferimento
Heap Indice columnstore cluster
Indice cluster Indice columnstore cluster
Indice non cluster Indice columnstore non cluster (incluse le colonne chiave e le colonne incluse dell'indice non cluster specificato e la colonna di partizione della tabella, se presente)
indice columnstore non cluster Indice columnstore non cluster (incluse le stesse colonne dell'indice columnstore non cluster specificato)
Indice columnstore cluster Indice columnstore cluster

Nota

Quando si stima la compressione columnstore da un oggetto di origine rowstore (indice cluster, indice non cluster o heap), se sono presenti colonne nell'oggetto di origine con un tipo di dati non supportato in un indice columnstore, sp_estimate_compression_savings verrà generato un errore.

Analogamente, quando il @data_compression parametro è impostato su NONE, ROWo PAGE e l'oggetto di origine è un indice columnstore, la tabella seguente descrive gli oggetti di riferimento utilizzati.

Oggetto Source Oggetto di riferimento
Indice columnstore cluster Heap
indice columnstore non cluster Indice non cluster (incluse le colonne contenute nell'indice columnstore non cluster come colonne chiave e la colonna di partizione della tabella, se presente, come colonna inclusa)

Nota

Quando si stima la compressione rowstore (NONE, ROW o PAGE) da un oggetto di origine columnstore, assicurarsi che l'indice di origine non contenga più di 32 colonne chiave perché si tratta del limite supportato in un indice rowstore (non cluster).

Esempi

R. Stimare i risparmi con la compressione ROW

Nell'esempio seguente viene stimata la dimensione della tabella se viene compressa usando ROW la Production.WorkOrderRouting compressione.

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

B. Stimare i risparmi con la compressione PAGE e XML

Si applica a: SQL Server 2022 (16.x)

Nell'esempio seguente viene stimata la dimensione della Production.ProductModel tabella se viene compressa usando PAGE la compressione e il valore xml_compression è abilitato.

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

Passaggi successivi