Zagadnienia dotyczące ustawień automatycznego zwiększania i automatycznego zmniejszania w programie SQL Server

Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 315512

Podsumowanie

Ustawienia autogrow i autoshrink kontrolują, jak SQL Server automatycznie zwiększa (autogrow) lub zmniejsza (autoshrink) rozmiar plików danych bazy danych i plików dziennika. Ustawienia domyślne są odpowiednie w wielu systemach SQL Server, ale niektóre środowiska wymagają ich dostosowania. W tym artykule wyjaśniono, jak te ustawienia wpływają na wydajność i zawiera najlepsze rozwiązania ułatwiające unikanie wolnych transakcji, fragmentacji plików i nieplanowanego użycia miejsca na dysku.

Ten artykuł dotyczy administratorów baz danych (DBA), którzy konfigurują zachowanie zwiększania i zmniejszania plików dla baz danych w SQL Server.

Jak skonfigurować ustawienia autogrow i autoshrink?

1. Skonfiguruj lub zmodyfikuj ustawienia automatycznego zwiększania i automatycznego zmniejszania przy użyciu jednej z następujących metod:

   • SQL Server Management Studio

   • Polecenie ALTER DATABASE:

     • Użyj File and Filegroup options, aby zmodyfikować ustawienia autogrow (FILEGROWTH).
     • Użyj opcji SET , aby skonfigurować AUTO_SHRINK ustawienie.

   Nota

   Aby uzyskać więcej informacji na temat ustawiania tych ustawień na poziomie pliku bazy danych, zobacz Dodawanie danych lub plików dziennika do bazy danych.

   Możesz również skonfigurować opcję automatycznego zwiększania podczas tworzenia bazy danych.

2. Aby wyświetlić bieżące ustawienia, uruchom następujące polecenie języka Transact-SQL:

   sp_helpdb [ [ @dbname= ] 'name' ]
   

   Możesz również wykonać zapytanie dotyczące widoku wykazu sys.database_files dla bieżącej bazy danych lub widoku wykazu sys.master_files dla wszystkich baz danych w wystąpieniu. Te widoki obejmują przyrost, niezależnie od tego, czy jest wyrażony w procentach, oraz maksymalny rozmiar pliku. Aby sprawdzić, czy AUTO_SHRINK jest włączona, wykonaj zapytanie dotyczące kolumny is_auto_shrink_on w widoku sys.databases.

3. Należy pamiętać, że ustawienia automatycznego zwiększania obowiązują dla każdego pliku osobno. W związku z tym należy je ustawić w co najmniej dwóch miejscach dla każdej bazy danych (jeden dla pliku danych podstawowych i jeden dla podstawowego pliku dziennika). Jeśli masz wiele plików danych i plików dziennika, musisz ustawić opcje dla każdego pliku. W zależności od środowiska można uzyskać różne ustawienia dla każdego pliku bazy danych.

Jakie są domyślne ustawienia Autogrow?

W SQL Server 2016 (13.x) i nowszych wersjach wartość domyślna FILEGROWTH to 64 MB zarówno dla plików danych, jak i plików dziennika. Starsze wersje używają mniejszych lub opartych na procentach wartości domyślnych. Aby uzyskać informacje o ustawieniu domyślnym w każdej wersji, zobacz ALTER DATABASE (Transact-SQL) File and Filegroup options.

Co należy wziąć pod uwagę przed włączeniem AUTO_SHRINK?

AUTO_SHRINK jest opcją bazy danych w programie SQL Server. Po włączeniu tej opcji dla bazy danych zadanie w tle może zmniejszyć plik bazy danych. To zadanie w tle ocenia wszystkie bazy danych spełniające kryteria zmniejszania, a następnie zmniejsza pliki danych lub pliki dziennika.

Dokładnie oceń ustawienie tej opcji dla baz danych w wystąpieniu SQL Server. Częste operacje zwiększania i zmniejszania mogą prowadzić do różnych problemów z wydajnością:

• Jeśli wiele baz danych jest często zmniejszanych i ponownie powiększanych, może wystąpić fragmentacja systemu plików. Fragmentacja może poważnie wpłynąć na wydajność. Efekt ten występuje zarówno w przypadku używania ustawień automatycznych, jak i częstego ręcznego zwiększania i zmniejszania plików.

