Zagadnienia dotyczące ustawień autogrow i autoshrink w SQL Server

Artykuł
06/20/2023

Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 315512

Podsumowanie

Domyślne ustawienia autogrow i autoshrink są odpowiednie w wielu systemach SQL Server. Istnieją jednak środowiska, w których może być konieczne dostosowanie parametrów autogrow i autoshrink. Ten artykuł zawiera pewne podstawowe informacje ułatwiające wybranie tych ustawień dla danego środowiska.

Oto kilka kwestii, które należy wziąć pod uwagę, jeśli zdecydujesz się dostroić parametry autogrow i autoshrink.

Jak mogę skonfigurować ustawienia

Możesz skonfigurować lub zmodyfikować ustawienia autogrow i autoshrink przy użyciu jednego z następujących elementów:
- SQL Server Management Studio
- Instrukcja ALTER DATABASE
  - Modyfikowanie ustawień automatycznego wzrostu za pomocą opcji Plik i Grupa plików
  - Użyj opcji SET , aby skonfigurować AUTO_SHRINK ustawienia.
Uwaga

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.

Podczas tworzenia bazy danych można również skonfigurować opcję automatycznego zwiększania.

Aby wyświetlić bieżące ustawienia, uruchom następujące polecenie Języka Transact-SQL:
```
sp_helpdb [ [ @dbname= ] 'name' ]
```
Należy pamiętać, że ustawienia autogrowowania są dla każdego pliku. W związku z tym należy ustawić je w co najmniej dwóch miejscach dla każdej bazy danych (jeden dla podstawowego pliku danych i jeden dla podstawowego pliku dziennika). Jeśli masz wiele danych i/lub plików dziennika, musisz ustawić opcje dla każdego pliku. W zależności od środowiska może skończyć się na różnych ustawieniach dla każdego pliku bazy danych.

Zagadnienia dotyczące AUTO_SHRINK

AUTO_SHRINKjest opcją bazy danych w SQL Server. Po włączeniu tej opcji dla bazy danych ta baza danych kwalifikuje się do zmniejszania przez zadanie w tle. To zadanie w tle ocenia wszystkie bazy danych spełniające kryteria zmniejszania i zmniejszania plików danych lub dzienników.

Należy dokładnie ocenić 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 przechodzi częste operacje zmniejszania i zwiększania, może to łatwo doprowadzić do fragmentacji na poziomie systemu plików. Może to mieć poważny wpływ na wydajność. Dotyczy to tego, czy używasz ustawień automatycznych, czy też często ręcznie powiększasz i zmniejszasz pliki.
Po AUTO_SHRINK pomyślnym zmniejszeniu pliku danych lub dziennika kolejna operacja DML lub DDL może znacznie zwolnić, jeśli jest wymagane miejsce i pliki muszą rosnąć.
Zadanie w AUTO_SHRINK tle może zająć zasoby, gdy istnieje wiele baz danych, które wymagają zmniejszenia.
Zadanie w AUTO_SHRINK tle będzie musiało uzyskać blokady i inne synchronizacje, które mogą powodować konflikt z innymi zwykłymi działaniami aplikacji.

Rozważ ustawienie wymaganych rozmiarów baz danych i ich wstępne zwiększanie. Pozostaw nieużywane miejsce w plikach bazy danych, jeśli uważasz, że wzorce użycia aplikacji będą potrzebne ponownie. Może to zapobiec częstemu zmniejszaniu i wzrostowi plików bazy danych.

Zagadnienia dotyczące funkcji AUTOGROW

Jeśli uruchomisz transakcję, która wymaga więcej miejsca w dzienniku niż jest dostępna, i włączono opcję automatycznego zwiększania poziomu dla dziennika transakcji tej bazy danych, czas potrzebny na ukończenie transakcji będzie zawierać czas potrzebny na zwiększenie dziennika transakcji o skonfigurowaną kwotę. Jeśli przyrost wzrostu jest duży lub istnieje inny czynnik, który powoduje, że zajmuje dużo czasu, zapytanie, w którym otwierasz transakcję, może zakończyć się niepowodzeniem z powodu błędu przekroczenia limitu czasu. Ten sam rodzaj problemu 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, które wymagają zapisu w dzienniku transakcji, również będą musiały poczekać na zakończenie operacji zwiększania.
Jeśli masz wiele przyrostów plików w plikach dziennika, może być zbyt duża liczba plików dziennika wirtualnego (VLF). Może to prowadzić do problemów z wydajnością podczas uruchamiania bazy danych/operacji online, replikacji, dublowania i przechwytywania danych zmian (CDC). Ponadto może to czasami powodować problemy z wydajnością modyfikacji danych.

Uwaga

