Управление пространством файлов для баз данных в Azure SQL Управляемом экземпляре

Область применения: Управляемый экземпляр SQL Azure

В этой статье объясняется, как отслеживать файлы в базах данных в Управляемом экземпляре SQL Azure и управлять ими. В нем описывается мониторинг размера файла базы данных, сжатие журнала транзакций, увеличение файла журнала транзакций и управление ростом файла журнала транзакций.

Эта статья относится к управляемому экземпляру Azure SQL. Сведения об управлении размером файлов журнала транзакций в SQL Server см. в разделе "Управление размером файла журнала транзакций".

Общие сведения о типах дискового пространства для базы данных

Понимание следующих объемов дискового пространства важно для управления файловыми пространствами базы данных.

Объем пространства базы данных	Определение	Комментарии
Место, занятое данными	Объем пространства, используемого для хранения данных базы данных.	Как правило, используемое пространство увеличивается (уменьшается) при операциях вставки (удаления). В некоторых случаях используемое пространство не изменяется при вставке или удалении в зависимости от объема и шаблона данных, участвующих в операции, и любого фрагментации. Например, удаление одной строки на каждой странице данных не обязательно приведет к уменьшению используемого пространства.
Выделенное пространство данных	Объем форматированного файлового пространства, который стал доступным для хранения данных базы данных.	Объем выделенного пространства увеличивается автоматически, но никогда не уменьшается после удалений. Это поведение гарантирует, что будущие вставки быстрее, так как пробелы не должны быть переформатированы.
Выделенное, но неиспользуемое пространство данных	Разница между объемом выделенного и используемого пространства данных.	Это количество представляет максимальный объем свободного пространства, которое можно освободить путем сжатия файлов данных базы данных.
Максимальный размер данных	Максимальный объем пространства, который можно использовать для хранения данных базы данных.	Объем выделенного пространства данных не может превышать максимальный объем данных.

На следующей схеме показана связь между разными типами дискового пространства для базы данных.

Диаграмма, демонстрирующая величину различных концепций пространства базы данных в таблице данных о количестве баз данных.

Запрос одной базы данных для сведений о пространстве файлов

Используйте следующий запрос на sys.database_files , чтобы вернуть объем выделенного места в файле базы данных и объем неиспользуемого пространства. Единицы результатов запроса указываются в МБ.

-- 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;

Мониторинг использования пространства журнала

Для мониторинга используемого пространства журнала используйте sys.dm_db_log_space_usage. Это динамическое административное представление возвращает информацию о текущем объеме пространства, используемом журналом, и указывает, когда требуется очистка журнала транзакций.

Сведения о текущем размере файла журнала, его максимальный размер и параметр автоматического увеличения файла, используйте sizemax_sizegrowth столбцы и столбцы для этого файла журнала в sys.database_files.

Метрики пространства хранилища, отображаемые в API метрик на основе Azure Resource Manager, измеряют только размер используемых страниц данных. Примеры см. в статье PowerShell Get-AZMetric.

Уменьшение размера файла журнала

Чтобы уменьшить физический размер файла физического журнала, удалив неиспользуемое пространство, сжать файл журнала. Сжатие делает разницу только в том случае, если файл журнала транзакций содержит неиспользуемое пространство. Если файл журнала заполнен, скорее всего, из-за открытых транзакций, изучите , что предотвращает усечение журнала транзакций.

Внимание

Операции сжатия не должны считаться регулярной операцией обслуживания. Файлы данных и журналов, которые растут из-за регулярных повторяющихся бизнес-операций, не требуют операций сжатия. Команды сжатия могут повлиять на производительность базы данных во время выполнения, поэтому по возможности их следует выполнять в периоды низкого уровня использования. Сжатие файлов данных не рекомендуется, если обычная рабочая нагрузка приложения приводит к росту файлов до того же выделенного размера снова.

Обратите внимание на потенциальное негативное влияние на производительность сжатия файлов базы данных. Дополнительные сведения см. в разделе "Обслуживание индекса после сжатия". В редких случаях автоматическое резервное копирование базы данных может повлиять на операции сжатия. При необходимости повторите операцию сжатия.

Перед сжатием журнала транзакций следует учитывать факторы, которые могут отложить усечение журнала. Если дисковое пространство требуется снова после сжатия журнала, журнал транзакций снова увеличивается и при этом увеличивает нагрузку на производительность во время операций роста журнала. Дополнительные сведения см. в разделе рекомендаций .

