Najlepszy sposób na defragmentację / kompaktowanie bazy danych do celów archiwalnych

Mamy instancję programu SQL Server używaną do archiwizacji wiadomości e-mail (dzięki pakietowi do archiwizacji innej firmy). Co jakiś czas oprogramowanie jest przenoszone do nowej pustej bazy danych. W przeszłości robiliśmy to co kwartał, ale teraz chcemy to robić co miesiąc. Ilość archiwizowanych danych wynosi około 15–20 GB miesięcznie, a większość danych znajduje się tylko w kilku tabelach (zwykle 2–4).

Gdy przejdziemy do nowej bazy danych, stara zostanie użyta wyłącznie do odczytu. Chciałbym zoptymalizować go do ładnego, ciasnego pliku danych, ze wszystkimi tabelami / indeksami sąsiadującymi i mającymi bardzo wysoki współczynnik wypełnienia oraz niewiele pustej przestrzeni na końcu pliku danych. Ponadto używamy Wersji Standardowej na tym serwerze, ze wszystkimi wynikającymi z tego ograniczeniami (w przeciwnym razie użyłbym już kompresji danych).

Kilka możliwości, o których mogę myśleć:

Indeksy REBUILD / REORGANIZE, DBCC SHRINKFILE (OK, nie jest to rozsądna opcja, ponieważ DBCC SHRINKFILE podzieli sikanie na wszystko, czego dotknie, ale włączam to dla kompletności.)
Utwórz nową bazę danych z wyłączonymi automatycznymi statystykami. Skrypt i odtwórz wszystkie tabele ze źródłowej bazy danych. Użyj programu bcp, aby wyeksportować / zaimportować dane do nowej bazy danych w kolejności klucza klastra. Skrypt i odtwórz wszystkie indeksy. Przelicz wszystkie statystyki przy pełnym skanie.
Utwórz nową bazę danych z wyłączonymi automatycznymi statystykami. Skrypt i odtwórz wszystkie tabele ze źródłowej bazy danych. Użyj SSIS lub T-SQL, aby przesłać dane do nowej bazy danych. Skrypt i odtwórz wszystkie indeksy. Przelicz wszystkie statystyki przy pełnym skanie.

Ostatnim krokiem w każdym przypadku byłoby ustawienie bazy danych w tryb tylko do odczytu.

Jakie są inne dobre / lepsze opcje, aby to zrobić? Moim problemem jest przeniesienie danych w taki sposób, aby zachować wysoki współczynnik wypełnienia i logicznie ciągły.

Edytować:

Powinienem wspomnieć, że około 75% danych wydaje się być przechowywanych w kolumnach obrazu (LOB).

sql-server sql-server-2012

— db2
źródło

Czy obchodzi Cię (lub aplikacja), czy tabele fizycznie kończą się w grupie plików innej niż PRIMARY?

— Jon Seigel

@JonSeigel Chyba nie, a tak naprawdę to całkiem niezły pomysł, ponieważ zaoszczędziłoby mi to konieczności tworzenia bazy danych szablonów i przenoszenia wszystkich danych.

— db2

Zastanawiasz się tylko nad rozwiązaniami, które sam kodujesz, czy możesz też przejrzeć jakąś aplikację, która Ci w tym pomoże? Możesz użyć SQL Storage Compress firmy RedGate do kompresji danych na żywo. Lub możesz spróbować Virtual Restore, aby skompresowane kopie zapasowe były dostępne jako dbs online (bez konieczności posiadania kompletnego miejsca). Wszystkie są oparte na starszym sterowniku plików systemu Windows Hyperbac, który bardzo dobrze kompresuje dane na żywo i kopie zapasowe.

— Marian

@Marian Brzmi interesująco, ale na razie chciałbym trzymać się natywnych możliwości SQL Servera. Muszę po prostu bardzo skutecznie zdefragmentować bazy danych, bez pozostawienia całej ilości nieużywanego miejsca w plikach. Jeśli jest to narzędzie innej firmy, które wykonuje pracę zamiast ręcznie pisać skrypty, nie ma sprawy.

— db2

