Zarządzanie miejscem na pliki dla baz danych w usłudze Azure SQL Managed Instance

Dotyczy:Azure SQL Managed Instance

W tym artykule wyjaśniono, jak monitorować pliki w bazach danych i zarządzać nimi w usłudze Azure SQL Managed Instance. Obejmuje on monitorowanie rozmiaru pliku bazy danych, zmniejszanie dziennika transakcji, powiększanie pliku dziennika transakcji i kontrolowanie wzrostu pliku dziennika transakcji.

Ten artykuł dotyczy usługi Azure SQL Managed Instance. Aby uzyskać informacje na temat zarządzania rozmiarem plików dziennika transakcji w programie SQL Server, zobacz Zarządzanie rozmiarem pliku dziennika transakcji.

Omówienie typów miejsca do magazynowania dla bazy danych

Zrozumienie następujących ilości miejsca do magazynowania jest ważne w przypadku zarządzania przestrzenią plików bazy danych.

Liczba baz danych Definicja Komentarze
Używane miejsce danych Ilość miejsca używanego do przechowywania danych bazy danych. Ogólnie rzecz biorąc, przestrzeń używana zwiększa się (zmniejsza się) podczas wstawiania (usuwania). W niektórych przypadkach wykorzystywana przestrzeń nie zmienia się podczas operacji wstawiania lub usuwania, w zależności od ilości i układu danych objętych operacją oraz fragmentacji. Na przykład usunięcie jednego wiersza z każdej strony danych niekoniecznie zmniejsza ilość używanego miejsca.
Przydzielone miejsce na dane Ilość miejsca na sformatowane pliki udostępnionego na potrzeby przechowywania danych bazy danych. Ilość przydzielonego miejsca zwiększa się automatycznie, ale nigdy nie zmniejsza się po usunięciach. Takie zachowanie sprawia, że przyszłe operacje wstawiania są szybsze, ponieważ nie trzeba formatować przestrzeni na nowo.
Przydzielone miejsce na dane, ale nieużywane Różnica między ilością przydzielonego miejsca na dane a używanym miejscem na dane. Ta wielkość odzwierciedla maksymalną ilość wolnego miejsca, którą można odzyskać, zmniejszając pliki danych bazy danych.
Maksymalny rozmiar danych Maksymalna ilość miejsca, która może być używana do przechowywania danych bazy danych. Przydzielona ilość przydzielonego miejsca na dane nie może przekroczyć maksymalnego rozmiaru danych.

Na poniższym diagramie przedstawiono relację między różnymi typami miejsca do magazynowania dla bazy danych.

Diagram przedstawiający rozmiar różnicowych pojęć dotyczących przestrzeni bazy danych w tabeli ilości bazy danych.

Wykonywanie zapytań względem pojedynczej bazy danych pod kątem informacji o przestrzeni plików

Użyj następującego zapytania w sys.database_files , aby zwrócić ilość przydzielonego miejsca na plik bazy danych i ilość przydzielonego nieużywanego miejsca. Jednostki wyniku zapytania są podawane w MB.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Monitorowanie użycia miejsca w dzienniku

Monitorowanie użycia miejsca w dzienniku przy użyciu sys.dm_db_log_space_usage. Ten DMV zwraca informacje o wskaźniku aktualnie używanego miejsca w dzienniku i wskazuje, kiedy dziennik transakcji wymaga skrócenia.

Aby uzyskać informacje o bieżącym rozmiarze pliku dziennika, jego maksymalnym rozmiarze oraz opcji automatycznego zwiększania rozmiaru tego pliku, użyj kolumn size, max_size i growth dla tego pliku dziennika w sys.database_files.

Metryki miejsca do magazynowania wyświetlane w interfejsach API metryk opartych na usłudze Azure Resource Manager mierzą tylko rozmiar używanych stron danych. Przykłady można znaleźć w artykule programu PowerShell Get-AZMetric.

Zmniejsz rozmiar pliku dziennika

Aby zmniejszyć rozmiar fizyczny pliku dziennika fizycznego, usuwając nieużywane miejsce, zmniejsz plik dziennika. Zmniejszanie ma znaczenie tylko wtedy, gdy plik dziennika transakcji zawiera nieużywane miejsce. Jeśli plik dziennika jest pełny, prawdopodobnie z powodu otwartych transakcji, zbadaj , co uniemożliwia obcinanie dziennika transakcji.