• Po pomyślnym zmniejszeniu przez AUTO_SHRINK pliku danych lub pliku dziennika kolejna operacja DML lub DDL może znacznie spowolnić działanie, jeśli jest wymagane miejsce, a pliki muszą rosnąć.

• Zadanie AUTO_SHRINK w tle może używać znaczących zasobów, gdy wiele baz danych wymaga zmniejszania.

• Zadanie AUTO_SHRINK w tle musi uzyskać blokady i inne obiekty synchronizacji, które mogą powodować konflikt z regularnymi działaniami aplikacji.

Zamiast tego ustaw bazy danych na wymagany rozmiar i wstępnie je zwiększ. Pozostaw nieużywane miejsce w plikach bazy danych, jeśli uważasz, że sposób użycia aplikacji będzie ponownie wymagał tego miejsca. Aby uzyskać więcej informacji na temat zmniejszania plików, zobacz Zmniejsz bazę danych i DBCC SHRINKFILE.

Co należy wziąć pod uwagę, zanim zaczniesz polegać na autogrow?

Autogrow może mieć wpływ na czas trwania transakcji, inne sesje i liczbę plików dziennika wirtualnego w dzienniku transakcji:

• Jeśli uruchomisz transakcję, która wymaga więcej miejsca w dzienniku transakcji niż jest dostępna, a automatyczne zwiększanie jest włączone dla dziennika transakcji tej bazy danych, czas ukończenia transakcji obejmuje czas, przez jaki dziennik transakcji rośnie o skonfigurowaną wartość. Jeśli przyrost jest duży lub inny czynnik powoduje, że zwiększanie może trwać długo, zapytanie, w którym otwierasz transakcję, może zakończyć się niepowodzeniem z powodu błędu przekroczenia limitu czasu. Ten sam problem może wynikać z automatycznego zwiększania części danych bazy danych.

• Jeśli uruchomisz dużą transakcję, która wymaga zwiększenia dziennika, inne transakcje wymagające zapisu w dzienniku transakcji również muszą poczekać na zakończenie operacji zwiększania.

• Jeśli masz wiele przyrostów plików w plikach dziennika, może istnieć zbyt duża liczba wirtualnych plików dziennika (VLF). Wiele vlFs może obniżyć wydajność uruchamiania bazy danych, operacji online i innych funkcji zależnych od dziennika. Aby sprawdzić liczbę VLF, wykonaj zapytanie względem funkcji dynamicznego zarządzania sys.dm_db_log_info.

Jeśli zwiększasz bazę danych o niewielkie przyrosty lub zwiększasz ją, a następnie zmniejszasz, może to prowadzić do fragmentacji dysku. Fragmentacja dysku może powodować problemy z wydajnością w pewnych okolicznościach. Niewielkie przyrosty mogą również zmniejszyć wydajność systemu.

Jak inicjalizacja natychmiastowa plików wpływa na automatyczne zwiększanie rozmiaru?

Natychmiastowe inicjowanie plików (IFI) przyspiesza operacje automatycznego zwiększania plików danych, ponieważ SQL Server nie zeruje nowego miejsca. W SQL Server 2022 (16.x) i nowszych wersjach zdarzenia automatycznego zwiększania dziennika transakcji do 64 MB mogą również korzystać z funkcji IFI. Bez funkcji IFI autogrow trwa dłużej, ponieważ przyrost wzrostu staje się większy. Aby uzyskać informacje o wymaganiach i ograniczeniach, zobacz Natychmiastowa inicjalizacja plików bazy danych.

Jakie są najlepsze rozwiązania dotyczące automatycznego zwiększania i automatycznego zmniejszania?

• W przypadku zarządzanego systemu produkcyjnego należy traktować autogrow jedynie jako zabezpieczenie na wypadek nieoczekiwanego wzrostu. Nie zarządzaj na bieżąco przyrostem plików danych i dziennika przy użyciu automatycznego zwiększania.

