Logiczne odczyty różnią się podczas uzyskiwania dostępu do tych samych danych LOB

Oto trzy proste testy, które odczytują te same dane, ale raportują bardzo różne logiczne odczyty:

Ustawiać

Poniższy skrypt tworzy tabelę testową ze 100 identycznymi wierszami, z których każdy zawiera kolumnę xml z wystarczającą ilością danych, aby upewnić się, że jest przechowywana poza wierszem. W mojej testowej bazie danych długość wygenerowanego pliku XML wynosi 20 204 bajtów na każdy wiersz.

-- Conditional drop
IF OBJECT_ID(N'dbo.XMLTest', N'U') IS NOT NULL
    DROP TABLE dbo.XMLTest;
GO
-- Create test table
CREATE TABLE dbo.XMLTest
(
    ID integer IDENTITY PRIMARY KEY,
    X xml NULL
);
GO
-- Add 100 wide xml rows
DECLARE @X xml;

SET @X =
(
    SELECT TOP (100) *
    FROM  sys.columns AS C
    FOR XML 
        PATH ('row'),
        ROOT ('root'),
        TYPE
);

INSERT dbo.XMLTest
    (X)
SELECT TOP (100)
    @X
FROM  sys.columns AS C;

-- Flush dirty buffers
CHECKPOINT;

Testy

Poniższe trzy testy odczytują kolumnę xml za pomocą:

1. Proste SELECTstwierdzenie
2. Przypisywanie xml do zmiennej
3. Używanie SELECT INTOdo tworzenia tabeli tymczasowej

-- No row count messages or graphical plan
-- Show I/O statistics
SET NOCOUNT ON;
SET STATISTICS XML OFF;
SET STATISTICS IO ON;
GO
PRINT CHAR(10) + '=== Plain SELECT ===='

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT XT.X 
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== Assign to a variable ===='

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

DECLARE @X xml;

SELECT
    @X = XT.X
FROM dbo.XMLTest AS XT;
GO
PRINT CHAR(10) + '=== SELECT INTO ===='

IF OBJECT_ID(N'tempdb..#T', N'U') IS NOT NULL
    DROP TABLE #T;

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

SELECT 
    XT.X
INTO #T
FROM dbo.XMLTest AS XT
GO
SET STATISTICS IO OFF;

Wyniki

Dane wyjściowe to:

=== Zwykły WYBIERZ ====
Tabela „XMLTest”. Liczba skanów 1, odczyty logiczne 3, odczyty fizyczne 1, odczyty z wyprzedzeniem 0,
    lob logiczne odczytuje 795, lob fizyczne odczytuje 37, lob odczytuje z wyprzedzeniem 796.

=== Przypisz do zmiennej ====
Tabela „XMLTest”. Liczba skanów 1, odczyty logiczne 3, odczyty fizyczne 1, odczyty z wyprzedzeniem 0,
    lob logiczne odczytuje 0, lob fizycznie odczytuje 0, lob odczytuje z wyprzedzeniem 0.

=== WYBIERZ NA ====
Tabela „XMLTest”. Liczba skanów 1, odczyty logiczne 3, odczyty fizyczne 1, odczyty z wyprzedzeniem 0,
    lob logiczne odczytuje 300, lob fizyczne odczytuje 37, lob odczytuje z wyprzedzeniem 400.

pytania

• Dlaczego odczyty LOB są tak różne?
• Z pewnością w każdym teście czytano dokładnie te same dane?

Nie wszystkie odczyty są równe. SQL Server wie, że dostęp do danych LOB jest kosztowny i stara się go unikać, gdy jest to możliwe. Istnieją również szczegółowe różnice w sposobie odczytu danych LOB w każdym przypadku:

Podsumowanie

Liczby są różne, ponieważ:

• Select czyta LOB w pakietowych wielkości kawałki
• Test przypisania zmiennych w ogóle nie odczytuje LOB
• Test „wybierz do” odczytuje LOB na całych stronach

Szczegół

1. Równina SELECT

   

   Skanowanie indeksu klastrowanego nie odczytuje żadnych danych LOB. Przypisuje tylko uchwyt LOB silnika pamięci masowej . Uchwyt nie jest używany, dopóki formant nie powróci do katalogu głównego planu.

   Treść LOB bieżącego wiersza jest odczytywana w porcjach wielkości pakietu TDS i przesyłana strumieniowo do klienta. Logiczne odczyty liczą liczbę dotknięć strony, więc:

   Liczba zgłoszonych odczytów jest równa liczbie wykonanych odczytów fragmentarycznych plus jeden za każdym razem, gdy nastąpi przejście do strony LOB.

   Na przykład: Logiczny odczyt jest liczony na początku każdego fragmentu, gdy proces dotyka strony odpowiadającej bieżącej pozycji strumienia. Gdy pakiety są mniejsze niż strona bazy danych (zwykły przypadek), kilka logicznych odczytów jest liczonych dla tej samej strony. Gdyby rozmiar pakietu był tak duży, że cały LOB mógłby zmieścić się w jednym kawałku, liczba zgłoszonych logicznych odczytów byłaby liczbą stron LOB.

2. Zmienne przypisanie

   

   Skanowanie indeksów klastrowych przypisuje uchwyt LOB jak poprzednio. W katalogu głównym planu uchwyt LOB jest kopiowany do zmiennej. Same dane LOB nigdy nie są dostępne (zero odczytów LOB), ponieważ zmienna nigdy nie jest czytana. Nawet gdyby tak było, byłoby to możliwe tylko poprzez ostatnio przypisany uchwyt LOB.

   Brak odczytów LOB, ponieważ dane LOB nigdy nie są dostępne.

3. SELECT INTO

   

   Ten plan używa dostawcy zbiorczego zestawu wierszy do kopiowania danych LOB z tabeli źródłowej do nowej tabeli. Przetwarza kompletną stronę LOB przy każdym odczycie (brak przesyłania strumieniowego lub dzielenia).

   Liczba odczytów logicznych odpowiada liczbie stron LOB w tabeli testowej.