Jeśli połączysz opcje autogrow i autoshrink, możesz utworzyć niepotrzebne obciążenie. Upewnij się, że progi wyzwalające operacje zwiększania i zmniejszania nie spowodują częstych zmian rozmiaru w górę i w dół. Na przykład możesz uruchomić transakcję, która powoduje wzrost dziennika transakcji o 100 MB do czasu zatwierdzenia. Jakiś czas później autoshrink uruchamia i zmniejsza dziennik transakcji o 100 MB. Następnie uruchomisz tę samą transakcję i spowoduje to ponowne wzrost dziennika transakcji o 100 MB. W tym przykładzie tworzysz niepotrzebne obciążenie i potencjalnie tworzysz fragmentację pliku dziennika, z których każda może negatywnie wpłynąć na wydajność.

Jeśli zwiększasz bazę danych o małe przyrosty lub zwiększasz ją, a następnie zmniejszasz, możesz skończyć z fragmentacją dysku. Fragmentacja dysku może w pewnych okolicznościach powodować problemy z wydajnością. Scenariusz niewielkiego wzrostu może również zmniejszyć wydajność systemu.

W SQL Server można włączyć natychmiastowe inicjowanie plików. Natychmiastowe inicjowanie plików przyspiesza alokację plików tylko dla plików danych. Natychmiastowe inicjowanie plików nie ma zastosowania do plików dziennika. Aby uzyskać więcej informacji, zobacz Inicjowanie pliku błyskawicznego bazy danych.

Najlepsze rozwiązania dotyczące autogrowowania i autoshrinku

W przypadku zarządzanego systemu produkcyjnego należy uznać autogrow za jedynie nieprzewidziane w przypadku nieoczekiwanego wzrostu. Nie zarządzaj danymi i nie loguj wzrostu na co dzień przy użyciu funkcji automatycznego zwiększania poziomu.
Do monitorowania rozmiarów plików i proaktywnego zwiększania rozmiaru plików można używać alertów lub programów monitorowania. Pomaga to uniknąć fragmentacji i umożliwia przeniesienie tych działań konserwacyjnych na godziny poza godzinami szczytu.
Autoshrink i autogrow muszą być starannie oceniane przez wytrenowanego administratora bazy danych (DBA); Nie można pozostawić ich niezarządzanych.
Przyrost automatycznego zwiększania musi być wystarczająco duży, aby uniknąć kar za wydajność wymienionych w poprzedniej sekcji. Dokładna wartość do użycia w ustawieniu konfiguracji i wybór między procentowym wzrostem a określonym wzrostem rozmiaru MB zależy od wielu czynników w środowisku. Ogólną regułą, która służy do testowania, jest ustawienie ustawienia automatycznego zwiększania rozmiaru pliku na około 1-8.
Włącz ustawienie dla każdego pliku, aby zapobiec zwiększaniu się każdego pliku do punktu, w którym zużywa on wszystkie dostępne miejsce na \<MAXSIZE> dysku.
Zachowaj rozmiar transakcji tak mały, jak to możliwe, aby zapobiec nieplanowanemu wzrostowi plików.

Dlaczego muszę martwić się o miejsce na dysku, jeśli ustawienia rozmiaru są automatycznie kontrolowane

Ustawienie automatycznego zwiększania rozmiaru bazy danych nie może przekroczyć limitów dostępnego miejsca na dysku 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 sprawdzać dostępne miejsce na dysku twardym. Ustawienie automatycznego MAXSIZE zwiększania jest również ograniczone przez parametr wybrany dla każdego pliku. Aby zmniejszyć możliwość wyczerpania miejsca, można monitorować licznik monitor wydajności SQL Server: Databases Object: Data File(s) Size (KB) i skonfigurować alert, gdy baza danych osiągnie określony rozmiar.
Nieplanowany rozwój plików danych lub dzienników może zająć miejsce, które inne aplikacje oczekują na dostępność i może powodować problemy z tymi innymi aplikacjami.
Przyrost wzrostu dziennika transakcji musi być wystarczająco duży, aby wyprzedzić potrzeby jednostek transakcji. Nawet po włączeniu funkcji autogrow można otrzymać komunikat o tym, że dziennik transakcji jest pełny, jeśli nie może on rosnąć wystarczająco szybko, aby zaspokoić potrzeby zapytania.
SQL Server nie stale testuje 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 tę bazę danych i zmniejsza ją w razie potrzeby. Następnie czeka kilka minut przed sprawdzeniem następnej bazy danych skonfigurowanej pod kątem autoshrinku. Innymi słowy, SQL Server nie sprawdza jednocześnie wszystkich baz danych i zmniejsza je jednocześnie. Będzie on działać za pośrednictwem baz danych w sposób okrężny w celu rozłożenia obciążenia w danym okresie. W związku z tym w zależności od tego, ile baz danych skonfigurowano do automatycznego zmniejszania w określonym wystąpieniu SQL Server, może upłynąć kilka godzin od momentu osiągnięcia progu przez bazę danych do momentu jej rzeczywistego zmniejszenia.