Czy w swoich szacunkach wielkości uwzględniłeś ilość miejsca zajmowanego przez indeksy? Również jeśli masz pola tekstowe, które są ustawione jako wielobajtowe ( N[VAR]CHARzamiast [VAR]CHAR), a pliki wejściowe to UTF-8 lub zwykły jeden bajt na znak, zwiększy to twoje wymagania dotyczące pamięci nawet dwa razy. Ponadto pamiętaj, że jeśli masz klastrowany klucz / indeks w tabeli, rozmiar tego wpływa na wszystkie inne indeksy w tabeli, ponieważ zawierają one wartość klucza klastrowego dla każdego wiersza (tak by dać skrajny przykład, jeśli tabela ma NCHAR (10). ) w przypadku, gdy zrobiłaby to INT i to jest twój klastrowany klucz / indeks, nie tylko używasz dodatkowych 16 bajtów na wiersz na stronach danych, ale również marnujesz 16 bajtów na wiersz w każdym innym indeksie w tej tabeli ) .
Ponadto część miejsca zostanie przydzielona, ale nieużywana, albo dlatego, że silnik DB zostawił trochę miejsca przydzielonego po usunięciu, aby można go było szybko wykorzystać do nowych danych w tej tabeli lub ponieważ wzorzec wstawiania i usuwania pozostawił wiele stron tylko część pełny.
Możesz uruchomić:
SELECT o.name
, SUM(ps.reserved_page_count)/128.0 AS ReservedMB
, SUM(ps.used_page_count)/128.0 AS UsedMB
, SUM(ps.reserved_page_count-ps.used_page_count)/128.0 AS DiffMB
FROM sys.objects o
JOIN sys.dm_db_partition_stats ps ON o.object_id = ps.object_id
WHERE OBJECTPROPERTYEX(o.object_id, 'IsMSShipped') = 0
GROUP BY o.name
ORDER BY SUM(ps.reserved_page_count) DESC
aby szybko sprawdzić, które stoliki zajmują miejsce.
Również EXEC sp_spaceuseduruchomienie w ramach tej DB zwróci dwa zestawy wyników. Pierwsza pokazuje całkowitą przestrzeń przydzieloną w systemie plików dla plików danych i ile z nich jest nieprzydzielone, druga pokazuje, ile przydzielonej przestrzeni jest wykorzystywane na strony danych, strony indeksu lub jest aktualnie nieużywane.
sp_spaceused zwróci również przestrzeń używaną przez dany obiekt, więc możesz zapętlić to, aby zbudować tabelę do analizy:
-- TEMP TABLES FOR ANALYSIS
CREATE TABLE #tTables (sName NVARCHAR(MAX), iRows BIGINT, iReservedKB BIGINT, iDataKB BIGINT, iIndexKB BIGINT, iUnusedKB BIGINT)
CREATE TABLE #tTmp (sName NVARCHAR(MAX), iRows BIGINT, sReservedKB NVARCHAR(MAX), sDataKB NVARCHAR(MAX), sIndexKB NVARCHAR(MAX), sUnusedKB NVARCHAR(MAX))
-- COLLECT SPACE USE PER TABLE
EXEC sp_msforeachtable 'INSERT #tTmp EXEC sp_spaceused [?];'
-- CONVERT NUMBER-AS-TEXT COLUMNS TO NUMBER TYPES FOR EASIER ANALYSIS
INSERT #tTables SELECT sName, iRows
, CAST(REPLACE(sReservedKB, ' KB', '') AS BIGINT)
, CAST(REPLACE(sDataKB , ' KB', '') AS BIGINT)
, CAST(REPLACE(sIndexKB , ' KB', '') AS BIGINT)
, CAST(REPLACE(sUnusedKB , ' KB', '') AS BIGINT)
FROM #tTmp
DROP TABLE #tTmp
-- DO SOME ANALYSIS
SELECT sName='TOTALS', iRows=SUM(iRows), iReservedKB=SUM(iReservedKB), iDataKB=SUM(iDataKB), iIndexKB=SUM(iIndexKB), iUnusedKB=SUM(iUnusedKB) FROM #tTables ORDER BY sName
SELECT * FROM #tTables ORDER BY iReservedKB DESC
-- CLEAN UP
DROP TABLE #tTables
Powyższy kod wyświetli wszystkie rozmiary tabel na jednej liście plus jeden wiersz dla sum. W razie potrzeby możesz użyć różnych widoków systemu (takich jak sys.objectsi sys.dm_db_partition_statsużywanych w pierwszym zapytaniu powyżej, zobacz http://technet.microsoft.com/en-us/library/ms177862.aspx, aby uzyskać więcej szczegółów), aby uzyskać więcej szczegółów, takich jak miejsce używane przez każdy indeks.
W pliku danych znajdują się trzy klasy nieużywanego miejsca:
- To, co nie jest przypisane do niczego (pokazuje to w pierwszym zestawie wyników
sp_spaceusedbez określonego obiektu)
- To, co jest przydzielone do obiektu (zarezerwowane), ale nie jest aktualnie używane (pokazuje to wynik „nieużywany” w
sp_spaceuseddanych wyjściowych.
- Zamknięty na częściowo używanych stronach (będzie to wyglądać na wykorzystane, ponieważ wszystko jest podzielone na pojedyncze strony, jedna strona ma długość 8192 bajtów). Jest to trudniejsze do wykrycia / obliczenia. Wynika to z połączenia dwóch czynników:
- Podziel strony. W miarę dodawania danych często kończą się puste strony (silnik pamięci może zawsze znormalizować zawartość strony, ale byłoby to bardzo nieefektywne), a po usunięciu wierszy zawartość strony nie jest automatycznie pakowana (ponownie mogą być, ale dodatkowe Obciążenie I / O jest generalnie dalekie od wartości).
- Mechanizm pamięci masowej nie podzieli wiersza na wiele stron (wraz z rozmiarem strony, z którego pochodzi 8 192 bajtów na wiersz). Jeśli wiersze mają stały rozmiar i zajmują 1100 bajtów, to „zmarnujesz” co najmniej 492 bajty każdego bloku danych przypisanego do tej tabeli (7 wierszy zajmuje 7700 bajtów, a 8 nie pasuje, więc pozostałe bajty wygrały ” do użycia). Im szersze rzędy, tym może być gorzej. Tabele / indeksy z wierszami o zmiennej długości (które są znacznie częstsze niż wiersze o całkowicie ustalonej długości) generalnie są lepsze (ale trudniej je obliczyć).
Kolejnym zastrzeżeniem są tutaj duże obiekty ( TEXTkolumny,[N]VARCHAR(MAX) wartości powyżej określonego rozmiaru i tak dalej), ponieważ są umieszczane poza stroną, po prostu biorąc 8 bajtów w głównym wierszu danych, aby trzymać wskaźnik do danych w innym miejscu), więc może przekroczyć limit 8192 bajtów na wiersz.
tl; dr: Szacowanie oczekiwanych rozmiarów baz danych może być o wiele bardziej zaangażowane, niż początkowo zakładać.