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

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

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

Эта статья относится к Управляемый экземпляр SQL Azure. Хотя и очень похоже, сведения об управлении размером файлов журнала транзакций в 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, измеряют только размер используемых страниц данных. Примеры см. в разделе Get-metrics PowerShell.

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

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

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

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

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

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

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

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

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

• DBCC SHRINKFILE (Transact-SQL)

• Сжатие файла

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

• Log File Auto Shrink Event Class.

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

• sys.dm_db_log_space_usage (Transact-SQL)

• sys.database_files (Transact-SQL) (смsizemax_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');

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

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

В Управляемый экземпляр 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 для текущего размера журнала транзакций всех баз данных в данном экземпляре, и необходимый рост увеличивается для достижения требуемого размера, см. этот сценарий для анализа и исправления VLFs, предоставляемых командой SQL Tiger.

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

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

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

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

  • Файл будет сжат либо до размера, в котором 25 % пространства не используется, либо до исходного размера, каким бы большим он ни был.
  • Сведения об изменении параметра свойства auto_shrink см. в разделе "Просмотр" или "Изменение свойств базы данных" и параметров ALTER DATABASE SET (Transact-SQL).

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

• Автоматическое резервное копирование в Управляемый экземпляр SQL Azure
• Параметры инструкции ALTER DATABASE для файлов и файловых групп (Transact-SQL)
• Общие сведения об ограничениях ресурсов Управляемого экземпляра SQL Azure
• Устранение ошибок журнала транзакций с помощью Управляемый экземпляр SQL Azure