Zarządzanie przestrzenią plików dla baz danych w Azure SQL Database

dotyczy: Azure SQL Database

W tym artykule opisano różne typy miejsca do magazynowania dla baz danych w programie Azure SQL Database oraz kroki, które można wykonać, gdy przydzielone miejsce na pliki musi być jawnie zarządzane.

Uwaga

Ten artykuł nie dotyczy Azure SQL Managed Instance.

Omówienie

W Azure SQL Database istnieją wzorce obciążenia, w których alokacja bazowych plików danych dla baz danych może być większa niż ilość używanych stron danych. Ten stan może wystąpić, gdy ilość używanego miejsca zwiększy się, a następnie dane zostaną usunięte. Przyczyną jest to, że przydzielone miejsce na pliki nie jest automatycznie odzyskane po usunięciu danych.

Monitorowanie użycia miejsca na pliki i zmniejszania plików danych może być konieczne w następujących scenariuszach:

  • Zezwalanie na wzrost ilości danych w elastycznej puli, gdy miejsce na pliki przydzielone dla jej baz danych osiągnie maksymalny rozmiar dla puli.
  • Zezwalanie na zmniejszenie maksymalnego rozmiaru pojedynczej bazy danych lub elastycznej puli.
  • Zezwalanie na zmianę warstwy usługi lub wydajności dla pojedynczej bazy danych lub elastycznej puli na warstwę obsługującą mniejszy rozmiar maksymalny.

Monitorowanie użycia miejsca na pliki

Większość metryk miejsca do magazynowania wyświetlanych w następujących interfejsach API mierzy tylko rozmiar używanych stron danych:

Jednak następujące interfejsy API mierzą również rozmiar miejsca przydzielonego dla baz danych i pul elastycznych:

Zmniejszanie plików danych

Azure SQL Database nie zmniejsza automatycznie plików danych w celu odzyskania nieużywanego przydzielonego miejsca ze względu na potencjalny wpływ na wydajność bazy danych. Jednak klienci mogą zmniejszać pliki danych za pośrednictwem samoobsługi w momencie ich wyboru, korzystając z kroków opisanych w części Odzyskiwanie nieużywanego przydzielonego miejsca.

Zmniejszanie pliku dziennika transakcji

W przeciwieństwie do plików Azure SQL Database plik dziennika transakcji jest automatycznie zmniejszany, aby uniknąć nadmiernego użycia miejsca, które może prowadzić do błędów związanych z zbyt dużymi ilościami miejsca. Zazwyczaj nie jest konieczne zmniejszanie pliku dziennika transakcji przez klientów.

W warstwach Premium i Krytyczne dla działania firmy usługi, jeśli dziennik transakcji stanie się duży, może znacząco przyczynić się do użycia magazynu lokalnego w celu osiągnięcia maksymalnego limitu magazynu lokalnego. Jeśli użycie magazynu lokalnego jest zbliżone do limitu, klienci mogą zdecydować się na zmniejszenie dziennika transakcji przy użyciu DBCC SHRINKFILE, jak pokazano w poniższym przykładzie. Magazyn lokalny jest zwalniany natychmiast po zakończeniu działania polecenia bez oczekiwania na okresową operację automatycznego zmniejszania.

DBCC SHRINKFILE (2);

Opis 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.

Ilość bazy danych Definicja Komentarze
Używane miejsce na dane Ilość miejsca używanego do przechowywania danych bazy danych. Ogólnie rzecz biorąc, ilość używanego miejsca zwiększa się (zmniejsza) w przypadku wstawiania (usuwania). W niektórych przypadkach używane miejsce nie zmienia się w przypadku wstawiania lub usuwania w zależności od ilości i wzorca danych związanych z operacją i wszelkiej fragmentacji. Na przykład usunięcie jednego wiersza z każdej strony danych nie musi zmniejszać używanego miejsca.
Przydzielone miejsce na dane Ilość dostępnego miejsca na sformatowane pliki do przechowywania danych bazy danych. Ilość przydzielonego miejsca zwiększa się automatycznie, ale nigdy nie zmniejsza się po usunięciu. To zachowanie gwarantuje, że przyszłe wstawienia będą szybsze, ponieważ nie trzeba ponownie sformatować miejsca.
Przydzielone miejsce na dane, ale nieużywane Różnica między przydzieloną ilością przydzielonego miejsca na dane i używanym obszarem danych. Ta ilość reprezentuje maksymalną ilość wolnego miejsca, którą można odzyskać, zmniejszając pliki danych bazy danych.
Maksymalny rozmiar danych Maksymalna ilość miejsca, które może być używane do przechowywania danych bazy danych. 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.

typy i relacje miejsca do magazynowania

Wykonywanie zapytania o informacje o przestrzeni dyskowej w pojedynczej bazie danych

Następujące zapytania mogą służyć do określania ilości miejsca do magazynowania dla pojedynczej bazy danych.

Używane miejsce na dane bazy danych

Zmodyfikuj następujące zapytanie, aby zwrócić ilość używanej przestrzeni danych bazy danych. Jednostki wyniku zapytania są w MB.