Uwaga

Operacji zmniejszania nie należy uważać za rutynową czynność konserwacyjną. Pliki danych i dzienników, które rosną z powodu regularnych, cyklicznych operacji biznesowych nie wymagają operacji zmniejszania. Polecenia zmniejszania wpływają na wydajność bazy danych podczas działania i jeśli to możliwe, powinny być uruchamiane w okresach niskiego użycia. Zmniejszanie plików danych nie jest zalecane, jeśli zwykłe obciążenie aplikacji powoduje ponowne zwiększenie rozmiaru plików do tego samego przydzielonego rozmiaru.

Należy pamiętać o potencjalnym negatywnym wpływie wydajności na zmniejszanie plików bazy danych. Aby uzyskać więcej informacji, zobacz obsługa indeksu po zmniejszeniu rozmiaru. W rzadkich sytuacjach automatyczne kopie zapasowe bazy danych mogą wpływać na operacje zmniejszania bazy danych. W razie potrzeby spróbuj ponownie wykonać operację zmniejszania.

Przed zmniejszeniem dziennika transakcji należy pamiętać o czynnikach, które mogą opóźnić obcinanie dziennika transakcji. Jeśli po zmniejszeniu pliku dziennika miejsce na dysku będzie ponownie potrzebne, plik dziennika transakcji ponownie się powiększy, powodując spadek wydajności podczas operacji zwiększania pliku dziennika. Aby uzyskać więcej informacji, zobacz sekcję rekomendacji .

Można zmniejszyć plik dziennika tylko wtedy, gdy baza danych jest w trybie online, a co najmniej jeden plik dziennika wirtualnego (VLF) jest bezpłatny. W niektórych przypadkach zmniejszenie dziennika może nie być możliwe do czasu następnego obcięcia dziennika.

Czynniki, takie jak długotrwała transakcja, mogą utrzymywać VLF-y aktywne przez dłuższy czas, mogą ograniczać zmniejszanie rozmiaru dzienników, a nawet całkowicie zapobiegać zmniejszaniu rozmiaru dziennika. Aby uzyskać informacje, zobacz Czynniki, które mogą opóźnić usuwanie zapisów dziennika.

Zmniejszanie pliku dziennika usuwa co najmniej jeden plik VFS , który nie zawiera żadnej części dziennika logicznego (czyli nieaktywnych plików VFS). Po zmniejszeniu pliku dziennika transakcji, nieaktywne pliki VLF są usuwane z końca pliku dziennika, aby zmniejszyć jego rozmiar do rozmiaru docelowego.

Aby uzyskać więcej informacji na temat operacji zmniejszania, zapoznaj się z następującą dokumentacją:

Zmniejszanie pliku dziennika (bez zmniejszania plików bazy danych)

Monitorowanie zdarzeń zmniejszania pliku dziennika

  • Klasa zdarzeń automatycznego zmniejszania pliku dziennika.

Monitorowanie przestrzeni logu

Konserwacja indeksu po zmniejszeniu

Po zakończeniu operacji zmniejszania względem plików danych indeksy mogą zostać pofragmentowane. Fragmentacja zmniejsza efektywność optymalizacji wydajności indeksu dla niektórych obciążeń, takich jak zapytania korzystające z dużych skanów. Jeśli spadek wydajności wystąpi po zakończeniu operacji zmniejszania, rozważ konserwację indeksu w celu ponownego skompilowania indeksów. Należy pamiętać, że ponowne kompilowanie indeksu wymaga wolnego miejsca w bazie danych, dlatego może spowodować zwiększenie przydzielonego miejsca, przeciwdziałając efektowi zmniejszania.

Aby uzyskać więcej informacji na temat konserwacji indeksu, zobacz Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów.

Ocena gęstości stron indeksu

