Niska ocena liczebności dyskwalifikuje WSTAW z minimalnego rejestrowania?

Dlaczego drugie INSERTstwierdzenie jest ~ 5 razy wolniejsze niż pierwsze?

Biorąc pod uwagę ilość wygenerowanych danych dziennika, uważam, że drugi nie kwalifikuje się do minimalnego rejestrowania. Jednak dokumentacja w Przewodniku wydajności ładowania danych wskazuje, że oba wkładki powinny mieć możliwość minimalnego logowania. Jeśli więc minimalne rejestrowanie jest kluczową różnicą w wydajności, dlaczego drugie zapytanie nie kwalifikuje się do minimalnego rejestrowania? Co można zrobić, aby poprawić sytuację?

Zapytanie nr 1: Wstawianie wierszy 5 mm za pomocą polecenia WSTAW ... Z (TABLOCK)

Rozważ następujące zapytanie, które wstawia wiersze 5 mm do sterty. To zapytanie wykonuje 1 secondi generuje 64MBdane dziennika transakcji zgłoszone przez sys.dm_tran_database_transactions.

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
FROM dbo.fiveMillionNumbers
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

Zapytanie nr 2: Wstawienie tych samych danych, ale SQL nie docenia liczby wierszy

Rozważmy teraz bardzo podobne zapytanie, które działa na dokładnie tych samych danych, ale zdarza się, że rysuje z tabeli (lub złożonej SELECTinstrukcji z wieloma złączeniami w moim rzeczywistym przypadku produkcyjnym), gdzie oszacowanie liczności jest zbyt niskie. To zapytanie wykonuje się 5.5 secondsi generuje 461MBdane dziennika transakcji.

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that produces 5MM rows but SQL estimates just 1000 rows
FROM dbo.fiveMillionNumbersBadEstimate
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

Pełny skrypt

Zobacz tę Pastebin, aby uzyskać pełny zestaw skryptów do generowania danych testowych i wykonywania jednego z tych scenariuszy. Pamiętaj, że musisz użyć bazy danych, która jest w SIMPLE modelu odzyskiwania .

Kontekst biznesowy

Częściowo poruszamy się wokół milionów wierszy danych i ważne jest, aby te operacje były jak najbardziej wydajne, zarówno pod względem czasu wykonania, jak i obciążenia dysku we / wy. Początkowo mieliśmy wrażenie, że utworzenie tabeli stosu i użycie INSERT...WITH (TABLOCK)było dobrym sposobem na zrobienie tego, ale teraz jesteśmy mniej pewni, biorąc pod uwagę, że zaobserwowaliśmy sytuację pokazaną powyżej w rzeczywistym scenariuszu produkcyjnym (choć przy bardziej złożonych zapytaniach, a nie wersja uproszczona tutaj).

Dlaczego drugie zapytanie nie kwalifikuje się do minimalnego rejestrowania?

W przypadku drugiego zapytania dostępne jest minimalne rejestrowanie , ale silnik decyduje się nie używać go w czasie wykonywania.

Jest to próg minimalny dla INSERT...SELECTponiżej której nie zdecyduje się skorzystać z optymalizacji obciążenia luzem. Konfigurowanie operacji masowego zestawu wierszy wiąże się z kosztami, a wstawianie tylko kilku rzędów nie zapewni efektywnego wykorzystania przestrzeni.

Co można zrobić, aby poprawić sytuację?

Użyj jednej z wielu innych metod (np. SELECT INTO), Które nie mają tego progu. Alternatywnie możesz być w stanie przepisać zapytanie źródłowe w jakiś sposób, aby zwiększyć szacunkową liczbę wierszy / stron powyżej progu INSERT...SELECT.

Zobacz także samo-odpowiedź Geoffa, aby uzyskać więcej przydatnych informacji.

Być może interesujące ciekawostki: SET STATISTICS IO raportuje logiczne odczyty dla tabeli docelowej tylko wtedy, gdy nie są używane optymalizacje ładowania zbiorczego .

Udało mi się odtworzyć problem na własnym urządzeniu testowym:

USE test;

CREATE TABLE dbo.SourceGood
(
    SourceGoodID INT NOT NULL
        CONSTRAINT PK_SourceGood
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData VARCHAR(384) NOT NULL
);

CREATE TABLE dbo.SourceBad
(
    SourceBadID INT NOT NULL
        CONSTRAINT PK_SourceBad
        PRIMARY KEY CLUSTERED
        IDENTITY(-2147483647,1)
    , SomeData VARCHAR(384) NOT NULL
);