-- Connect to master
-- Database data space used in MB
SELECT TOP 1 storage_in_megabytes AS DatabaseDataSpaceUsedInMB
FROM sys.resource_stats
WHERE database_name = 'db1'
ORDER BY end_time DESC;

Przydzielone miejsce na dane bazy danych i nieużywane przydzielone miejsce

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

-- Connect to database
-- Database data space allocated in MB and database data space allocated unused in MB
SELECT SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB,
SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB
FROM sys.database_files
GROUP BY type_desc
HAVING type_desc = 'ROWS';

Maksymalny rozmiar danych bazy danych

Zmodyfikuj następujące zapytanie, aby zwrócić maksymalny rozmiar danych bazy danych. Jednostki wyniku zapytania są w bajtach.

-- Connect to database
-- Database data max size in bytes
SELECT DATABASEPROPERTYEX('db1', 'MaxSizeInBytes') AS DatabaseDataMaxSizeInBytes;

Opis typów miejsca do magazynowania dla puli elastycznej

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

Ilość elastycznej puli Definicja Komentarze
Używane miejsce na dane Suma przestrzeni danych używanej przez wszystkie bazy danych w elastycznej puli.
Przydzielone miejsce na dane Sumowanie przestrzeni danych przydzielonej przez wszystkie bazy danych w elastycznej puli.
Przydzielone miejsce na dane, ale nieużywane Różnica między przydzieloną ilością przydzielonego miejsca na dane a przestrzenią danych używaną przez wszystkie bazy danych w elastycznej puli. Ta ilość reprezentuje maksymalną ilość miejsca przydzielonego dla elastycznej puli, którą można odzyskać, zmniejszając pliki danych bazy danych.
Maksymalny rozmiar danych Maksymalna ilość miejsca na dane, która może być używana przez pulę elastyczną dla wszystkich jej baz danych. Miejsce przydzielone dla puli elastycznej nie powinno przekraczać maksymalnego rozmiaru puli elastycznej. Jeśli wystąpi ten warunek, przydzielone nieużywane miejsce może zostać odzyskane przez zmniejszenie plików danych bazy danych.

Uwaga

Komunikat o błędzie "Pula elastyczna osiągnęła limit magazynu" wskazuje, że obiektom bazy danych przydzielono wystarczająco dużo miejsca, aby spełnić limit magazynu elastycznej puli, ale w alokacji przestrzeni danych może być nieużywane miejsce. Rozważ zwiększenie limitu magazynu elastycznej puli lub jako rozwiązanie krótkoterminowe, co pozwala na oszczędność miejsca na danych za pomocą poniższej sekcji Odzyskiwanie nieużywanego przydzielonego miejsca. Należy również pamiętać o potencjalnym negatywnym wpływie zmniejszania plików bazy danych na wydajność. Zobacz sekcję Ponowne kompilowanie indeksów poniżej.

Wykonywanie zapytań dotyczących informacji o przestrzeni dyskowej w elastycznej puli

Poniższe zapytania mogą służyć do ustalenia ilości miejsca do magazynowania dla elastycznej puli.

Używane miejsce na dane elastycznej puli

Zmodyfikuj następujące zapytanie, aby zwrócić ilość używanej przestrzeni danych elastycznej puli. Jednostki wyniku zapytania są w MB.

-- Connect to master
-- Elastic pool data space used in MB  
SELECT TOP 1 avg_storage_percent / 100.0 * elastic_pool_storage_limit_mb AS ElasticPoolDataSpaceUsedInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

Przydzielone miejsce na dane elastycznej puli i nieużywane przydzielone miejsce

Zmodyfikuj poniższe przykłady, aby zwrócić tabelę zawierającą przydzielone miejsce i nieużywane miejsce dla każdej bazy danych w elastycznej puli. Tabela zawiera zamówienia baz danych z tych baz danych z największą ilością nieużywanego przydzielonego miejsca do najmniejszej ilości nieużywanego przydzielonego miejsca. Jednostki wyniku zapytania są w MB.

Wyniki zapytania służące do określania miejsca przydzielonego dla każdej bazy danych w puli można dodać razem, aby określić całkowitą ilość miejsca przydzielonego dla elastycznej puli. Przydzielone miejsce w puli elastycznej nie powinno przekraczać maksymalnego rozmiaru puli elastycznej.

Ważne

Moduł powershell Azure Resource Manager jest nadal obsługiwany przez Azure SQL Database, ale cały przyszły rozwój jest dla modułu Az.Sql. Moduł AzureRM będzie nadal otrzymywać poprawki błędów do co najmniej grudnia 2020 r. Argumenty poleceń w module Az i modułach AzureRm są zasadniczo identyczne. Aby uzyskać więcej informacji na temat ich zgodności, zobacz Introducing the new Azure PowerShell Az module(Wprowadzenie do nowego modułu Az Azure PowerShell Az).

Skrypt programu PowerShell wymaga SQL Server modułu programu PowerShell — zobacz Pobieranie modułu programu PowerShell do zainstalowania.

$resourceGroupName = "<resourceGroupName>"
$serverName = "<serverName>"
$poolName = "<poolName>"
$userName = "<userName>"
$password = "<password>"