Jeśli obcięcie plików danych nie spowoduje wystarczającego zmniejszenia przydzielonego miejsca, możesz zdecydować się na zmniejszenie plików danych bazy danych w celu odzyskania nieużywanego miejsca z tych plików. Jednak jako opcjonalny, ale zalecany krok należy najpierw określić średnią gęstość stron dla indeksów w bazie danych. Przy tej samej ilości danych operacja zmniejszania kończy się szybciej, jeśli gęstość stron jest wysoka, ponieważ przenosi mniej stron. Jeśli gęstość stron jest niska dla niektórych indeksów, rozważ przeprowadzenie konserwacji tych indeksów w celu zwiększenia gęstości stron przed zmniejszeniem plików danych. Ten krok umożliwia funkcji shrink dalsze zmniejszenie przydzielonej przestrzeni dyskowej.

Aby określić gęstość stron dla wszystkich indeksów w bazie danych, użyj następującego zapytania. Gęstość strony jest zgłaszana w kolumnie avg_page_space_used_in_percent .

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Jeśli istnieją indeksy o dużej liczbie stron o gęstości mniejszej niż 60–70%, rozważ ponowne skompilowanie lub reorganizację tych indeksów przed zmniejszeniem plików danych.

Uwaga

W przypadku większych baz danych zapytanie określające gęstość stron może zająć dużo czasu (godzin). Ponadto ponowne kompilowanie lub reorganizacja dużych indeksów wymaga również znacznego czasu i użycia zasobów. Istnieje kompromis między poświęcaniem dodatkowego czasu na zwiększenie gęstości stron z jednej strony, a zmniejszenie czasu trwania i osiągnięcie wyższych oszczędności miejsca na innym.

Jeśli istnieje wiele indeksów o niskiej gęstości stron, możesz je ponownie skompilować równolegle w wielu sesjach bazy danych, aby przyspieszyć proces. Upewnij się jednak, że nie zbliżasz się do limitów zasobów bazy danych, i pozostaw wystarczającą ilość zasobów dla obciążeń aplikacji. Monitoruj użycie zasobów (CPU, operacje we/wy danych, operacje we/wy dziennika) w portalu Azure lub za pomocą widoku sys.dm_db_resource_stats. Uruchom dalsze równoległe ponowne kompilowanie tylko wtedy, gdy wykorzystanie zasobów w każdym z tych wymiarów pozostaje znacznie niższe niż 100%. Jeśli użycie procesora, wejścia/wyjścia danych lub wejścia/wyjścia dziennika wynosi 100%, możesz skalować bazę danych w pionie, aby udostępnić więcej rdzeni procesora i zwiększyć przepływność operacji we/wy, co pozwoli szybciej ukończyć proces dzięki większej liczbie równoległych przebudów.

Przykładowe polecenie ponownego kompilowania indeksu

Poniżej przedstawiono przykładowe polecenie służące do ponownego kompilowania indeksu i zwiększania gęstości strony przy użyciu instrukcji ALTER INDEX :

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8,
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)),
RESUMABLE = ON);

To polecenie inicjuje odbudowę indeksu w trybie online i z możliwością wznawiania. Ten typ przebudowy umożliwia współbieżnym obciążeniom roboczym dalsze korzystanie z tabeli podczas trwania przebudowy oraz pozwala wznowić przebudowę, jeśli zostanie ona z jakiegokolwiek powodu przerwana. Jednak ten typ odbudowy jest wolniejszy niż ponowne kompilowanie w trybie offline, co blokuje dostęp do tabeli. Jeśli żadne inne obciążenia nie muszą uzyskiwać dostępu do tabeli podczas ponownej kompilacji, ustaw opcje ONLINE i RESUMABLE na OFF i usuń klauzulę WAIT_AT_LOW_PRIORITY.

Aby dowiedzieć się więcej na temat konserwacji indeksu, zobacz Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów.

Zmniejszanie wielu plików danych

Jak wspomniano wcześniej, zmniejszanie ruchu danych jest długotrwałym procesem. Jeśli baza danych zawiera wiele plików danych, możesz przyspieszyć proces przez równoległe zmniejszanie wielu plików danych. Tę operację wykonuje się przez otwarcie wielu sesji bazy danych i użycie DBCC SHRINKFILE w każdej sesji z inną wartością file_id. Podobnie jak w przypadku wcześniejszego odtwarzania indeksów, przed rozpoczęciem każdego nowego polecenia zmniejszania równoległego upewnij się, że masz dostateczne zasoby (CPU, operacje wejścia/wyjścia danych, operacje wejścia/wyjścia dziennika).

