sp_estimate_data_compression_savings (Transact-SQL)

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 columnstore, di riga, di pagina, 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.

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

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

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.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

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

Argomenti

[ @schema_name = ] N'schema_name'

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

[ @object_name = ] N'object_name'

Nome della tabella o della vista indicizzata su cui si trova l'indice. @object_name è sysname, senza impostazione predefinita.

[ @index_id = ] index_id

ID dell'indice. @index_id è int e può essere uno dei valori seguenti:

• numero ID di un indice
• NULL
• 0 se object_id è un heap

Per restituire informazioni per tutti gli indici per una tabella o vista di base, specificare NULL. Se si specifica NULL, è necessario specificare NULL anche per @partition_number.

[ @partition_number = ] partition_number

Numero di partizione nell'oggetto . @partition_number è int e può essere uno dei valori seguenti:

• numero di partizione di un indice o di un heap
• NULL
• 1 per un indice o un heap non partizionato

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

[ @data_compression = ] N'data_compression'

Specifica il tipo di compressione da valutare. @data_compression è nvarchar(60)e può essere uno dei valori seguenti:

• NONE
• ROW
• PAGE
• COLUMNSTORE
• 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 uno dei valori seguenti:

• NULL (predefinito)
• 0
• 1

@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.

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 viene 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 restituisce le dimensioni stimate senza frammentazione dei dati, 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

In SQL Server 2017 (14.x) e versioni precedenti questa procedura non è applicabile agli indici columnstore e pertanto non accetta i COLUMNSTORE parametri di compressione dei dati e COLUMNSTORE_ARCHIVE. In SQL Server 2019 (15.x) e versioni successive 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 i parametri di compressione dei dati e COLUMNSTORE_ARCHIVE quando i metadati tempDB ottimizzati per la COLUMNSTORE memoria sono abilitati.

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 usano le COLUMNSTORE opzioni 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 parametro @data_compression è impostato su COLUMNSTORE o COLUMNSTORE_ARCHIVE.

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

Esempi

Gli esempi di codice Transact-SQL in questo articolo utilizzano il database campione AdventureWorks2022 o AdventureWorksDW2022, che è possibile scaricare dalla home page di Esempi di Microsoft SQL Server e progetti collettivi.

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.

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) e versioni successive

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

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

Contenuto correlato

• CREATE TABLE (Transact-SQL)
• CREATE INDEX (Transact-SQL)
• sys.partitions (Transact-SQL)
• stored procedure motore di database (Transact-SQL)
• Implementazione della compressione Unicode