# get list of databases in elastic pool
$databasesInPool = Get-AzSqlElasticPoolDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $serverName -ElasticPoolName $poolName
$databaseStorageMetrics = @()

# for each database in the elastic pool, get space allocated in MB and space allocated unused in MB
foreach ($database in $databasesInPool) {
    $sqlCommand = "SELECT DB_NAME() as DatabaseName, `
    SUM(size/128.0) AS DatabaseDataSpaceAllocatedInMB, `
    SUM(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) AS DatabaseDataSpaceAllocatedUnusedInMB `
    FROM sys.database_files `
    GROUP BY type_desc `
    HAVING type_desc = 'ROWS'"
    $serverFqdn = "tcp:" + $serverName + ".database.windows.net,1433"
    $databaseStorageMetrics = $databaseStorageMetrics + 
        (Invoke-Sqlcmd -ServerInstance $serverFqdn -Database $database.DatabaseName `
            -Username $userName -Password $password -Query $sqlCommand)
}

# display databases in descending order of space allocated unused
Write-Output "`n" "ElasticPoolName: $poolName"
Write-Output $databaseStorageMetrics | Sort -Property DatabaseDataSpaceAllocatedUnusedInMB -Descending | Format-Table

Poniższy zrzut ekranu jest przykładem danych wyjściowych skryptu:

Przykład przydzielonego miejsca w puli elastycznej i nieużywanego przydzielonego miejsca

Maksymalny rozmiar danych elastycznej puli

Zmodyfikuj następujące zapytanie T-SQL, aby zwrócić ostatni zarejestrowany maksymalny rozmiar danych elastycznej puli. Jednostki wyniku zapytania są w MB.

-- Connect to master
-- Elastic pools max size in MB
SELECT TOP 1 elastic_pool_storage_limit_mb AS ElasticPoolMaxSizeInMB
FROM sys.elastic_pool_resource_stats
WHERE elastic_pool_name = 'ep1'
ORDER BY end_time DESC;

Odzyskiwanie nieużywanego przydzielonego miejsca

Uwaga

Polecenia zmniejszania mają wpływ na wydajność bazy danych podczas działania i, jeśli to możliwe, powinny być uruchamiane w okresach niskiego użycia.

DBCC shrink

Po zidentyfikowaniu baz danych w celu odzyskania nieużywanego przydzielonego miejsca zmodyfikuj nazwę bazy danych za pomocą następującego polecenia, aby zmniejszyć rozmiar plików danych dla każdej bazy danych.

-- Shrink database data space allocated.
DBCC SHRINKDATABASE (N'db1');

Polecenia zmniejszania mają wpływ na wydajność bazy danych podczas działania i, jeśli to możliwe, powinny być uruchamiane w okresach niskiego użycia.

Należy również pamiętać o potencjalnym negatywnym wpływie zmniejszania plików bazy danych na wydajność. Zobacz sekcję Ponowne kompilowanie indeksów poniżej.

Aby uzyskać więcej informacji na temat tego polecenia, zobacz SHRINKDATABASE.

Automatyczne zmniejszanie

Dla bazy danych można też włączyć automatyczne zmniejszanie. Automatyczne zmniejszanie zmniejsza złożoność zarządzania plikami i ma mniejszy wpływ na wydajność bazy danych niż SHRINKDATABASE program lub SHRINKFILE . Automatyczne zmniejszanie może być szczególnie przydatne w zarządzaniu elastycznymi pulami z wieloma bazami danych, które znacząco się powiększają i zmniejszają ilość używanego miejsca. Jednak automatyczne zmniejszanie może być mniej efektywne w odzyskaniu miejsca na pliki niż SHRINKDATABASE i SHRINKFILE .

Automatyczne zmniejszanie jest domyślnie wyłączone, co jest zalecane w przypadku większości baz danych. Jeśli konieczne staje się włączenie automatycznego zmniejszania, zaleca się wyłączenie go po osiągnięcia celów zarządzania przestrzenią, zamiast trwałego włączania. Aby uzyskać więcej informacji, zobacz Zagadnienia dotyczące AUTO_SHRINK.

Aby włączyć automatyczne zmniejszanie, wykonaj następujące polecenie w bazie danych (nie w bazie danych master).

-- Enable auto-shrink for the current database.
ALTER DATABASE CURRENT SET AUTO_SHRINK ON;

Aby uzyskać więcej informacji o tym poleceniu, zobacz DATABASE SET options (Opcje ZESTAWU BAZY DANYCH).

Ponowne kompilowanie indeksów

Po zakończeniu fragmentacji plików danych indeksy mogą stać się pofragmentowane i utracić efektywność optymalizacji wydajności. Jeśli wystąpi spadek wydajności, rozważ ponowne skompilowanie indeksów baz danych. Aby uzyskać więcej informacji na temat fragmentacji i konserwacji indeksu, zobacz Optimize index maintenance to improve query performance and reduce resource consumption (Optymalizowanie konserwacji indeksu w celu zwiększenia wydajności zapytań i zmniejszenia zużycia zasobów).

Następne kroki