Czy umieszczenie plików dziennika transakcji na osobnym dysku ma przewagę wydajności?

Istnieje wiele postów na blogu i artykułów na temat najlepszych praktyk wychwalających zalety umieszczania pliku danych programu SQL Server na jednym dysku twardym i dziennika transakcji na innym. Podany powód jest taki, że plik bazy danych będzie doświadczał losowych odczytów i zapisów, podczas gdy dziennik transakcji będzie zawierał tylko zapisy sekwencyjne.

Ale co, jeśli masz setki baz danych? Czy istnieje prawdziwa korzyść z wydajności wynikająca z umieszczania setek plików dziennika transakcji na osobnym dysku? Jeśli zapisywanych jest wiele dzienników transakcji, to myślę, że zapis dziennika transakcji byłby tak samo losowy jak zapisywanie w bazie danych.

Poprawny. Teoretycznie, jeśli masz 100 DB, potrzebujesz 100 dysków, po jednym dla każdego dziennika. W praktyce jednak nie przejmujemy się takim przypadkiem, ponieważ mając 100 zestawów DB, oczywiście nie oczekujesz najwyższej wydajności TPC dla każdego DB. Prawdopodobnie będziesz mieć DB z wysoką przepustowością i rygorystycznymi umowami SLA i możesz je mieć na osobnych wrzecionach, podczas gdy wiele SLA niższego poziomu tłoczy się na kilku współdzielonych dyskach.

Dziennik transakcji jest dokładnie tym ... dziennikiem wszystkich transakcji. Jako taki wypełnia się stale w jednym kierunku, aż do punktu kontrolnego i nadpisanego lub obciętego i nadpisanego. Nadpisywanie jest sekwencyjne.

Rozważ swoje pliki danych. Rekord klienta może zawierać zamówienie sprzed 5 lat i jeden sprzed 10 lat. Jeśli usuniesz te zamówienia (powiedzmy, że archiwizujesz dane w innym miejscu), w bieżącym dzienniku masz 1 i kolejno 2. Jednak w plikach danych dotknąłeś bloków, które zostały napisane 5 i 10 lat temu.

To na pewno ma znaczenie :)

Samo umieszczenie dziennika transakcji na oddzielnym dysku zapewni lepszą wydajność niż umieszczenie go razem z innymi plikami danych / dzienników. Jeśli umieścisz wszystkie dzienniki na tym samym dysku, tak naprawdę nie wykonujesz sekwencyjnego We / Wy, ale losowe We / Wy między tymi plikami dziennika.