Obecność pola XML powoduje, że większość danych tabeli znajduje się na stronach LOB_DATA (w rzeczywistości ~ 90% stron tabeli to LOB_DATA).
Samo umieszczenie kolumny XML w tabeli nie ma tego efektu. Obecność danych XML powoduje , że pod pewnymi warunkami część danych wiersza jest przechowywana poza wierszem na stronach LOB_DATA. I choć jeden (a może kilka ;-) może argumentować, że XMLkolumna oznacza, że rzeczywiście będą dane XML, nie ma gwarancji, że dane XML będą musiały być przechowywane poza wierszem: chyba że wiersz jest już prawie wypełniony poza tym, że są danymi XML, małe dokumenty (do 8000 bajtów) mogą zmieścić się w rzędzie i nigdy nie przechodzić na stronę LOB_DATA.
czy mam rację, myśląc, że strony LOB_DATA mogą powodować powolne skanowanie nie tylko ze względu na ich rozmiar, ale także dlatego, że SQL Server nie może skutecznie skanować indeksu klastrowego, gdy w tabeli jest wiele stron LOB_DATA?
Skanowanie odnosi się do spojrzenia na wszystkie rzędy. Oczywiście, gdy czytana jest strona danych, wszystkie wiersze , nawet jeśli wybrano podzbiór kolumn. Różnica w stosunku do danych LOB polega na tym, że jeśli nie wybierzesz tej kolumny, dane poza wierszem nie zostaną odczytane. Dlatego niesprawiedliwe jest wyciąganie wniosków na temat tego, jak skutecznie SQL Server może skanować ten Indeks klastrowany, ponieważ nie dokładnie go przetestowałeś (lub przetestowałeś jego połowę). Wybrałeś wszystkie kolumny, w tym kolumnę XML, i jak wspomniałeś, tam właśnie znajduje się większość danych.
Wiemy już więc, że SELECT TOP 1000 *test nie polegał jedynie na odczytaniu serii 8 000 stron danych, wszystkie w jednym rzędzie, ale przeskakiwaniu do innych lokalizacji w każdym rzędzie . Dokładna struktura tych danych LOB może się różnić w zależności od ich wielkości. Na podstawie badań pokazanych tutaj ( Jaki jest rozmiar wskaźnika LOB dla typów (MAX), takich jak Varchar, Varbinary, Etc? ), Istnieją dwa rodzaje przydziałów LOB poza wierszem:
- Inline Root - dla danych od 8001 do 40 000 (naprawdę 42 000) bajtów, jeśli pozwala na to miejsce, będzie od 1 do 5 wskaźników (24 - 72 bajtów) W RZĄDZIE, które wskazują bezpośrednio na stronę (strony) LOB.
- DRZEWO TEKSTOWE - w przypadku danych przekraczających 42 000 bajtów lub jeśli od 1 do 5 wskaźników nie mieści się w rzędzie, wówczas na stronie początkowej listy wskaźników na stronach LOB będzie tylko 24-bajtowy wskaźnik (tzn. „ strona_tekstowa ”).
Jeden z tych dwóch sytuacji występuje za każdym razem pobierać dane LOB, które jest ponad 8000 bajtów lub po prostu nie pasował w wierszu. Zamieściłem skrypt testowy na PasteBin.com ( skrypt T-SQL do testowania przydziałów i odczytów LOB ), który pokazuje 3 typy przydziałów LOB (w zależności od wielkości danych), a także wpływ każdego z nich na logiczne i odczyty fizyczne. W twoim przypadku, jeśli dane XML naprawdę mają mniej niż 42 000 bajtów na wiersz, to żaden z nich (lub bardzo mało) nie powinien mieć najmniej wydajnej struktury TEXT_TREE.
Jeśli chcesz przetestować, jak szybko SQL Server może skanować ten Indeks klastrowany, wykonaj, SELECT TOP 1000ale określ jedną lub więcej kolumn nie zawierających tej kolumny XML. Jak to wpływa na twoje wyniki? Powinno być nieco szybsze.
czy uzasadnione jest posiadanie takiej struktury tabeli / wzorca danych?
Biorąc pod uwagę, że mamy niepełny opis faktycznej struktury tabeli i wzorca danych, każda odpowiedź może nie być optymalna w zależności od brakujących szczegółów. Mając to na uwadze, powiedziałbym, że nie ma nic oczywiście nierozsądnego w strukturze tabeli lub wzorcu danych.
Mogę (w aplikacji ac #) kompresować XML z 20KB do ~ 2,5KB i przechowywać go w kolumnie VARBINARY, co zapobiega użyciu stron danych LOB. To przyspiesza WYBIERANIE 20 razy w moich testach.
Dzięki temu zaznaczanie wszystkich kolumn, a nawet tylko danych XML (teraz w VARBINARY) jest szybsze, ale w rzeczywistości szkodzi zapytaniom, które nie wybierają danych „XML”. Zakładając, że masz około 50 bajtów w innych kolumnach i masz FILLFACTOR100, a następnie:
Bez kompresji: 15 000 XMLdanych powinno wymagać 2 stron LOB_DATA, co wymaga 2 wskaźników dla Inline Root. Pierwszy wskaźnik ma 24 bajty, a drugi 12, w sumie 36 bajtów przechowywanych w wierszu dla danych XML. Całkowity rozmiar wiersza wynosi 86 bajtów i można zmieścić około 93 tych wierszy na stronie danych o wielkości 8060 bajtów. Dlatego 1 milion wierszy wymaga 10 753 stron danych.
Kompresja niestandardowa: 2,5 tys. VARBINARYDanych zmieści się w rzędzie. Całkowity rozmiar wiersza wynosi 2610 (2,5 * 1024 = 2560) bajtów i można zmieścić tylko 3 z tych wierszy na stronie danych o długości 8060 bajtów. Dlatego 1 milion wierszy wymaga 333 334 stron danych.
Ergo, wdrożenie niestandardowej kompresji powoduje 30-krotny wzrost liczby stron danych dla indeksu klastrowanego. Znaczenie wszystkich zapytań z użyciem skanowania indeksu klastrowego mają teraz około 322,500 więcej stron danych do odczytania. Proszę zapoznać się ze szczegółową sekcją poniżej, aby uzyskać dodatkowe konsekwencje tego typu kompresji.
Przestrzegałbym przed robieniem jakichkolwiek refaktoryzacji opartych na wydajności SELECT TOP 1000 * . Prawdopodobnie nie będzie to zapytanie, które aplikacja wygeneruje, i nie powinno być wykorzystywane jako jedyna podstawa do potencjalnie niepotrzebnych optymalizacji.
Aby uzyskać bardziej szczegółowe informacje i więcej testów do wypróbowania, zobacz sekcję poniżej.
Na to pytanie nie można udzielić ostatecznej odpowiedzi, ale możemy przynajmniej poczynić pewne postępy i zasugerować dodatkowe badania, które pomogą nam przybliżyć się do dokładnego rozwiązania problemu (najlepiej na podstawie dowodów).
Co wiemy:
- Tabela ma około 1 miliona wierszy
- Rozmiar stołu to około 15 GB
- Tabela zawiera jedną
XMLkolumnę oraz kilka innych kolumn typów: INT, BIGINT, UNIQUEIDENTIFIER, „itp”
XMLkolumna „rozmiar” wynosi średnio około 15 tys- Po uruchomieniu
DBCC DROPCLEANBUFFERSnastępująca kwerenda zajmuje 20–25 sekund:SELECT TOP 1000 * FROM TABLE
- Indeks klastrowany jest skanowany
- Fragmentacja indeksu klastrowego jest bliska 0%
Co naszym zdaniem wiemy:
- Żadna inna aktywność dysku poza tymi zapytaniami. Jesteś pewny? Nawet jeśli nie ma innych zapytań użytkowników, czy mają miejsce operacje w tle? Czy istnieją procesy zewnętrzne względem programu SQL Server działające na tym samym komputerze, które mogą zajmować część operacji we / wy? Może nie być, ale nie jest jasne na podstawie wyłącznie dostarczonych informacji.
- Zwracane jest 15 MB danych XML. Na czym opiera się ten numer? Oszacowanie wyprowadzone z 1000 wierszy razy średnio 15k danych XML na wiersz? Lub programowa agregacja tego, co otrzymano dla tego zapytania? Jeśli jest to tylko oszacowanie, nie polegałbym na nim, ponieważ dystrybucja danych XML może nie być nawet w sposób sugerowany przez zwykłą średnią.
Kompresja XML może pomóc. Jak dokładnie zrobiłbyś kompresję w .NET? Poprzez GZipStream lub DeflateStream klas? To nie jest opcja o zerowym koszcie. Z pewnością skompresuje niektóre dane o duży procent, ale będzie również wymagało więcej procesora, ponieważ będziesz potrzebował dodatkowego procesu do kompresji / dekompresji danych za każdym razem. Ten plan całkowicie wyeliminowałby również Twoją zdolność do:
- Zapytanie o dane XML przez
.nodes, .value, .query, i .modifyfunkcje XML.
indeksować dane XML.
Należy pamiętać (ponieważ wspomniano, że XML jest „bardzo redundantny”), że XMLtyp danych jest już zoptymalizowany, ponieważ przechowuje nazwy elementów i atrybutów w słowniku, przypisując identyfikator indeksu liczb całkowitych do każdego elementu, a następnie używając tego identyfikatora liczb całkowitych w całym dokumencie (stąd nie powtarza pełnej nazwy dla każdego użycia, ani nie powtarza go ponownie jako tag zamykający dla elementów). Rzeczywiste dane usunęły również obce białe miejsca. Dlatego wyodrębnione dokumenty XML nie zachowują swojej oryginalnej struktury i dlaczego puste elementy wyodrębniają się tak, <element />jakby weszły jako<element></element>. Tak więc wszelkie korzyści z kompresji za pomocą GZip (lub cokolwiek innego) można znaleźć tylko poprzez kompresję wartości elementu i / lub wartości atrybutu, co jest znacznie mniejszą powierzchnią, którą można poprawić, niż większość by się spodziewała, i najprawdopodobniej nie jest warta utraty możliwości, jak wspomniano bezpośrednio powyżej.
Należy również pamiętać, że kompresja danych XML i przechowywanie VARBINARY(MAX)wyniku nie wyeliminuje dostępu do LOB, po prostu go zmniejszy. W zależności od wielkości reszty danych w wierszu skompresowana wartość może zmieścić się w wierszu lub może nadal wymagać stron LOB.
Ta informacja, choć pomocna, nie jest prawie wystarczająca. Istnieje wiele czynników wpływających na wydajność zapytań, dlatego potrzebujemy znacznie bardziej szczegółowego obrazu tego, co się dzieje.
Czego nie wiemy, ale musimy:
- Dlaczego występowanie
SELECT *materii? Czy jest to wzorzec używany w kodzie? Jeśli tak, dlaczego?
- Jaka jest wydajność zaznaczania tylko kolumny XML? Jakie są statystyki i czas, jeśli wykonasz tylko
SELECT TOP 1000 XmlColumn FROM TABLE;:?
Ile z 20–25 sekund potrzebnych na zwrócenie tych 1000 wierszy jest związanych z czynnikami sieciowymi (przesyłanie danych przez sieć), a ile z czynnikami klienta (renderowanie około 15 MB plus reszta nie- Dane XML do siatki w SSMS, a może zapisywanie na dysk)?
Rozróżnienie tych dwóch aspektów operacji może czasem być wykonane po prostu przez nie zwracanie danych. Teraz można pomyśleć o wyborze tabeli tymczasowej lub zmiennej tabeli, ale wprowadziłoby to tylko kilka nowych zmiennych (tj. Dysk I / O dla tempdb, zapisy dziennika transakcji, możliwy automatyczny wzrost danych tempdb i / lub pliku dziennika, potrzeba spacja w puli buforów itp.). Wszystkie te nowe czynniki mogą faktycznie wydłużyć czas zapytania. Zamiast tego zazwyczaj przechowuję kolumny w zmiennych (odpowiedniego typu danych; nie SQL_VARIANT), które są zastępowane każdym nowym wierszem (tj SELECT @Column1 = tab.Column1,....).
JEDNAK , jak wskazał @PaulWhite w tym pytaniu i odpowiedziach DBA.StackExchange, Logical czyta inaczej przy dostępie do tych samych danych LOB , z dodatkowymi badaniami moich własnych opublikowanych na PasteBin ( skrypt T-SQL do testowania różnych scenariuszy dla odczytów LOB ) , LOB nie są dostępne między konsekwentnie SELECT, SELECT INTO, SELECT @XmlVariable = XmlColumn, SELECT @XmlVariable = XmlColumn.query(N'/'), i SELECT @NVarCharVariable = CONVERT(NVARCHAR(MAX), XmlColumn). Nasze opcje są tutaj nieco bardziej ograniczone, ale oto, co można zrobić:
- Wyeliminuj problemy z siecią, wykonując zapytanie na serwerze z programem SQL Server, w SSMS lub SQLCMD.EXE.
- Wyklucz problemy klienta w SSMS, przechodząc do Opcje zapytania -> Wyniki -> Siatka i zaznaczając opcję „Odrzuć wyniki po wykonaniu”. Należy pamiętać, że ta opcja zapobiegnie WSZYSTKIM wyjściom, w tym wiadomościom, ale nadal może być użyteczna, aby wykluczyć czas potrzebny SSMS na przydzielenie pamięci dla każdego wiersza, a następnie narysowanie jej w siatce.
Alternatywnie, można wykonać zapytanie poprzez SQLCMD.EXE i przekierować iść donikąd poprzez: -o NUL:.
- Czy z tym zapytaniem jest powiązany typ oczekiwania? Jeśli tak, co to za typ oczekiwania?
Jaki jest rzeczywisty rozmiar danych dla zwracanychXML kolumn ? Średni rozmiar tej kolumny w całej tabeli nie ma tak naprawdę znaczenia, jeśli wiersze „TOP 1000” zawierają nieproporcjonalnie dużą część wszystkich danych. Jeśli chcesz wiedzieć o TOP 1000 wierszach, spójrz na te wiersze. Uruchom następujące czynności:XML
SELECT TOP 1000 tab.*,
SUM(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [TotalXmlKBytes],
AVG(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [AverageXmlKBytes]
STDEV(DATALENGTH(tab.XmlColumn)) / 1024.0 AS [StandardDeviationForXmlKBytes]
FROM SchemaName.TableName tab;
- Dokładny schemat tabeli. Proszę podać pełne
CREATE TABLE oświadczenie, w tym wszystkie indeksy.
- Plan zapytań? Czy to coś, co możesz opublikować? Te informacje prawdopodobnie nic nie zmienią, ale lepiej wiedzieć, że nie, niż zgadywać, że nie będzie i nie będzie w błędzie ;-)
- Czy w pliku danych występuje fragmentacja fizyczna / zewnętrzna? Chociaż może to nie być duży czynnik, ponieważ używasz „SATA klasy konsumenckiej”, a nie SSD, a nawet Super-drogich SATA, efekt mniej optymalnie uporządkowanych sektorów będzie bardziej zauważalny, szczególnie ze względu na liczbę tych sektorów które należy odczytać zwiększa się.
Jakie są dokładne wyniki następującego zapytania:
SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(),
OBJECT_ID(N'dbo.SchemaName.TableName'), 1, 0, N'LIMITED');
AKTUALIZACJA
Przyszło mi do głowy, że powinienem spróbować odtworzyć ten scenariusz, aby sprawdzić, czy mam podobne zachowanie. Tak więc utworzyłem tabelę z kilkoma kolumnami (podobnymi do niejasnego opisu w pytaniu), a następnie zapełniłem ją 1 milionem wierszy, a kolumna XML zawiera około 15k danych na wiersz (patrz kod poniżej).
Zauważyłem, że wykonanie SELECT TOP 1000 * FROM TABLEzadania za pierwszym razem zakończyło się w 8 sekund, a potem za każdym razem 2–4 sekundy (tak, wykonywanie DBCC DROPCLEANBUFFERSprzed każdym uruchomieniem SELECT *zapytania). A mój kilkuletni laptop nie jest szybki: SQL Server 2012 SP2 Developer Edition, 64-bitowy, 6 GB RAM, podwójny 2,5 GHz Core i5 i dysk SATA 5400 RPM. Używam również SSMS 2014, SQL Server Express 2014, Chrome i kilku innych rzeczy.
W oparciu o czas odpowiedzi mojego systemu powtórzę, że potrzebujemy więcej informacji (tj. Dane szczegółowe dotyczące tabeli i danych, wyniki sugerowanych testów itp.), Aby pomóc zawęzić przyczynę 20–25 sekundowego czasu odpowiedzi które widzisz.
SET ANSI_NULLS, NOCOUNT ON;
GO
IF (OBJECT_ID(N'dbo.XmlReadTest') IS NOT NULL)
BEGIN
PRINT N'Dropping table...';
DROP TABLE dbo.XmlReadTest;
END;
PRINT N'Creating table...';
CREATE TABLE dbo.XmlReadTest
(
ID INT NOT NULL IDENTITY(1, 1),
Col2 BIGINT,
Col3 UNIQUEIDENTIFIER,
Col4 DATETIME,
Col5 XML,
CONSTRAINT [PK_XmlReadTest] PRIMARY KEY CLUSTERED ([ID])
);
GO
DECLARE @MaxSets INT = 1000,
@CurrentSet INT = 1;
WHILE (@CurrentSet <= @MaxSets)
BEGIN
RAISERROR(N'Populating data (1000 sets of 1000 rows); Set # %d ...',
10, 1, @CurrentSet) WITH NOWAIT;
INSERT INTO dbo.XmlReadTest (Col2, Col3, Col4, Col5)
SELECT TOP 1000
CONVERT(BIGINT, CRYPT_GEN_RANDOM(8)),
NEWID(),
GETDATE(),
N'<test>'
+ REPLICATE(CONVERT(NVARCHAR(MAX), CRYPT_GEN_RANDOM(1), 2), 3750)
+ N'</test>'
FROM [master].[sys].all_columns sac1;
IF ((@CurrentSet % 100) = 0)
BEGIN
RAISERROR(N'Executing CHECKPOINT ...', 10, 1) WITH NOWAIT;
CHECKPOINT;
END;
SET @CurrentSet += 1;
END;
--
SELECT COUNT(*) FROM dbo.XmlReadTest; -- Verify that we have 1 million rows
-- O.P. states that the "clustered index fragmentation is close to 0%"
ALTER INDEX [PK_XmlReadTest] ON dbo.XmlReadTest REBUILD WITH (FILLFACTOR = 90);
CHECKPOINT;
--
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS IO, TIME ON;
SELECT TOP 1000 * FROM dbo.XmlReadTest;
SET STATISTICS IO, TIME OFF;
/*
Scan count 1, logical reads 21, physical reads 1, read-ahead reads 4436,
lob logical reads 5676, lob physical reads 1, lob read-ahead reads 3967.
SQL Server Execution Times:
CPU time = 171 ms, elapsed time = 8329 ms.
*/
A ponieważ chcemy odliczyć czas potrzebny na odczytanie stron spoza LOB, uruchomiłem następujące zapytanie, aby zaznaczyć wszystkie oprócz kolumny XML (jeden z testów, które zasugerowałem powyżej). Powraca to w ciągu 1,5 sekundy dość konsekwentnie.
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET STATISTICS IO, TIME ON;
SELECT TOP 1000 ID, Col2, Col3, Col4 FROM dbo.XmlReadTest;
SET STATISTICS IO, TIME OFF;
/*
Scan count 1, logical reads 21, physical reads 1, read-ahead reads 4436,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1666 ms.
*/
Wniosek (na razie)
Na podstawie mojej próby odtworzenia twojego scenariusza nie sądzę, abyśmy mogli wskazać dysk SATA lub niesekwencyjne operacje we / wy jako główną przyczynę 20–25 sekund, szczególnie dlatego, że wciąż nie wiem, jak szybko zwraca zapytanie, jeśli nie zawiera kolumny XML. I nie byłem w stanie odtworzyć dużej liczby odczytów logicznych (nie LOB), które wyświetlasz, ale mam wrażenie, że muszę dodać więcej danych do każdego wiersza w świetle tego i oświadczenia:
~ 90% stron tabeli to LOB_DATA
Moja tabela ma 1 milion wierszy, z których każdy zawiera nieco ponad 15 000 danych XML i sys.dm_db_index_physical_statspokazuje, że istnieją 2 miliony stron LOB_DATA. Pozostałe 10% to wtedy 222 tys. Stron danych IN_ROW, ale mam ich tylko 11,630. Po raz kolejny potrzebujemy więcej informacji dotyczących faktycznego schematu tabeli i rzeczywistych danych.