• Użyj alertów lub programów monitorowania, aby aktywnie monitorować rozmiary plików i zawczasu zwiększać rozmiar plików. Takie podejście pomaga uniknąć fragmentacji i umożliwia przeniesienie tych działań konserwacyjnych do godzin poza godzinami szczytu.

• Przeszkolony administrator bazy danych powinien dokładnie ocenić automatyczne zmniejszanie i automatyczne zwiększanie. Nie pozostawiaj ich bez zarządzania.

• Zwiększ przyrost automatycznego zwiększania, aby uniknąć kar za wydajność opisanych wcześniej w tym artykule. Dokładna wartość używana w ustawieniu konfiguracji oraz wybór między wzrostem procentowym a określonym wzrostem rozmiaru MB zależą od wielu czynników w danym środowisku. Ogólną regułą, której można użyć do testowania, jest ustawienie ustawienia autogrow na około jedną ósmą wielkości pliku. Aby uzyskać zalecenia dotyczące wzrostu dziennika transakcji, zobacz Zarządzanie rozmiarem pliku dziennika transakcji.

• Włącz ustawienie MAXSIZE dla każdego pliku, aby zapobiec zwiększaniu się dowolnego pliku do punktu, w którym jest używana cała dostępna ilość miejsca na dysku.

• Zachowaj rozmiar transakcji tak mały, jak to możliwe, aby zapobiec nieplanowanemu zwiększaniu się pliku.

Dlaczego nadal muszę monitorować miejsce na dysku po włączeniu automatycznego zwiększania?

• Ustawienie automatycznego zwiększania nie może zwiększać rozmiaru bazy danych ponad limity dostępnego miejsca na dyskach, dla których zdefiniowano pliki. W związku z tym, jeśli korzystasz z funkcji automatycznego zwiększania rozmiaru baz danych, nadal musisz niezależnie sprawdzić dostępne miejsce na dysku twardym. Ustawienie automatycznego zwiększania jest również ograniczone przez MAXSIZE parametr wybierany dla każdego pliku. Aby zmniejszyć ryzyko braku miejsca, możesz monitorować licznik Monitora wydajności SQL Server: obiekt Databases: Data File(s) Size (KB) i skonfigurować alert, gdy baza danych osiągnie określony rozmiar.

• Nieplanowany wzrost ilości danych lub plików dziennika może zająć miejsce, które powinno pozostać dostępne dla innych aplikacji, i może spowodować problemy w działaniu tych aplikacji.

• Przyrost rozmiaru dziennika transakcji musi być wystarczająco duży, aby nadążać za wymaganiami jednostek transakcyjnych. Nawet w przypadku włączenia automatycznego zwiększania można otrzymać komunikat informujący, że dziennik transakcji jest pełny, jeśli nie może zwiększać się wystarczająco szybko, aby zaspokoić potrzeby zapytania. Aby uzyskać więcej informacji, zobacz Rozwiązywanie problemów z pełnym dziennikiem transakcji (błąd SQL Server 9002).

Jak często SQL Server sprawdza bazy danych pod kątem autozmniejszania?

Program SQL Server nie sprawdza stale baz danych, które osiągnęły skonfigurowany próg automatycznego zmniejszania. Zamiast tego analizuje dostępne bazy danych i znajduje pierwszą, która jest skonfigurowana do automatycznego zmniejszania. Sprawdza bazę danych i w razie potrzeby zmniejsza jej rozmiar. Następnie zaczeka kilka minut przed sprawdzeniem następnej bazy danych skonfigurowanej pod kątem automatycznego zmniejszania.

Innymi słowy, program SQL Server nie sprawdza jednocześnie wszystkich baz danych i nie zmniejsza jednocześnie ich rozmiaru. Przechodzi przez bazy danych w trybie round-robin, aby rozłożyć obciążenie w czasie. W związku z tym w zależności od liczby baz danych skonfigurowanych do automatycznego zmniejszania w konkretnym wystąpieniu programu SQL Server może upłynąć kilka godzin od momentu, gdy baza danych osiągnie próg, dopóki faktycznie nie zmniejszy się.

Treści powiązane

• Architektura dziennika transakcji programu SQL Server i przewodnik po zarządzaniu
• DBCC SHRINKDATABASE
• Pliki bazy danych i grupy plików