Projekt dużej tabeli SQL

Mam ogólne pytanie dotyczące projektowania tabel w programie SQL Server 2008. Obecnie mamy stolik, który ma ponad 600 GB i rośnie o około 3 GB dziennie. Ta tabela ma odpowiednie indecies, ale staje się poważnym zawieszeniem podczas uruchamiania zapytań i tylko ze względu na jego rozmiar. Pytanie brzmi: czy mam podzielić tabelę na wiele tabel według roku i miesiąca (pasowałoby to do sposobu, w jaki inne działy dzielą ich duże zestawy danych), czy też powinniśmy wykorzystać partycjonowanie wbudowane w SQL Server. Wygląda na to, że użycie partycjonowania wymagałoby mniej zmian kodu. Z tego, co przeczytałem podczas partycjonowania, wciąż tylko odpytujesz jedną tabelę, a serwer zajmuje się sposobem uzyskiwania danych. Gdybyśmy wybrali trasę z wieloma tabelami, musielibyśmy poradzić sobie z pobieraniem danych z wielu tabel.

„Ta tabela zawiera odpowiednie nieprzyzwoitości, ale staje się poważnym problemem podczas uruchamiania zapytań”

Samo partycjonowanie nie poprawia wydajności zapytania, chyba że SQL Server jest w stanie wyeliminować partycje podczas uruchamiania zapytania. Twoja klauzula WHERE musi być zgodna ze sposobem partycjonowania. Otrzymujemy tylko jedno pole do użycia jako pole partycjonowania, więc jeśli to pole nie jest uwzględnione w klauzuli WHERE, nadal istnieje prawdopodobieństwo przeskanowania całej tabeli, pomimo posiadania partycji.

„i tylko ze względu na jego rozmiar”.

Partycjonowanie może ułatwić pewne operacje konserwacyjne, ale wciąż są rzeczy, których nie możemy zrobić dla poszczególnych partycji. Jeśli problemy z utrzymaniem indeksu i aktualizacjami statystyk powodują problemy, lepiej podzielić projekt na tabelę archiwum i tabelę aktualizowaną na bieżąco. Gdy musisz okresowo przenosić dane z tabeli na żywo do tabeli archiwum, robisz to, odbudowujesz indeksy przy współczynniku wypełnienia 100%, aktualizujesz statystyki przy pełnym skanowaniu, a następnie ustawisz jej grupa plików na tylko do odczytu. Partycjonowanie może pomóc w ładowaniu tabeli archiwum - ale partycjonowanie tabeli na żywo może nie. (Rzucam tutaj kilka zaawansowanych koncepcji, jakby to było szybkie i proste, ale szkicuję tutaj trochę tła.)

„Wygląda na to, że użycie partycjonowania wymagałoby mniej zmian w kodzie”.

Trochę tak - na pierwszy rzut oka wygląda to w ten sposób, ale im bardziej się w to zagłębia, masz opcje takie jak widoki podzielone na partycje. Możesz zmienić nazwę istniejącej tabeli, umieścić widok w jej miejscu, a następnie możesz wprowadzić własne zmiany w tabelach leżących u podstaw (i dodać wiele tabel) bez zmiany aplikacji.

Pisałem więcej o pułapkach partycjonowania tutaj:

http://www.brentozar.com/archive/2008/06/sql-server-partitioning-not-the-answer-to-everything/

Podział na partycje w izolacji może być wystarczający, ale można uzyskać lepsze wyniki, łącząc się z widokami podzielonymi na partycje i wieloma tabelami. To bardzo zależy od wzorca zapytań i wzrostu.

Obecne ograniczenie partycjonowania polega na tym, że statystyki kolumn są utrzymywane tylko w tabeli, a nie na poziomie partycji. Jeśli masz wzorzec zapytań, który korzystałby z dokładniejszych statystyk, połączenie partycjonowania tabeli z widokami podzielonymi na partycje może przynieść znaczące korzyści w zakresie wydajności.

