Управление файловым пространством отдельной базы данных или базы данных в пуле Базы данных SQL AzureManage file space for single and pooled databases in Azure SQL Database

В этой статье описываются различные типы дискового пространства отдельной базы данных или базы данных в пуле Базы данных SQL Azure, а также действия, которые нужно предпринять, если управление файловым пространством, выделенным для баз данных и эластичных пулов, должно осуществляться явным образом.This article describes different types of storage space for single and pooled databases in Azure SQL Database, and steps that can be taken when the file space allocated for databases and elastic pools needs to be explicitly managed.

Примечание

Эта статья не относится к параметру развертывания управляемого экземпляра Базы данных SQL Azure.This article does not apply to the managed instance deployment option in Azure SQL Database.

ОбзорOverview

В отдельной базе данных или базе данных в пуле Базы данных SQL Azure существуют шаблоны рабочей нагрузки, в которых распределение базовых файлов для баз данных может превысить объем используемых страниц данных.With single and pooled databases in Azure SQL Database, there are workload patterns where the allocation of underlying data files for databases can become larger than the amount of used data pages. Это может произойти, когда используемое пространство увеличивается, а данные затем удаляются.This condition can occur when space used increases and data is subsequently deleted. Это объясняется тем, что выделенное файловое пространство автоматически не освобождается.The reason is because file space allocated is not automatically reclaimed when data is deleted.

Мониторинг использования файлового пространства и сжатие файлов базы данных может потребоваться в следующих сценариях:Monitoring file space usage and shrinking data files may be necessary in the following scenarios:

  • предоставление места для роста данных в эластичном пуле, когда файловое пространство, выделенное для баз данных, достигает максимального размера пула;Allow data growth in an elastic pool when the file space allocated for its databases reaches the pool max size.
  • уменьшение максимального размера отдельной базы данных или эластичного пула;Allow decreasing the max size of a single database or elastic pool.
  • изменение уровня служб или производительности отдельной базы данных или эластичного пула с меньшим максимальным размером.Allow changing a single database or elastic pool to a different service tier or performance tier with a lower max size.

Мониторинг использования файлового пространстваMonitoring file space usage

Большинство метрик дискового пространства, отображаемых на портале Azure, и следующие API-интерфейсы измеряют только размер используемых страниц данных:Most storage space metrics displayed in the Azure portal and the following APIs only measure the size of used data pages:

Тем не менее следующие API-интерфейсы также измеряют размер пространства, выделяемого для баз данных и эластичных пулов:However, the following APIs also measure the size of space allocated for databases and elastic pools:

Сжатие файлов данныхShrinking data files

Служба Базы данных SQL не сможет автоматически сжать файлы данных для освобождения неиспользуемого выделенного пространства из-за возможного влияния на производительность базы данных.The SQL Database service does not automatically shrink data files to reclaim unused allocated space due to the potential impact to database performance. Тем не менее клиенты могут сжать файлы данных самостоятельно в любое время, выполнив действия, описанные в разделе Освобождение неиспользуемого выделенного пространства.However, customers may shrink data files via self-service at a time of their choosing by following the steps described in reclaim unused allocated space.

Примечание

В отличие от файлов данных, служба Базы данных SQL автоматически сжимает файлы журналов, так как эта операция не влияет на производительность базы данных.Unlike data files, the SQL Database service automatically shrinks log files since that operation does not impact database performance.

Основные сведения о типах дискового пространства для базы данныхUnderstanding types of storage space for a database

Для управления файловым пространством базы данных важно разобраться со следующими объемами дискового пространства.Understanding the following storage space quantities are important for managing the file space of a database.

Объем пространства базы данныхDatabase quantity ОпределениеDefinition КомментарииComments
Место, занятое даннымиData space used Объем пространства, используемого для хранения данных базы данных, в страницах по 8 КБ.The amount of space used to store database data in 8 KB pages. Как правило, используемое пространство увеличивается (уменьшается) при операциях вставки (удаления).Generally, space used increases (decreases) on inserts (deletes). В некоторых случаях используемое пространство остается неизменным при операциях вставки или удаления в зависимости от объема и шаблона данных, участвующих в операции и фрагментации.In some cases, the space used does not change on inserts or deletes depending on the amount and pattern of data involved in the operation and any fragmentation. Например, удаление одной строки на каждой странице данных не обязательно приведет к уменьшению используемого пространства.For example, deleting one row from every data page does not necessarily decrease the space used.
Выделенное пространство данныхData space allocated Объем форматированного файлового пространства, который стал доступным для хранения данных базы данных.The amount of formatted file space made available for storing database data. Объем выделенного пространства увеличивается автоматически, но никогда не уменьшается после удалений.The amount of space allocated grows automatically, but never decreases after deletes. Такое поведение гарантирует, что будущие вставки выполняются быстрее, так как пространство не нуждается в переформатировании.This behavior ensures that future inserts are faster since space does not need to be reformatted.
Выделенное, но неиспользуемое пространство данныхData space allocated but unused Разница между объемом выделенного и используемого пространства данных.The difference between the amount of data space allocated and data space used. Это количество представляет максимальный объем свободного пространства, которое можно освободить путем сжатия файлов данных базы данных.This quantity represents the maximum amount of free space that can be reclaimed by shrinking database data files.
Максимальный размер данныхData max size Максимальный объем пространства, который можно использовать для хранения данных базы данных.The maximum amount of space that can be used for storing database data. Объем выделенного пространства данных не может превышать максимальный размер данных.The amount of data space allocated cannot grow beyond the data max size.