Następujące przykładowe polecenie zmniejsza plik danych z wartością file_id 4, próbując zmniejszyć przydzielony rozmiar do 52 000 MB, przenosząc strony w pliku:

DBCC SHRINKFILE (4, 52000);

Jeśli chcesz zmniejszyć przydzielone miejsce dla pliku do minimum możliwe, wykonaj instrukcję bez określania rozmiaru docelowego:

DBCC SHRINKFILE (4);

Jeśli obciążenie jest uruchomione współbieżnie z zmniejszeniem, może zacząć używać miejsca do magazynowania zwolnionego przez zmniejszenie przed zakończeniem zmniejszania i obcięciem pliku. W takim przypadku operacja zmniejszania nie może zmniejszyć przydzielonego miejsca do określonego rozmiaru docelowego.

Ten problem można rozwiązać, zmniejszając każdy plik w mniejszych krokach. Oznacza to, że w poleceniu DBCC SHRINKFILE należy ustawić element docelowy, który jest nieco mniejszy niż bieżące przydzielone miejsce dla pliku. Jeśli na przykład przydzielone miejsce dla pliku z file_id 4 wynosi 200 000 MB i chcesz zmniejszyć go do 100 000 MB, możesz najpierw ustawić docelowy rozmiar 170 000 MB:

DBCC SHRINKFILE (4, 170000);

Po zakończeniu tego polecenia obcina plik i zmniejsza przydzielony rozmiar do 170 000 MB. Następnie można powtórzyć to polecenie, ustawiając element docelowy najpierw na 140 000 MB, a następnie do 110 000 MB itd., aż plik zostanie przesunięty do żądanego rozmiaru. Jeśli polecenie zostanie wykonane, ale plik nie zostanie skrócony, użyj mniejszych wartości, na przykład 15 000 MB zamiast 30 000 MB.

Aby monitorować postęp zmniejszania dla wszystkich współbieżnie uruchomionych sesji zmniejszania, można użyć następującego zapytania:

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

Uwaga

Postęp zmniejszania może być nieliniowy, a wartość w percent_complete kolumnie może pozostać niezmieniona przez długi czas, mimo że zmniejszanie jest nadal w toku.

Po zakończeniu zmniejszania wszystkich plików danych użyj zapytania dotyczącego użycia miejsca, aby określić wynikowe zmniejszenie przydzielonego rozmiaru magazynu. Jeśli nadal istnieje duża różnica między używanym miejscem a przydzielonym miejscem, można ponownie skompilować indeksy. Ponowne kompilowanie może tymczasowo zwiększyć przydzielone miejsce, jednak ponowne zmniejszanie plików danych po ponownym skompilowaniu indeksów powinno spowodować głębsze zmniejszenie przydzielonego miejsca.

Powiększanie pliku dziennika

W usłudze Azure SQL Managed Instance możesz dodać miejsce do pliku dziennika, powiększając istniejący plik dziennika, jeśli miejsce na dysku zezwala. Dodawanie pliku dziennika do bazy danych nie jest obsługiwane. Jeden plik dziennika transakcji jest wystarczający, chyba że brakuje miejsca w dzienniku, a miejsce na dysku również kończy się na woluminie, który zawiera plik dziennika.

