Dlaczego przestrzeń danych tabeli może zajmować 4x rozmiar surowych danych?

Mam tabelę z 490 wierszami M i 55 GB miejsca na stole, czyli około 167 bajtów na wiersz. Tabela ma trzy kolumny: a VARCHAR(100), a DATETIME2(0)i a SMALLINT. Średnia długość tekstu w VARCHARpolu wynosi około 21,5, więc nieprzetworzone dane powinny mieć około 32 bajtów na wiersz: 22 + 2 dla VARCHAR, 6 dla DATETIME2i 2 dla 16-bitowej liczby całkowitej.

Pamiętaj, że powyższe miejsce to tylko dane, a nie indeksy. Używam wartości zgłoszonej w obszarze Właściwości | Przechowywanie | Ogólne | Przestrzeń danych.

Oczywiście musi być trochę narzutu, ale 135 bajtów na wiersz wydaje się dużo, szczególnie w przypadku dużego stołu. Dlaczego to może być? Czy ktoś jeszcze widział podobne mnożniki? Jakie czynniki mogą wpłynąć na ilość wymaganej dodatkowej przestrzeni?

Dla porównania próbowałem stworzyć tabelę z dwoma INTpolami i 1 M rzędami. Wymagane miejsce na dane wyniosło 16,4 MB: 17 bajtów na wiersz, w porównaniu do 8 bajtów surowych danych. Innym tabeli test o INTi VARCHAR(100)wypełniona samego tekstu jako rzeczywistym stole wykorzystuje 39 bajtów w rzędzie (44) K wierszy, w których byłoby oczekiwać 28 Plus trochę.

Tak więc stół produkcyjny ma znacznie więcej kosztów ogólnych. Czy to dlatego, że jest większy? Spodziewałbym się, że rozmiary indeksu będą w przybliżeniu N * log (N), ale nie rozumiem, dlaczego przestrzeń wymagana dla danych rzeczywistych jest nieliniowa.

Z góry dziękuję za wszelkie wskazówki!

EDYTOWAĆ:

Wszystkie wymienione pola są NOT NULL. Tabela rzeczywista ma klastrowane PK na VARCHARpolu i DATETIME2polu, w tej kolejności. Dla dwóch testów pierwszym INTbył (klastrowany) PK.

Jeśli ma to znaczenie: tabela jest zapisem wyników ping. Te pola to URL, data / godzina pingowania i opóźnienie w milisekundach. Dane są stale dodawane i nigdy nie aktualizowane, ale dane są okresowo usuwane, aby ograniczyć je do zaledwie kilku rekordów na godzinę na adres URL.

EDYTOWAĆ:

Bardzo interesująca odpowiedź tutaj sugeruje, że dla indeksu z dużą ilością czytania i pisania przebudowa może nie być korzystna. W moim przypadku zajmowane miejsce jest problemem, ale jeśli ważniejsza jest wydajność zapisu, lepiej byłoby mieć luźne indeksy.

sql-server-2008 disk-space

— Jon of All Trades
źródło

Odpowiedzi:

Po dyskusji w komentarzach do pierwotnego pytania, wydaje się, że w tym przypadku utracona przestrzeń jest spowodowana wyborem klucza klastrowego, co doprowadziło do ogromnej fragmentacji.

W takich sytuacjach zawsze warto sprawdzić stan fragmentacji za pomocą sys.dm_db_index_physical_stats.

Edycja: Po aktualizacji w komentarzach

Średnia gęstość stron (przed odbudowaniem indeksu klastrowanego) wyniosła 24%, co idealnie pasuje do pierwotnego pytania. Strony były pełne tylko w 1/4, więc całkowity rozmiar był czterokrotnie większy niż rozmiar surowych danych.

— Mark Storey-Smith
źródło

Struktury na dysku mają narzut:

nagłówek wiersza
pusta mapa bitowa + wskaźnik
przesunięcia kolumn o zmiennej długości
wskaźniki wersji wiersza (opcjonalnie)
...

Biorąc 2 x 4 bajty int kolumn, masz

4 bajty nagłówka wiersza
2 bajtowy wskaźnik do bitmapy NULL
8 bajtów na 2 kolumny int
3 bajty NULL bitmapa

Wow 17 bajtów!

Możesz zrobić to samo w przypadku drugiego stołu testowego, który ma większy narzut niż twój oryginalny:

2 bajty na liczbę kolumn o zmiennej długości
2 bajty na kolumnę o zmiennej długości

Skąd ta różnica? Ponadto (nie będę do nich linkować)

