Czy ponowne indeksowanie aktualizuje statystyki?

W ubiegłym tygodniu robiłem kurs MS10775A i pojawiło się jedno pytanie, na które trener nie mógł odpowiedzieć w wiarygodny sposób:

Czy ponowne indeksowanie aktualizuje statystyki?

Znaleźliśmy dyskusje online, w których dowodzi, że tak jest, a nie tak.

Podczas aktualizowania statystyk możesz wziąć pod uwagę następujące kwestie (skopiowane z Przebudowy Indeksów vs. Aktualizacji Statystyki (Benjamin Nevarez)

1. Domyślnie UPDATE STATISTICSinstrukcja używa tylko próbki rekordów tabeli. Używanie UPDATE STATISTICS WITH FULLSCANspowoduje przeskanowanie całej tabeli.

2. Domyślnie UPDATE STATISTICSinstrukcja aktualizuje statystyki indeksu i kolumny. Użycie tej COLUMNSopcji spowoduje aktualizację tylko statystyk kolumn. Użycie tej INDEXopcji spowoduje aktualizację tylko statystyk indeksu.

3. Odbudowanie indeksu , na przykład za pomocą, ALTER INDEX … REBUILDspowoduje również zaktualizowanie statystyki indeksu za pomocą równoważnika użycia, WITH FULLSCAN chyba że tabela zostanie podzielona na partycje, w którym to przypadku statystyki są tylko próbkowane (dotyczy SQL Server 2012 i nowszych).

4. Statystyki utworzone ręcznie przy użyciu CREATE STATISTICSnie są aktualizowane przez żadną ALTER INDEX ... REBUILDoperację, w tym ALTER TABLE ... REBUILD. ALTER TABLE ... REBUILDaktualizuje statystyki dla indeksu klastrowanego, jeśli jest on zdefiniowany w odbudowywanej tabeli.

5. Reorganizacja indeksu , na przykład za pomocą ALTER INDEX … REORGANIZE, nie aktualizuje żadnych statystyk.

Krótka odpowiedź brzmi: musisz użyć, UPDATE STATISTICSaby zaktualizować statystyki kolumn i że przebudowa indeksu zaktualizuje tylko statystyki indeksu. Możesz wymusić aktualizację wszystkich statystyk w tabeli, w tym statystyk indeksów i statystyk utworzonych ręcznie, przy użyciu UPDATE STATISTICS (tablename) WITH FULLSCAN;składni.

Poniższy kod ilustruje powyższe reguły:

Najpierw utworzymy tabelę z kilkoma kolumnami i indeksem klastrowym:

USE tempdb;

IF OBJECT_ID(N'dbo.SomeTable', N'U') IS NOT NULL
DROP TABLE dbo.SomeTable;

CREATE TABLE dbo.SomeTable
(
    rn int NOT NULL IDENTITY(1,1)
        CONSTRAINT pk
        PRIMARY KEY NONCLUSTERED
    , i int NOT NULL INDEX i 
    , d sysname NOT NULL
) ON [PRIMARY] WITH (DATA_COMPRESSION = NONE);

CREATE UNIQUE CLUSTERED INDEX cx ON dbo.SomeTable (i, d);

CREATE STATISTICS d ON dbo.SomeTable (d) WITH FULLSCAN;

INSERT INTO dbo.SomeTable (d, i)
SELECT c1.name, c1.id
FROM sys.syscolumns c1;

To zapytanie pokazuje datę ostatniej aktualizacji każdego obiektu statystyki:

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

Wyniki pokazują, że nie nastąpiły jeszcze żadne aktualizacje, co jest poprawne, ponieważ właśnie utworzyliśmy tabelę:

╔═══════════════╦═══════════╦═══════════╗
║ ObjectName ║ StatsName ║ StatsDate ║
╠═══════════════╬═══════════╬═══════════╣
║ dbo.SomeTable ║ cx ║ NULL ║
║ dbo.SomeTable ║ i ║ NULL ║
║ dbo.SomeTable ║ pk ║ NULL ║
║ dbo.SomeTable ║ d ║ NULL ║
╚═══════════════╩═══════════╩═══════════╝

Przebudujmy całą tabelę i zobaczmy, czy to aktualizuje statystyki:

ALTER TABLE dbo.SomeTable REBUILD;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

╔═══════════════╦═══════════╦═════════════════════ ════╗
║ ObjectName ║ StatsName ║ StatsDate ║
╠═══════════════╬═══════════╬═════════════════════ ════╣
║ dbo.SomeTable ║ cx ║ 2018-09-17 14: 09: 13.590 ║
║ dbo.SomeTable ║ i ║ NULL ║
║ dbo.SomeTable ║ pk ║ NULL ║
║ dbo.SomeTable ║ d ║ NULL ║
╚═══════════════╩═══════════╩═════════════════════ ════╝

Wyniki pokazują, że tylko statystyki indeksu klastrowego zostały zaktualizowane.

Następnie wykonujemy dyskretną UPDATE STATSoperację:

UPDATE STATISTICS dbo.SomeTable(d) WITH FULLSCAN;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

Jak widać, właśnie zaktualizowaliśmy statystyki w dkolumnie:

╔═══════════════╦═══════════╦═════════════════════ ════╗
║ ObjectName ║ StatsName ║ StatsDate ║
╠═══════════════╬═══════════╬═════════════════════ ════╣
║ dbo.SomeTable ║ cx ║ 2018-09-17 14: 09: 13.590 ║
║ dbo.SomeTable ║ i ║ NULL ║
║ dbo.SomeTable ║ pk ║ NULL ║
║ dbo.SomeTable ║ d ║ 2018-09-17 14: 09: 13.597 ║
╚═══════════════╩═══════════╩═════════════════════ ════╝

Teraz zaktualizujemy statystyki dla całej tabeli:

UPDATE STATISTICS dbo.SomeTable WITH FULLSCAN;

SELECT ObjectName = sc.name + N'.' + o.name
    , StatsName = s.name
    , StatsDate = STATS_DATE(s.object_id, s.stats_id)
FROM sys.stats s
    INNER JOIN sys.objects o ON s.object_id = o.object_id
    INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE sc.name = N'dbo'
    AND o.name = N'SomeTable';

╔═══════════════╦═══════════╦═════════════════════ ════╗
║ ObjectName ║ StatsName ║ StatsDate ║
╠═══════════════╬═══════════╬═════════════════════ ════╣
║ dbo.SomeTable ║ cx ║ 2018-09-17 14: 09: 13.600 ║
║ dbo.SomeTable ║ i ║ 2018-09-17 14: 09: 13.600 ║
║ dbo.SomeTable ║ pk ║ 2018-09-17 14: 09: 13.603 ║
║ dbo.SomeTable ║ d ║ 2018-09-17 14: 09: 13.607 ║
╚═══════════════╩═══════════╩═════════════════════ ════╝

Jak widać, jedynym sposobem na upewnienie się, że wszystkie statystyki są aktualizowane, jest albo aktualizacja każdej z nich ręcznie, albo aktualizacja całej tabeli UPDATE STATISTICS (table);.

Na stronie Microsoft SQL Server Docs dla statystyki stanów :

Operacje takie jak odbudowywanie, defragmentacja lub reorganizacja indeksu nie zmieniają dystrybucji danych. Dlatego nie trzeba aktualizować statystyk po wykonaniu operacji ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG lub ALTER INDEX REORGANIZE . Optymalizator zapytań aktualizuje statystyki podczas odbudowywania indeksu w tabeli lub widoku za pomocą ALTER INDEX REBUILD lub DBCC DBREINDEX, jednak ta aktualizacja statystyk jest produktem ubocznym ponownego tworzenia indeksu. Optymalizator zapytań nie aktualizuje statystyk po operacjach DBCC INDEXDEFRAG lub ALTER INDEX REORGANIZE.