Azure SQL Database의 데이터베이스에 대 한 파일 공간 관리

적용 대상: Azure SQL Database

이 문서에서는 Azure SQL Database의 데이터베이스에 대 한 다양 한 저장소 공간 유형과 할당 된 파일 공간을 명시적으로 관리 해야 하는 경우 수행할 수 있는 단계에 대해 설명 합니다.

참고

이 문서는 Azure SQL Managed Instance에 적용되지 않습니다.

개요

Azure SQL Database를 사용 하는 경우 데이터베이스에 대 한 기본 데이터 파일 할당이 사용 된 데이터 페이지의 용량 보다 클 수 있는 워크 로드 패턴이 있습니다. 이 상태는 사용되는 공간이 증가하고 그 후에 데이터가 삭제되는 경우 발생할 수 있습니다. 이렇게 되는 이유는 데이터가 삭제될 때 할당되어 있는 파일 공간이 자동으로 회수되지 않기 때문입니다.

파일 공간 사용량 모니터링 및 데이터 파일 축소는 다음과 같은 시나리오에서 필요할 수 있습니다.

  • 데이터베이스에 할당된 파일 공간이 풀 최대 크기에 도달하는 경우 탄력적 풀의 데이터 증가를 허용합니다.
  • 단일 데이터베이스 또는 탄력적 풀의 최대 크기 감소를 허용합니다.
  • 단일 데이터베이스 또는 탄력적 풀을 더 작은 최대 크기의 다른 서비스 계층 또는 성능 계층으로 변경하는 것을 허용합니다.

파일 공간 사용량 모니터링

Azure Portal 및 다음 API에 표시되는 대부분의 스토리지 공간 메트릭은 사용한 데이터 페이지의 크기만 측정합니다.

그러나 다음 API는 데이터베이스 및 탄력적 풀에 할당된 공간의 크기도 측정합니다.

데이터 파일 축소

Azure SQL Database는 데이터베이스 성능에 영향을 줄 수 있으므로 사용 되지 않는 할당 된 공간을 회수할 수 있도록 데이터 파일을 자동으로 축소 하지 않습니다. 그러나 고객은 사용 되지 않은 할당 된 공간 회수에 설명 된 단계에 따라 선택한 시점에 셀프 서비스를 통해 데이터 파일을 축소할 수 있습니다.

참고

데이터 파일과 달리 Azure SQL Database는 데이터베이스 성능에 영향을 주지 않으므로 로그 파일을 자동으로 축소 합니다.

데이터베이스의 스토리지 공간 유형 이해

다음 스토리지 공간 수량을 이해하는 것은 데이터베이스의 파일 공간을 관리하는 데 중요합니다.

데이터베이스 수량 정의 의견
사용된 데이터 공간 8KB 페이지에 데이터베이스 데이터를 저장하는 데 사용된 공간의 크기입니다. 일반적으로 사용된 공간은 삽입(삭제) 시 증가(감소)합니다. 작업 및 조각화와 관련된 데이터의 크기 및 패턴에 따라 삽입 또는 삭제 시 사용된 공간이 변경되지 않는 경우가 있습니다. 예를 들어 모든 데이터 페이지에서 하나의 행을 삭제한다고 해서 사용된 공간이 반드시 감소하지는 않습니다.
할당된 데이터 공간 데이터베이스 데이터 저장에 사용할 수 있는 형식화된 파일 공간의 크기입니다. 할당된 공간의 크기는 자동으로 증가하지만 삭제 후에는 감소하지 않습니다. 이 동작은 공간을 다시 형식화할 필요가 없기 때문에 향후 삽입이 더 빨라질 수 있습니다.
할당되었지만 사용되지 않은 데이터 공간 할당된 데이터 공간의 크기와 사용된 데이터 공간 간의 차이입니다. 이 수량은 데이터베이스 데이터 파일을 축소하면 회수할 수 있는 사용 가능한 공간의 최대 크기를 나타냅니다.
데이터 최대 크기 데이터베이스 데이터 저장에 사용할 수 있는 최대 공간의 크기입니다. 할당된 데이터 공간 크기는 데이터 최대 크기를 초과할 수 없습니다.