На следующей схеме показана связь между разными типами дискового пространства для базы данных.The following diagram illustrates the relationship between the different types of storage space for a database.

типы дискового пространства и их связи

Запрос отдельной базы данных для получения сведений о дисковом пространствеQuery a single database for storage space information

Следующие запросы можно использовать для определения объема дискового пространства для отдельной базы данных.The following queries can be used to determine storage space quantities for a single database.

Место, занятое данными базы данныхDatabase data space used

Измените следующий запрос, чтобы получить объем места, занятого данными базы данных.Modify the following query to return the amount of database data space used. Единицы результатов запроса указываются в МБ.Units of the query result are in 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

Выделенное пространство данных базы данных и неиспользуемое выделенное пространствоDatabase data space allocated and unused allocated space

Используйте следующий запрос, чтобы получить объем выделенного пространства данных базы данных и неиспользуемого выделенного пространства.Use the following query to return the amount of database data space allocated and the amount of unused space allocated. Единицы результатов запроса указываются в МБ.Units of the query result are in 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'

Максимальный размер данных базы данныхDatabase data max size

Измените следующий запрос, чтобы получить максимальный размер данных базы данных.Modify the following query to return the database data max size. Единицы результатов запроса указываются в байтах.Units of the query result are in bytes.

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

Основные сведения о типах дискового пространства для эластичного пулаUnderstanding types of storage space for an elastic pool

Для управления файловым пространством эластичного пула важно разобраться со следующими объемами дискового пространства.Understanding the following storage space quantities are important for managing the file space of an elastic pool.

Объем пространства эластичных пуловElastic pool quantity ОпределениеDefinition КомментарииComments
Место, занятое даннымиData space used Общий объем пространства данных, используемого всеми базами данных в эластичном пуле.The summation of data space used by all databases in the elastic pool.
Выделенное пространство данныхData space allocated Общий объем пространства данных, выделенного всеми базами данных в эластичном пуле.The summation of data space allocated by all databases in the elastic pool.
Выделенное, но неиспользуемое пространство данныхData space allocated but unused Разница между объемом выделенного пространства данных и пространства данных, используемого всеми базами данных в эластичном пуле.The difference between the amount of data space allocated and data space used by all databases in the elastic pool. Это количество представляет максимальный объем выделенного для эластичного пула пространства, которое можно освободить путем сжатия файлов данных базы данных.This quantity represents the maximum amount of space allocated for the elastic pool that can be reclaimed by shrinking database data files.
Максимальный размер данныхData max size Максимальный объем пространства данных, который эластичный пул может использовать для всех своих баз данных.The maximum amount of data space that can be used by the elastic pool for all of its databases. Выделенное для эластичного пула пространство не должно превышать максимальный размер эластичного пула.The space allocated for the elastic pool should not exceed the elastic pool max size. В этом случае выделенное неиспользуемое пространство можно освободить путем сжатия файлов данных базы данных.If this condition occurs, then space allocated that is unused can be reclaimed by shrinking database data files.

Запрос эластичного пула для получения сведений о дисковом пространствеQuery an elastic pool for storage space information

Следующие запросы можно использовать для определения объема дискового пространства для эластичного пула.The following queries can be used to determine storage space quantities for an elastic pool.

Используемое пространство данных эластичного пулаElastic pool data space used

Измените следующий запрос, чтобы получить объем пространства, занятого данными эластичного пула.Modify the following query to return the amount of elastic pool data space used. Единицы результатов запроса указываются в МБ.Units of the query result are in 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

Выделенное пространство данных эластичного пула и неиспользуемое выделенное пространствоElastic pool data space allocated and unused allocated space

Измените следующие примеры, чтобы получить таблицу с перечнем выделенного и неиспользуемого пространства для каждой базы данных в эластичном пуле.Modify the following examples to return a table listing the space allocated and unused allocated space for each database in an elastic pool. Таблица сортирует базы данных по объему неиспользованного выделенного пространства от наибольших к наименьшим.The table orders databases from those databases with the greatest amount of unused allocated space to the least amount of unused allocated space. Единицы результатов запроса указываются в МБ.Units of the query result are in MB.

