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

Tłumaczenia artykułów Tłumaczenia artykułów
Numer ID artykułu: 315512 - Zobacz jakich produktów dotyczą zawarte w tym artykule porady.
Rozwiń wszystko | Zwiń wszystko

Streszczenie

W wielu systemach z programem SQL Server nie ma potrzeby dostosowywania domyślnych ustawień autogrow i autoshrink. Jednak w niektórych środowiskach włączanie parametrów autogrow i autoshrink lub ich dostosowanie jest konieczne. W tym artykule podano podstawowe informacje, pomocne przy wyborze właściwych ustawień w danym środowisku.

Więcej informacji

Oto kilka zagadnień, które należy rozważyć w przypadku podjęcia decyzji o zmianie konfiguracji parametrów autogrow i autoshrink.

Jak skonfigurować te ustawienia?

  1. Ustawienia autogrow i autoshrink można skonfigurować przy użyciu jednej z następujących metod:
    • Za pomocą instrukcji ALTER DATABASE (niedostępna w wersji SQL Server 7.0).
    • Za pomocą programu SQL Server Management Studio lub SQL Enterprise Manager.
    • Za pomocą procedury składowanej sp_dboption (przestarzała od wersji SQL Server 2005).
    Uwaga Użytkownicy wersji SQL Server 2005 powinni skorzystać z programu SQL Server Management Studio zamiast z programu SQL Enterprise Manager. Aby uzyskać więcej informacji dotyczących konfigurowania tych ustawień w programie SQL Server 2005, odwiedź następujące witryny MSDN (Microsoft Developer Network) w sieci Web:
    Jak to zrobić: dodawanie danych lub plików dziennika do bazy danych (SQL Server Management Studio)
    http://msdn.microsoft.com/pl-pl/library/ms189253.aspx
    Właściwości bazy danych (strona Pliki)
    http://msdn.microsoft.com/pl-pl/library/ms180254.aspx
    Opcję autogrow można również skonfigurować podczas tworzenia bazy danych.

    Bieżące ustawienia są widoczne we właściwościach bazy danych, które można wyświetlić za pomocą programu SQL Enterprise Manager (SEM). Można również uruchomić następujące polecenie Transact-SQL:
    sp_helpdb [ [ @dbname= ] 'nazwa' ]
  2. Należy pamiętać, że ustawienie autogrow dotyczy konkretnego pliku. Dlatego w przypadku każdej bazy danych należy skonfigurować ten parametr w co najmniej dwóch miejscach (dla podstawowego pliku danych i dla podstawowego pliku dziennika). W przypadku większej liczby plików danych lub plików dziennika należy ustawić te opcje dla każdego z tych plików z osobna. Różne pliki bazy danych mogą wymagać różnych ustawień w zależności od używanego środowiska.

