Projekt bazy danych SQL Server dla „zarchiwizowanych, ale dostępnych” danych

Mamy tę dużą bazę danych (> 1 TB), którą zamierzamy „zmniejszyć”. Baza danych obraca się wokół jednej głównej jednostki, nazwijmy ją „Odwiedź”. Do dyskusji powiedzmy, że jest to baza danych praktyki medycznej.

Istnieje w sumie 30 „rodzajów wizyt”, takich jak procedura, coroczna, kontrolna, immunizacyjna itp., Z których każdy stanowi tabelę pomocniczą do „wizyty”, np. „Visit_immuno”.

Baza danych zgromadziła około 12 lat danych od 2000 r. Ktoś zaproponował, aby przechowywać około 3 lat danych w wersji „na żywo”, a resztę - w bazie danych „old_data”. Data jest przechowywana TYLKO w tabeli „Odwiedziny”, ponieważ jest znormalizowana. Tabela odwiedzin zawiera także ROWVERSIONkolumnę i kolumnę BIGINTpseudo-tożsamości (klastrowaną). Dla wszystkich celów i założeń, powiedzmy, klucz klastrowania jest wypełniany przez SEKWENCJĘ (SQL Server 2012 Enterprise) - nazwiemy go cid.

visit.dateNie zawsze w tej samej kolejności, jako klucz grupowania, na przykład gdy lekarz idzie na dłuższych wizyt i zwrotów ze swojej „teczki” danych, zostaje włączony do głównej tabeli. Istnieją również pewne aktualizacje tabeli „odwiedzin”, które powodują, że ROWVERSIONkolumna nie jest zsynchronizowana zarówno z kolumnami, jak cidi date- po prostu, ani ROWVERSIONnie cidstworzą odpowiednich kluczy partycji z tego powodu.

Zasadą biznesową usuwania danych z „na żywo” jest to, że visit.datemuszą one trwać dłużej niż 36 miesięcy ivisit_payment musi istnieć rekord potomny . Ponadto baza danych „old_data” nie zawiera żadnych tabel podstawowych oprócz visit%.

W rezultacie otrzymujemy:

Live DB (codzienne użytkowanie) - Wszystkie tabele Old-Data DB - starsze dane dla visit%tabel

Propozycja wymaga połączonej bazy danych, która jest powłoką zawierającą synonimy dla WSZYSTKICH tabel bazowych Live DB(oprócz visit%) oraz widoków, które UNIONUJĄ WSZYSTKO w visit%tabelach w dwóch bazach danych.

Zakładając, że te same indeksy są tworzone w Old-DataDB, będzie dobrze wykonywać zapytania o Unii wszystko Wyświetleń ? Jakiego rodzaju wzorce zapytań mogą wyzwolić plan wykonania dla widoków UNION-ALL ?

Dla wygody załóżmy, że wywoływana jest baza danych na żywo LiveDbi wywoływana jest baza danych AchiveArchiveDb

• Dodaj widok UNION ALL LiveDbwskazując na tabele w ArchiveDbbazie danych za pomocą synonimu (nie ma potrzeby wykonywania połączonej bazy danych z synonimami)
• „Podziel na partycje” visit.datei zdenormalizuj tę kolumnę visit_paymentsrównież, jeśli jeszcze jej tam nie ma (poprawia to wydajność łączenia kolokacji)
• Archiwizuj tylko dwa duże tabele, jeśli to możliwe (zmniejsza ryzyko zadziałania optymalizatora). Zachowaj widok UNION ALL i pozostałe tabele, LiveDbaby wszystkie połączenia do mniejszych tabel były lokalne
• Dodaj ograniczenie Sprawdź na stołach w obu LiveDbi ArchiveDb który opisuje zakres visit.datezawartych w tabeli. Pomaga to optymalizatorowi wyeliminować tabelę archiwum zarówno z wyszukiwań, jak i skanów zawierających kolumnę visit.data. Będziesz musiał okresowo aktualizować to ograniczenie.
• W widoku UNION ALL dodaj kryterium GDZIE, które będzie filtrowane visit.data. Jest to dodatek do podpowiedzi, którą już podałeś w ograniczeniu sprawdzania. Maksymalizuje to ryzyko zepchnięcia filtrów
• Jeśli masz EE, podziel tabelę na partycje w bazie danych archiwum (ale NIE w bazie danych na żywo). Jeśli chcesz być naprawdę fantazyjny, użyj kopii zapasowej / przywracania archiwalnych baz danych na poziomie grupy plików, aby zaoszczędzić na czasach tworzenia kopii zapasowych.
• Rozważ włączenie AchiveDbtrybu odzyskiwania SIMPLE, jeśli jeszcze nie jest. Kopie zapasowe dziennika transakcji prawdopodobnie nie będą potrzebneArchiveDb
• Użyj WSTAW ... Z (TABLOCK) WYBIERZ ... Z (ROWLOCK), aby wymusić minimalne logowanie do miejsca docelowego podczas przenoszenia danych pomiędzy LiveDbiArchiveDb