Aby powiększyć plik dziennika, użyj klauzuli MODIFY FILE instrukcji ALTER DATABASE i określ składnię SIZE i MAXSIZE. Aby uzyskać więcej informacji, zobacz ALTER DATABASE (Transact-SQL) File and Filegroup options (Opcje pliku ALTER DATABASE (Transact-SQL) i grupy plików.

Aby uzyskać więcej informacji, zobacz Zalecenia.

Kontrolowanie wzrostu pliku dziennika transakcji

Aby zarządzać rozwojem pliku dziennika transakcji, użyj instrukcji ALTER DATABASE (Transact-SQL) File and Filegroup options (Opcje pliku i grupy plików ). Zwróć uwagę na następujące opcje:

  • SIZE Użyj opcji , aby zmienić bieżący rozmiar pliku w jednostkach KB, MB, GB i TB.
  • Użyj opcji FILEGROWTH, aby zmienić wartość przyrostu. Wartość 0 oznacza, że automatyczny wzrost jest wyłączony i nie jest dozwolone żadne dodatkowe miejsce.
  • MAXSIZE Użyj opcji , aby kontrolować maksymalny rozmiar pliku dziennika w kb, MB, GB i TB jednostek lub ustawić wzrost na UNLIMITED.

Zalecenia

Podczas pracy z plikami dziennika transakcji należy wziąć pod uwagę następujące zalecenia:

  • Ustaw przyrost automatycznego wzrostu (autogrow) dziennika transakcji, skonfigurowany za pomocą opcji FILEGROWTH, na wartość wystarczającą do obsługi obciążenia transakcyjnego. Ustaw odpowiednio duży przyrost rozmiaru pliku dziennika, aby uniknąć częstego zwiększania jego rozmiaru. Rozmiar dziennika transakcji można prawidłowo określić, monitorując ilość miejsca zajętego w dzienniku podczas wykonywania następujących czynności:

    • Czas wymagany do wykonania pełnej kopii zapasowej, ponieważ nie można wykonać kopii zapasowych dziennika, dopóki nie zostanie ukończona.
    • Czas wymagany dla największych operacji konserwacji indeksu.
    • Czas wymagany do wykonania największej partii w bazie danych.

  • Ustaw autogrow dla plików danych i plików dziennika za pomocą opcji FILEGROWTH w size zamiast percentage, aby umożliwić lepszą kontrolę nad współczynnikiem wzrostu, ponieważ wartość procentowa rośnie wraz ze wzrostem rozmiaru pliku.

    • W usłudze Azure SQL Managed Instance, natychmiastowe inicjowanie plików może przynieść korzyści w zakresie wzrostu dziennika transakcji nawet do 64 MB. Domyślny przyrost rozmiaru automatycznego dla nowych baz danych to 64 MB. Zdarzenia automatycznego zwiększania rozmiaru pliku dziennika transakcji powyżej 64 MB nie mogą korzystać z natychmiastowej inicjalizacji pliku.
    • Zgodnie z najlepszymi praktykami nie należy ustawiać wartości opcji FILEGROWTH powyżej 1024 MB dla dzienników transakcji.

  • Unikaj ustawiania małego przyrostu automatycznego, ponieważ może wygenerować zbyt wiele małych plików VF i zmniejszyć wydajność. Aby określić optymalną dystrybucję VLF dla bieżącego rozmiaru dziennika transakcji wszystkich baz danych w danym wystąpieniu oraz wymagane przyrosty wzrostu, aby osiągnąć wymagany rozmiar, zobacz ten skrypt do analizowania i naprawiania VLF, dostarczony przez zespół SQL Tiger Team.

  • Unikaj ustawiania dużego przyrostu automatycznego zwiększania, ponieważ może to spowodować dwa problemy:

    • Baza danych może zostać wstrzymana podczas przydzielania nowego miejsca, co potencjalnie powoduje przekroczenie limitu czasu zapytania.
    • Może on generować zbyt mało i dużych plików VFS , a także wpływać na wydajność. Aby określić optymalną dystrybucję VLF dla bieżącego rozmiaru dziennika transakcji wszystkich baz danych w danym wystąpieniu oraz wymagane przyrosty wzrostu, aby osiągnąć wymagany rozmiar, zobacz ten skrypt do analizowania i naprawiania VLF, dostarczony przez zespół SQL Tiger Team.

  • Nawet przy włączonym autoprzyroście może zostać wyświetlony komunikat, że dziennik transakcji jest pełny, jeśli nie może on zwiększać się wystarczająco szybko, aby spełnić wymagania zapytania. Aby uzyskać więcej informacji na temat zmiany przyrostu, zobacz ALTER DATABASE (Transact-SQL) File and Filegroup options.

  • Możesz ustawić pliki dziennika tak, aby automatycznie zmniejszały się. Jednak ta praktyka nie jest zalecana, a właściwość bazy danych auto_shrink jest domyślnie ustawiona na WARTOŚĆ FALSE. Jeśli ustawisz auto_shrink wartość TRUE, automatyczne zmniejszanie rozmiaru pliku zmniejsza się tylko wtedy, gdy więcej niż 25 procent jego miejsca jest nieużywane.

Powiązana zawartość

  • Last updated on