Jaki jest wpływ na wydajność?

  • Gdy opcja autogrow dziennika transakcji bazy danych jest włączona i uruchomiona zostanie transakcja wymagająca więcej miejsca, niż jest dostępne w pliku dziennika, czas wykonania tej transakcji będzie obejmować również czas niezbędny na powiększenie dziennika o skonfigurowaną wartość. Jeśli przyrost rozmiaru jest duży lub jeśli zadziała inny czynnik wydłużający proces powiększania, to zapytanie otwierające transakcję może zakończyć się niepowodzeniem z powodu przekroczenia limitu czasu. Autopowiększanie tej części bazy, w której przechowywane są dane, również może spowodować taki problem. Informacje dotyczące zmiany konfiguracji parametru autogrow zawiera temat „ALTER DATABASE” w dokumentacji SQL Server Books Online.
  • Uruchomienie dużej, wymagającej powiększenia dziennika transakcji spowoduje, że inne transakcje wymagające zapisu w tym dzienniku będą musiały oczekiwać na zakończenie operacji powiększania.
  • Łączne użycie parametrów autogrow i autoshrink może niepotrzebnie obciążać system. Dlatego warto zapewnić, że ustawione progi wyzwalające operacje powiększania i zmniejszania nie będą powodować częstych zmian rozmiaru. Załóżmy na przykład, że pewna transakcja do momentu zatwierdzenia powoduje powiększenie dziennika transakcji o 100 MB. Później uruchamiana jest operacja autoshrink, która zmniejsza dziennik transakcji o 100 MB. Następnie użytkownik uruchamia tę samą transakcję po raz kolejny, co ponownie powoduje zwiększenie rozmiaru dziennika transakcji o 100 MB. W podanym przykładzie występuje niepotrzebne obciążenie systemu, a ponadto może dojść do fragmentacji pliku dziennika. Każdy z tych czynników może mieć negatywny wpływ na wydajność.
  • Fragmentacja fizyczna wynikająca ze zmiany rozmiaru plików dziennika lub plików danych może w bardzo poważnym stopniu obniżać wydajność. Ten problem występuje zarówno w przypadku użycia ustawień automatycznych, jak i często powtarzanego ręcznego powiększania i zmniejszania plików.
  • Powiększanie bazy danych o niewielkie wartości lub powiększanie, a następnie zmniejszanie bazy danych powoduje fragmentację dysku. Fragmentacja dysku może w pewnych okolicznościach obniżać wydajność. Sytuacja, w której występuje przyrost o niewielkie wartości, również może obniżyć wydajność systemu.
  • W programie SQL Server 2005 i w nowszych wersjach można włączyć błyskawiczne inicjowanie plików. Błyskawiczne inicjowanie plików przyspiesza alokację plików tylko w przypadku plików danych. Nie obejmuje natomiast plików dziennika.
  • Jedną z przyczyn występowania częstych przyrostów plików dziennika jest korzystanie z nadmiernej liczby wirtualnych plików dziennika. Może to prowadzić do problemów z wydajnością przy uruchamianiu bazy danych, wykonywaniu operacji w trybie online, replikacji, dublowaniu oraz przechwytywaniu zmienionych danych. Niekiedy mogą również wystąpić problemy z wydajnością podczas modyfikowania danych.

Najlepsze rozwiązania

  • W zarządzanych systemach produkcyjnych opcja autogrow powinna być stosowana wyłącznie jako mechanizm awaryjny na wypadek nieoczekiwanego przyrostu. W zwykłych okolicznościach nie należy zarządzać przyrostem danych i dzienników za pomocą opcji autogrow.
  • Rozmiary plików można śledzić i zapobiegawczo je zwiększać, stosując alerty i programy monitorujące. Pozwala to uniknąć fragmentacji i umożliwia przeniesienie czynności konserwacyjnych poza godziny szczytu.
  • Nad stosowaniem opcji autoshrink i autogrow musi uważnie czuwać wykwalifikowany administrator bazy danych. Nie należy pozostawiać tych opcji bez nadzoru.
  • Wartość przyrostu autogrow musi być na tyle duża, aby nie dochodziło do problemów z wydajnością opisanych w poprzedniej sekcji. Dokładna wartość, którą należy skonfigurować, oraz wybór między przyrostem procentowym a przyrostem o określoną liczbę megabajtów zależą od wielu czynników. Przy testowaniu można wykorzystać ogólną zasadę, która zaleca ustawienie wartości parametru autogrow na około 1/8 rozmiaru pliku.
  • Dla każdego pliku należy włączyć opcję <MAXSIZE> (maksymalny rozmiar). Pozwala to uniknąć sytuacji, w której pojedynczy plik rozrasta się do tego stopnia, że zajmuje całe wolne miejsce na dysku.
  • Rozmiar transakcji powinien być jak najmniejszy, co pozwala uniknąć niezaplanowanego zwiększenia rozmiaru pliku.