Результаты запроса для определения выделенного пространства для каждой базы данных в пуле могут суммироваться для представления общего пространства, выделенного для эластичного пула.The query results for determining the space allocated for each database in the pool can be added together to determine the total space allocated for the elastic pool. Выделенное пространство эластичного пула не должно превышать максимальный размер эластичного пула.The elastic pool space allocated should not exceed the elastic pool max size.

Важно!

Модуль PowerShell Azure Resource Manager (RM) по-прежнему поддерживается базой данных SQL Azure, но вся будущая разработка предназначена для модуля AZ. SQL.The PowerShell Azure Resource Manager (RM) module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. Модуль AzureRM продолжит принимать исправления ошибок до 2020 декабря.The AzureRM module will continue to receive bug fixes until at least December 2020. Аргументы для команд в модуле AZ и в модулях AzureRm существенно идентичны.The arguments for the commands in the Az module and in the AzureRm modules are substantially identical. Дополнительные сведения о совместимости см. в разделе Введение в новый модуль Azure PowerShell AZ.For more about their compatibility, see Introducing the new Azure PowerShell Az module.

Сценарию PowerShell требуется модуль SQL Server PowerShell. Дополнительные сведения см. в разделе Установка модуля SQL Server PowerShell.The PowerShell script requires SQL Server PowerShell module – see Download PowerShell module to install.

$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

В качестве примера выходных данных скрипта приведен следующий снимок экрана:The following screenshot is an example of the output of the script:

пример выделенного и неиспользуемого пространства эластичного пула

Максимальный размер данных эластичного пулаElastic pool data max size

Измените следующий запрос T-SQL, чтобы получить максимальный размер данных эластичного пула.Modify the following T-SQL query to return the elastic pool data max size. Единицы результатов запроса указываются в МБ.Units of the query result are in 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

Освобождение неиспользуемого выделенного пространстваReclaim unused allocated space

Примечание

Эта команда может влиять на производительность базы данных во время выполнения, поэтому по возможности ее следует выполнять в периоды низкого уровня использования.This command can impact database performance while it is running, and if possible should be run during periods of low usage.

Сжатие DBCCDBCC shrink

Определив, в каких базах данных необходимо освободить неиспользуемое выделенное пространство, измените имя базы данных в следующей команде, чтобы сжать файлы для каждой базы данных.Once databases have been identified for reclaiming unused allocated space, modify the name of the database in the following command to shrink the data files for each database.

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

Эта команда может влиять на производительность базы данных во время выполнения, поэтому по возможности ее следует выполнять в периоды низкого уровня использования.This command can impact database performance while it is running, and if possible should be run during periods of low usage.

Дополнительные сведения об этой команде см. в статье DBCC SHRINKDATABASE (Transact-SQL).For more information about this command, see SHRINKDATABASE.

Автоматическое сжатиеAuto-shrink

Кроме того, для базы данных можно включить автоматическое сжатие.Alternatively, auto shrink can be enabled for a database. Автоматическое сжатие упрощает управления файлами и оказывает меньшее влияние на производительность базы данных, чем SHRINKDATABASE или SHRINKFILE.Auto shrink reduces file management complexity and is less impactful to database performance than SHRINKDATABASE or SHRINKFILE. Автоматическое сжатие может быть особенно полезным для управления эластичными пулами с множеством баз данных.Auto shrink can be particularly helpful for managing elastic pools with many databases. Однако автоматическое сжатие может быть менее эффективным при восстановлении файлового пространства, чем операции SHRINKDATABASE и SHRINKFILE.However, auto shrink can be less effective in reclaiming file space than SHRINKDATABASE and SHRINKFILE. Чтобы включить автоматическое сжатие, измените имя базы данных в следующей команде.To enable auto shrink, modify the name of the database in the following command.

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

Дополнительные сведения об этой команде см. в статье Параметры ALTER DATABASE SET (Transact-SQL).For more information about this command, see DATABASE SET options.

Перестроение индексовRebuild indexes

После сжатия файлов базы данных индексы могут стать фрагментированными, а эффективность оптимизации их производительности может ухудшиться.After database data files are shrunk, indexes may become fragmented and lose their performance optimization effectiveness. Если происходит замедление, попробуйте перестроить индексы базы данных.If performance degradation occurs, then consider rebuilding database indexes. Дополнительные сведения о фрагментации и перестроении индексов см. в статье Реорганизация и перестроение индексов.For more information on fragmentation and rebuilding indexes, see Reorganize and Rebuild Indexes.

Дополнительная информацияNext steps