To tylko myśl, ale dlaczego nie utworzyć nowej grupy plików, dodać pliku, ustawić rozsądny wzrost (powiedzmy 500 MB), a następnie odbudować tabele na tej nowej grupie plików. Następnie zmniejsz plik podstawowy do prawie zera. Nie będzie cię obchodzić ani odrobina fragmentacji tabel systemowych.

— Nic,

Odpowiedzi:

Aby wyeliminować fizyczne rozdrobnienie plików, możesz przenieść indeks klastrowy z upuszczeniem istniejącym do nowej grupy plików. Ponieważ mają być RO, wszystkie wypełnią 100%, ponieważ nie wymaga miejsca na wstawki, podziały stron spowodowane aktualizacjami.

Pozwoliłoby to również na częściowe przywracanie i bardzo szybkie przejście do bazy danych online, jeśli kiedykolwiek zdecydujesz się na Enterprise. Enterprise pozwala także na indeksy magazynu kolumn, co znacznie skraca czas zapytania dla danych tylko do odczytu, które są masywnym zaokrągleniem.

Możesz użyć opcji shrinkfile raz przed przejściem do odczytu tylko bez poważnych problemów z fragmentacją, aby usunąć miejsce na końcu pliku, jak chcesz.

Na marginesie, po prostu sprawdzanie, czy używasz najnowszych typów danych dla swojego LOBS. tj. nvarchar (max) czy varchar (max) zamiast ntext lub text, varbinary (max) zamiast image?

— Zniszczone dobra
źródło

Niestety używa głównie tekstu i obrazu. Jest to aplikacja innej firmy, więc nie mam możliwości jej zmiany.

— db2

@ jest naprawdę przezroczysty dla aplikacji, a serwer SQL przechowuje informacje w wierszu, jeśli <8k. Jeśli sprzedawca stwierdzi, że nie jest obsługiwany, zapytam go, dlaczego nadal używają typów danych, które były pierwotnie nieaktualne w SQL Server 2005!

— DamagedGoods

Nie mogę być całkowicie pewien, że aplikacja nie robi rzeczy specyficznych dla tekstu / obrazu, takich jak WRITETEXT, które zawiodłyby po zmianie typu danych. Ale wracając do głównego punktu, wygląda na to, że odtworzenie indeksu klastrowego nie spowoduje przeniesienia danych LOB wraz z nim.

— db2

możesz to zrobić, ale musisz przejść do projektanta w interfejsie GUI, a następnie rozwinąć właściwości, a następnie masz „zwykłą przestrzeń danych”, ale także grupę plików TEXTIMAGE, zmieniając to, ale bądź ostrożny, to odtworzy tabelę! oczywiście możesz to

— napisać

Rozumiem, może to być przydatny sposób na wygenerowanie przynajmniej odpowiednich skryptów odbudowujących.

— db2

Napotkałem podobny problem z narzędziem strony trzeciej, które również używało typu danych obrazu do przechowywania nieustrukturyzowanych danych, i rozwiązałem go, przekształcając kolumnę w strumień plików . Będziesz musiał przeprowadzić pewne testy, aby upewnić się, że aplikacja nadal działa zgodnie z oczekiwaniami, ale da ci to możliwość napisania własnego procesu archiwizacji, który skutecznie przenosi dane do bazy danych archiwum.

— Liam Confrey
źródło

Podejrzewam, że strumień plików nie byłby dobrze skalowany w tym przypadku. Mamy ponad 14 milionów wierszy w 17 bazach danych i odbieramy wiadomości około 15 000 dziennie. Znaczna część treści komunikatów ma mniej niż 4 KB, więc marnotrawstwo klastra NTFS byłoby prawdopodobnie brutalne (i to nawet jeśli dodamy nowy wolumin dysku o rozmiarze bloku mniejszym niż 64 KB).

— db2

W takim przypadku, czy możesz przekonwertować typ danych na coś takiego jak nvarchar (max) i użyć klauzuli TEXTIMAGE_ON, aby określić inną grupę plików dla tych dużych obiektów? To pozwoli ci przechowywać dane poza wierszem i pozwoli ci stworzyć własny proces zarządzania archiwizacją.

— Liam Confrey,

użycie strumienia plików naprawdę zależy od tego, jak duże są LOBS. Myślę, że należy wziąć pod uwagę> 1 MB na rekord. Więc zgodziłbym się w tym przypadku, że nie jest to opcja

— DamagedGoods