Поделиться через

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

  • Статья

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

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

Эта статья относится к управляемому экземпляру 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. Это динамическое административное представление возвращает информацию о текущем объеме пространства, используемом журналом, и указывает, когда требуется очистка журнала транзакций.

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

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

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

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

Внимание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рекомендации

Далее приведены некоторые общие рекомендации по работе с файлами журналов транзакций.

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

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

  • При настройке автоматического увеличения для файлов данных и журналов с помощью параметра FILEGROWTH, может быть предпочтительнее задать его size вместо percentage, чтобы обеспечить более эффективный контроль над коэффициентом роста, так как процент является постоянно растущим объемом.

    • В управляемом экземпляре SQL Azure мгновенная инициализация файлов может улучшить увеличение журнала транзакций до 64 МБ. Размер автоматического увеличения по умолчанию для новых баз данных составляет 64 МБ. События автоматического увеличения файла журнала транзакций, превышающие 64 МБ, не могут воспользоваться мгновенной инициализацией файла.
    • Рекомендуется не устанавливать для журналов транзакций значение параметра FILEGROWTH выше 1024 МБ.

  • Небольшое автоматическое увеличение может создать слишком много небольших VLFs и снизить производительность. Чтобы определить оптимальное распределение VLF для текущего размера журнала транзакций всех баз данных в данном экземпляре и необходимые шаги увеличения для достижения требуемого размера, ознакомьтесь с этим сценарием для анализа и исправления VLF, предоставленным командой SQL Tiger.

  • Значительное автоматическое увеличение прироста может привести к двум проблемам:

    • Большое автоматическое увеличение может привести к приостановке работы базы данных при выделении нового пространства, что может вызвать истечение времени ожидания запроса.
    • Большое автоматическое приращение может привести к тому, что VLFs будет слишком мало и они будут слишком большими, а также может повлиять на производительность. Чтобы определить оптимальное распределение VLF для текущего размера журнала транзакций всех баз данных в данном экземпляре и необходимые приросты для достижения требуемого размера, см. этот скрипт для анализа и исправления VLF, предоставленный командой SQL Tiger.

  • Даже если включено автоматическое увеличение, вы можете получить сообщение, что журнал транзакций заполнен, если его размер не может достаточно быстро увеличиваться под нужды вашего запроса. Дополнительные сведения об изменении прироста см. раздел ALTER DATABASE (Transact-SQL) File and Filegroup options.

  • Вы можете настроить автоматическое сжатие файлов журналов. Но делать это не рекомендуется, и параметру базы данных auto_shrink по умолчанию задано значение FALSE. Если параметру auto_shrink задано значение TRUE, автоматическое сжатие уменьшает размер файла, только если в нем не использовано более 25 % объема.

Связанный контент