CREATE TABLE dbo.InsertTest
(
    SourceBadID INT NOT NULL
        CONSTRAINT PK_InsertTest
        PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(384) NOT NULL
);
GO

INSERT INTO dbo.SourceGood WITH (TABLOCK) (SomeData) 
SELECT TOP(5000000) o.name + o1.name + o2.name
FROM syscolumns o
    , syscolumns o1
    , syscolumns o2;
GO

ALTER DATABASE test SET AUTO_UPDATE_STATISTICS OFF;
GO

INSERT INTO dbo.SourceBad WITH (TABLOCK) (SomeData)
SELECT TOP(5000000) o.name + o1.name + o2.name
FROM syscolumns o
    , syscolumns o1
    , syscolumns o2;
GO

ALTER DATABASE test SET AUTO_UPDATE_STATISTICS ON;
GO

BEGIN TRANSACTION;

INSERT INTO dbo.InsertTest WITH (TABLOCK)
SELECT *
FROM dbo.SourceGood;

SELECT * FROM sys.dm_tran_database_transactions;

/*
database_transaction_log_record_count
472 
database_transaction_log_bytes_used
692136
*/

COMMIT TRANSACTION;


BEGIN TRANSACTION;

INSERT INTO dbo.InsertTest WITH (TABLOCK)
SELECT *
FROM dbo.SourceBad;

SELECT * FROM sys.dm_tran_database_transactions;

/*
database_transaction_log_record_count   
5000003 
database_transaction_log_bytes_used
642699256
*/

COMMIT TRANSACTION;

To nasuwa pytanie, dlaczego nie „naprawić” problemu, aktualizując statystyki w tabelach źródłowych przed uruchomieniem minimalnie zalogowanej operacji?

TRUNCATE TABLE dbo.InsertTest;
UPDATE STATISTICS dbo.SourceBad;

BEGIN TRANSACTION;

INSERT INTO dbo.InsertTest WITH (TABLOCK)
SELECT *
FROM dbo.SourceBad;

SELECT * FROM sys.dm_tran_database_transactions;

/*
database_transaction_log_record_count
472
database_transaction_log_bytes_used
692136
*/

COMMIT TRANSACTION;

Przepisz zapytanie źródłowe w jakiś sposób, aby zwiększyć szacunkową liczbę wierszy

Rozwijając pomysł Paula, obejściem, jeśli naprawdę jesteś zdesperowany, jest dodanie fałszywej tabeli, która gwarantuje, że szacunkowa liczba wierszy dla wkładki będzie wystarczająco wysoka, aby zapewnić jakość do optymalizacji załadunku luzem. Potwierdziłem, że uzyskuje to minimalne rejestrowanie i poprawia wydajność zapytań.

-- Create a dummy table that SQL Server thinks has a million rows
CREATE TABLE dbo.emptyTableWithMillionRowEstimate (
    n INT PRIMARY KEY
)
GO
UPDATE STATISTICS dbo.emptyTableWithMillionRowEstimate
WITH ROWCOUNT = 1000000
GO

-- Concatenate this table into the final rowset:
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
FROM dbo.fiveMillionNumbersBadEstimate
-- Add in dummy rowset to ensure row estimate is high enough for bulk load optimization
UNION ALL
SELECT NULL FROM dbo.emptyTableWithMillionRowEstimate
OPTION (MAXDOP 1)

Ostateczne dania na wynos

1. Użyj SELECT...INTOdo jednorazowych operacji wstawiania, jeśli wymagane jest minimalne logowanie. Jak podkreśla Paul, zapewni to minimalne rejestrowanie, niezależnie od oszacowania wiersza
2. Tam, gdzie to możliwe, pisz zapytania w prosty sposób, który optymalizator zapytań może skutecznie uzasadnić. Możliwe może być podzielenie zapytania na wiele części, na przykład w celu umożliwienia tworzenia statystyk na tabeli pośredniej.
3. Jeśli masz dostęp do SQL Server 2014, wypróbuj go w swoim zapytaniu; w moim rzeczywistym przypadku produkcyjnym właśnie go wypróbowałem, a nowy Kardynał Estymator dał znacznie wyższą (i lepszą) ocenę; zapytanie było wówczas minimalnie rejestrowane. Ale może to nie być pomocne, jeśli potrzebujesz obsługi SQL 2012 i wcześniejszych wersji.
4. Jeśli jesteś zdesperowany, zastosowanie mogą mieć takie hackerskie rozwiązania!

Powiązany artykuł

Wpis na blogu Paula White'a z maja 2019 r. Minimalna rejestracja za pomocą INSERT… WYBIERZ w tabelach ze stertami omawia niektóre z tych informacji bardziej szczegółowo.