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

Статья
10/20/2023

Применимо к:Управляемый экземпляр SQL Azure

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

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

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

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

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

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

Diagram that demonstrates the size of difference database space concepts in the database quantity table.

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

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

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

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

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

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

Примечание.

Ход сжатия может быть нелинейным, и значение в столбце 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 этот параметр.