다음 다이어그램에서는 데이터베이스에 대한 여러 스토리지 공간 유형 간의 관계를 보여 줍니다.

스토리지 공간 유형 및 관계

스토리지 공간 정보에 대해 단일 데이터베이스 쿼리

다음 쿼리를 사용하여 단일 데이터베이스의 스토리지 공간 수량을 확인할 수 있습니다.

사용된 데이터베이스 데이터 공간

다음 쿼리를 수정하여 사용된 데이터베이스 데이터 공간의 크기를 반환합니다. 쿼리 결과의 단위는 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;

할당된 데이터베이스 데이터 공간 및 사용되지 않은 공간

다음 쿼리를 사용하여 할당된 데이터베이스 데이터 공간 크기 및 할당된 사용되지 않은 공간 크기를 반환합니다. 쿼리 결과의 단위는 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';

데이터베이스 데이터 최대 크기

다음 쿼리를 수정하여 데이터베이스 데이터 최대 크기를 반환합니다. 쿼리 결과의 단위는 바이트입니다.

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

탄력적 풀을 위한 스토리지 공간 유형 이해

다음 스토리지 공간 수량을 이해하는 것은 탄력적 풀의 파일 공간을 관리하는 데 중요합니다.

탄력적 풀 수량 정의 의견
사용된 데이터 공간 탄력적 풀에서 모든 데이터베이스에 사용되는 데이터 공간의 합계입니다.
할당된 데이터 공간 탄력적 풀에서 모든 데이터베이스에 할당된 데이터 공간의 합계입니다.
할당되었지만 사용되지 않은 데이터 공간 탄력적 풀에서 모든 데이터베이스에 할당된 데이터 공간의 크기와 사용된 데이터 공간 간의 차이입니다. 이 수량은 데이터베이스 데이터 파일을 축소하면 회수할 수 있는 탄력적 풀에 대해 할당된 공간의 최대 크기를 나타냅니다.
데이터 최대 크기 해당 데이터베이스 모두에 대해 탄력적 풀에서 사용할 수 있는 최대 데이터 공간의 크기입니다. 탄력적 풀에 할당된 공간은 탄력적 풀 최대 크기를 초과할 수 없습니다. 이 상태가 발생하면 데이터베이스 데이터 파일을 축소하여 사용되지 않은 할당된 공간을 회수할 수 있습니다.

참고

"탄력적 풀이 저장소 용량 한도에 도달 했습니다." 오류 메시지는 데이터베이스 개체가 탄력적 풀 저장소 용량 한도를 충족 하기에 충분 한 공간을 할당 했지만 데이터 공간 할당에 사용 되지 않은 공간이 있을 수 있음을 나타냅니다. 탄력적 풀의 저장소 한도를 늘리거나 단기 솔루션으로 아래에서 사용 되지 않은 할당 된 공간 회수 섹션을 사용 하 여 데이터 공간을 확보 하는 것이 좋습니다. 또한 데이터베이스 파일 축소의 잠재적 부정적 성능 영향에 대해 알고 있어야 합니다. 아래의 인덱스 다시 작성 섹션을 참조 하십시오.

스토리지 공간 정보를 탄력적 풀에 쿼리

탄력적 풀에 대한 스토리지 공간 수량을 확인하려면 다음 쿼리를 사용할 수 있습니다.

사용되는 탄력적 풀 데이터 공간

다음 쿼리를 수정하여 사용된 탄력적 풀 데이터 공간의 크기를 반환합니다. 쿼리 결과의 단위는 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;

할당된 탄력적 풀 데이터 공간 및 사용되지 않은 공간

다음 예를 수정 하 여 탄력적 풀의 각 데이터베이스에 할당 된 공간 및 사용 되지 않은 할당 된 공간을 나열 하는 테이블을 반환 합니다. 테이블에는 데이터베이스가 사용되지 않은 할당된 공간이 가장 큰 것에서 사용되지 않은 할당된 공간이 가장 작은 순서로 정렬됩니다. 쿼리 결과의 단위는 MB입니다.