Wszystkie powyższe nie gwarantują, że optymalizator wyeliminuje tabele archiwum z wyszukiwań i skanów, ale zwiększa to prawdopodobieństwo.

Kiedy eliminacja nie nastąpi. Są to efekty, które możesz zobaczyć (ta lista może być niekompletna). W przypadku poszukiwań otrzymasz dodatkowe wyszukiwanie dla każdego zapytania (powoduje to wzrost liczby operacji we / wy na sekundę). W przypadku skanów wyniki mogą mieć katastrofalny wpływ na wydajność, ponieważ możesz skończyć skanowaniem zarówno archiwum, jak i tabel na żywo. Oto typowe sposoby uruchomienia optymalizatora:

• Jeśli połączysz visit%tabele razem i nie uwzględnisz visit.datakryteriów łączyć (dlatego chcesz denormalizować). Z tego powodu możesz zmodyfikować niektóre swoje zapytania
• Jeśli uzyskasz łączenie skrótowe między visit.datainną tabelą (na przykład wymiar daty), możesz nie uzyskać właściwej eliminacji tabel
• Jeśli spróbujesz agregować dane w zarchiwizowanych tabelach
• Jeśli odfiltrujesz coś, ALE visit.data, na przykład, szukaj bezpośrednio na kluczu widoku.

W ostatnim scenariuszu możesz uchronić się przed najgorszymi efektami, dodając kolejne ograniczenie sprawdzania cid- jeśli jest to możliwe. Wspomniałeś, że sekwencja cidnie „czysta” w odniesieniu do dat i postępu wierszy w tabeli. Czy możesz jednak utrzymać tabelę zawierającą informację: „Od cidtego numeru nie ma żadnej liczby visit.data” lub podobny? Może to spowodować dodatkowe ograniczenie.

Kolejną rzeczą, na którą należy uważać, jest to, że równoległe zapytania mogą spawnować DUŻO więcej wątków po zapytaniu o widok podzielony na partycje (ponieważ obie „pod-tabele” będą narażone na te same równoległe optymalizacje). Z tego powodu możesz chcieć ograniczyć MAXDOP na serwerze lub równoległe zapytania.

Nawiasem mówiąc, jeśli dobrze znasz zapytania - możesz nawet nie potrzebować tych samych indeksów w dwóch bazach danych (zakłada to, że masz 100% pewności, że uzyskasz odpowiednią eliminację tabel). Możesz nawet rozważyć użycie magazynu kolumn dla ArchiveDb.

Sposób, w jaki to zrobiliśmy, to zapisywanie starych danych partiami do nowo utworzonej bazy danych i usuwanie starych danych z bazy danych na żywo. W ten sposób oba db są dostępne. Możesz także wykonać kopię zapasową nowo utworzonej bazy danych i przywrócić ją w innym miejscu, aby usunąć duży ślad z serwerów produkcyjnych. Mam nadzieję, że jest to akceptowalne rozwiązanie dla twoich potrzeb.