Вы можете сжать файл журнала, только если база данных активна и хотя бы один виртуальный файл журнала (VLF) свободен. В некоторых случаях сжатие журнала может оказаться невозможным до следующего усечения журнала.

Такие факторы, как длительная транзакция, могут хранить VLFs активными в течение длительного периода, могут ограничить сжатие журналов или даже предотвратить сжатие журнала вообще. Дополнительные сведения см. в разделе Факторы, которые могут вызвать задержку усечения журнала.

Сжатие файла журнала удаляет виртуальные файлы журнала, которые не содержат частей логического журнала (то есть, неактивные виртуальные файлы журнала). При сокращении файла журнала транзакций неактивные VLFS удаляются из конца файла журнала, чтобы уменьшить размер журнала до приблизительного целевого размера.

Дополнительные сведения об операциях сжатия см. в следующей документации:

Сжатие файла журнала (без сжатия файлов базы данных)

Мониторинг событий сжатия файла журнала

Класс событий автоматического сжатия файла журнала.

Мониторьте пространство журнала

sys.dm_db_log_space_usage (Transact-SQL)
sys.database_files (Transact-SQL) (Смотрите столбцы size, max_size, и growth для журнала или файлов журнала.)

Обслуживание индекса после сокращения

После завершения операции сжатия в файлах данных индексы могут быть фрагментированы. Фрагментация снижает эффективность оптимизации производительности индекса для определенных рабочих нагрузок, таких как запросы с помощью больших проверок. Если снижение производительности происходит после завершения операции сжатия, рассмотрите возможность обслуживания индекса для перестроения индексов. Имейте в виду, что перестроение индекса требует свободного места в базе данных, поэтому может привести к увеличению выделенного пространства, противодействуя эффекту сжатия.

Дополнительные сведения об обслуживании индексов см. в статье Оптимизация обслуживания индексов для повышения производительности запросов и снижения потребления ресурсов.

Определение плотности страницы индекса

Если усечение файлов данных не приводит к достаточному сокращению выделенного пространства, вы можете решить сократить файлы данных базы данных, чтобы освободить неиспользуемое пространство из этих файлов. Но в качестве необязательного, но рекомендуемого шага сначала следует определить среднюю плотность страниц для индексов в базе данных. Для того же объема данных сжатие завершается быстрее, если плотность страниц высока, так как она перемещает меньше страниц. Если плотность страниц для некоторых индексов низкая, рассмотрите возможность выполнения обслуживания этих индексов, чтобы повысить плотность страниц, прежде чем сжимать файлы данных. Этот шаг позволяет уменьшить объем выделенного места в хранилище.

Чтобы определить плотность страниц для всех индексов в базе данных, используйте следующий запрос. Плотность страницы указывается в столбце 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;

Если есть индексы с большим количеством страниц, плотность страниц которых ниже 60–70 %, рассмотрите возможность перестроения или реорганизации этих индексов перед сжатием файлов данных.

Примечание.

Для больших баз данных запрос для определения плотности страниц может занять много времени (часы). Кроме того, перестроение или реорганизация больших индексов также требует значительных затрат времени и ресурсов. Существует компромисс между тратить дополнительное время на увеличение плотности страницы с одной стороны, и сокращение длительности и достижение более высокой экономии места на другом.

Если есть несколько индексов с низкой плотностью страниц, вы можете перестроить их параллельно на нескольких сеансах базы данных, чтобы ускорить процесс. Однако убедитесь, что вы не приближаетесь к ограничениям ресурсов базы данных, и оставьте достаточное количество ресурсов для рабочих нагрузок приложений. Мониторинг потребления ресурсов (ЦП, операций ввода-вывода данных, операций ввода-вывода журнала) на портале Azure или с помощью представления sys.dm_db_resource_stats . Запуск дальнейшего параллельного перестроения только в том случае, если использование ресурсов для каждого из этих измерений остается значительно ниже 100%. Если загрузка ЦП, операций ввода-вывода данных или операций ввода-вывода журнала составляет 100%, вы можете увеличить масштаб базы данных, чтобы иметь больше ядер ЦП и увеличить пропускную способность операций ввода-вывода, что позволяет более параллельным перестроениям выполнять процесс быстрее.

Пример команды перестроения индекса

Ниже приведен пример команды для перестроения индекса и увеличения плотности страницы с помощью инструкции 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);

