Please note that the following info is not intended to be a comprehensive
description of how data pages are laid out, such that one can calculate
the number of bytes used per any set of rows, as that is very complicated.
Dane nie są jedyną rzeczą zajmującą miejsce na stronie z danymi 8k:
Jest zarezerwowane miejsce. Możesz używać tylko 8060 z 8192 bajtów (to 132 bajty, które nigdy nie były twoje):
- Nagłówek strony: To dokładnie 96 bajtów.
- Tablica szczelin: jest to 2 bajty na wiersz i wskazuje przesunięcie, od którego zaczyna się każdy wiersz na stronie. Rozmiar tej tablicy nie jest ograniczony do pozostałych 36 bajtów (132 - 96 = 36), w przeciwnym razie można by skutecznie ograniczyć się do umieszczenia maksymalnie 18 wierszy na stronie danych. Oznacza to, że każdy wiersz jest o 2 bajty większy niż myślisz. Ta wartość nie jest uwzględniona w „rozmiarze rekordu” zgłoszonym przez
DBCC PAGE, dlatego jest tutaj trzymana osobno, zamiast uwzględniać ją w informacjach dla poszczególnych wierszy poniżej.
- Metadane dla wiersza (w tym między innymi):
- Rozmiar różni się w zależności od definicji tabeli (tj. Liczby kolumn, zmiennej długości lub stałej długości itp.). Informacje zaczerpnięte z komentarzy @ PaulWhite i @ Aaron, które można znaleźć w dyskusji dotyczącej tej odpowiedzi i testowania.
- Nagłówek wiersza: 4 bajty, z których 2 oznaczają typ rekordu, a pozostałe dwa stanowią przesunięcie w stosunku do bitmapy NULL
- Liczba kolumn: 2 bajty
- NULL Bitmap: które kolumny są obecnie
NULL. 1 bajt na każdy zestaw 8 kolumn. I dla wszystkich kolumn, nawet NOT NULLtych. Stąd minimum 1 bajt.
- Macierz przesunięcia kolumny o zmiennej długości: minimum 4 bajty. 2 bajty do przechowywania liczby kolumn o zmiennej długości, a następnie 2 bajty na każdą kolumnę o zmiennej długości do przechowywania przesunięcia do miejsca, w którym się zaczyna.
- Informacje o wersji: 14 bajtów (będzie to widoczne, jeśli baza danych jest ustawiona na jedną
ALLOW_SNAPSHOT_ISOLATION ONlub dwie READ_COMMITTED_SNAPSHOT ON).
- Aby uzyskać więcej informacji na ten temat, zobacz następujące pytanie i odpowiedź: Tablica miejsc i całkowity rozmiar strony
- Proszę zobaczyć następujący post na blogu od Paula Randalla, który zawiera kilka interesujących szczegółów na temat tego, jak układają się strony danych: Naśmiewanie się z STRONĄ DBCC (część 1?)
Wskaźniki LOB dla danych, które nie są przechowywane w wierszu. To by odpowiadało DATALENGTH+ pointer_size. Ale nie są to standardowe rozmiary. Zobacz następujący post na blogu, aby uzyskać szczegółowe informacje na temat tego złożonego tematu: Jaki jest rozmiar wskaźnika LOB dla typów (MAX), takich jak Varchar, Varbinary, itp.? . Pomiędzy tym połączonym postem a kilkoma dodatkowymi testami, które przeprowadziłem , (domyślne) reguły powinny wyglądać następująco:
- Legacy / przestarzałe typy LOB, że nikt nie powinien być już za pomocą SQL Server 2005 (
TEXT, NTEXT, i IMAGE):
- Domyślnie zawsze przechowują swoje dane na stronach LOB i zawsze używają 16-bajtowego wskaźnika do pamięci LOB.
- JEŻELI użyto opcji sp_tableoption do ustawienia
text in rowopcji, to:
- jeśli na stronie jest miejsce do przechowywania wartości, a wartość nie jest większa niż maksymalny rozmiar w wierszu (konfigurowalny zakres od 24 do 7000 bajtów z domyślną wartością 256), to zostanie on zapisany w wierszu,
- w przeciwnym razie będzie to 16-bajtowy wskaźnik.
- Dla nowszych typów LOB wprowadzone w SQL Server 2005 (
VARCHAR(MAX), NVARCHAR(MAX), i VARBINARY(MAX)):
- Domyślnie:
- Jeśli wartość nie jest większa niż 8000 bajtów i na stronie jest miejsce, to zostanie ona zapisana w rzędzie.
- Inline Root - dla danych od 8001 do 40 000 (naprawdę 42 000) bajtów, o ile 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. 24 bajty dla początkowej strony LOB o wielkości 8 tys. I 12 bajtów na każdą dodatkową stronę o wielkości 8 tys. Dla maksymalnie czterech kolejnych 8 tys. Stron.
- TEXT_TREE - 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 będzie tylko 24-bajtowy wskaźnik do strony początkowej listy wskaźników do stron LOB (tj. „Text_tree „strona).
- JEŚLI do ustawienia opcji użyto sp_tableoption
large value types out of row , zawsze używaj 16-bajtowego wskaźnika do pamięci LOB.
- Powiedziałem „domyślne” reguły, ponieważ nie testowałem wartości w wierszu pod kątem wpływu niektórych funkcji, takich jak kompresja danych, szyfrowanie na poziomie kolumny, przezroczyste szyfrowanie danych, zawsze szyfrowane itp.
Strony przepełnienia LOB: Jeśli wartość wynosi 10 000, będzie to wymagało 1 pełnej strony przepełnienia 8 000, a następnie części drugiej strony. Jeśli żadne inne dane nie mogą zająć pozostałej przestrzeni (lub nawet jest to dozwolone, nie jestem pewien tej zasady), masz około 6 KB „zmarnowanego” miejsca na tym drugim arkuszu danych przepełnienia LOB.
Niewykorzystane miejsce: strona danych o wielkości 8 tys. To po prostu: 8192 bajtów. Nie różni się rozmiarem. Umieszczone na nim dane i meta-dane nie zawsze jednak dobrze pasują do wszystkich 8192 bajtów. Wierszy nie można podzielić na wiele stron danych. Jeśli więc pozostało 100 bajtów, ale żaden wiersz (lub żaden wiersz, który pasowałby do tej lokalizacji, w zależności od kilku czynników) nie może się tam zmieścić, strona danych nadal zajmuje 8192 bajtów, a twoje drugie zapytanie liczy tylko liczbę strony danych. Możesz znaleźć tę wartość w dwóch miejscach (pamiętaj, że część tej wartości to pewna ilość zarezerwowanego miejsca):
DBCC PAGE( db_name, file_id, page_id ) WITH TABLERESULTS;Poszukaj ParentObject= "PAGE HEADER:" i Field= "m_freeCnt". ValuePole jest liczba nieużywanych bajtów.SELECT buff.free_space_in_bytes FROM sys.dm_os_buffer_descriptors buff WHERE buff.[database_id] = DB_ID(N'db_name') AND buff.[page_id] = page_id;Jest to ta sama wartość, co zgłoszona przez „m_freeCnt”. Jest to łatwiejsze niż DBCC, ponieważ może uzyskać wiele stron, ale przede wszystkim wymaga, aby strony zostały wczytane do puli buforów.
Miejsce zarezerwowane przez FILLFACTOR<100. Nowo utworzone strony nie respektują tego FILLFACTORustawienia, ale wykonanie operacji REBUILD zarezerwuje to miejsce na każdej stronie danych. Idea zarezerwowanego miejsca polega na tym, że będzie on używany przez niesekwencyjne wstawki i / lub aktualizacje, które już zwiększają rozmiar wierszy na stronie, ponieważ kolumny o zmiennej długości są aktualizowane o nieco więcej danych (ale niewystarczająco, aby spowodować podział strony). Ale możesz z łatwością zarezerwować miejsce na stronach danych, które naturalnie nigdy nie otrzymają nowych wierszy i nigdy nie zaktualizują istniejących wierszy lub przynajmniej nie zaktualizują w sposób, który zwiększyłby rozmiar wiersza.
Podziały stron (fragmentacja): Konieczność dodania wiersza do lokalizacji, w której nie ma miejsca na wiersz, spowoduje podział strony. W takim przypadku około 50% istniejących danych zostaje przeniesionych na nową stronę, a nowy wiersz jest dodawany do jednej z 2 stron. Ale teraz masz trochę więcej wolnego miejsca, które nie jest uwzględnione w DATALENGTHobliczeniach.
Wiersze oznaczone do usunięcia. Po usunięciu wierszy nie zawsze są one natychmiast usuwane ze strony danych. Jeśli nie można ich natychmiast usunąć, są „oznaczeni na śmierć” (odniesienie Stevena Segala) i zostaną później fizycznie usunięte przez proces oczyszczania duchów (wierzę, że tak się nazywa). Mogą one jednak nie mieć związku z tym konkretnym pytaniem.
Strony duchów? Nie jestem pewien, czy jest to właściwy termin, ale czasami strony danych nie są usuwane, dopóki nie zostanie ODBUDOWANY Indeks klastrowany. To również stanowiłoby więcej stron niż DATALENGTHsuma. To na ogół nie powinno się zdarzyć, ale natknąłem się na to raz, kilka lat temu.
SPARSE kolumny: rzadkie kolumny oszczędzają miejsce (głównie dla typów danych o stałej długości) w tabelach, w których duży% wierszy NULLdotyczy jednej lub więcej kolumn. Ta SPARSEopcja powoduje, że NULLtyp wartości zwiększa się o 0 bajtów (zamiast normalnej kwoty o stałej długości, takiej jak 4 bajty dla an INT), ale wartości inne niż NULL zajmują dodatkowe 4 bajty dla typów o stałej długości i zmienną kwotę dla typy o zmiennej długości. Problem polega na tym, DATALENGTHże nie zawiera dodatkowych 4 bajtów dla wartości innych niż NULL w kolumnie SPARSE, więc te 4 bajty muszą zostać ponownie dodane. Możesz sprawdzić, czy są jakieś SPARSEkolumny przez:
SELECT OBJECT_SCHEMA_NAME(sc.[object_id]) AS [SchemaName],
OBJECT_NAME(sc.[object_id]) AS [TableName],
sc.name AS [ColumnName]
FROM sys.columns sc
WHERE sc.is_sparse = 1;
Następnie dla każdej SPARSEkolumny zaktualizuj oryginalne zapytanie, aby użyć:
SUM(DATALENGTH(FieldN) + 4)
Należy pamiętać, że powyższe obliczenia, aby dodać standardowe 4 bajty, są nieco uproszczone, ponieważ działają tylko dla typów o stałej długości. ORAZ istnieje dodatkowe metadane na wiersz (z tego, co do tej pory mogę powiedzieć), które zmniejszają przestrzeń dostępną dla danych, po prostu przez posiadanie co najmniej jednej kolumny SPARSE. Aby uzyskać więcej informacji, zobacz stronę MSDN dotyczącą użycia rzadkich kolumn .
Indeks i inne strony (np. IAM, PFS, GAM, SGAM itp.): Nie są to strony „danych” pod względem danych użytkownika. Będą to zawyżać całkowity rozmiar stołu. Jeśli używasz programu SQL Server 2012 lub nowszego, możesz użyć funkcji sys.dm_db_database_page_allocationsdynamicznego zarządzania (DMF), aby wyświetlić typy stron (mogą korzystać z wcześniejszych wersji programu SQL Server DBCC IND(0, N'dbo.table_name', 0);):
SELECT *
FROM sys.dm_db_database_page_allocations(
DB_ID(),
OBJECT_ID(N'dbo.table_name'),
1,
NULL,
N'DETAILED'
)
WHERE page_type = 1; -- DATA_PAGE
Ani DBCC INDnor sys.dm_db_database_page_allocations(z tą klauzulą WHERE) nie zgłosi żadnych stron indeksu i tylko DBCC INDta zgłosi co najmniej jedną stronę IAM.
DATA_COMPRESSION: Jeśli masz ROWlub PAGEKompresja włączona indeks klastrowany lub sterty, to można zapomnieć o większości z tego, co zostało wymienione do tej pory. 96-bajtowy nagłówek strony, tablica szczelin 2 bajtów na wiersz i 14 bajtów na wiersz informacji o wersji są nadal dostępne, ale fizyczna reprezentacja danych staje się bardzo złożona (o wiele bardziej niż to, co już wspomniano podczas kompresji nie jest używany). Na przykład dzięki kompresji wierszy program SQL Server próbuje użyć najmniejszego możliwego kontenera, aby dopasować każdą kolumnę dla każdego wiersza. Więc jeśli masz BIGINTkolumnę, która inaczej (zakładając, że nie SPARSEjest również włączona) zawsze zajmuje 8 bajtów, jeśli wartość wynosi od -128 do 127 (tj. 8-bitowa liczba całkowita ze znakiem), wówczas użyje tylko 1 bajtu, a jeśli wartość może zmieścić się wSMALLINT, zajmie tylko 2 bajty. Typy Integer, które są albo NULLalbo 0zajmują żadnego miejsca i są po prostu oznaczone jako NULLalbo „pusty” (tj 0) w odwzorowaniu tablicy poza kolumnami. I jest wiele, wiele innych zasad. Czy masz dane Unicode ( NCHAR, NVARCHAR(1 - 4000)ale nie NVARCHAR(MAX) , nawet jeśli są przechowywane w wierszu)? Kompresja Unicode została dodana w SQL Server 2008 R2, ale nie można przewidzieć wyniku wartości „skompresowanej” we wszystkich sytuacjach bez faktycznej kompresji, biorąc pod uwagę złożoność reguł .
Tak naprawdę, twoje drugie zapytanie, choć bardziej dokładne pod względem całkowitej fizycznej przestrzeni zajmowanej na dysku, jest naprawdę bardzo dokładne tylko po wykonaniu REBUILDindeksu klastrowanego. A potem musisz uwzględnić każde FILLFACTORustawienie poniżej 100. I nawet wtedy zawsze są nagłówki stron, a często wystarczająca ilość „zmarnowanej” przestrzeni, której po prostu nie można wypełnić, ponieważ jest zbyt mała, aby zmieścić się w dowolnym wierszu w tym tabela, a przynajmniej wiersz, który logicznie powinien iść w tym gnieździe.
Jeśli chodzi o dokładność drugiego zapytania przy określaniu „wykorzystania danych”, najbardziej sprawiedliwym wydaje się wycofanie bajtów nagłówka strony, ponieważ nie są one wykorzystaniem danych: są to koszty ogólne prowadzenia działalności. Jeśli na stronie danych znajduje się 1 wiersz, a ten wiersz to tylko jeden TINYINT, to ten 1 bajt nadal wymagał istnienia strony danych, a zatem 96 bajtów nagłówka. Czy ten 1 dział powinien zostać obciążony za całą stronę z danymi? Jeśli ta strona danych zostanie następnie wypełniona przez Dział 2, czy równomiernie podzielą ten „koszt ogólny”, czy zapłacą proporcjonalnie? Najłatwiej jest to po prostu wycofać. W takim przypadku użycie wartości 8mnożenia przeciwko number of pagesjest zbyt wysokie. Co powiesz na:
-- 8192 byte data page - 96 byte header = 8096 (approx) usable bytes.
SELECT 8060.0 / 1024 -- 7.906250
Dlatego użyj czegoś takiego jak:
(SUM(a.total_pages) * 7.91) / 1024 AS [TotalSpaceMB]
dla wszystkich obliczeń w kolumnach „liczba_stron”.
ORAZ , biorąc pod uwagę, że użycie DATALENGTHdla każdego pola nie może zwrócić metadanych dla wiersza, które należy dodać do zapytania dla tabeli, w którym otrzymujesz DATALENGTHdla każdego pola, filtrując według każdego „działu”:
- Typ rekordu i przesunięcie do NULL Bitmap: 4 bajty
- Liczba kolumn: 2 bajty
- Slot Array: 2 bajty (nieuwzględnione w „rozmiarze rekordu”, ale nadal należy uwzględnić)
- NULL Bitmap: 1 bajt na 8 kolumn (dla wszystkich kolumn)
- Wersji wiersza: 14 bajtów (jeśli baza danych ma albo
ALLOW_SNAPSHOT_ISOLATIONczy READ_COMMITTED_SNAPSHOTustawiony ON)
- Kolumna przesunięcia kolumny o zmiennej długości: 0 bajtów, jeśli wszystkie kolumny mają stałą długość. Jeśli którakolwiek kolumna ma zmienną długość, wówczas 2 bajty plus 2 bajty na każdą tylko kolumnę o zmiennej długości.
- Wskaźniki LOB: ta część jest bardzo nieprecyzyjna, ponieważ nie będzie wskaźnika, jeśli wartość jest
NULL, a jeśli wartość mieści się w wierszu, może być znacznie mniejsza lub znacznie większa niż wskaźnik, a wartość jest przechowywana poza wiersz, a następnie rozmiar wskaźnika może zależeć od ilości danych. Ponieważ jednak chcemy tylko oszacowania (tj. „Swag”), wydaje się, że 24 bajty to dobra wartość do wykorzystania (cóż, tak dobra jak każda inna ;-). To jest dla każdego MAXpola.
Dlatego użyj czegoś takiego jak:
Ogólnie (nagłówek wiersza + liczba kolumn + tablica boków + mapa bitowa NULL):
([RowCount] * (( 4 + 2 + 2 + (1 + (({NumColumns} - 1) / 8) ))
Ogólnie (wykrywaj automatycznie, jeśli dostępne są „informacje o wersji”):
+ (SELECT CASE WHEN snapshot_isolation_state = 1 OR is_read_committed_snapshot_on = 1
THEN 14 ELSE 0 END FROM sys.databases WHERE [database_id] = DB_ID())
JEŻELI istnieją kolumny o zmiennej długości, dodaj:
+ 2 + (2 * {NumVariableLengthColumns})
JEŚLI są jakieś MAXkolumny / LOB, dodaj:
+ (24 * {NumLobColumns})
Ogólnie:
)) AS [MetaDataBytes]
Nie jest to dokładne i znowu nie zadziała, jeśli masz włączoną kompresję wierszy lub strony w indeksie sterty lub klastra, ale zdecydowanie powinno cię to przybliżyć.
AKTUALIZACJA dotycząca tajemnicy 15% różnicy
My (w tym ja) byliśmy tak skoncentrowani na zastanowieniu się, jak układają się strony danych i jak DATALENGTHmogą wyjaśniać rzeczy, które nie poświęciliśmy dużo czasu na przeglądanie drugiego zapytania. Uruchomiłem to zapytanie dla pojedynczej tabeli, a następnie porównałem te wartości z tym, co było zgłaszane, sys.dm_db_database_page_allocationsi nie były to te same wartości dla liczby stron. Na przeczucie usunąłem funkcje agregujące GROUP BYi zastąpiłem SELECTlistę a.*, '---' AS [---], p.*. A potem stało się jasne: ludzie muszą uważać, skąd na tych mrocznych interwebach czerpią informacje i skrypty ;-). Drugie zapytanie zamieszczone w pytaniu nie jest dokładnie poprawne, szczególnie w przypadku tego konkretnego pytania.
Drobny problem: poza tym nie ma większego sensu GROUP BY rows(i nie ma tej kolumny w funkcji agregującej), ŁĄCZENIE pomiędzy sys.allocation_unitsi sys.partitionsjest technicznie niepoprawne. Istnieją 3 rodzaje Jednostek Alokacji, a jeden z nich powinien ŁĄCZYĆ się w inne pole. Dość często partition_idi hobt_idsą takie same, więc może nigdy nie być problemu, ale czasami te dwa pola mają różne wartości.
Główny problem: zapytanie wykorzystuje used_pagespole. To pole obejmuje wszystkie typy stron: dane, indeks, IAM itp., Tc. Jest jeszcze inny, bardziej odpowiednie pole do użycia, gdy dotyczy tylko rzeczywiste dane: data_pages.
Dostosowałem drugie zapytanie w pytaniu, mając na uwadze powyższe elementy, i używając rozmiaru strony danych, który wycofuje nagłówek strony. Usunąłem także dwa złączeń, które były niepotrzebne: sys.schemas(zastąpiony wywołaniu SCHEMA_NAME()) i sys.indexes(indeks Klastra jest zawsze index_id = 1i mamy index_idw sys.partitions).
SELECT SCHEMA_NAME(st.[schema_id]) AS [SchemaName],
st.[name] AS [TableName],
SUM(sp.[rows]) AS [RowCount],
(SUM(sau.[total_pages]) * 8.0) / 1024 AS [TotalSpaceMB],
(SUM(CASE sau.[type]
WHEN 1 THEN sau.[data_pages]
ELSE (sau.[used_pages] - 1) -- back out the IAM page
END) * 7.91) / 1024 AS [TotalActualDataMB]
FROM sys.tables st
INNER JOIN sys.partitions sp
ON sp.[object_id] = st.[object_id]
INNER JOIN sys.allocation_units sau
ON ( sau.[type] = 1
AND sau.[container_id] = sp.[partition_id]) -- IN_ROW_DATA
OR ( sau.[type] = 2
AND sau.[container_id] = sp.[hobt_id]) -- LOB_DATA
OR ( sau.[type] = 3
AND sau.[container_id] = sp.[partition_id]) -- ROW_OVERFLOW_DATA
WHERE st.is_ms_shipped = 0
--AND sp.[object_id] = OBJECT_ID(N'dbo.table_name')
AND sp.[index_id] < 2 -- 1 = Clustered Index; 0 = Heap
GROUP BY SCHEMA_NAME(st.[schema_id]), st.[name]
ORDER BY [TotalSpaceMB] DESC;