Tam, gdzie charakter twoich danych zmienia się z miesiąca na miesiąc, z roku na rok, pomocne mogą być również widoki podzielone na partycje. Wyobraź sobie sprzedawcę, który ciągle zmieniał swoje linie produktów, tak że zakresy produktów Product.ProductId są stosowane z roku na rok. Dzięki pojedynczej tabeli kolejności / szczegółów zamówienia, a tym samym pojedynczemu histogramowi statystyk, statystyki niewiele oferują optymalizatorowi zapytań. Tabela na rok (Order_2010, Order_2011, OrderLine_2010, OrderLine_2011) podzielona na partycje w połączeniu z widokami podzielonymi na partycje (Order, OrderLine) zapewni optymalizatorowi bardziej szczegółowe i potencjalnie przydatne statystyki.

Możesz wprowadzić partycjonowanie tabel przy stosunkowo niewielkim wysiłku, więc zacznij od tego, zmierz wpływ, a następnie oceń, czy widoki podzielone na partycje byłyby warte dodatkowego wysiłku.

Kimberly Tripp opublikowała wiele wskazówek i białej księgi na temat partycjonowania, które są ogólnie uważane za konieczne do przeczytania na ten temat. Kendra Little ma również dobry materiał i przydatną listę referencyjną innych artykułów

Wydajność jest zwykle najważniejszym powodem, dla którego ludzie chcą partycjonować. Osobiście uważam, że poprawa czasu odzyskiwania jest taka sama lub większa w przypadku VLDB. Poświęć trochę czasu na zrozumienie częściowej dostępności i częściowego przywracania, zanim zaczniesz, ponieważ może to wpłynąć na przyjęte podejście.

Jeśli nie masz idealnego, ale nierzadkiego procesu wysyłania kopii zapasowych przez sieć, możesz oczekiwać 3-godzinnego czasu przywracania aktualnego 600 GB. W roku, w którym przekroczyłeś 1,5 TB, masz problem.

Jak powiedziałeś, masz tutaj dwie opcje:

1. Wykorzystaj wiele tabel
2. Wykorzystaj partycjonowanie

Za pomocą 1 możesz utworzyć WIDOK, który łączy wszystkie te tabele razem, i po prostu zaktualizuj go, aby zawierał nowo utworzone tabele. Uważam to za sposób na emulację partycjonowania. Zalety tej metody obejmują niewymaganie wersji Enterprise Edition programu SQL Server.

Za pomocą 2 możesz wyrównać indeksy do partycji i wyrównać partycje do innej pamięci. Po skonfigurowaniu funkcji partycji i schematu partycji jest to wykonywane po podzieleniu lub scaleniu partycji. Zalety tej metody to brak konieczności ręcznego przenoszenia rekordów do nowej tabeli. Ponieważ funkcja i schemat partycji obsługują to za Ciebie. Co więcej, jak powiedziałeś, dostęp do danych jest niewielki lub żaden nie wymaga zmiany kodu.

Jeśli masz wersję Enterprise, zdecydowanie dałbym wygląd partycjonowania. Pomimo złożonego wyglądu, naprawdę nie jest tak źle. Jeśli nie, partycjonowanie nie jest dla ciebie nawet opcją.

Tworzenie tabel podzielonych na partycje

Modyfikowanie tabel podzielonych na partycje

Projektowanie partycji do zarządzania podzbiorami danych

Mam nadzieję że to pomoże,

Matt

Z twojego pytania wydaje się, że przechowujesz dane historyczne (logi), a twoje ograniczenia wydają się wynikać z szybkości zapytań, a nie problemów z miejscem do przechowywania. Dla mnie partycja nie pomoże.

Kiedy mówisz, że masz odpowiednie indeksy, czy zawiera ono indeks w polu daty? Miałem dobre wyniki używając indeksu na trunc (datownik, dzień) z Postgres. Następnie musisz upewnić się, że wszystkie zapytania wybiorą dzień przed jakąkolwiek inną manipulacją. Uważaj, znacznik czasu z polem strefy czasowej nie jest indeksowalny (ponieważ „przesuwa się” w zależności od strefy czasowej), dlatego do indeksowania potrzebujesz „ustalonego” znacznika czasu.