풀의 각 데이터베이스에 할당된 공간을 확인하는 쿼리 결과를 함께 추가하여 탄력적 풀에 대한 할당된 총 공간을 확인할 수 있습니다. 할당된 탄력적 풀 공간은 탄력적 풀 최대 크기를 초과할 수 없습니다.

중요

PowerShell Azure Resource Manager 모듈은 여전히 Azure SQL Database에서 지원되지만 향후의 모든 개발은 Az.Sql 모듈을 위한 것입니다. AzureRM 모듈은 적어도 2020년 12월까지 버그 수정을 계속 수신할 예정입니다. Az 모듈 및 AzureRm 모듈의 명령에 대한 인수는 실질적으로 동일합니다. 호환성에 대한 자세한 내용은 새로운 Azure PowerShell Az 모듈 소개를 참조하세요.

PowerShell 스크립트를 사용하려면 SQL Server PowerShell 모듈이 필요합니다. 설치하려면 PowerShell 모듈 다운로드를 참조하세요.

$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

다음 스크린샷은 스크립트 출력의 예입니다.

탄력적 풀에 할당된 공간 및 사용되지 않는 할당된 공간 예

탄력적 풀 데이터 최대 크기

다음 T-sql 쿼리를 수정 하 여 마지막으로 기록 된 탄력적 풀 데이터 최대 크기를 반환 합니다. 쿼리 결과의 단위는 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;

사용되지 않는 할당된 공간 회수

참고

축소 명령은 실행 되는 동안 데이터베이스 성능에 영향을 줍니다. 사용 가능한 경우 사용량이 적은 기간 동안에는 실행 해야 합니다.

DBCC 축소

사용되지 않은 할당된 공간을 회수하기 위해 데이터베이스를 확인하고 나면, 다음 명령에서 데이터베이스 이름을 수정하여 각 데이터베이스에 대한 데이터 파일을 축소합니다.

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

축소 명령은 실행 되는 동안 데이터베이스 성능에 영향을 줍니다. 사용 가능한 경우 사용량이 적은 기간 동안에는 실행 해야 합니다.

또한 데이터베이스 파일 축소의 잠재적 부정적 성능 영향에 대해 알고 있어야 합니다. 아래의 인덱스 다시 작성 섹션을 참조 하십시오.

이 명령에 대한 자세한 내용은 SHRINKDATABASE를 참조하세요.

자동 축소

또는 데이터베이스에 대한 자동 축소를 사용하도록 설정할 수 있습니다. 자동 축소는 파일 관리의 복잡도를 줄여 주며 데이터베이스 성능에 대한 영향이 SHRINKDATABASE 또는 SHRINKFILE보다 더 적습니다. 자동 축소는 많은 데이터베이스에서 탄력적 풀을 관리하는 데 특히 유용할 수 있습니다. 그러나 자동 축소의 파일 공간 회수 효과는 SHRINKDATABASESHRINKFILE보다 떨어질 수 있습니다. 기본적으로 대부분의 데이터베이스에는 자동 축소를 사용 하지 않도록 설정 되어 있습니다. 자세한 내용은 AUTO_SHRINK 고려 사항을 참조 하세요.

자동 축소를 사용하도록 설정하려면 다음 명령에서 데이터베이스의 이름을 수정합니다.

-- Enable auto-shrink for the database.
ALTER DATABASE [db1] SET AUTO_SHRINK ON;

이 명령에 대한 자세한 내용은 DATABASE SET 옵션을 참조하세요.

인덱스 다시 작성

데이터베이스 데이터 파일이 축소된 후에는 인덱스가 조각화되어 성능 최적화 효과가 상실될 수 있습니다. 성능 저하가 발생하는 경우 데이터베이스 인덱스를 다시 작성하는 것을 고려합니다. 조각화 및 인덱스 다시 작성에 대한 자세한 내용은 인덱스 재구성 및 다시 작성을 참조하세요.

다음 단계