czy kiedykolwiek przebudowałeś indeksy, aby je zdefragmentować?
usuwa nie odzyskuje miejsca
strony danych zostaną podzielone, jeśli wstawisz na środku
aktualizacje mogą powodować przekazywanie wskaźników (pozostawia lukę)
przepełnienie wiersza
usunięto kolumnę varchar bez przebudowy indeksu lub DBCC CLEANTABLE
sterta lub tabela (sterta nie ma indeksu klastrowego = rekordy rozproszone po całym)
Poziom izolacji RCSI (dodatkowe 14 bajtów na wiersz)
końcowe spacje (SET ANSI_PADDING jest domyślnie WŁĄCZONY) w varchar. Użyj DATALENGTH, aby sprawdzić, a nie LEN
Uruchom sp_spaceused za pomocą @updateusage = 'true'
...

Zobacz: SQL Server: Jak utworzyć tabelę, która wypełnia jedną stronę o wielkości 8 KB?

Od SO:

— gbn
źródło

Próbka kolumny 2x4 bajty int nie jest w 100% poprawna. Będziesz miał 4 bajtowy nagłówek wiersza (2 bajty stanu i 2 bajty dla rozmiaru danych o stałej długości). Wtedy będziesz miał 2x4 bajty na dane. Dwa bajty dla liczby kolumn i jeden bajt dla pustej mapy bitowej, co daje łączną długość rekordu 15 bajtów, a nie 17.

— Mark S. Rasmussen

@Mark S. Rasmussen: Skąd bierze się „2 bajty dla rozmiaru danych o stałej długości”? MSDN? A bitmapa zerowa ma zawsze 3 bajty: sqlskills.com/blogs/paul/post/… + msdn.microsoft.com/en-us/library/ms178085%28v=sql.90%29.aspx

— gbn

Wow, świetny szczegół! Uwzględniłem pole długości VARCHARsw powyższym oszacowaniu, ale nie liczbę kolumn. Ta tabela nie ma pól NULLable (należy o tym wspomnieć), czy nadal alokuje dla nich bajty?

— Jon of All Trades,

Czy wskaźniki odbudowy wpłynęłyby na część danych wymaganego miejsca? Być może odbudowałby indeks klastrowany. Wkładki zdarzają się często na środku, ale gdybym zamienił kolejność pól grupowania, które by się zatrzymały. Większość pozostałych nie powinna mieć zastosowania w tym przypadku, ale jest to świetne odniesienie do ogólnego przypadku. Sprawdzę twoje linki. Dobry towar!

— Jon of All Trades

@gbn 2 bajty dla danych o stałej długości stanowią część wspomnianego 4-bajtowego nagłówka wiersza. Jest to wskaźnik wskazujący koniec części o ustalonej długości danych / początek liczby kolumn / pustą mapę bitową. Bitmapa NULL nie zawsze ma trzy bajty. Jeśli podasz liczbę kolumn, będą to co najmniej trzy bajty, ale może być więcej - podzieliłem mapę bitową i liczbę kolumn w moim opisie. Ponadto bitmapa NULL nie zawsze jest obecna, chociaż tak będzie w tym przypadku.

— Mark S. Rasmussen

Czy typy danych zmieniły się z czasem? Czy kolumny o zmiennej długości zostały usunięte? Czy indeksy były często defragmentowane, ale nigdy nie odbudowywane? Czy usunięto wiele wierszy lub znaczną zaktualizowano wiele kolumn o zmiennej długości? Dobra dyskusja tutaj .

— Aaron Bertrand
źródło

Mam 97% pewności, że nie zmieniłem typu danych ani nie usunąłem pola. Gdybym to zrobił, byłby naprawdę wcześnie, gdy stół miałby znacznie mniej rzędów. Nie ma żadnych usunięć ani aktualizacji, dane są tylko dołączane.

— Jon of All Trades,

Korekta: są skreślenia i całkiem sporo. Tabela ma znaczny wzrost netto, więc wyobrażam sobie, że ta przestrzeń zostanie szybko ponownie wykorzystana.

— Jon of All Trades

W przypadku dużej liczby danych dane mogą być ponownie wykorzystane. Jaki jest klucz klastrowania tabeli? Czy wstawki znajdują się pośrodku stołu czy na końcu?

— mrdenny,

Klucz klastra jest związek, na VARCHARi DATETIME2pól, w tej kolejności. Wkładki będą równomiernie rozmieszczone dla pierwszego pola. W drugim polu nowe wartości i zawsze będą większe niż jakiekolwiek istniejące.

— Jon of All Trades