Czy indeksy skompresowane programu SQL Server pozostają kompresowane podczas odbudowywania bez określania kompresji danych?

Po odbudowaniu indeksów programu SQL Server za pomocą kompresji strony ( ALTER INDEX IX1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)), czy kolejne przebudowy (wykonywane przez niektóre skrypty konserwacji powyżej pewnego progu fragmentacji) muszą ponownie określać kompresję danych? Czy w przeciwnym razie indeksy byłyby skutecznie zdekompresowane?

Indeksy pozostają skompresowane podczas ich przebudowywania / reorganizacji.

Utwórz tabelę i skompresowany indeks

 CREATE TABLE DBO.TEST_INDX(id int, bla varchar(255));
 CREATE INDEX IX1 ON dbo.TEST_INDX(id)  WITH (DATA_COMPRESSION = PAGE);

Sprawdź kompresję

 SELECT i.name, p.data_compression_desc 
 FROM sys.partitions P
 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
 WHERE P.data_compression > 0 and I.name = 'IX1';

Wynik

name    data_compression_desc
IX1     PAGE

Odbuduj indeks

ALTER INDEX IX1 on  DBO.TEST_INDX rebuild 

Sprawdź kompresję

 SELECT i.name, p.data_compression_desc 
 FROM sys.partitions P
 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
 WHERE P.data_compression > 0 and I.name = 'IX1'

Wynik

name    data_compression_desc
IX1     PAGE

Wyłączenie ich, a następnie przebudowanie ma inny skutek, ponieważ wyłączenie powoduje usunięcie indeksu przy jednoczesnym zachowaniu jego definicji.

alter index IX1 on  DBO.TEST_INDX DISABLE ;
alter index IX1 on  DBO.TEST_INDX REBUILD ;

Wynik

name    data_compression_desc

Kompresja została utracona, definicja kompresji również zostałaby utracona podczas upuszczania i tworzenia indeksu przez SSMS bez dostosowywania skryptu tworzenia indeksu.

Dlaczego?

Ponieważ opcja data_compression nie jest zachowywana podczas wykonywania skryptów instrukcji tworzenia indeksu.

jednak jeśli wyłączymy indeks, przebuduj z kompresją, a następnie przebuduj ponownie:

alter index IX1 on  DBO.TEST_INDX DISABLE ;
alter index IX1 on  DBO.TEST_INDX REBUILD  WITH (DATA_COMPRESSION = PAGE);
alter index IX1 on  DBO.TEST_INDX REBUILD;

Wynik

name    data_compression_desc
IX1 PAGE

Testowanie odbudowy za pomocą rozwiązania konserwacyjnego Oli Hallengren

Parametry są modyfikowane do celów testowych.

Dodaj dane, aby dostać się do jednej strony, ponieważ są one potrzebne dla parametru MinNumberOfPages.

INSERT INTO dbo.TEST_INDX(id,bla)
VALUES(5,'test');
go 10 

Wykonaj proces optymalizacji indeksu, aby wydrukować instrukcję.

EXECUTE dbo.IndexOptimize
@Databases = 'TestDB',
@FragmentationLow = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Indexes = 'TestDB.DBO.TEST_INDX',
@Execute = 'N',
@MinNumberOfPages = 1;

Wynik:

Command: ALTER INDEX [IX1] ON [TestDB].[dbo].[TEST_INDX] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF)

Comment: ObjectType: Table, IndexType: NonClustered, ImageTex
t: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1, Fragmentation: 0
Outcome: Not Executed
Duration: 00:00:00
Date and time: 2019-01-09 14:48:12

Wykonanie wygenerowanego polecenia

ALTER INDEX [IX1] ON [TestDB].[dbo].[TEST_INDX] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF)

Kompresja zostaje zachowana

name    data_compression_desc
IX1 PAGE

Testowanie przebudowy z planem konserwacji (zdecydowanie opowiadałbym się za rozwiązaniem ola)

Odbuduj indeksy

Wybierz tabelę testową

Dodaj kilka poziomów fragmentacji testowej.

Wstaw kilka wartości, aby rozpocząć fragmentację

INSERT INTO dbo.TEST_INDX(id)
SELECT id from TEST_INDX
go 4

Sprawdź procent fragmentacji

SELECT 
I.[name] AS  INDX ,
IPS.avg_fragmentation_in_percent,
IPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), object_id('[dbo].[TEST_INDX]'), NULL, NULL, NULL) AS IPS
INNER JOIN sys.indexes AS I ON I.[object_id] = IPS.[object_id]
AND IPS.index_id = I.index_id
WHERE IPS.database_id = DB_ID()
and I.name = 'IX1'

Wynik

INDX    avg_fragmentation_in_percent    page_count
IX1 66,6666666666667    3

Uruchom plan

Interesujące jest to, że patrząc na raport planu, DATA_COMPRESSION = PAGEopcja jest dodawana do wygenerowanego REBUILDpolecenia!

Command:USE [TestDB]
GO
ALTER INDEX [IX1] ON [dbo].[TEST_INDX] REBUILD PARTITION = ALL WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, RESUMABLE = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80, DATA_COMPRESSION = PAGE)

Podział:

INDX    avg_fragmentation_in_percent    page_count
IX1 0   2

Kompresja:

name    data_compression_desc
IX1 PAGE