Udostępnij za pośrednictwem

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

  • Artykuł

Oryginalna wersja produktu: SQL Server
Oryginalny numer KB: 315512

Podsumowanie

Domyślne ustawienia automatycznego zwiększania i automatycznego ściągania są odpowiednie w wielu systemach programu SQL Server. Istnieją jednak środowiska, w których może być wymagane dostosowanie parametrów automatycznego zwiększania i automatycznego zmniejszania. Ten artykuł zawiera podstawowe informacje, które zawierają wskazówki dotyczące wybierania tych ustawień dla środowiska.

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

Jak mogę skonfigurować ustawienia

  1. Ustawienia automatycznego zwiększania i automatycznego zmieniania można skonfigurować lub zmodyfikować przy użyciu jednej z następujących opcji:

    • SQL Server Management Studio

    • Instrukcja ALTER DATABASE

      • Użyj opcji Plik i Grupa plików, aby zmodyfikować ustawienia automatycznego zwiększania
      • Użyj opcji SET, aby skonfigurować AUTO_SHRINK ustawienia.

    Uwaga 16.

    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.

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

    sp_helpdb [ [ @dbname= ] 'name' ]

  2. Należy pamiętać, że ustawienia automatycznego zwiększania są na plik. 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/lub dziennika, musisz ustawić opcje dla każdego pliku. W zależności od środowiska można kończyć się różnymi ustawieniami dla każdego pliku bazy danych.

Zagadnienia dotyczące AUTO_SHRINK

AUTO_SHRINK to opcja bazy danych w programie SQL Server. Po włączeniu tej opcji dla bazy danych ta baza danych będzie kwalifikować się do zmniejszenia przez zadanie w tle. To zadanie w tle ocenia wszystkie bazy danych spełniające kryteria zmniejszania i zmniejszania danych lub plików dziennika.

Należy dokładnie ocenić ustawienie tej opcji dla baz danych w wystąpieniu programu 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, łatwo doprowadzi to do fragmentacji na poziomie systemu plików. Może to mieć poważny wpływ na wydajność. Jest to prawdą, czy używasz ustawień automatycznych, czy też ręcznie powiększasz i zmniejszasz pliki często.

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

  • Zadanie AUTO_SHRINK w tle może podjąć zasoby, gdy istnieje wiele baz danych, które wymagają zmniejszenia.

  • Zadanie AUTO_SHRINK w tle będzie musiało uzyskać blokady i inne synchronizacje, które mogą powodować konflikt z innymi zwykłymi działaniami aplikacji.

Rozważ ustawienie baz danych na wymagany rozmiar i wstępne ich rozwijanie. 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 zwiększaniu plików bazy danych.

Zagadnienia dotyczące AUTOGROW

  • Jeśli uruchamiasz transakcję, która wymaga więcej miejsca w dzienniku niż jest dostępna, i włączono opcję automatycznego zwiększania dla dziennika transakcji tej bazy danych, wówczas czas potrzebny na ukończenie transakcji będzie obejmować czas potrzebny dziennikowi transakcji na wzrost 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 otwarto 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 ukończenie operacji zwiększania.

  • Jeśli masz wiele wzrostów plików w plikach dziennika, może istnieć zbyt duża liczba plików dziennika wirtualnego (VLF). Może to prowadzić do problemów z wydajnością związanych z uruchamianiem bazy danych/operacjami online, replikacją, dublowaniem i przechwytywaniem danych zmian (CDC). Ponadto czasami może to powodować problemy z wydajnością związane z modyfikacjami danych.

Uwaga 16.

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żna uruchomić transakcję, która powoduje wzrost dziennika transakcji o 100 MB do momentu zatwierdzenia. Jakiś czas po tym autoshrink uruchamia i zmniejsza dziennik transakcji o 100 MB. Następnie uruchomisz tę samą transakcję i spowoduje to ponowne zwiększenie dziennika transakcji o 100 MB. W tym przykładzie tworzysz niepotrzebne obciążenie i potencjalnie tworzysz fragmentację pliku dziennika, co może negatywnie wpłynąć na wydajność.

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

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

Najlepsze rozwiązania dotyczące automatycznego zwiększania i automatycznego zwiększania

  • W przypadku zarządzanego systemu produkcyjnego należy rozważyć automatyczne zwiększenie, aby stanowić jedynie awaryjne rozwiązanie dla nieoczekiwanego wzrostu. Nie zarządzaj wzrostem danych i dzienników na bieżąco z automatycznym zwiększaniem.

  • Za pomocą alertów lub programów monitorowania można aktywnie monitorować rozmiary plików i rozwijać pliki. Pomaga to uniknąć fragmentacji i umożliwia przeniesienie tych działań konserwacyjnych do godzin poza szczytem.

  • Autoshrink i autogrow muszą być dokładnie oceniane przez wyszkolonego administratora bazy danych (DBA); Nie mogą pozostać niezarządzane.

  • Przyrost automatycznego zwiększania musi być wystarczająco duży, aby uniknąć kar za wydajność wymienionych w poprzedniej sekcji. Dokładna wartość używana w ustawieniu konfiguracji i wybór między wzrostem procentowym a określonym wzrostem rozmiaru MB zależy od wielu czynników w danym środowisku. Ogólną regułą kciuka, której można użyć do testowania, jest ustawienie automatycznego zwiększania na około jeden-osiem rozmiaru pliku.

  • Włącz \<MAXSIZE> ustawienie 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 wzrostowi plików.

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

  • Ustawienie automatycznego zwiększania nie może zwiększać rozmiaru bazy danych poza 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ć możliwość braku miejsca, możesz monitorować licznik monitor wydajności programu SQL Server: Bazy danych Object: Data File(s) Size (KB) i skonfigurować alert, gdy baza danych osiągnie określony rozmiar.

  • Nieplanowany wzrost danych lub plików dziennika może zająć miejsce, które inne aplikacje oczekują, że będą dostępne i mogą powodować problemy z tymi innymi aplikacjami.

  • Przyrost wzrostu dziennika transakcji musi być wystarczająco duży, aby wyprzedzać potrzeby jednostek transakcji. 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 być wystarczająco szybki, aby zaspokoić potrzeby zapytania.

  • Program SQL Server nie stale testuje baz danych, które osiągnęły skonfigurowany próg automatycznego szyfrowania. Zamiast tego analizuje dostępne bazy danych i znajduje pierwszą, która jest skonfigurowana do automatycznego myślenia. Sprawdza, czy baza danych i zmniejsza ją w razie potrzeby. Następnie zaczeka kilka minut przed sprawdzeniem następnej bazy danych skonfigurowanej pod kątem automatycznego ściągniania. Innymi słowy, program SQL Server nie sprawdza jednocześnie wszystkich baz danych i zmniejsza je jednocześnie. Będzie ona działać przez bazy danych w sposób działania okrężnego, aby rozsyłać obciążenie w danym okresie. 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ę.

Informacje