Wewnętrzne kopie zapasowe - Co się dzieje, gdy uruchomione jest zadanie tworzenia kopii zapasowej - pod względem blokowania i narzutu wydajności w SQL Server?

W przypadku MySQL wiem, że kopia zapasowa bazy danych jest wykonywana tabela po tabeli w instrukcjach SQL, co powoduje blokowanie, a jeśli zaktualizujesz kolumny podczas tworzenia kopii zapasowej, możesz mieć problemy z integralnością.

W moim rozumieniu nie dotyczy to Microsoft SQL Server, ale jak SQL Server sobie z tym radzi? Czy jest jakieś wewnętrzne zamrożenie, aby zachować spójność bazy danych?

Słyszałem również, że tworzenie kopii zapasowych jest jednowątkowe, co oznacza, że ​​używa tylko jednego rdzenia, zakładając, że wykonujesz kopię zapasową w jednym pliku. Zakładając również, że masz maszynę wielordzeniową, na przykład 16 rdzeni lub co najmniej znacznie większą liczbę niż jeden.

Z mojego osobistego doświadczenia nigdy nie miałem problemów podczas wykonywania kopii zapasowych, ani problemów z blokowaniem, ani narzutami, ale moje doświadczenie jest ograniczone. Dlatego zawsze zalecam włączenie kompresji kopii zapasowej we właściwościach serwera.

Co się dzieje, gdy uruchomione jest zadanie tworzenia kopii zapasowej? A także czy istnieją znaczące różnice dla różnych wersji? na przykład 2008,2012 i 2014 (nie licencje).

Wszystkie twoje punkty są zapisane w mitach zapasowych - autorstwa Paula Randala

30-01) operacje tworzenia kopii zapasowych powodują blokowanie

Nie. Operacje tworzenia kopii zapasowych nie blokują obiektów użytkowników . Kopie zapasowe powodują naprawdę duże obciążenie odczytu w podsystemie we / wy, więc może wyglądać, jakby obciążenie zostało zablokowane, ale tak naprawdę nie jest. To tylko spowolnienie. Istnieje szczególny przypadek, w którym kopia zapasowa, która musi pobierać masowo rejestrowane zakresy, przyjmuje blokadę pliku, która może zablokować operację punktu kontrolnego - ale DML nigdy nie jest blokowany.

Słyszałem również, że tworzenie kopii zapasowych jest jednowątkowe, co oznacza, że ​​używa tylko jednego rdzenia, zakładając, że wykonujesz kopię zapasową w jednym pliku.

Kopia zapasowa po wykonaniu jednego pliku lub urządzenia będzie korzystać z 1 wątku zapisującego. Więc jeśli tworzysz kopie zapasowe na wielu plikach / urządzeniach (tak, że wiele plików .bak) będzie miało jeden wątek zapisujący na plik / urządzenie.

Najłatwiejszym sposobem na poprawę wydajności tworzenia kopii zapasowej jest umożliwienie równoległego wykonywania operacji tworzenia kopii zapasowej, co jest znane jako tworzenie kopii zapasowej. Domyślnie jest jeden wątek czytnika danych dla każdej litery dysku lub punktu montowania, z którego są odczytywane, i jeden wątek zapisujący dane dla każdego zapisywanego urządzenia kopii zapasowej.

Czek

1. SQL Server 2008 Microsoft Certified Master (MCM) Filmy wideo dotyczące gotowości, w szczególności wewnętrzne kopie zapasowe.
2. Spojrzenie na wewnętrzne kopie zapasowe i jak śledzić wydajność tworzenia kopii zapasowych i przywracania (część 1) - autor: Jonathan Kehayias
3. Spojrzenie na wewnętrzne kopie zapasowe i sposób śledzenia wydajności tworzenia kopii zapasowych i przywracania (część 2) - autor: Jonathan Kehayias

Artykuł napisany przez Paula dotyczący wewnętrznych kopii zapasowych jest doskonały i musisz go przeczytać. Dodanie do tego, co powiedzieli inni i podkreślenie określonej części pytania

Słyszałem również, że tworzenie kopii zapasowych jest jednowątkowe, co oznacza, że ​​używa tylko jednego rdzenia, zakładając, że wykonujesz kopię zapasową w jednym pliku. Zakładając również, że masz maszynę wielordzeniową, na przykład 16 rdzeni lub co najmniej znacznie większą liczbę niż jeden.

Operacja tworzenia kopii zapasowej, can use parallelismale pamiętaj, że nie jest to paralelizm napędzany przez Optymalizator w SQL Server, ale związany z liczbą dysków, z których kopia zapasowa musi odczytać plik danych i gdzie kopia zapasowa zapisuje plik danych oraz ilość utworzonych plików kopii zapasowej.

Nie można użyć MAXDOPpodpowiedzi podczas tworzenia kopii zapasowej programu SQL Server