Эта команда инициирует возобновляемое перестроение индекса в режиме онлайн. Этот тип перестроения позволяет параллельным рабочим нагрузкам продолжать использовать таблицу, пока выполняется перестроение, и позволяет возобновить перестроение, если он прерывается по какой-либо причине. Но такой тип перестроения медленнее автономного перестроения, которое блокирует доступ к таблице. Если другим рабочим нагрузкам не требуется доступ к таблице во время перестроения, задайте для параметров ONLINE и RESUMABLE значение OFF и удалите предложение WAIT_AT_LOW_PRIORITY.

Сжатие нескольких файлов данных

Как отмечалось ранее, сжатие при перемещении данных — это длительный процесс. Если в базе данных есть несколько файлов данных, вы можете ускорить процесс, сжав несколько файлов данных параллельно. Эта операция выполняется путем открытия нескольких сеансов базы данных и использования DBCC SHRINKFILE для каждого сеанса с другим file_id значением. Как и при перестроении индексов (см. выше), перед запуском каждой новой команды параллельного сжатия убедитесь, что у вас есть достаточный запас ресурсов (процессор, дисковая активность, ввод-вывод журнала).

Следующий пример команды уменьшает файл данных file_id 4, пытаясь уменьшить объем выделенной памяти до 52 000 МБ, перемещая страницы внутри файла.

DBCC SHRINKFILE (4, 52000);

Если вы хотите уменьшить выделенное пространство для файла до минимально возможного, выполните оператор без указания целевого размера.

DBCC SHRINKFILE (4);

Если рабочая нагрузка выполняется одновременно с уменьшением, она может начать использовать место хранения, освобожденное уменьшением, до того как уменьшение завершится и файл будет усечен. В этом случае сжатие не может уменьшить выделенное пространство для указанного целевого объекта.

Эту проблему можно устранить, сжимая каждый файл в меньших шагах. Это означает, что в команде DBCC SHRINKFILE вы задаете целевой объект, который немного меньше текущего выделенного пространства для файла. Например, если выделенное пространство для файла с идентификатором file_id 4 составляет 200 000 МБ, и вы хотите уменьшить его до 100 000 МБ, вы можете сначала задать целевое значение 170 000 МБ.

DBCC SHRINKFILE (4, 170000);

После завершения этой команды он усечен файл и уменьшает размер выделенного файла до 170 000 МБ. После этого можно повторить эту команду, установив целевой объект сначала на 140 000 МБ, а затем до 110 000 МБ и т. д., пока файл не будет сжат до требуемого размера. Если команда завершится, но файл не усечен, используйте меньшие шаги, например 15 000 МБ, а не 30 000 МБ.

Чтобы отслеживать ход сжатия для всех одновременно запущенных сеансов сжатия, вы можете использовать следующий запрос.

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');

Примечание.

Ход сжатия может быть нелинейным, и значение в percent_complete столбце может оставаться неизменным в течение длительного периода времени, даже если сжатие по-прежнему выполняется.

После завершения сжатия для всех файлов данных используйте запрос на использование пространства , чтобы определить результирующий уменьшение размера выделенного хранилища. Если между используемым пространством и выделенным пространством по-прежнему существует большая разница, можно перестроить индексы. Перестроение может временно увеличить выделенное пространство дальше, однако сжатие файлов данных снова после перестроения индексов должно привести к более глубокому сокращению выделенного пространства.

Увеличение файла журнала

В Управляемом экземпляре SQL Azure можно добавить пространство в файл журнала, увеличив существующий файл журнала, если дисковое пространство разрешено. Добавление файла журнала в базу данных не поддерживается. Один файл журнала транзакций достаточно, если не истекает пространство журнала, а дисковое пространство также выполняется на томе, в котором хранится файл журнала.

Чтобы увеличить файл журнала, используйте MODIFY FILE предложение инструкции ALTER DATABASE и укажите SIZE и MAXSIZE синтаксис. Дополнительные сведения см. в разделе ALTER DATABASE (Transact-SQL) File и Filegroup options.

Дополнительные сведения см. в рекомендациях.

Управление увеличением размера файла журнала транзакций

Чтобы управлять ростом файла журнала транзакций, используйте инструкцию alter DATABASE (Transact-SQL) file and Filegroup options . Обратите внимание на следующие параметры:

SIZE Используйте параметр для изменения текущего размера файла в единицах КБ, МБ, ГБ и ТБ.
FILEGROWTH Используйте параметр для изменения увеличения роста. Значение 0 указывает, что автоматический рост выключен и дополнительное пространство не разрешено.
MAXSIZE Используйте параметр для управления максимальным размером файла журнала в КБ, МБ, ГБ и единицах ТБ или для настройки ростаUNLIMITED.

Обратная связь

Были ли сведения на этой странице полезными?

Last updated on 2025-08-26