Dlaczego trzeba sprawdzać wolne miejsce na dysku, skoro ustawienia rozmiaru są sterowane automatycznie?

  • Ustawienie autogrow nie może zwiększyć rozmiaru bazy danych poza limity określone ilością wolnego miejsca na dyskach, na których zapisywane są pliki. Dlatego też użytkownicy, którzy używają funkcji autogrow do określania rozmiaru baz danych, muszą niezależnie monitorować ilość wolnego miejsca na dysku. Ustawienie autogrow jest też ograniczone przez parametr MAXSIZE, który jest wybierany dla każdego pliku. Śledzenie licznika monitora wydajności o nazwie SQL Server: Databases Object :Data File(s) Size (KB) (SQL Server: obiekt bazy danych: rozmiar plików danych w KB) umożliwia ograniczenie ryzyka zabraknięcia miejsca na dysku. Na podstawie tego licznika można skonfigurować alert, który będzie generowany po osiągnięciu przez bazę danych pewnego zadanego rozmiaru.
  • Niezamierzony przyrost pliku danych lub dziennika może spowodować zajęcie na dysku miejsca, z którego korzystają inne aplikacje, a przez to doprowadzić do problemów z tymi aplikacjami.
  • Przyrost zwiększania rozmiaru dziennika transakcji musi być na tyle duży, aby z wyprzedzeniem zaspokoił potrzeby jednostek transakcji. Program może zgłaszać komunikat o zapełnieniu dziennika transakcji nawet wtedy, gdy opcja autogrow jest włączona. Zdarza się to wówczas, gdy przyrost dziennika na potrzeby zapytania nie następuje dostatecznie szybko.
  • W programie SQL Server bazy danych nie są stale monitorowane pod kątem skonfigurowanego progu autozmniejszania (autoshrink). Wyszukiwana jest tylko pierwsza z dostępnych baz danych, dla której skonfigurowano autozmniejszanie. Baza ta jest sprawdzana i w razie potrzeby zmniejszana. Po upływie kilku minut sprawdzana jest kolejna baza danych skonfigurowana do autozmniejszania (autoshrink). Inaczej mówiąc, w programie SQL Server nie zachodzi jednoczesne sprawdzanie i zmniejszanie wszystkich baz danych. Program przetwarza bazy danych cyklicznie, aby rozłożyć obciążenie w czasie. Dlatego też, w zależności od liczby baz danych skonfigurowanych do autozmniejszania w danym wystąpieniu programu SQL Server, od osiągnięcia przez bazę zadanego progu zmniejszania do faktycznego jej zmniejszenia może minąć kilka godzin.

Materiały referencyjne

Aby uzyskać więcej informacji o powiększaniu i zmniejszaniu plików bazy danych i dziennika, kliknij następujące numery artykułów w celu wyświetlenia tych artykułów z bazy wiedzy Microsoft Knowledge Base:
256650 Zmniejszanie dziennika transakcji programu SQL Server 7.0
272318 Zmniejszanie dziennika transakcji w programie SQL Server 2000 za pomocą instrukcji DBCC SHRINKFILE
317375 Nieoczekiwane zwiększenie rozmiaru lub przepełnienie dziennika transakcji na komputerze z programem SQL Server
247751 USTERKA: Plan konserwacji bazy danych nie powoduje jej zmniejszenia
305635 Podczas automatycznego rozszerzania bazy danych upływa limit czasu
949523 Duże opóźnienie replikacji transakcyjnej w programie SQL Server 2005 w przypadku małej wartości właściwości rozmiaru początkowego i właściwości autopowiększania
Aby uzyskać więcej informacji dotyczących inicjowania plików bazy danych, odwiedź następującą witrynę MSDN (Microsoft Developer Network) w sieci Web:
Inicjowanie plików bazy danych
Aby uzyskać więcej informacji dotyczących włączania błyskawicznego inicjowania plików, odwiedź następującą witrynę firmy Microsoft w sieci Web:
Cel i metoda włączania błyskawicznego inicjowania plików
Aby uzyskać więcej informacji o architekturze fizycznej dziennika transakcji, odwiedź następującą witrynę MSDN w sieci Web:
Architektura fizyczna dziennika transakcji

Dokumentacja SQL Server Books Online — tematy: „Architektura fizyczna dziennika transakcji”; „Zmniejszanie dziennika transakcji”

Właściwości

Numer ID artykułu: 315512 - Ostatnia weryfikacja: 17 września 2011 - Weryfikacja: 3.0
Informacje zawarte w tym artykule dotyczą:
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Słowa kluczowe: 
kbsqlmanagementtools kbinfo KB315512

Przekaż opinię

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com