Nie można wygenerować planu wykonania w SSMS dla prostej operacji tworzenia kopii zapasowej TSQL.

Równoległość napędzana przez optymalizator zapytań w SQL Server jest w zasadzie dla zaangażowanych operatorów (w rzeczywistości jest bardziej złożona, ale dla uproszczenia można to zrobić), ponieważ operacja tworzenia kopii zapasowych nie obejmuje żadnego operatora jako takiego, nie może ona używać równoległości napędzanej przez optymalizator.

Napisałem artykuł na stronie Technet Wiki o kopii zapasowej i równoległości, w którym użyłem prostych przykładów, aby wyjaśnić równoległość podczas tworzenia kopii zapasowej programu SQL Server. Oto wniosek

1. Jeśli pliki bazy danych znajdują się na wielu dyskach, operacja tworzenia kopii zapasowej zainicjuje wątek na dysk urządzenia, aby odczytać dane. W ten sam sposób, jeśli przywracanie odbywa się na wielu dyskach / punktach instalacji, operacja tworzenia kopii zapasowej zainicjowałaby jeden wątek na dysk / punkt instalacji

2. Nawet jeśli zrzucisz wiele kopii kopii zapasowej na tym samym dysku, zrzucimy jeden wątek na plik kopii zapasowej.

3. Paralelizm związany z tworzeniem kopii zapasowej jest związany z paskami. Każdy pasek ma własny wątek roboczy i jest to tak naprawdę jedyna część tworzenia kopii zapasowych / przywracania, którą należy uznać za operacje równoległe.

4. Maksymalny stopień równoległości nie ma wpływu na operacje tworzenia kopii zapasowych.

Dostałem opinię eksperta na ten temat od Paula i Boba Dorra.

Co się dzieje, gdy uruchomione jest zadanie tworzenia kopii zapasowej? A także czy istnieją znaczące różnice dla różnych wersji? na przykład 2008,2012 i 2014 (nie licencje).

Proponuję ci przeczytać ten artykuł blog.msdn autorstwa Boba Dorra . Podkreślił kilka ważnych punktów

1. Po rozpoczęciu tworzenia kopii zapasowej tworzy serię buforów przydzielonych z pamięci poza pulę buforów. Cel wynosi zwykle 4 MB dla każdego bufora, co daje około 4 do 8 buforów. Szczegóły dotyczące obliczeń znajdują się w: http://support.microsoft.com/kb/904804/en-us

2. Bufory są przenoszone między kolejką wolną a kolejką danych. Czytnik pobiera bezpłatny bufor, wypełnia go danymi i umieszcza w kolejce danych. Program piszący pobiera wypełnione bufory danych z kolejki danych, przetwarza bufor i zwraca go do wolnej listy.

3. Otrzymujesz moduł zapisujący na urządzenie kopii zapasowej, z którego każde pobiera dane z kolejki danych. Zatem polecenie tworzenia kopii zapasowej z czterema (4) specyfikacjami dysku będzie miało cztery zapisujące i czytnik. Czytnik używa asynchronicznych operacji we / wy, dzięki czemu może nadążać za pisarzami.

Możesz włączyć trace flags 3213 and 3605, oba są nieudokumentowane, więc użyj go w środowisku testowym i zobacz, jaki interesujący komunikat jest zrzucany w dzienniku błędów SQL Server. Pojawi się coś takiego jak poniżej

Memory limit: 249MB
BufferCount:                7
Sets Of Buffers:            1
MaxTransferSize:            1024 KB
Min MaxTransferSize:        64 KB
Total buffer space:         7 MB
Tabular data device count:  1
Fulltext data device count: 0
Filestream device count:    0
TXF device count:           0
Filesystem i/o alignment:   512
Media Buffer count:            7
Media Buffer size:          1024KB

Nie jestem świadomy żadnych znaczących zmian w kodzie kopii zapasowej dla różnych wersji, takie rzeczy nie są dokumentowane. Wiem tylko o ulepszeniu wprowadzonym w SQL Server 2012 SP1 Cumulative Update 2,włączaniu tworzenia kopii zapasowych i przywracania z usługi magazynowania obiektów Blob systemu Windows Azure z SQL Server przy użyciu TSQL lub SMO. Przeczytaj tutaj

Zasadniczo SQL Server wykonuje brudną kopię wszystkich stron na dysku. Strony te są prawdopodobnie niespójne, jeśli istnieje współbieżna aktywność lub jeśli wcześniej istniała aktywność niezwiązana z punktem kontrolnym.

Następnie SQL Server kopiuje również niezbędną część dziennika transakcji, która jest potrzebna, aby zaktualizować nieaktualne strony do najnowszej wersji i zapewnić spójność przywracania.

Nie mogę mówić o wielowątkowości operacji tworzenia kopii zapasowej. Oczekuję, że zostanie to zrównoleglone. Jak inaczej można wykonać kopię zapasową bazy danych 10 TB w podsystemie IO 10 GB / s?